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:

  1. Start container by running docker-compose file
  2. 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_dbdata

2. 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-upgrade is 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_BACKUP variable 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.

Similar Posts