Appcontainers/MySQL Demo

CentOS 6.8 based Customizable MySQL 5.5 Container – 295 MB – Updated 06/16/2016

This container is built from appcontainers/centos:6
Any of the examples below can pull off of the Quay registry by putting quay.io/ in the front of the namespace/repository

Description:

This container has 2 variants of which are built from appcontainers/centos:6, and appcontainers/debian_jessie respectively. Both base OS’s are slightly modified bare bones of CentOS 6.8, and Debian 8 Jessie Minimal Installations.
The images all reside on both the docker hub as well as the quay registry. If you would prefer to use the images distributed from quay.io, just simply append quay.io to the front of the namespace/repository designation as such in the examples quay.io/appcontainers/centos, and quay.io/appcontainers/debian. Modifications to the minimal installations of the base images can be found by looking at the appcontainers/centos, and appcontainers/debian repositories located below:

Docker Hub: appcontainers/centos
Quay Registry: quay.io/appcontainers/centos
Docker Hub: appcontainers/debian
Quay Registry: quay.io/appcontainers/debian

This containers purpose is to get a customizable MySQL instance up and running with a single docker run statement. The container is built with environment variables that allow the user to plug in custom values which will allow the container to configure itself on first run. This will give the user a fully customized experience just as if you set up MySQL on your own from scratch.

NOTE: The following documentation will reference the latest tag, referring to CentOS, if you would like to run Debian instead, just add the tag :debian to the image name instead of using either the default (latest) or centos.

 

To see a full video demo of this container and all of its options in action visit:

 

Container Variables:

Set Variables: The container is built to allow several configurable variables to be passed in at runtime. The values are as follows:*

  • MYSQL_USER – This is the superuser account that will be allowed to create/alter databases in the instance. Default is root.
  • MYSQL_PASS – This will set the MySQL SuperUser/Root Password.
  • MYSQL_CLIENT – This is the client address space that the connections to the mysql server will be coming from.
  • MYSQL_DB – The name of the MySQL DB that will be created.
  • APP_USER – The username that the Application will use in order to connect to the MySQL DB.
  • APP_PASS – The password of the user that the Application uses to connect to the MySQL DB.
  • CREATEDB – This is a toggle that tells the server to initialize a new database or just allow superuser connections from other servers. (true/false)
  • ENV – Variable to hold the enviroment, currently it serves no other purpose but is there in case its needed
  • TERMTAG – Environment variable to hold the highlighted bash shell tag. Currently set to the repo name, but can be change from within the container via TERMTAG=”Whatever You Want”

Default Values: The configurable variable above are set in the Dockerfile to hold the following default values:*

  • MYSQL_USER = root
  • MYSQL_PASS = PAssw0rd
  • MYSQL_CLIENT = 172.17.%.%
  • MYSQL_DB = testapp
  • APP_USER = testuser
  • APP_PASS = testpass
  • CREATEDB = false
  • ENV = dev
  • TERMTAG = mysql

Setting any of the above values will tell the container to replace the default values already set within the container with the values that are supplied at runtime.

MODE VALUES:

There are 2 startup modes for this server, toggled by the CREATEDB variable. By default CREATEDB is set to false.

  • CREATEDB=false – In this mode, no new database will be configured, instead the values MYSQL_USER, MYSQL_PASS, and MYSQL_CLIENT will be used to set a grant to allow connections from the remote IP(s) specified in MYSQL_CLIENT variable to the MYSQL_USER using the MYSQL_PASS. This will effectively do a:
GRANT ALL PRIVILEGES ON *.* TO 'MYSQL_USER'@'MYSQL_CLIENT' IDENTIFIED BY MYSQL_PASS WITH GRANT OPTION;
  • CREATEDB=true – In this mode, mysql will start up and create a new database specified by the variable MYSQL_DB, it will then construct and run the following grant statement’
GRANT ALL PRIVILEGES ON MYSQL_DB.* TO 'APP_USER'@'MYSQL_CLIENT' IDENTIFIED BY APP_PASS;

Hence the App user, will have full permissions to the app database that was just created and nothing else. No external grants will be set.

Running the Container:

docker run -it -d --name mysql -h mysql -p 3306:3306 appcontainers/mysql

Running the container using default values will dictate that the MySQL root password will be set to ‘PAssword’, it will automatically create a database called ‘testapp’ and it will construct the following grant for the ‘application’ database.

GRANT ALL PRIVILEGES ON testapp.* TO 'testuser'@'%' IDENTIFIED BY 'testpass';

Running the container in standalone mode, with customized variable values:

docker run -d -it \
--name mysql \
-h mysql \
-p 3306:3306 \
-e CREATEDB=true \
-e MYSQL_CLIENT='%' \
-e MYSQL_PASS='Iloveflappyjacks' \
-e MYSQL_DB='testapp' \
-e APP_USER='admin' \
-e APP_PASS='yummy123' \
-e TERMTAG=mysqlawesome \
appcontainers/mysql

