fbpx

透過 MySQL Dump 移轉 MySQL 5.6 to 8.0 進行版本升級 (Docker)

為了 Docker 化升級 MySQL 8.0

這次為了 Dockerlize (Docker 化) 將 MySQL Linux Service 轉為 Docker,同時也想升級到 MySQL 8.0 的版本,於是使用了 mysqldump 命令匯出與匯入 Schema + Data,順便紀錄一下過程中遇到的問題。實際上標準的升級作業是可以透過 mysql_upgrade 進行升級,但是我剛好手邊已經有 Backup SQL 所以才會土法進行升級,其實用官方的 Upgrade 會比較正規一點。

首先先透過命令匯出目前的 MySQL 所有資料庫,包含資料與 mysql 資料庫,這樣才可以一併把 MySQL User 與權限一起匯出進行移轉。Command 如下:

mysqldump -h 127.0.0.1 \
  --port=3306 \
  -u root -p \
  --all-databases \
  --default-character-set=utf8mb4 \
  --ignore-table=mysql.innodb_index_stats \
  --ignore-table=mysql.innodb_table_stats \
  > /mnt/backup/mysql_current.sql

匯出記得指定正確的編碼 --default-character-set=utf8mb4 不然 emoji 會全部變成「?」

接著透過 Docker Compose 啟動 MySQL 8.0,啟動的時候我們先把 Port Bind 成 3307 與目前舊的服務區隔一下。如下:

version: '3.2'
services:

  mysql:
    image: library/mysql:8.0.27
    container_name: mysql
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=secret
      - MYSQL_DATABASE=my-db
      - MYSQL_USER=my-user
      - MYSQL_PASSWORD=my-pwd
    volumes:
      - ./volumes/mysql:/var/lib/mysql
    ports:
      - 3307:3306

啟動完成以後一樣透過 mysql client command 匯入 dump 出來的 SQL,如下:

mysql -h 127.0.0.1 --port=3307 -u root -p < /mnt/backup/mysql_current.sql

匯入完成以後透過 root 進入 mysql cli 重新刷新權限

mysql -h 192.168.0.1--port=3307 -u root -p

FLUSH PRIVILEGES;

排除 MySQL: ERROR 1449 (HY000) 錯誤

之後連線 MySQL 應該 Log 會噴「ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist」錯誤,是因為 MySQL 8.0 的 user 資料表多了六個新欄位,由於剛剛我們透過 mysqldump 匯出匯入,所以要手動補上這幾個欄位,SQL 如下:

USER mysql;

ALTER TABLE `user`
  ADD `Create_role_priv` enum('N','Y') COLLATE 'utf8_general_ci' NOT NULL DEFAULT 'N',
  ADD `Drop_role_priv` enum('N','Y') COLLATE 'utf8_general_ci' NOT NULL DEFAULT 'N' AFTER `Create_role_priv`,
  ADD `Password_reuse_history` smallint unsigned NULL AFTER `Drop_role_priv`,
  ADD `Password_reuse_time` smallint unsigned NULL AFTER `Password_reuse_history`,
  ADD `Password_require_current` enum('N','Y') COLLATE 'utf8_general_ci' NULL AFTER `Password_reuse_time`,
  ADD `User_attributes` json NULL AFTER `Password_require_current`;

CREATE USER 'mysql.infoschema'@'%' identified by 'PASSWORD';
GRANT ALL PRIVILEGES ON *.* TO 'mysql.infoschema'@'%';
FLUSH PRIVILEGES;

順利的話這樣就升級完成了~