MySQL 5.x數據遷移到MySQL 8.0

By James Qi, 2021年7月23日

很多年前一直在使用MySQL 5.x，前幾年開始用阿裡雲RDS的時候，也是使用的MySQL 5.5、5.6、5.7，最近有Aliyun RDS陸續到期，看到新購RDS有MySQL 8.0的選項，再查閱一些資料，說MySQL 8.0性能上比5.x有大幅提高，而且5.x逐步也不再被MySQL官方和阿裡雲支持，想到如果直接續費3年的話，老版本還有幾年都無法升級，就幹脆這次新購MySQL 8.0版本的RDS，把老數據進行遷移，下面是遷移中遇到一些問題的記錄。

我們采取的是使用mysqldump命令備份老RDS上的庫到.sql文件，然後使用mysql命令逐個還原.sql文件到新RDS上。

先是備份、還原了幾個utf8的MediaWiki網站數據庫都沒有問題，但mysqldump還原utf8mb4數據庫的時候遇到報錯：

mysqldump: Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file

查看

mysqldump --version
mysqldump  Ver 10.13 Distrib 5.1.73, for redhat-linux-gnu (x86_64)

還是MySQL 5.1.73的很老版本，在網上下載了MySQL-5.6.17-1.el6.x86_64.rpm-bundle.tar，解壓後得到MySQL-client-5.6.17-1.el6.x86_64.rpm（隻安裝Client也可以隻獲取這一個文件）。

使用rpm -qa | grep -i mysql查看安裝了哪些mysql相關内容：

mysql-5.1.73-8.el6_8.x86_64
mysql-libs-5.1.73-8.el6_8.x86_64

再用yum -y remove mysql-libs*卸載老版本，然後用rpm -ivh MySQL-client-5.6.17-1.el6.x86_64.rpm 安裝新版本，就可以看到新的版本号了：

mysql --version
mysql  Ver 14.14 Distrib 5.6.17, for Linux (x86_64) using  EditLine wrapper

再用mysqldump備份可以成功，帶有警告信息：

Warning: Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

這時用mysql還原的時候又會遇到報錯：

Warning: Using a password on the command line interface can be insecure.
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
ERROR 1227 (42000) at line 24: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
ERROR 1227 (42000) at line 3554: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

在網上搜索，因為權限和版本兼容性的原因，需要在.sql文件中去掉

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

SET @@GLOBAL.GTID_PURGED='16283487-45bd-11e7-9ef2-7cd30ac3f3fe:1-299493847,
43c1dc25-dbbe-11e5-a66f-a0d3c1f93abc:1-543829194,
500866a4-e70d-11e8-802d-7cd30adb113a:1-416157186,
5fd4db5e-fc00-11e6-be1d-a0d3c1f43dd0:1-93905767,
6567f578-fecb-11e7-95aa-7cd30adb15da:1-18921416,
72617ba5-eca2-11e9-a9e9-7cd30ae00d0e:1-704516432,
83fdc286-bbba-11e7-a056-a0d3c1f8397c:1-12603194,
8d21b36e-b6e5-11e6-bb7e-a0d3c1f8397c:1-41968137,
a0bcb219-2379-11e5-b4d9-a0d3c1f43dd0:1-3390044,
a7f7b8d7-da4f-11ea-b7c2-506b4b4197cc:1-828737650,
b667cab5-1e31-11e5-926a-a0d3c1f93abc:1-1814845499,
c0e956af-d2bd-11eb-8bbb-7cd30adb159e:1-116084394,
c37acaf4-1e31-11e5-926b-a0d3c1f43dd0:1-2289807,
e73f79ba-2a90-11e5-a317-a0d3c1f43dd0:1-1425172325';

SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

這些内容，可以手工編輯，也可以用Linux命令sed來批量删除：

sed -i '17,37d' /sql-backup/$i-2021-7-22.sql
sed -i '/SQL_LOG_BIN/d' /sql-backup/$i-2021-7-22.sql

後面還遇到一些問題，等會繼續記錄。

備份的時候遇到表過大報錯的問題：

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `kor_postcode` at row: 4646629

這個表有2G多數據，1G多索引。

采取的辦法：RDS的MySQL參數net_read_timeout從30改為120還是不行，mysqldump添加--quick 參數還是不行，添加參數--max_allowed_packet=2000M也還是不行，又按照這篇中改了--compress --skip-lock-tables --single-transaction --skip-extended-insert等都還是不行，最後是修改了ECS上/etc/my.cnf，添加兩行：

[client]
max_allowed_packet=1024M

再次運行mysqldump就可以了。

還原的時候遇到主鍵等重複的報錯：

Warning: Using a password on the command line interface can be insecure.
ERROR 1062 (23000) at line 4410: Duplicate entry 'pass' for key 'PRIMARY'

這個問題在search_total和其它表都有可能出現，特别是一些英文以外的字符，有可能是與字符集有關，但也沒有找到具體解決辦法。

嘗試手工去打開.sql文件，找到對應的行、對應的字符去修改或者删除，但又是太多了不好找，而且vim打開、查找都非常慢。

後來找了一些資料，幹脆把這種重複的覆蓋或者忽略，辦法是在mysqldump命令中添加--replace或者--insert-ignore參數。實測這樣導出的.sql文件是可以用mysql命令正常導入的。

最後的語句是這樣的：

mysqldump -h hostname -u username -ppassword --opt --default-character-set=utf8mb4 --set-gtid-purged=OFF --replace database > database.sql

mysql -h hostname -u username -ppassword -f -D database < database.sql

2021年9月24日補充：

Drupal 7網站的MySQL從5.x升級到8.0以後，drupal日志可能出現大量報錯，例如：

訪問：https://example.com/admin/structure/schema 這樣管理數據庫結構的頁面，出現報錯：

Notice: Undefined property: stdClass::$index_name in SchemaDatabaseSchema_mysql->inspect() (line 191 of /path/sites/all/modules/schema/engines/mysql.inc).

可以用drush這樣設置：

drush vset schema_status_report 0
drush vset schema_suppress_type_warnings 1
drush cc all

也就是設置

Include schema comparison reports in site status report ❌
When checked, schema comparison reports are run on the Administer page, and included in the site status report.
Suppress schema warnings. ☑️
When checked, missing schema type warnings will be suppressed.

然後暫時就不會報錯了，但要根本上解決還有待進一步研究。

自由标簽

MysSQL