How to migrate databases between MySQL containers?

January 7, 2021 / Eternal Team

This an interesting yet simple way to migrate databases between MySQL containers:

Step 1: Launch the first MySQL container.

$ docker container run --detach --env MYSQL_ALLOW_EMPTY_PASSWORD=True --volume mysqldb:/var/lib/mysql --publish 3306:3306 --name mysql-1 mysql

$ docker container exec --interactive --tty mysql-1 /bin/bash

# mysql -u root -p (Press enter on the "Enter password:" prompt)

mysql> create database tobemigrated1;

mysql> create database tobemigrated2;

mysql> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tobemigrated1 |
| tobemigrated2 |
+--------------------+
6 rows in set (0.00 sec)

Step 2: Stop the first MySQL container and Launch the second MySQL container.

$ docker container stop mysql-1
$ docker container run --detach --env MYSQL_ALLOW_EMPTY_PASSWORD=True --volume mysqldb:/var/lib/mysql --publish 3306:3306 --name mysql-2 mysql

$ docker container exec --interactive --tty mysql-2 /bin/bash

# mysql -u root -p (Press enter on the "Enter password:" prompt)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tobemigrated1 |
| tobemigrated2 |
+--------------------+
6 rows in set (0.00 sec)

Conclusion

You have successfully migrated your databases created in the first MySQL container to the second MySQL container.
You cannot mount the same “/var/lib/mysql” volume to more than 1 containers at a time as MySQL service does not support that. Still, any force-mounts into other containers after the first container will result in the second container throwing the following error:
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
To work around this situation you will always need to stop or remove the first container and then only will the very next container after it will be able to access the database files and then the same applies for the second container when you want to migrate the database to the third container as then you will need to stop or remove the second container for the third container to be able to access the databases files.
The technique used over here is “named-volumes”.