GreatSQL社区

搜索

GreatSQL社区

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

GreatSQL社区 已有 110 次阅读2024-6-5 10:37 |系统分类:运维实战

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
  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中。


评论 (0 个评论)

facelist

您需要登录后才可以评论 登录 | 立即注册

合作电话:010-64087828

社区邮箱:greatsql@greatdb.com

社区公众号
社区小助手
QQ群
GMT+8, 2024-6-18 22:29 , Processed in 0.012817 second(s), 7 queries , Redis On.
返回顶部