GTID主从配置后的主库数据迁移到从库操作

场景

在不停止主库、主库不锁库的情况下进行数据库主从复制，主库的数据导入给从库，并且从库开启同步功能。

windows环境下mysql线上数据库需要做主从备份，线上的数据库已经开启binlog日志并且已配置好主备使用的相关配置、用户以及权限。

GTID主从配置参考之前的文章：YSQL一个设备上的主从复制实现 (GTID版)-windows

从库服务器也已经配置好相关配置，现在需要在保证主库不停机、不锁库、不影响作业的情况下进行已有数据的迁移并且操作使从库的数据与主库保持一致并同步主库的操作。

注意事项

　　从第一步开始，到整个流程结束期间，不要对主库的表、字段、索引等数据库属性相关进行增删改（数据库里的数据没影响，保持正常的日常使用就行）

一、主库binlog初始化

　　此步骤要保证主库没有其他的从库正在执行主从操作，只适用于第一次做主从。此操作会清空所有的主库binlog文件。

　　1、登录主数据库（cmd操作）

mysql -uroot -p --protocol=tcp --host=localhost --port=3306

　　2、登录主数据库后执行初始化binlog命令

reset master;

二、导出主数据库数据

1、找一个文件夹打开新的cmd操作，不需要登录主数据库，导出对应数据库

mysqldump ： MySQL 自带的逻辑备份工具。

-uroot -p --host=localhost --port=3306：主数据库的登录账号、登录密码、host登录地址、主数据库端口。

--single-transaction：使用一个单独的连接，这样能保证在设置session级别的变量的时候不影响到其他连接 ，也就是不会锁库。

--default-character-set=utf8：指定导出文件的编码，不设置的话导出后会乱码。

--flush-logs：生成新的binlog文件。

--master-data=2：前提必须开启二进制文件，如果选项值等于2，CHANGE MASTER语句被写成SQL注释保存到导出的文件里。

--set-gtid-purged=off：不记录GTID，从表到时候使用新的GTID。

mysqldump -uroot -p --host=localhost --port=3306 --single-transaction --default-character-set=utf8 --hex-blob --flush-logs --master-data=2 --set-gtid-purged=off test > test.sql

三、把备份的数据导入到从库　

初始化从表

不管有没有事先准备好，在这一步都应该在cmd登录从库后运行一下以下的操作。

1、登录从库

mysql -uroot -p --protocol=tcp --host=localhost --port=3307

2、停止从库slave操作

stop slave;

3、断开与主库的连接

CHANGE master TO
master_HOST='127.0.0.1',
master_PORT=3306,
master_USER='user_cp',
master_PASSWORD='123456',
master_AUTO_POSITION=0;

4、查看一下从库的状态，确认已经断开连接

show slave status \G;

5、初始化从库的binlog日志（会清空binlog日志，并且会生成全新的日志）

reset master;

6、初始化从库的从库同步日志（会清空从库同步日志，并且会生成全新的日志）

reset slave;

7、在从库新建与要同步的数据库同名同配置的数据库

四、主库数据导入从库

把导出的主库数据文件.sql文件导入到从库内（这个过程用时会比较长）

mysql -uroot -p --host=localhost --port=3307 --default-character-set=utf8 test< test.sql

五、导入后的操作

1、导入完毕后重启从库数据库服务

2、登录从库

mysql -uroot -p --protocol=tcp --host=localhost --port=3307

3、查看状态

show slave status \G;

4、重新停止一下从库的slave（防止重启后自动开启）

stop slave;

5、连接主库

CHANGE master TO
master_HOST='127.0.0.1',
master_PORT=3306,
master_USER='user_cp',
master_PASSWORD='root,
master_AUTO_POSITION=1;

6、开启从库的slave

start slave;

7、查看状态

show slave status \G;

到此基本操作已经完毕，但是避免不了会出问题。

六、问题以及解决方法

1、主键冲突问题

查看状态如果主键冲突错误编号为：1062，并且在Last_SQL_Error列会有一下类似提示

Last_SQL_Error: Error 'Duplicate entry '1001-164761-0' for key 'PRIMARY'' on query. Default database.....

在从库安装目录下的my.ini文件内的[mysqld]标签下添加：

跳过所有的1062错误。

slave-skip-errors = 1062

关闭从库的slave

stop slave;

重启从库服务后登录从库，并且开启从库的slave

start slave;

问题搞定。

2、数据不一致

从库同步删改类数据时报错，错误信息为1032找不到某个表里的某条数据

1032 could not execute update_rows event on table.....

根据提示中具体的log文件，在主库存储binlog日志的路径下执行一下命令，把日志文件接码导出成可读log文件

mysqlbinlog --base64-output=decode-rows -v  mysql-bin.000019 > 17bin.log

根据提示中具体的end_log_pos 在文件里查找，找到确实的数据，在从库insert 上，然后启停从库的slave，查看状态。

 

如果是其他类似的报错可以根据报错中提示的end_log_pos 在主库日志文件中查询。

例如如果报错提示是在mysql-bin.000045日志文件中end_log_pos = 69264537。

在主库服务器中操作！！！

第一步：先把主库日志文件转码导出成可读log文件：

mysqlbinlog -v --verbose  /data/mysql/db/mysql-bin.000045 > /tmpbinlog.log

第二部：根据提示的end_log_pos 查找所在行数：

cat tmpbinlog.log | awk '/end_log_pos 69264537/ {print NR}'

例如输出的是6086010，表示出错的SQL 在6086010 行。

根据所在行进行范文读取，这里增加300行作为结束行：

cat tmpbinlog.log | awk 'NR==6086010,NR==6086310'

根据打印出的SQL 在从库中手动操作：

如果报错是修改失败，则可能是数据不存在，在主库中找到对应的数据复制到从库中。

如果报错是删除失败，则可能是数据不存在，根据打印的SQL手动在从库中添加数据。

然后重启从库同步。

 

如果该提示一直存在，数据可能存在批量不一致，建议重做slave。

 

 

 

搜索

复制