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.
Now you can see that all are up and available and the server Ids match what we set per cnf file eariler.
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.
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.pidsocket = /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqlsecure-file-priv= /var/lib/mysql-files# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0bind-address = 0.0.0.0port=3306server-id=80
# Custom config should go here!includedir /etc/mysql/conf.d/
cat my.5.7.cnf[mysqld]bind-address = 0.0.0.0server-id=57max_allowed_packet=32M
$ cat my.5.6.cnf[mysqld]bind-address = 0.0.0.0server-id=56
$ cat my.5.5.cnf[mysqld]bind-address = 0.0.0.0server-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 |
+--------------+-----------+-------------+
