MySQL & Dockers...a simple set up

MySQL & Dockers... are not new concepts,  people have been moving to Dockers for some time now.  For someone who is just moving to this for development, it can have a few hurdles.

While MySQL works just fine running locally, if you are testing code across different versions of MySQL it is nice to have several versions easily available.

One option for years has been of course https://mysqlsandbox.net/ by Giuseppe Maxia.  This is a very valid solution to be able to get several instances up and test replication and etc etc.

Dockers are now also another often used scenario when it comes to testing across different versions of MySQL. The following will just go over some of the steps to get several versions installed easily. I use OSX so these examples are for OSX.

You need Docker to start and of course and Docker Desktop is a handy tool for you to be able to get access easily.

• https://docs.docker.com/docker-for-mac/install/
• https://docs.docker.com/docker-for-windows/

Once I had Docker set up I can get my environment ready for MySQL. 

Here I created a Docker folder that contains the MySQL data directories, Config files as well as the mysql-files directory if I needed it. 

mkdir ~/Docker ;

mkdir ~/Docker/mysql_data;

mkdir ~/Docker/mysql-files;

mkdir ~/Docker/cnf;

Now inside mysql_data

cd  ~/Docker/mysql_data;

mkdir 8.0;

mkdir 5.7;

mkdir 5.6;

mkdir 5.5;

Now I set up simple cnf files for this example. The primary thing to note is the bind-address. This is set to ensure it is opened up for us to reach MySQL outside of the docker.  You can also notice that these files can be used to set up additional configuration information as you see fit per MySQL docker instance. 

cd  ~/Docker/cnf;




cat my.8.0.cnf

[mysqld]

pid-file        = /var/run/mysqld/mysqld.pid

socket          = /var/run/mysqld/mysqld.sock

datadir         = /var/lib/mysql

secure-file-priv= /var/lib/mysql-files

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

bind-address = 0.0.0.0

port=3306

server-id=80







# Custom config should go here

!includedir /etc/mysql/conf.d/




 cat my.5.7.cnf

[mysqld]

bind-address = 0.0.0.0

server-id=57

max_allowed_packet=32M




$ cat my.5.6.cnf

[mysqld]

bind-address = 0.0.0.0

server-id=56




$ cat my.5.5.cnf

[mysqld]

bind-address = 0.0.0.0

server-id=55

OK so now that we have configuration files set up, We need to build the dockers. A few things to note for the build commands. 

--name   We set a named reference for the docker. 

Here we are mapping the configuration files, data directory and mysql-files directories to the docker . This allows us to adjust the my.cnf file and etc easily. 

-v ~/Docker/cnf/my.8.0.cnf:/etc/mysql/my.cnf 

-v ~/Docker/mysql_data/8.0:/var/lib/mysql

-v ~/Docker/mysql-files:/var/lib/mysql-files

We want to be able to reach these MySQL instances outside of the docker so we need to publish and map the port accordingly. 

-p  3306:3306  This means 3306 local to 3306 inside docker

-p  3307:3306  This means 3307 local to 3306 inside docker

-p  3308:3306  This means 3308 local to 3306 inside docker

-p  3309:3306  This means 3309 local to 3306 inside docker

Then we also pass a couple of environment variables. 

-e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here>

So putting it all together...

docker run --restart always --name mysql8.0   -v ~/Docker/cnf/my.8.0.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/8.0:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p  3306:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:8.0




docker run --restart always --name mysql5.7   -v ~/Docker/cnf/my.5.7.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.7:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p  3307:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.7




docker run --restart always --name mysql5.6   -v ~/Docker/cnf/my.5.6.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.6:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p  3308:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.6




docker run --restart always --name mysql5.5   -v ~/Docker/cnf/my.5.5.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.5:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p  3309:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.5

After each execution of the above commands, you should get an id returned. 

example: 3cb07d7c21476fbf298648986208f3429ec664167d8eef7fed17bf9ee3ce6316

You can start/restart and access each docker terminal easily via the Docker Desktop or just keep note of the related IDs and you execute via the terminal.

The Docker Desktop also shows you all the variables you passed so you can validate. 

You can of course also access the CLI here, stop and start or destroy it easily. 

$ docker exec -it 3cb07d7c21476fbf298648986208f3429ec664167d8eef7fed17bf9ee3ce6316 /bin/sh; exit

# mysql -p 

If the Docker container is already running you can now access MySQL via your localhost terminal.

$ mysql --host=localhost  --protocol=tcp --port=3306 -p -u root 

Now if you are having any access issues remember to ensure that MySQL accounts are correct and that your ports and mapping correctly. 

• Lost connection to MySQL server at 'reading initial communication packet'
• ERROR 1045 (28000): Access denied for user 'root'@'192.168.0.5' (using password: YES)

Now you can see that all are up and available and the server Ids match what we set per cnf file eariler.

$ mysql --host=localhost --protocol=tcp --port=3306 -e "Select @@hostname, @@version, @@server_id "
+--------------+-----------+-------------+
| @@hostname | @@version | @@server_id |
+--------------+-----------+-------------+
| 58e9663afe8d | 8.0.19 | 80 |
+--------------+-----------+-------------+
$ mysql --host=localhost --protocol=tcp --port=3307 -e "Select @@hostname, @@version, @@server_id "
+--------------+-----------+-------------+
| @@hostname | @@version | @@server_id |
+--------------+-----------+-------------+
| b240917f051a | 5.7.29 | 57 |
+--------------+-----------+-------------+
$ mysql --host=localhost --protocol=tcp --port=3308 -e "Select @@hostname, @@version, @@server_id "
+--------------+-----------+-------------+
| @@hostname | @@version | @@server_id |
+--------------+-----------+-------------+
| b4653850cfe9 | 5.6.47 | 56 |
+--------------+-----------+-------------+
$ mysql --host=localhost --protocol=tcp --port=3309 -e "Select @@hostname, @@version, @@server_id "
+--------------+-----------+-------------+
| @@hostname | @@version | @@server_id |
+--------------+-----------+-------------+
| 22e169004583 | 5.5.62 | 55 |
+--------------+-----------+-------------+