In this blog we will explore how to setup a docker compose file in order to migrate from MySQL 5.7 to the latest MariaDB.
In the next blog we will explain how to setup a docker compose file to migrate from MySQL 8.0 to MariaDB.
The steps to migrate from MySQL 5.7 to MariaDB are:
- Start container by running docker-compose file
- Use the MySQL data directory and start MariaDB with MARIADB_AUTO_UPGRADE=1
Let’s explore each step.
1. Start MySQL
The MySQL container is started using the following docker-compose file.
# compose-mysql.yml
version: "3"
services:
mysql:
environment:
MYSQL_ROOT_PASSWORD: ${DB_ROOT_PASSWORD}
MYSQL_USER: ${DB_USER}
MYSQL_PASSWORD: ${DB_PASSWORD}
MYSQL_DATABASE: ${DB_DATABASE}
container_name: ${MYSQL_name}
image: mysql:5.7
healthcheck:
test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "--silent"]
interval: 5s
timeout: 3s
retries: 2
start_period: 0s
volumes:
# Preload files for MySQL data
- ./mysql:/docker-entrypoint-initdb.d:z
# We have to save MySQL volume that will be used in upgrade
- dbdata:/var/lib/mysql
volumes:
dbdata: {}
networks:
backend:- Verify it is up and running
$ docker compose -f compose-mysql.yml up
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
3a24898f78c7 mysql:5.7 "docker-entrypoint.s…" 27 seconds ago Up 15 seconds (healthy) 3306/tcp, 33060/tcp mysql-container- Verify table and data
$ docker exec -it mysql-container mysql -uroot -psecret -e "select * from testdb.countries"
+----------------------+
| name |
+----------------------+
| Bosnia & Herzegovina |
+----------------------+- The goal is to get this data after migration to MariaDB server.
- Stop the container
$ docker compose -f compose-mysql.yml down
[+] Running 3/3
✔ Container mysql-container Removed 1.6s
✔ Network migrate-mysql-mariadb_default Removed - Check the data directory by checking the named volume
$ docker volume ls -f name=dbdata
DRIVER VOLUME NAME
local migration-57_dbdata2. Use MySQL data directory and start MariaDB with MARIADB_AUTO_UPGRADE=1
- Start a new docker-compose file compose-migrate-mysql.yml, that sets the environment variable MARIADB_AUTO_UPGRADE to a non-empty value.
- With this change,
mariadb-upgradeis called on the MySQL data directory volume that is used as the MariaDB data directory.
# compose-migrate-mysql.yml
version: "3"
services:
mariadb-from-mysql57:
environment:
MARIADB_ROOT_PASSWORD: ${DB_ROOT_PASSWORD}
MARIADB_USER: ${DB_USER}
MARIADB_PASSWORD: ${DB_PASSWORD}
MARIADB_AUTO_UPGRADE: 1
container_name: ${MYSQL_MIGRATE_name}
image: mariadb:lts
volumes:
# MySQL data that we want to migrate
- dbdata:/var/lib/mysql
volumes:
dbdata: {}- The result is as follows
$ docker compose -f compose-migrate-mysql.yml up
mysql-container-migrated | 2024-01-04 13:28:35+00:00 [Note] [Entrypoint]: Temporary server started.
mysql-container-migrated | 2024-01-04 13:28:35+00:00 [Note] [Entrypoint]: Backing up system database to system_mysql_backup_unknown_version.sql.zst
mysql-container-migrated | 2024-01-04 13:28:35+00:00 [Note] [Entrypoint]: Backing up complete
mysql-container-migrated | 2024-01-04 13:28:35+00:00 [Note] [Entrypoint]: Starting mariadb-upgrade
mysql-container-migrated | The --upgrade-system-tables option was used, user tables won't be touched.
mysql-container-migrated | MariaDB upgrade detected
mysql-container-migrated | Phase 1/8: Checking and upgrading mysql database
...
mysql-container-migrated | 2024-01-04 13:28:41+00:00 [Note] [Entrypoint]: Finished mariadb-upgrade
mysql-container-migrated | 2024-01-04 13:28:41+00:00 [Note] [Entrypoint]: Stopping temporary server
mysql-container-migrated | 2024-01-04 13:28:41 0 [Note] mariadbd (initiated by: unknown): Normal shutdown
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
983036482fb3 mariadb:lts "docker-entrypoint.s…" 15 seconds ago Up 14 seconds 3306/tcp mysql-container-migrated- Since the
MARIADB_DISABLE_UPGRADE_BACKUPvariable is not set, a backup is also created. Backup can be removed by setting this variable to a non-empty value. - To check the backup run
$ docker exec mysql-container-migrated sh -c 'ls /var/lib/mysql/*zst'
/var/lib/mysql/system_mysql_backup_unknown_version.sql.zst- At the end we should verify that the upgrade completed successfully, by checking the data on the started container
$ docker exec -it mysql-container-migrated mariadb -uroot -psecret -e "select * from testdb.countries"
+----------------------+
| name |
+----------------------+
| Bosnia & Herzegovina |
+----------------------+- Note that you cannot use the MariaDB data directory anymore in MySQL, it is not a reverseable process.
# Not started container
$ docker run --name new-mysql-container-with-mariadb -v ./mysql-volume:/var/lib/mysql -eMYSQL_ROOT_PASSWORD=secret -d mysql:5.7
# Check logs
2024-01-04T13:38:30.761747Z 0 [ERROR] InnoDB: Only one log file found.
2024-01-04T13:38:30.761757Z 0 [ERROR] InnoDB: Plugin initialization aborted with error not found
2024-01-04T13:38:31.362301Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2024-01-04T13:38:31.362327Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2024-01-04T13:38:31.362332Z 0 [ERROR] Failed to initialize builtin plugins.
2024-01-04T13:38:31.362334Z 0 [ERROR] Aborting- Stop the container and remove the volume to cleanup the space
$ docker compose -f compose-migrate-mysql.yml down -v
[+] Running 3/3
✔ Container mysql-container-migrated Removed 0.5s
✔ Volume migration-57_dbdata Removed 0.0s
✔ Network migration-57_default Removed Conclusion and further reading
The blog shows how to easily migrate from MySQL 5.7 to the latest MariaDB release in containers using a single-step process.
If you come across any problems in this blog; with the design, or edge cases that don’t work as expected, please let us know. You are welcome to chat about it on Zulip. As always you can use our JIRA issue tracker, specifying the MDEV project, for any bugs/feature requests you may encounter.