How do I migrate a MySQL database between Docker containers?

Migrate MySQL between containers: export from the source container with docker exec source-container mysqldump -u root -p database_name > backup.sql, copy the file to the host, then import to the target container: docker exec -i target-container mysql -u root -p database_name < backup.sql. Use docker volumes for persistent data migration.

How do I backup MySQL data volumes in Docker?

Backup MySQL Docker volumes with: docker run --rm -v mysql_data:/source -v $(pwd):/backup ubuntu tar czf /backup/mysql_backup.tar.gz /source. This creates a compressed archive of the volume. Restore by reversing the process. Alternatively, use mysqldump inside the container for logical backups.

Can I migrate MySQL data from Docker to AWS RDS?

Migrate MySQL from Docker to RDS: create a mysqldump from the Docker container, copy the SQL file to an EC2 instance or S3, then use the mysql client to import to RDS: mysql -h rds-endpoint -u username -p database_name < backup.sql. Alternatively, use AWS Database Migration Service (DMS) for automated migration with minimal downtime.

What are best practices for MySQL data in Docker containers?

Best practices for MySQL in Docker: always use named volumes (not bind mounts) for data persistence, avoid storing data inside the container layer, use environment variables for credentials, set up regular mysqldump backups to S3, use Docker secrets for password management, and for production consider AWS RDS instead of self-managed MySQL containers.

How to migrate databases between MySQL containers? -

Q: How do I backup MySQL data volumes in Docker?

Backup MySQL Docker volumes with: docker run --rm -v mysql_data:/source -v $(pwd):/backup ubuntu tar czf /backup/mysql_backup.tar.gz /source. This creates a compressed archive of the volume. Restore by reversing the process. Alternatively, use mysqldump inside the container for logical backups.

Q: Can I migrate MySQL data from Docker to AWS RDS?

Migrate MySQL from Docker to RDS: create a mysqldump from the Docker container, copy the SQL file to an EC2 instance or S3, then use the mysql client to import to RDS: mysql -h rds-endpoint -u username -p database_name < backup.sql. Alternatively, use AWS Database Migration Service (DMS) for automated migration with minimal downtime.

Q: What are best practices for MySQL data in Docker containers?

Best practices for MySQL in Docker: always use named volumes (not bind mounts) for data persistence, avoid storing data inside the container layer, use environment variables for credentials, set up regular mysqldump backups to S3, use Docker secrets for password management, and for production consider AWS RDS instead of self-managed MySQL containers.

January 7, 2021 / Nirav Shah

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”.

Spread Love By Sharing:

Nirav Shah

Nirav Shah is the Director of Eternal Web Pvt Ltd, an AWS Advanced Consulting Partner and certified Odoo Partner based in the UK. With over a decade of experience in cloud computing, digital transformation, and ERP implementation, Nirav helps enterprises adopt the right technology to solve complex business challenges. He specialises in AWS infrastructure, Odoo ERP, and web development solutions for businesses across the UK and beyond.