This example will result in starting the container with the bash prompt indicating mysqlawesome, and will publish the mysql port on the host, allowing other computers the ability to connect to published port (3389). It will also set the root MySQL password to ‘Iloveflappyjacks’, it will then automatically create a database called ‘testapp’ and will set the following GRANT on the database

GRANT ALL PRIVILEGES ON testapp.* TO 'admin'@'%' IDENTIFIED BY 'yummy123';

NOTE: The above example will create a standalone server without persistent data. This means that if the container is stopped, AND removed, and a new container is started, than all of the data in the databases will be gone, as the database data was only contained in the container that was removed.

Run the container with with a persistent storage container:

Start a data volume container
docker run -it --name dbdata -h dbdata -v /var/lib appcontainers/centos
Start a data volume container
docker run -it --name dbdata -h dbdata -v /var/lib appcontainers/centos
Stop the container (does not need to run) if it is running (verify with docker ps -aq)
docker stop dbdata
Start the mysql container (Connections only allowed from other containers via 172.17 subnet).
docker run -d -it \
--name mysql \
-h mysql \
--volumes-from dbdata \
-e CREATEDB=true \
-e MYSQL_CLIENT='172.17.%.%' \
-e MYSQL_PASS='Iloveflappyjacks' \
-e MYSQL_DB='myawesomeapp' \
-e APP_USER='admin' \
-e APP_PASS='yummy123' \
appcontainers/mysql

This example will launch the container connecting the /var/lib directory on mysql container to /var/lib on the dbdata container. This will allow you to stop start, remove, or restart the mysql container, keeping the data in tact. It will also set the mysql root login password to ‘Iloveflappyjacks’, create a database named ‘myawesomeapp’ and set the following grant on that database:*

GRANT ALL PRIVILEGES ON myawesomeapp.* TO 'admin'@'172.17.%.%' IDENTIFIED BY 'yummy123';

This example will not only allow connections from the 172.17.x.x IP space, but also will NOT allow any computers other than local running containers to access the MySQL port. In order to allow other computers on your network, or other containers, running on another host to access the MySQL port, you will need to add the publish statement to the above run command. -p 3306:3306 will bind the containers port 3306 to the IP of the host running the container via port 3306. Any computer access the docker server IP:3306 will be forwarded by the host directly to the MySQL container allowing access (provided that the proper GRANT is in place).*

Run the container with with CREATEDB=False (Defalut).

docker run -it \
--name mysql \
-h mysql \
--volumes-from dbdata \
-e MYSQL_USER='wpadmin' \ 
-e MYSQL_PASS='testpass' \
-e MYSQL_CLIENT='192.168.1.%' \
appcontainers/mysql

This will run the container, attach to the /var/lib directory of the dbdata persistent volume container, create a user named wpadmin, grant all privileges on all databases to that user and allow that user to issue grants as well, and set that user password to testpass. It will also set the root password to testpass. Connections coming from 192.168.1.0/24 will be allowed to connect with the wpadmin user.

Access the new Install:

Navigate to the IP address of the container on the port specified (3306 default) with MySQL workbench or your db tool of choice to access MySQL IF you include -p 3306:3306 in the docker run line.

Launching the Container via docker-compose:

Environment that will allow an external user/app to connect and create a new database

Copy the text below and paste it into a file named docker-compose.yml. Then you can navigate to the directory and if you have docker-compose installed, just issue the following command in order to launch off the application stack:

docker-compose up -d

