使用 Docker 官方镜像进行迁移 - MariaDB.org

在这篇博客中，我们将探讨 如何设置一个 docker compose 文件以便从 MySQL 5.7 迁移到最新的 MariaDB。

在下一篇博客中，我们将解释如何设置一个 docker compose 文件以便从 MySQL 8.0 迁移到 MariaDB。

从 MySQL 5.7 迁移到 MariaDB 的步骤如下：

通过运行 docker-compose 文件启动容器
使用 MySQL 数据目录并使用 MARIADB_AUTO_UPGRADE=1 启动 MariaDB

让我们来探讨每个步骤。

1. 启动 MySQL

MySQL 容器使用以下 docker-compose 文件启动。

# 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:

验证它是否正在运行

$ 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

验证表和数据

$ docker exec -it mysql-container mysql -uroot -psecret -e "select * from testdb.countries"
+----------------------+
| name                 |
+----------------------+
| Bosnia & Herzegovina |
+----------------------+

目标是在迁移到 MariaDB 服务器后获取这些数据。
停止容器

$ docker compose -f compose-mysql.yml down
[+] Running 3/3
 ✔ Container mysql-container              Removed                                                                                                                                                                         1.6s 
 ✔ Network migrate-mysql-mariadb_default  Removed

$ docker volume ls -f name=dbdata
DRIVER    VOLUME NAME
local     migration-57_dbdata

2. 使用 MySQL 数据目录并使用 MARIADB_AUTO_UPGRADE=1 启动 MariaDB

启动一个新的 docker-compose 文件 compose-migrate-mysql.yml，该文件将环境变量 MARIADB_AUTO_UPGRADE 设置为非空值。
通过此更改，将在用作 MariaDB 数据目录的 MySQL 数据目录卷上调用 mariadb-upgrade。

# 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: {}

结果如下所示

$ 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

由于未设置 MARIADB_DISABLE_UPGRADE_BACKUP 变量，因此还会创建备份。通过将此变量设置为非空值可以删除备份。
要检查备份，请运行

$ docker exec mysql-container-migrated  sh -c 'ls /var/lib/mysql/*zst'
/var/lib/mysql/system_mysql_backup_unknown_version.sql.zst

最后，我们应该通过检查启动的容器上的数据来验证升级是否成功完成

$ docker exec -it mysql-container-migrated mariadb -uroot -psecret -e "select * from testdb.countries"
+----------------------+
| name                 |
+----------------------+
| Bosnia & Herzegovina |
+----------------------+

请注意，您不能再在 MySQL 中使用 MariaDB 数据目录，这是一个不可逆的过程。

# 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

停止容器并移除卷以清理空间

$ 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