一、背景概述
在將資料庫從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` (
......
三、問題解決
- 可以初始化一個新的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
- 匯入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 😃
關於 GreatSQL
GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。
相關連結： GreatSQL社群 Gitee GitHub Bilibili
GreatSQL社群：
社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html
技術交流群：
微信：掃碼新增
GreatSQL社群助手微信好友，傳送驗證資訊加群。
