MySQL5.7 通过逻辑备份迁移到GreatSQL注意事项

* 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

1. 导入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 :)