VMware替代实战手册：更高效的MySQL数据库迁移方案

五、附录

(一)准备迁移用户

建议使用数据库全权限用户如root@'%'(和root@'localhost'不是同一个用户)进行迁移。如果源端不能使用全权限数据库用户执行迁移,需在源端创建迁移用户。创建用户及赋权语句如下:

注意:迁移用户的密码中特殊字符仅支持:()`~!@#$^&*_-+=|{}[]:<>.?/。

MySQL5.6、5.7、8.0 全量迁移用户权限

mysql> create user dtsuser@'%' identified with mysql_native_password by 'dtspassword';

mysql> grant select,event,show view,lock tables,reload on *.* to dtsuser@'%';

MySQL5.6、5.7、8.0 全量+增量迁移用户权限

mysql> create user dtsuser@'%' identified with mysql_native_password by 'dtspassword';

mysql> grant select,event,show view,lock tables,replication slave,replication client,reload on *.* to dtsuser@'%';

(二)在线开启GTID

GTID(Global Transaction ID,全局事务ID),用来强化数据库的主备一致性、故障恢复,以及容错能力。用于取代过去传统的主从复制(即:基于binlog和position的复制)。

若迁移任务为全量+增量迁移情况,则必须开启此参数。

以下操作主从均需要执行:

1.开启GTID预检查

mysql> set @@global.enforce_gtid_consistency=WARN;

开启此参数后,需观察MySQL错误日志,若有违反GTID规则的事务会有告警,应及时调整。

设置告警后,部分操作会被告警,请注意调整业务或关闭GTID,例如:

(1) 执行CREATE TABLE ... SELECT语句:

(MySQL8.0.21以后对于支持原子DDL的存储引擎,例如InnoDB引擎,支持该操作)

例如:

create table t1 select * from sbtest3;

查看错误日志:

2023-06-19T11:44:05.956128+08:00 82810 [Warning] Statement violates GTID consistency: CREATE TABLE ... SELECT.

修改:

create table t1 like sbtest3;

insert into t1 select * from sbtest3;

(2) 在事务中执行CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE语句:

例如:

begin;

select * from sbtest3 for update;

create temporary table t2(id int);

查看错误日志:

2023-06-19T11:52:42.254719+08:00 82810 [Warning] Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.  These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.

修改:

避免在事务中执行创建或删除临时表。

2.开启GTID校验

mysql> set @@global.enforce_gtid_consistency=ON;

这一步一旦执行,违反GTID的操作都会被拒绝,比如 create table as select,所以上一步WARN阶段确保无违反GTID规则的事务。

3.开启GTID_MODE

mysql> set @@global.gtid_mode=OFF_PERMISSIVE;

观察ongoing_anonymous_transaction_count值:

mysql> show global status like '%ongoing_anonymous_transaction_count%';

确认已经没有匿名的事物,建议多观察一段时间,如果不为0,强行修改可能会导致数据丢失。

4.GTID_MODE设置为ON_PERMISSIVE

mysql> set @@global.gtid_mode=ON_PERMISSIVE;

5.GTID_MODE设置为ON

mysql> set @@global.gtid_mode=ON;

6.从库执行(若源端为单机,忽略此步骤)

mysql> stop slave;

mysql> change master to master_auto_position=1;

mysql> start slave;

mysql> show slave status\G

这一步,所有老的relay log都清理掉了,新relay log包含的全是GTID操作Event。

7.修改配置文件(永久生效)

若未添加至配置文件,则数据库重启后参数失效,GTID关闭。

主从均执行

# vim /etc/my.cnf

在mysqld下添加以下内容

[mysqld]

gtid_mode=ON

enforce_gtid_consistency=ON

(三)修改BINLOG_FORMAT

BINLOG_FORMAT是MySQL中的一个参数,用于指定二进制日志文件的格式。MySQL的复制方式与binlog(二进制日志文件)格式一一对应。

mysql复制主要有三种方式:

基于SQL语句的复制(statement-based replication, SBR);

基于行的复制(row-based replication, RBR);

混合模式复制(mixed-based replication, MBR)。

对应的,binlog的格式也有三种:STATEMENT,ROW,MIXED。

修改BINLOG_FORMAT的步骤如下:

1.先在从库执行、再去主库执行

mysql> set global binlog_format=ROW;

2.修改配置文件(主从都修改)

# vim /etc/my.cnf

在mysqld下添加以下内容

[mysqld]

binlog_format=ROW

(四)手动迁移触发器trigger

1.检查询命令默认业务触发器没有创建在系统数据库中,所以排除系统数据库sys、mysql、information_schema、performance_schema。

mysql> select TRIGGER_SCHEMA,count(*) as tiggers_cnt from information_schema.`TRIGGERS` where TRIGGER_SCHEMA not in ('sys','mysql','information_schema','performance_schema') group by TRIGGER_SCHEMA;

如上命令执行后有结果,如图所示,源端业务数据库sakila、test分别有6、1个触发器,则需要迁移。

如上命令执行后查不到数据,则表示业务数据库中无触发器需要迁移。

2.方法一:(推荐)

1.在目标端数据库后台执行如下命令导出源端触发器。注意:在-B参数后面添加需要导出的业务数据库(即上一章节查询出来的TRIGGER_SCHEMA)的名字,如有多个使用空格分隔。

-h:源端数据库ip地址,如“10.5.54.66”。

-P:源端数据库端口号,如“3306”。

-u:源端数据库迁移账号,如“root”

-p:源端数据库迁移账号密码,如“Admin-123”。

# mysqldump -h10.5.54.66 -P3306 -uroot -pAdmin-123 --single-transaction --set-gtid-purged=OFF --default-character-set=utf8mb4 --add-drop-trigger --no-create-db=true --no-create-info=true --no-data=true -B sakila test > ./tri.sql

注意:导出源端触发器用户需要有“trigger”权限。

(2)导入到目标端数据库。

# mysql -uroot -pQwer@123 -S/run/sock/mysql.sock < ./tri.sql

(3)检查触发器是否迁移成功

在目标端执行命令查询,参考“Part.5 附录中第4节 手动迁移触发器trigger的检查源端是否存在触发器”。

3.方法二

1.在目标端用root用户登录RDS主节点,访问源端数据库导出业务数据库触发器DDL语句。

# cd

# rm -rf trigdump.sql

# touch trigdump.sql

# mysql -h10.5.54.66 -P3306 -uroot -pAdmin-123 <<'EOF'

tee trigdump.sql

SELECT

CONCAT("DROP TRIGGER IF EXISTS `",

TRIGGER_SCHEMA,

"`.`",

TRIGGER_NAME,

"`;\nDELIMITER ;;\nCREATE TRIGGER `",

TRIGGER_SCHEMA,

"`.`",

TRIGGER_NAME,

"` ",

ACTION_TIMING,

" ",

EVENT_MANIPULATION,

" ON `",

EVENT_OBJECT_SCHEMA,

"`.`",

EVENT_OBJECT_TABLE,

"` FOR EACH ROW\n",

ACTION_STATEMENT,

";;\nDELIMITER ;") AS TRIG

FROM

information_schema.TRIGGERS

WHERE

TRIGGER_SCHEMA IN ('sakila','test')\G

notee

exit

EOF

# sed -i '/^*/d' trigdump.sql

# sed -i 's/TRIG: //' trigdump.sql

# echo "COMMIT;" >> trigdump.sql

(2)导入触发器至目标端主节点

# mysql -uroot -p -S/run/sock/mysql.sock < trigdump.sql

(3)检查触发器是否迁移成功

在目标端执行命令查询,参考“Part.5 附录中第4节 手动迁移触发器trigger的检查源端是否存在触发器”。

翻译

搜索

复制