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` (......
复制代码
三、问题解决
可以初始化一个新的 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 中。
评论