[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对应的二进制日志记录,极大地降低了这些任务的复杂度。

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


三、总结

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