__NOTE: If you need assistance setting up docker-compose please visit [http://www.appcontainers.com] (http://www.appcontainers.com/installing-and-deploying-containers-using-docker-compose/) to watch the tutorial or read about the installation process.

Centos:

dbdata:
  image: appcontainers/centos
  hostname: dbdata
  stdin_open: true
  tty: true
  volumes:
  - /var/lib
  environment:
  - TERMTAG=DBDATA
  command: sleep 1
 
db:
  image: appcontainers/mysql:centos
  hostname: db
  stdin_open: true
  tty: true
  restart: always
  volumes_from:
  - dbdata
  environment:
  - ENV=dev
  - TERMTAG=mysql
  - CREATEDB=true
  - MYSQL_PASS=adminpassword123
  - MYSQL_DB=testapp
  - APP_USER=testuser
  - APP_PASS=testpass
  - MYSQL_CLIENT=172.17.%.%

Debian:

dbdata:
  image: appcontainers/debian
  hostname: dbdata
  stdin_open: true
  tty: true
  volumes:
  - /var/lib
  environment:
  - TERMTAG=DBDATA
  command: sleep 1
 
db:
  image: appcontainers/mysql:debian
  hostname: db
  stdin_open: true
  tty: true
  restart: always
  volumes_from:
  - dbdata
  environment:
  - ENV=dev
  - TERMTAG=mysql
  - CREATEDB=true
  - MYSQL_PASS=adminpassword123
  - MYSQL_DB=testapp
  - APP_USER=testuser
  - APP_PASS=testpass
  - MYSQL_CLIENT=172.17.%.%

Note: Using the above docker-compose.yml file will create a user named admin that has ALL PRIVILEGES WITH GRANT to ALL databases on the instance. Both admin and root’s password will be set to “adminpassword123”.

Environment that will create a database and configure user access to it

Copy the text below and paste it into a file named docker-compose.yml. Then you can navigate to the directory and if you have docker-compose installed, just issue the following command in order to launch off the application stack:

docker-compose up -d

centos:

dbdata:
  image: appcontainers/centos
  hostname: dbdata
  stdin_open: true
  tty: true
  volumes:
  - /var/lib
  environment:
  - TERMTAG=DBDATA
  command: sleep 1
 
db:
  image: appcontainers/mysql:centos
  hostname: db
  stdin_open: true
  tty: true
  restart: always
  volumes_from:
  - dbdata
  environment:
  - MYSQL_USER=admin
  - MYSQL_PASS=adminpassword123
  - MYSQL_CLIENT=172.17.%.%
  - ENV=production
  - TERMTAG=MYSQL
  - CREATEDB=false

debian:

dbdata:
  image: appcontainers/debian
  hostname: dbdata
  stdin_open: true
  tty: true
  volumes:
  - /var/lib
  environment:
  - TERMTAG=DBDATA
  command: sleep 1
 
db:
  image: appcontainers/mysql:debian
  hostname: db
  stdin_open: true
  tty: true
  restart: always
  volumes_from:
  - dbdata
  environment:
  - MYSQL_USER=admin
  - MYSQL_PASS=adminpassword123
  - MYSQL_CLIENT=172.17.%.%
  - ENV=production
  - TERMTAG=MYSQL
  - CREATEDB=false

Note: Using the above docker-compose.yml file will spin up a container who’s environment type is DEV (not really used other than for designation), has the Bash prompt set with the title “mysql”, and has created a database named “testapp”, applying the following grant:

GRANT ALL PRIVILEGES ON testapp.* TO 'testuser'@'172.17.%.%' IDENTIFIED BY 'testpass'

The root password on this container will also be set to adminpassword123.

Note: To access the mysql instance once the mysql container starts up, just type ‘mysql -p’ to connect as root and type the password set in the MYSQL_PASS variable, or you can also access the instance using the following syntax:

mysql --user=root --password=$MYSQL_PASS

 

Dockerfile Change-log:

06/16/2016 - Updated to latest OS Versions, Removed Ubuntu tags
12/14/2015 - Added MySQL auto start so the container can run in service mode.
05/07/2015 - Runconfig slight rework to display more init info on run.
05/01/2015 - Remove non EN locales to save around 100MB of space.
04/08/2015 - Added CREATEDB feature, and changed MYSQL_CLIENTADDR to MYSQL_CLIENT. Server can now be set to either create a new database on run or not. Added docker-compose yaml file.

 

Verification

-----------------------------------
Test Standalone Version NO CREATEDB
-----------------------------------
# If you want to launch the container with the ability to attach to it, then run it with -it -d
# and add command: /bin/bash
# This will create a database instance that will allow the defined mysql user to connect and create a db.
docker run -it -d \
--name mysql \
-h mysql \
-p 3306:3306 \
-e ENV=production \
-e TERMTAG=MYSQL \
-e CREATEDB=false \
-e MYSQL_USER=admin \
-e MYSQL_PASS=adminpassword123 \
-e MYSQL_CLIENT=172.17.%.% \
appcontainers/mysql

---------------------------------
Test Standalone Version CREATEDB
---------------------------------
# If you want to launch the container with the ability to attach to it, then run it with -it -d
# and add command: /bin/bash
# This will create a database instance, create a database, and allow the defined user to connect to the database.
docker run -it -d \
--name mysql \
-h mysql \
-p 3306:3306 \
-e ENV=production \
-e TERMTAG=MYSQL \
-e CREATEDB=true \
-e MYSQL_DB=testapp \
-e MYSQL_PASS=adminpassword123 \
-e APP_USER=testuser \
-e APP_PASS=testpass \
-e MYSQL_CLIENT=172.17.%.% \
appcontainers/mysql

-------------------------------------
Test docker-compose front end upgrade
-------------------------------------
# Start the compose stack
docker-compose up -d

# Remove the front end
docker kill mysql_db_1
docker rm mysql_db_1

# restart the stack (This will just re-create the front end, leaving the data layer alone.)
docker-compose up -d

0 comments