| | | |

Shifting a MySQL Database from Docker to Kubernetes

Migrate a MySQL Database from Docker to Kubernetes

Not long ago, I had to migrate an application from Docker to Kubernetes. As part of this process, I needed to move the MySQL 8.0.39 database from a Docker container to a Kubernetes cluster. I thought it could be interesting to share how I accomplished this task, as it might help others facing a similar challenge.

The article assumes you have already deployed your MySQL database in your Kubernetes cluste.
I won’t be covering the full application migration process here, only the MySQL database migration.

Migrating a MySQL database from a Docker container to a Kubernetes cluster can be a seamless process if you follow the right steps. In this article, I will guide you through the process, ensuring that your data remains intact and your database operates smoothly in the new environment.

Steps to Migrate MySQL Database from Docker to Kubernetes

1. Dump the Database(s)

First, you need to create a dump of your database you wish to migrate. For this example, we’ll rename the database to “mydb”.

  1. Docker Exec into the MySQL Container:
    docker exec -it mysql-container-name /bin/bash
  2. Create the Dump:Make sure to send the dump to a path that is mounted on the host server so that you can retrieve it once you exit the container.
    mysqldump -u root -p --single-transaction --databases mydb > /var/lib/mysql/new_dump_mydb.sql
     
    The --single-transaction parameter ensures that the dump is consistent and does not lock the database, making it ideal for live databases. Of course, you need to plan a maintenance window for this operation. In my case, I was sure that no transactions would occur during the maintenance period, which made the process smoother.
     
    IMPORTANT NOTE: You will  need to migrate the DB users, to do so, you need to migrate the DB called “mysql” first.

2. Copy the Dump to the Kubernetes Admin Server

Use scp to securely copy the dump file to the server from which you are administering the Kubernetes cluster.

scp /var/lib/mysql/new_dump_mydb.sql user@kubernetes-admin-server:/root/new_dump_mydb.sql

3. Copy the Dump to the Kubernetes MySQL Pod

Kubernetes provides a powerful command, kubectl cp, which allows you to copy files directly to a pod. This command is amazing because it simplifies the process of transferring files to your Kubernetes environment without needing to set up additional infrastructure.

kubectl cp /root/new_dump_mydb.sql NAMESPACE/POD_NAME:/tmp -c my-mysql-container-name

4. Access the MySQL Pod

Next, you need to access the MySQL pod to import the database.

kubectl exec -it mysql-pod-name -n namespace /bin/bash
 
Check the dump is under /tmp

5. Import the Database

Once inside the pod, you can import the databases using the following command:

mysql -u root -p mydb < /tmp/new_dump_mydb.sql

6. Create Users and add GRANTs

After importing the database, you need to create the user (unless the componants accessing the DB remain hosted where they are, in my case the front end was also moved to the Kubernetes cluster) and apply the appropriate GRANTs.

Create the user using the Worker Node and Pod CIDR . You can check the existing user GRANTs and create new ones with the same privileges and the new CIDR. If you don’t do that, your front end Pod and all other Pods which requires to login to the DB won’t be able to do so.

Login as root:

mysql -u root -p

Check the imported database is present:

show databases;

Check all users:

SELECT user, host FROM mysql.user;

Check the existing GRANTs for the desired user using the SHOW GRANTS command:
SHOW GRANTS FOR 'username'@'host';

Output Exemple:
GRANT SELECT ON mydb.* TO 'user'@'10.10.1.%' IDENTIFIED BY 'password';

10.10.1.0/24 = old CIDR

Create a new user using new CIDR:
create user 'user'@'100.60.%.%' IDENTIFIED BY 'password';

100.60.0.0/16 = new CIDR (Pod CIDR)

Apply the desired GRANT to the new user you can copy/past the “SHOW GRANTS” output from above and adapt with new CIDR:
GRANT SELECT ON mydb.* TO 'user'@'100.60.%.%' IDENTIFIED BY 'password';

Repeat the same for the Worker Nodes CIDR.

FLUSH PRIVILEGES;

The FLUSH PRIVILEGES; command in MySQL is used to reload the grant tables, which store information about user privileges.

7. Verify the Settings

Finally, verify the settings by using the SHOW GRANTS command again to ensure that the new GRANTs are correctly applied.

SHOW GRANTS FOR 'user'@'new_cidr';

Conclusion

Migrating a MySQL database from Docker to Kubernetes involves several steps, but with the right tools and commands, the process can be straightforward. By following the steps outlined in this article, you can ensure a smooth transition of your database to a Kubernetes environment.

Similar Posts