文章目录
主从同步
mysql主从同步是一种复制技术,它通过将主服务器上的数据的更改复制到一个或多个从服务器上,实现数据的自动同步
主从同步的核心原理是主库将对数据库的更改操作记录到binlog文件中,再将这些二进制日志文件发送到从库上并执行,实现数据同步
同步流程
主从同步主要涉及到3个线程,master上的dump线程,slave上的io线程和sql线程
dump线程会在主库有更新时将更新的事件记录到binlog中,并且主库会创建logdump线程通知从库需要进行同步操作
io线程用于向主库发起请求,主库会返回binlog文件的名称,位置,以及需要数据更新的位置,并将binlog保存在relay log中,relaylog相当于一个缓冲区
sql线程负责执行sql,当其检测到relaylog有更新时,就会将更新的内容从relaylog中取出并同步到slave数据库中
应用场景
- 数据备份与恢复:通过主从同步,将主库数据实时复制到从服务器上,在主库发生故障时,快速切换到从库
- 高可用、负载均衡:从库可接受读请求,减轻主库压力,并提供负载均衡,有多个从库提供读,在主库故障时,将从库提升为主库
- 分布式数据处理:将一个大任务拆分为多个小任务,在不同的从库上并行执行,提高任务执行效率
- 地理位置灵活性:通过在不同地区部署主从库,实现数据就近同步与访问,对于跨区域、全球化业务非常有用
同步方式
所有的同步方式都是基于binlog的,只是为了应对不同的场景而进行了拓展
- 基于二进制日志的复制:默认的同步方式
- 半同步复制:改进的同步方式,要求至少一个从库确认同步后才返回,提高了数据可靠性、一致性
- 组复制:多个服务器作为一个组,组内通过协议进行数据同步
- gtid复制:基于全局事务id进行复制,取代binlog基于偏移量定位的方式,降低了人为指定复制位置的风险,并会忽略已经执行过的事务,保证数据一致
binlog复制方式
- 基于语句statement:主库将数据变更的sql语句记录在binlog中,从库通过在本地执行sql实现同步
- 基于行row:将实际变更的数据记录在binlog中,能够正确的复制每一行,但是对于做了全表更新的操作会导致binlog文件很大
- 混合模式mixed:结合语句与行的方式,默认是基于语句复制。对于一些不能通过sql重现的操作则自动切换为行复制
建议: s t a t e m e n t 模式在生产环境中应该少用,因为主从同步时, n o w 以及 u u i d 等与时间有关的方法会产生不同的结果 \textcolor{red}{建议:statement模式在生产环境中应该少用,因为主从同步时,now以及uuid等与时间有关的方法会产生不同的结果} 建议:statement模式在生产环境中应该少用,因为主从同步时,now以及uuid等与时间有关的方法会产生不同的结果
主从的一些命令
#会显示主库的所有binlog文件名及其大小
show master logs;
#删除所有binlog文件,并清空日志索引文件,从000001开始记录日志文件 当主从运行时不能执行该命令,会导致从库找不到binlog而报错
reset master;
#删除从库的中继日志文件,使用前必须使用stop slave停止从库同步操作,从库重启前需要重新配置主库,如binlog的方式需要重新指定文件名和偏移量,以保证从正确的位置开始同步
reset slave;
#开启同步
start slave;
#停止同步,实际上就是关闭了io线程和sql线程
stop slave;
查看主从状态
show master status#查看主库状态,要在主库上执行
该命令会返回以下信息:
- File:当前正在写入的二进制日志文件名
- Position:当前正在写入的文件的位置
- Binlog_Do_DB:指定需要写入二进制日志文件的数据库
- Binlog_Ignore_DB:指定不需要写入二进制日志文件的数据库
- Executed_Gtid_Set:当前执行的全局事务id,开启gtid的配置才有
show slave status#查看从库状态,要在从库上执行
该命令会返回以下信息:
-
Slave_IO_State:从库io线程的状态
- connecting to master:正在连接主库,如果长时间连不上,请检查两库是否能ping通,ip、端口、连接主库的用户名、密码是否正确
- waiting for master to send event:连接成功,等待主库发送同步通知
- Queueing master event to the relay log:从库已经将binlog同步到relaylog中,以便sql线程执行数据同步操作
- .状态挺多…,具体可查看官网https://dev.mysql.com/doc/refman/5.7/en/replica-io-thread-states.html
-
Master_Host、Master_Port、Master_User:主库的ip,端口以及负责主从同步的用户
-
Connect_Retry:连接断开后,重新尝试连接的时间间隔,默认60s,可通过change master to语句设置
-
Master_Log_File:当前io线程读取的主库binlog文件名
-
Read_Master_Log_Pos:当前io线程读取的主库binlog文件的偏移位置
-
Relay_Log_File:当前sql线程正在读取的中继日志文件名
-
Relay_Log_Pos:读取sql线程正在读取的中继日志文件的位置
-
Relay_Master_Log_File:当前sql线程读取并执行relaylog中的事件,对应的是主服务器上的哪个binlog文件
-
Slave_IO_Running:io线程是否成功启动并连接到主库上
-
Slave_SQL_Running:sql线程是否成功启动
-
Replicate_Do_DB、Replicate_Ignore_DB、Replicate_Do_Table、Replicate_Ignore_Table、Replicate_Wild_Do_Table、Replicate_Wild_Ignore_Table:这些以replicate开头的信息表明在进行主从复制时忽略哪些库表,需要同步哪些库表
-
Last_Errno、Last_Error:sql线程读取中继日志的错误编号与错误信息
-
Skip_Counter:跳过sql执行步数
-
Exec_Master_Log_Pos:sql线程当前执行的事件,对应在master的相应binlog文件中的位置
-
Relay_Log_Space:所有现在存在的中继日志大小总和
-
Until_Condition:指定什么情况下从库停止进行同步
-
Until_Log_File、Until_Log_Pos:用于显示当前sql线程已同步到的日志文件名和偏移位置
-
Master_SSL_…:Master_SSL开头的配置项都是与加密相关的
-
Second_Behind_Master:slave当前时间戳和master记录该事件时的时间戳的差值(可以看作主从延迟时间)
-
Last_IO_Errno、Last_IO_Error、Last_SQL_Errno、Last_SQL_Error:io线程和sql线程的错误编号及错误消息
-
Replicate_Ignore_Server_Ids:从库忽略的主库id,不会对这些主库进行同步操作(一个从库对应多个主库的情况)
-
Master_Server_Id、Master_UUID、Master_Info_File:主库的server_id,uuid,从库保存主库相关内容的目录
-
SQL_Delay:从库延迟执行同步操作的时间,设置后主库执行的操作,从库会等待指定时间后才进行同步
CHANGE MASTER TO MASTER_DELAY = 3600; #设置延迟时间为3600s(1小时)
-
SQL_Remaining_Delay:当sql线程等待时,该选项表示执行下一个事件的时间差
-
Slave_SQL_Running_State:sql线程状态
-
Master_Retry_Count:当主从连接丢失时,从库尝试连接主库的重试次数
-
Master_Bind:在有多个网络接口时,使用哪一个网络接口
-
Last_IO_Error_Timestamp、Last_SQL_Error_Timestamp:最后一次io或sql线程错误时的时间戳
-
Retrieved_Gtid_Set:io线程获取到的Gtid集合
-
Executed_Gtid_Set:sql线程执行过的Gtid集合
一些错误场景
1.在主从模式下,删除从库中的一条数据,然后在主库中对该数据进行更新操作。使用show slave status发现1032错误,显示在从库的表中找不到该记录
解决:先停掉从库同步操作,指定跳过该错误事件
stop slave;
set global sql_slave_skip_counter=1;
start slave;
如果是基于gtid构建的主从,则需要跳过报错的gtid
stop slave sql_thread;
set gtid_next='974060d4-5809-11ec-a945-78aa82c76790:20';
begin;
commit;
set gtid_next='automatic';
start slave sql_thread;
2.1062主键冲突异常
在从库中添加的数据之后,在主库中添加数据,在主从同步时发现主库数据的主键在从库中已存在导致主键冲突
解决:反向处理sql,如果是因为从库插入的数据导致主键冲突,则删除该数据即可
server_uuid
mysql5.6后,用128位的uuid(32个16进制字符)替代了32位的server_id,因为server_id依赖于人工配置,有可能产生冲突。
在首次启动mysql时会执行generate_server_uuid自动生成一个uuid,并保存在auto.cnf文件中,auto.cnf目前唯一的作用就是存储该uuid,当mysql重启时,会读取auto.cnf文件中的uuid作为服务id
gtid
全局事务id,格式为uuid:事务id,其中,事务id是从1开始自增的
在mysql中,gtid的更多表现形式为gtids,表示一个gtid组成的集合
3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5:11-18, #表示事务1到5,11到18的集合
2C256447-3F0D-431B-9A12-575BB20C1507:1-27#表示事务1到27的集合
存储结构
gtid在mysql中存储结构类似于一个hashmap,其中key是128位uuid映射成的32位整型数字,value是由事务id区间构成的链表,这些事务id区间称为interval
基于gtid的主从同步
引入gtid的首要目的,是为了保证从库在同步时不会执行相同的事务操作,其次,是为了以gtid代替使用binlog文件名+偏移量的定位方式
基于gtid主从同步工作流程
1.当一个事务在主端执行并提交,会生成一个gtid一同记录到binlog中
2.binlog传输到从库,并存储到中继日志里,从库读取relaylog中的gtid并设置到GTID_NEXT属性中,表示下一个要执行的事务
3.sql线程从中继日志拿到gtid,对比从库的binlog是否已存在该gtid
4.如果有记录,则该事务已经执行过了,从库会忽略
5.如果没记录,从库会执行该gtid事务,并记录到自己的binlog中,在执行前会检查其他session是否持有该gtid,确保事务不会重复执行
主从的一些配置
master_info_repository:从库如何记录主库的状态,可为table或file,如果是file,则会创建master.info文件,如果为table,则会在mysql库中创建slave_master_info的表
sync_master_info:从库刷新主库状态的方式,是一个数值,与master_info_repository有关。如果master_info_repository为file,如果sync_master_info设置为n,当n>0,则slave会在n个事件后使用fdatasync()将主库状态更新到master.info文件中,如果n=0,则会将主库状态写入操作系统缓存中,等待操作系统同步。如果master_info_repository为table,当n>0,则slave在n个事件后更新slave_master_info表,当n=0,则表永远不会更新。
relay_log_info_repository:中继日志同步的位置信息记录,可为table或file,table则创建mysql库下的slave_relay_log_info表,file则创建relay-log.info文件
sync_relay_log_info:与sync_master_info同理
relay_log_recovery:on或off,在从库宕机且relaylog损坏情况下,主库日志已经传到了从库,从库还没来得及应用,开启该配置项,从库会自动放弃未执行的relaylog,并生成一个新的relaylog,将从库的io线程的position重新指向新的relaylog,并将sql线程的position退回到与io线程一致,重新开始同步,这样事务不会丢失
replica_skip_errors:跳过主从复制错误,可为off、all、ErorCode、ddl_exist_errors
aylog损坏情况下,主库日志已经传到了从库,从库还没来得及应用,开启该配置项,从库会自动放弃未执行的relaylog,并生成一个新的relaylog,将从库的io线程的position重新指向新的relaylog,并将sql线程的position退回到与io线程一致,重新开始同步,这样事务不会丢失
replica_skip_errors:跳过主从复制错误,可为off、all、ErorCode、ddl_exist_errors