[MySQL]-主从同步实战

时间:2022-12-16 21:00:49

[MySQL]-主从同步实战

森格 | 2022年12月14日

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


一、场景介绍

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

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

报错内容如下:

Slave_IO_Running: Yes
Last_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 主从 Config
server_id = 3306  # 主从库的该ID一定要不一样
gtid_mode = on
enforce-gtid-consistency = true
master_info_repository = table
relay-log-info-repository = table

# bin-log Config
binlog-format = row  # 选择row格式
expire_logs_days = 3 #开启全局事务ID
binlog_rows_query_log_events = 1 # 记录event的同时,也记录原始SQL语句
binlog_cache_size = 4M
max_binlog_size = 512M
max_binlog_cache_size = 1G
relay-log-info-repository = table
relay-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/bash
source ~/.bash_profile
mysqldump -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过程顺利结束(根据数据库大小,机器配置,速度可能会有差异)。

[MySQL]-主从同步实战

2.3 备份还原

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

shell 脚本如下:

#!bin/bash
source ~/.bash_profile
mysql -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]-主从同步实战

下面就好说了

# 进入从库一顿操作
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对应的二进制日志记录,极大地降低了这些任务的复杂度。

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


三、总结

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