服务器mysql主从同步断开后，进行主从恢复

问题：mysql的主从断开了，在从服务器上通过以下命令查看状态，发现Slave_SQL_Running为No

SHOW SLAVE STATUS\G;

Slave_IO_Running：表示从服务器读取主服务器日志的线程 Slave_SQL_Running：表示从服务器执行日志的线程（一般都是这个失效导致主从不同步）

解决思路：

因为本地的主从数据库很早就断开了，导致后面很多数据不一致，索性直接将主库的数据先拷贝一份（项目已上线，所以会一直有数据操作。备份主服务器数据库的数据时需先查看主服务器的当前日志文件及偏移量），然后将主服务器拷贝的数据同步到从服务器，之后在从服务器重新配置与主服务器的关系（一般是需要调整，因为查看主服务器状态和备份数据存在一点时间差，所以这个阶段的操作可能会多备份一点数据）。

ps:测试服是一台服务器配置的主从，正式是两台。 单台服务器配置主从和参开前期文章[单机一主一从配置]，

单机一主一从解决方案：

查看主服务器的状态（登录mysql后操作）

SHOW MASTER STATUS;

主服务器数据备份（mysql安装bin目录下执行，需指定端口）

./mysqldump -u root -p --host=127.0.0.1 --port=3307 --all-databases --master-data=2 > dump.sql

可选参数： –single-transaction： 在 InnoDB 引擎的事务中生成一致性快照，避免锁表 –quick：每次读取一行数据以降低内存消耗，适合大表。 –lock-tables=false：禁用表锁定，通常与 --single-transaction 一起使用 -master-data：{1：会直接将 CHANGE MASTER 命令插入到备份文件中；2：仅将二进制日志信息作为注释写入备份文件，不会影响恢复时的执行}

备份的数据同步至从服务器（mysql安装bin目录下执行，需指定端口）

./mysql -u root -p --host=127.0.0.1 --port=3308 < dumps.sql

从服务器暂停后重置（需登录mysql操作）

-- 暂停从服务器

STOP SLAVE;

-- 从服务重置

RESET SLAVE ALL;

从服务器建立与主服务器绑定关系（需登录mysql操作）

CHANGE MASTER TO

MASTER_HOST = '主服务器ip',

MASTER_PORT = 3306, --主服务器端口

MASTER_USER = 'copy', --之前设置的从用户账户

MASTER_PASSWORD = 'XXXXXXX', -- 主服务器密码

MASTER_LOG_FILE = 'master1-bin.000048', -- 你需要确认日志文件名（参考第一步）

MASTER_LOG_POS = 190395145; -- 日志文件位置（参考第一步）

重新启动从服务器

START SLAVE;

查看从服务器状态

SHOW SLAVE STATUS\G;

若重启后还是存在问题，继续按照下面的方法逐步排查修复（若两个都是Yes,则表示主从已经配置好了，不用继续执行下面的步骤了）

执行下面的命令，查询是什么数据导致的失败（一般就是找不到记录，主键冲突等）

select * from performance_schema.replication_applier_status_by_worker\G;

上面就表示日志中主库有一个修改操作，然后操作同步至从库时，在从库中未找到对应记录，解决方法就是将主库的这条数据单独拷贝到从库。 但是不知道具体是哪一条，需要看第9步骤对应的命令

查看主库日志文件，定位错误

./mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /usr/local/mysql-8.0.31/data/3307/master1-bin.000048 | grep -A 20 "end_log_pos 190437215" --color

/usr/local/mysql-8.0.31/data/3307/master1-bin.000048：表示主库对应的错误日志位置 end_log_pos 190437215：表示日志中的位置偏移

可以看到@n对应的值就是数据表的字段，查询xxl_job.xxl_job_log表第一个字段值为“1689280”对应的记录，并拷贝至从库

再次执行4-7步骤，直至Slave_IO_Running=Yes, Slave_SQL_Running=Yes

多机一主一从解决方案：

查看主服务器的状态（登录mysql后操作）

SHOW MASTER STATUS;

主服务器数据备份（mysql安装bin目录下执行，不需要指定端口）

./mysqldump -u root -p --all-databases --single-transaction --quick --lock-tables=false > dumps.sql

–single-transaction： 在 InnoDB 引擎的事务中生成一致性快照，避免锁表 –quick：每次读取一行数据以降低内存消耗，适合大表。 –lock-tables=false：禁用表锁定，通常与 --single-transaction 一起使用

主服务器发送文件至从服务器（可手动拷贝）

scp /usr/local/mysql/bin/dumps.sql root@172.XXX.XXX.XXX:/usr/local/mysql/bin/

172.XXX.XXX.XXX：从服务器ip地址

从服务器备份数据文件删忽略GTID（删除GTID_PURGED对应的行）

sed -i '/GTID_PURGED/d' dumps.sql

从服务器数据拷贝（从服务器mysql安装bin目录下执行）

./mysql -u root -p --host=127.0.0.1 < dumps.sql

从服务器暂停后重置（需登录mysql操作）

-- 暂停从服务器

STOP SLAVE;

-- 从服务重置

RESET SLAVE ALL;

从服务器建立与主服务器绑定关系（需登录mysql操作）

CHANGE MASTER TO

MASTER_HOST = '主服务器ip',

MASTER_PORT = 3306, --主服务器端口

MASTER_USER = 'copy', --之前设置的从用户账户

MASTER_PASSWORD = 'XXXXXXX', -- 主服务器密码

MASTER_LOG_FILE = 'master1-bin.000048', -- 你需要确认日志文件名（参考第一步）

MASTER_LOG_POS = 190395145; -- 日志文件位置（参考第一步）

重新启动从服务器

START SLAVE;

查看从服务器状态

SHOW SLAVE STATUS\G;

若重启后还是存在问题，继续按照下面的方法逐步排查修复（若两个都是Yes,则表示主从已经配置好了，不用继续执行下面的步骤了）

执行下面的命令，查询是什么数据导致的失败（一般就是找不到记录、逐渐冲突等）

select * from performance_schema.replication_applier_status_by_worker\G;

上面就表示日志中主库有一个修改操作，然后操作同步至从库时，在从库中未找到对应记录，解决方法就是将主库的这条数据单独拷贝到从库。 但是不知道具体是哪一条，需要看第9步骤对应的命令

查看主库日志文件，定位错误

./mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /usr/local/mysql-8.0.31/data/3307/master1-bin.000048 | grep -A 20 "end_log_pos 190437215" --color

/usr/local/mysql-8.0.31/data/3307/master1-bin.000048：表示主库对应的错误日志位置 end_log_pos 190437215：表示日志中的位置偏移

可以看到@n对应的值就是数据表的字段，查询xxl_job.xxl_job_log表第一个字段值为“1689280”对应的记录，并拷贝至从库

再次执行6-9步骤，直至Slave_IO_Running=Yes, Slave_SQL_Running=Yes

【备注】：

单机配置和多机配置操作步骤大都一样，主要流程：

主数据库查询当前二进制文件和偏移数据，“FIle”，“Position”主数据库数据备份从服务器执行拷贝数据从服务器重新配置主从连接问题排查（根据从服务器的错误信息，到主服务器中对应的二进制文件检索详情）