MySQL5.7 通過邏輯備份遷移到GreatSQL註意事項

一、背景概述

在將資料庫從MySQL 5.7遷移到GreatSQL8.0.32時，由於數據量較小且關註安全性，決定使用mysqldump執行邏輯備份，並將數據導入GreatSQL。但在備份時採用了備份全庫(--all-databases)的方式，在導入GreatSQL後，修改用戶密碼時出現錯誤。這是因為mysqldump備份時包括了mysql系統庫，而MySQL 5.7中的mysql系統庫採用了MyISAM存儲引擎，而GreatSQL的mysql系統庫採用了InnoDB存儲引擎。因此，在導入數據後，部分系統表被覆蓋，導致了錯誤的出現。

二丶問題復現

1.部署2個實例

部署MySQL 5.7與GreatSQL 8.0.32，具體步驟省略

2.MySQL 創建測試數據

通過sysbench創建10張表

$ sysbench lua/oltp_read_write.lua --mysql-db=sysbench --mysql-host=192.168.1.162 --mysql-port=6003 --mysql-user=root --mysql-password=greatsql --tables=10 --table_size=5000 --report-interval=2 --threads=10 --time=600 --mysql-ignore-errors=all prepare

3.MySQL 創建測試用戶

mysql> create user test1@'%' identified by 'greatsql';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to test1@'%';
Query OK, 0 rows affected (0.01 sec)

4.MySQL進行全庫備份

$ /mysql57/svr/mysql/bin/mysqldump -uroot -pgreatsql -h192.168.1.162 -P6003  --single-transaction --set-gtid-purged=OFF --all-databases > all.sql

5.GreatSQL導入備份數據

greatsql> source all.sql;

在導入過程中有如下報錯，從這裡可以看出導入時有系統表被導入，並且部分系統表不支持被修改：

6.GreatSQL執行修改用戶密碼操作

greatsql> alter user test1@'%' identified by 'abc123';

ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.

此時報錯違反了 GTID 的一致性，由於導入數據後有部分MySQL庫下的系統表被修改成了MyISAM存儲引擎，並且修改用戶密碼會涉及多張系統表的更新，在一個 SQL 語句中既對非事務表進行了更新操作，又對事務表進行了更新操作，這違反了 GTID 的一致性規則。

7.GreatSQL執行查看庫表信息的操作

當執行刷新許可權的操作後，執行下麵的SQL時也會報錯

greatsql> flush privileges;
Query OK, 0 rows affected, 11 warnings (0.00 sec)

greatsql> show tables;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

greatsql> show databases;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

mysql.infoschema用戶是MySQL8.0中引入的，mysql.infoschema用戶是MySQL資料庫的系統用戶，用來管理和訪問系統自帶實例information_schema；導入MySQL系統庫後，user表被覆蓋為MySQL5.7的user表，此用戶不存在，因此在刷新許可權後訪問元數據信息時報錯；

8.查看mysqldump備份了哪些系統表

$ grep 'CREATE TABLE ' all.sql
......
CREATE TABLE `innodb_index_stats` (
CREATE TABLE `innodb_table_stats` (
CREATE TABLE `ndb_binlog_index` (
CREATE TABLE `plugin` (
CREATE TABLE `proc` (
CREATE TABLE `procs_priv` (
CREATE TABLE `proxies_priv` (
CREATE TABLE `server_cost` (
CREATE TABLE `servers` (
CREATE TABLE IF NOT EXISTS `slave_master_info` (
CREATE TABLE IF NOT EXISTS `slave_relay_log_info` (
CREATE TABLE `slave_worker_info` (
CREATE TABLE `tables_priv` (
CREATE TABLE `time_zone` (
CREATE TABLE `time_zone_leap_second` (
CREATE TABLE `time_zone_name` (
CREATE TABLE `time_zone_transition` (
CREATE TABLE `time_zone_transition_type` (
CREATE TABLE `user` (
CREATE TABLE IF NOT EXISTS `general_log` (
CREATE TABLE IF NOT EXISTS `slow_log` (
......

三、問題解決

1. 可以初始化一個新的GreatSQL實例，備份這個新的GreatSQL實例的MySQL系統庫

$ /greatsql/svr/mysql/bin/mysqldump -uroot -pgreatdb -h127.0.0.1 -P6001  --single-transaction --set-gtid-purged=OFF -B mysql > mysql.sql

2. 導入mysql系統庫的備份文件到GreatSQL實例中

greatsql> source mysql.sql;

3.導入完成後，刷新許可權

greatsql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

4.執行創建用戶測試

greatsql> create user test1@'%' identified by 'greatsql';
Query OK, 0 rows affected (0.01 sec)

greatsql> grant all on *.* to test1@'%';
Query OK, 0 rows affected (0.01 sec)

5.執行 show 操作

greatsql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| sys_audit          |
| sysbench           |
| test               |
+--------------------+
7 rows in set (0.01 sec)

greatsql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.01 sec)

四、總結

1.在從MySQL5.7向GreatSQL或者MySQL8.0 通過邏輯備份遷移時，不要備份系統庫mysql，避免發生問題；

2.用戶及許可權可以單獨進行備份然後導入到GreatSQL或者MySQL8.0中。

Enjoy GreatSQL