[MySQL]-主从同步实战

森格 | 2022年12月14日

本文主要为在平时work中遇到的主从同步上的问题的处理，对其进行巩固总结。

一、场景介绍

​ 在一个风和日丽的下午4点半，突然就收到一个主从同步失败的提示，三两下打开从库一看，好嘛成这样了：

# 从库上查看同步状态mysql>show slave status\G;

报错内容如下：

Slave_IO_Running: YesLast_IO_Error:Got fatal error 1236 from master when reading data from binary log:'could not find next log;the first event 'mysql-bin.xxx' at xxx,the last event read from from './mysql-bin.xxx' at xxx,the last byte read from './mysql-bin.xx' at xxx'

这一大串报错，给我好一顿看，简单来说就是不能从主库的binlog读取数据了。

赶紧去主库的实例一看，好嘛，哪个给我binlog重置了啊，看着这mysql-bin.000001，内心拔凉。

转念一问周围队友，原配置主库的binlog过期时间为7天，正碰巧开发在灌入数据，主库的磁盘空间不够了。

那也没有办法，那就把主从同步再搭起来呗，准备准备，搞起。

当前的主从同步的主库的配置如下（从库除 server_id 之外均相同）：

# GTID 主从 Configserver_id = 3306  # 主从库的该ID一定要不一样gtid_mode = onenforce-gtid-consistency = truemaster_info_repository = tablerelay-log-info-repository = table# bin-log Configbinlog-format = row  # 选择row格式expire_logs_days = 3 #开启全局事务IDbinlog_rows_query_log_events = 1 # 记录event的同时，也记录原始SQL语句binlog_cache_size = 4Mmax_binlog_size = 512Mmax_binlog_cache_size = 1Grelay-log-info-repository = tablerelay-log-recovery = 1

二、搭建主从

经过上面队友的一顿binlog重置操作，我们终于来到了本文的重头戏：主从同步的搭建。

2.1 创建账号

我们先来创建一个同步账号，待会设置slave的时候要用到。

# 在主库创建账号mysql>create user 'sync'@'%' identified by 'Sync@123456';# 授予账号监视的权限mysql>grant replication slave on *.* to 'sync'@'%';# 刷新权限mysql>flush privileges;

到这里的话，我们的第一步算是完成了。

2.2 备份主库

注意：这里是在从库的实例进行的，防止影响主库的性能。

第二步就是把主库做下备份，当前，如果主库内容过大，可能导致前台进程跑的时间过长被断掉，这里使用 shell脚本 + nohup 后台执行。

shell 脚本如下：

#!bin/bashsource ~/.bash_profilemysqldump -uusername -p'password' -P3306 -h masterHost --single-transaction --skip-lock-tables --default-set=utf8mb4 --master-data=2 -A > ./dump_master.sql

参数说明：

  • –master-data=2：CHANGE MASTER语句会被写成SQL注释。

  • –single-transaction：在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态（仅仅适用于 Innodb存储引擎）。

Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does)

  • –default-set：设置默认字符集为 utf8mb4。
  • –skip-lock-tables：一次性锁定当前库的所有表。

nohup后台执行：

# 防止终端关闭，进行结束，在这里使用后台进行执行hostname>nohup sh dump_matser.sh &# 查看进程hostname>ps -ef | grep dump_master.sh

检查dump文件

hostname>tail -n10 ./dump_master.sql

有如下信息，就说明dump过程顺利结束（根据数据库大小，机器配置，速度可能会有差异）。

2.3 备份还原

这一步就比较简单了，同样还是用 shell脚本 + nohup 后台执行。

shell 脚本如下：

#!bin/bashsource ~/.bash_profilemysql -uusername -p'password' -P3306 -h slaveHost < ./dump_master.sql

nohup后台执行：

# 防止终端关闭，进行结束，在这里使用后台进行执行hostname>nohup sh dump_matser.sh &# 查看进程hostname>ps -ef | grep dump_master.sh

执行应该是会出现如下错误：

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

这里要把从库的GTID重置为空一下，才可以继续进行

# 登录从库mysql>reset master;

这样子就可以继续执行备份还原了。

2.4 配置主从

在这里又分为了两个章节，主要原因是因为博主当时是用的传统的基于LOG_FILE和LOG_POS来进行的，感觉就是很蹩脚，后面队友提示可以GTID来搞，不得不说，很爽，不用自己再去找点位和文件信息了。不过掌握两种方式是很有必要的，下面一起来看一下吧！

2.4.1 基于LOG_FILE LOG_POS

还记得我们上面设置的 –master-data=2 嘛，这里就要用到了。

# 文件太大打开很慢，这里用grep命令快速查看下hostname>grep 'CHANGE MASTER TO' ./dump_master.sql

该信息在dump下来的sql的前面位置，给大家看一下：

下面就好说了

# 进入从库一顿操作mysql>stop slave;# 把上面的信息对应下面语句就可以了mysql>CHANGE MASTER TO MASTER_HOST='host_master',MASTER_USER='sync',MASTER_PASSWORD='Sync@123456',master_port=3306,MASTER_LOG_FILE='xxx',MASTER_LOG_POS=xxx;# go on mysql>start slave;mysql>show slave master\G;

操作完就可以看到状态已经恢复正常了。

2.4.2 基于GTID

这里直接不需要我们再去查看LOG_FILE和LOG_POS，直接开搞：

# 进入从库一顿操作mysql>stop slave;# 执行下面语句mysql>CHANGE MASTER TO MASTER_HOST='host_master',MASTER_USER='sync',MASTER_PASSWORD='Sync@123456',master_port=3306,master_auto_position=1;# go on mysql>start slave;mysql>show slave master\G;

到这里我们的主从就重新搭建好啦！可喜可贺啊！下次可得把binlog过期时间重新设置下了。

2.4.3 传统模式 VS GTID模式

  • 对应传统模式，需要找到正确的二进制日志文件和位置，否则就无法正确复制。
  • 对于GTID模式，自动根据GTID来定位对应的二进制日志文件和位置，更准确地说，是自动寻找从库缺失的GTID SET对应的二进制日志记录，极大地降低了这些任务的复杂度。

以上是对两者的一个基本理解。

三、总结

本文主要介绍了，当主从同步发生故障时，要去如何发现问题所在，如何去重新进行主从的搭建，本文是采用的手动恢复的一个方式，欢迎各位大佬给与建议。