MySQL主从同步及gtid简介_mysql gtid主从

主从同步

mysql主从同步是一种复制技术，它通过将主服务器上的数据的更改复制到一个或多个从服务器上，实现数据的自动同步

主从同步的核心原理是主库将对数据库的更改操作记录到binlog文件中，再将这些二进制日志文件发送到从库上并执行，实现数据同步

同步流程

主从同步主要涉及到3个线程，master上的dump线程，slave上的io线程和sql线程

dump线程会在主库有更新时将更新的事件记录到binlog中，并且主库会创建logdump线程通知从库需要进行同步操作

io线程用于向主库发起请求，主库会返回binlog文件的名称，位置，以及需要数据更新的位置，并将binlog保存在relay log中，relaylog相当于一个缓冲区

sql线程负责执行sql，当其检测到relaylog有更新时，就会将更新的内容从relaylog中取出并同步到slave数据库中

应用场景

• 数据备份与恢复：通过主从同步，将主库数据实时复制到从服务器上，在主库发生故障时，快速切换到从库
• 高可用、负载均衡：从库可接受读请求，减轻主库压力，并提供负载均衡，有多个从库提供读，在主库故障时，将从库提升为主库
• 分布式数据处理：将一个大任务拆分为多个小任务，在不同的从库上并行执行，提高任务执行效率
• 地理位置灵活性：通过在不同地区部署主从库，实现数据就近同步与访问，对于跨区域、全球化业务非常有用

同步方式

所有的同步方式都是基于binlog的，只是为了应对不同的场景而进行了拓展

1. 基于二进制日志的复制：默认的同步方式
2. 半同步复制：改进的同步方式，要求至少一个从库确认同步后才返回，提高了数据可靠性、一致性
3. 组复制：多个服务器作为一个组，组内通过协议进行数据同步
4. gtid复制：基于全局事务id进行复制，取代binlog基于偏移量定位的方式，降低了人为指定复制位置的风险，并会忽略已经执行过的事务，保证数据一致

binlog复制方式

1. 基于语句statement：主库将数据变更的sql语句记录在binlog中，从库通过在本地执行sql实现同步
2. 基于行row：将实际变更的数据记录在binlog中，能够正确的复制每一行，但是对于做了全表更新的操作会导致binlog文件很大
3. 混合模式mixed：结合语句与行的方式，默认是基于语句复制。对于一些不能通过sql重现的操作则自动切换为行复制

$建议：statement模式在生产环境中应该少用，因为主从同步时，now以及uuid等与时间有关的方法会产生不同的结果$

主从的一些命令

#会显示主库的所有binlog文件名及其大小
show master logs;
#删除所有binlog文件，并清空日志索引文件，从000001开始记录日志文件 当主从运行时不能执行该命令，会导致从库找不到binlog而报错
reset master;
#删除从库的中继日志文件，使用前必须使用stop slave停止从库同步操作，从库重启前需要重新配置主库，如binlog的方式需要重新指定文件名和偏移量，以保证从正确的位置开始同步
reset slave;
#开启同步
start slave;
#停止同步，实际上就是关闭了io线程和sql线程
stop slave;
1
2
3
4
5
6
7
8
9
10

查看主从状态

show master status#查看主库状态，要在主库上执行
1

该命令会返回以下信息：

1. File：当前正在写入的二进制日志文件名
2. Position：当前正在写入的文件的位置
3. Binlog_Do_DB：指定需要写入二进制日志文件的数据库
4. Binlog_Ignore_DB：指定不需要写入二进制日志文件的数据库
5. Executed_Gtid_Set：当前执行的全局事务id，开启gtid的配置才有

show slave status#查看从库状态，要在从库上执行
1

该命令会返回以下信息：

1. 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

2. Master_Host、Master_Port、Master_User：主库的ip，端口以及负责主从同步的用户

3. Connect_Retry：连接断开后，重新尝试连接的时间间隔，默认60s，可通过change master to语句设置

4. Master_Log_File：当前io线程读取的主库binlog文件名

5. Read_Master_Log_Pos：当前io线程读取的主库binlog文件的偏移位置

6. Relay_Log_File：当前sql线程正在读取的中继日志文件名

7. Relay_Log_Pos：读取sql线程正在读取的中继日志文件的位置

8. Relay_Master_Log_File：当前sql线程读取并执行relaylog中的事件，对应的是主服务器上的哪个binlog文件

9. Slave_IO_Running：io线程是否成功启动并连接到主库上

10. Slave_SQL_Running：sql线程是否成功启动

11. Replicate_Do_DB、Replicate_Ignore_DB、Replicate_Do_Table、Replicate_Ignore_Table、Replicate_Wild_Do_Table、Replicate_Wild_Ignore_Table：这些以replicate开头的信息表明在进行主从复制时忽略哪些库表，需要同步哪些库表

12. Last_Errno、Last_Error：sql线程读取中继日志的错误编号与错误信息

13. Skip_Counter：跳过sql执行步数

14. Exec_Master_Log_Pos：sql线程当前执行的事件，对应在master的相应binlog文件中的位置

15. Relay_Log_Space：所有现在存在的中继日志大小总和

16. Until_Condition：指定什么情况下从库停止进行同步

17. Until_Log_File、Until_Log_Pos：用于显示当前sql线程已同步到的日志文件名和偏移位置

18. Master_SSL_…：Master_SSL开头的配置项都是与加密相关的

19. Second_Behind_Master：slave当前时间戳和master记录该事件时的时间戳的差值（可以看作主从延迟时间）

20. Last_IO_Errno、Last_IO_Error、Last_SQL_Errno、Last_SQL_Error：io线程和sql线程的错误编号及错误消息

21. Replicate_Ignore_Server_Ids：从库忽略的主库id，不会对这些主库进行同步操作（一个从库对应多个主库的情况）

22. Master_Server_Id、Master_UUID、Master_Info_File：主库的server_id，uuid，从库保存主库相关内容的目录

23. SQL_Delay：从库延迟执行同步操作的时间，设置后主库执行的操作，从库会等待指定时间后才进行同步

    CHANGE MASTER TO MASTER_DELAY = 3600; #设置延迟时间为3600s(1小时)
    1

24. SQL_Remaining_Delay：当sql线程等待时，该选项表示执行下一个事件的时间差

25. Slave_SQL_Running_State：sql线程状态

26. Master_Retry_Count：当主从连接丢失时，从库尝试连接主库的重试次数

27. Master_Bind：在有多个网络接口时，使用哪一个网络接口

28. Last_IO_Error_Timestamp、Last_SQL_Error_Timestamp：最后一次io或sql线程错误时的时间戳

29. Retrieved_Gtid_Set：io线程获取到的Gtid集合

30. Executed_Gtid_Set：sql线程执行过的Gtid集合

一些错误场景

1.在主从模式下，删除从库中的一条数据，然后在主库中对该数据进行更新操作。使用show slave status发现1032错误，显示在从库的表中找不到该记录

解决：先停掉从库同步操作，指定跳过该错误事件

stop slave;
set global sql_slave_skip_counter=1;
start slave;
1
2
3

如果是基于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;
1
2
3
4
5
6

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的集合
1
2

存储结构

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