mysql 主从同步问题小结

最新推荐文章于 2025-10-27 21:35:48 发布

转载最新推荐文章于 2025-10-27 21:35:48 发布 · 133 阅读

文章标签：

#数据库

本文介绍了MySQL主从复制的基本原理，包括三个主要步骤：记录二进制日志、复制二进制日志事件到中继日志以及重做中继日志中的事件。此外，还详细描述了一次主从不同步的问题发现、诊断及解决过程。

复制如何工作

整体上来说，复制有3个步骤：

       (1)   master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
       (2)   slave将master的binary log events拷贝到它的中继日志(relay log)；
        (3)   slave重做中继日志中的事件，将改变反映它自己的数据。

下图描述了复制的过程：

该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务

复制原理：

1. Slave 上面的IO线程连接上 Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;

　　2. Master 接收到来自 Slave 的 IO 线程的请求后，通过负责复制的 IO 线程根据请求信息读取指定日志指定位置之后的日志信息，返回给 Slave 端的 IO 线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 Binary Log 中的位置;

　　3. Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master- info文件中，以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”

　　4. Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后，会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query。这样，实际上就是在 Master 端和 Slave 端执行了同样的 Query，所以两端的数据是完全一样的。

一次主从库不同步的解决过程：

发现问题：

我们知道从库主要是用于读压力比较大的应用的数据库端廉价扩展解决方案，作为经验不足DBA来说，当主从不同步时，并不会立马发现，直到开发人员反映ETL没有把oracle的数据传进mysql，可是我明明查着是能传过来的，再进一步讨论，发现我是在master查的，他是在slave查的，那么问题来了，mysql主从不同步。

既然主从同步主要是靠slave的 IO 和SQL 两个线程完成，那么很有可能是这两个线程的原因，

诊断问题：

在slave端查看状态：红色字部分，发现确实是SQL线程有问题，

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.4.106

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: log.000456

Read_Master_Log_Pos: 5037482

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 97848

Relay_Master_Log_File: log.000456

Slave_IO_Running: Yes

Slave_SQL_Running: no

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table: search.%,bid.%,en_US_bid.%,common.%,goods.%,jsprun.%,jsprunnew.%,jspxcms304.%,mkyongjava.%,shopxx.%,booth.%,discuz_test.%,zixun_en_test.%,zixun_ar_test.%,zixun_cn_test.%,huizhan_test.%,help_test.%,search_test.%,bbs.%,search_lj.%,huizhan_ar_test.%,huizhan_de_test.%,huizhan_en_test.%,huizhan_es_test.%,huizhan_fr_test.%,huizhan_in_test.%,huizhan_it_test.%,huizhan_ja_test.%,huizhan_ko_test.%,huizhan_ms_test.%,huizhan_pt_test.%,huizhan_ru_test.%,huizhan_th_test.%,huizhan_tw_test.%,huizhan_vi_test.%,zixun_de_test.%,zixun_es_test.%,zixun_fr_test.%,zixun_in_test.%,zixun_it_test.%,zixun_ja_test.%,zixun_ko_test.%,zixun_ms_test.%,zixun_pt_test.%,zixun_ru_test.%,zixun_th_test.%,zixun_tw_test.%,zixun_vi_test.%,search_testqa.%,bidtest_JA_JP.%,bidtest_DE_DE.%,bidtest_IN_ID.%,bidtest_ES_ES.%,bidtest_KO_KR.%,bidtest_TH_TH.%,bidtest_FR_FR.%,bidtest_AR_SA.%,bidtest_ZH_TW.%,bidtest_IT_IT.%,bidtest_EN_US.%,bidtest_MS_MY.%,bidtest_RU_RU.%,bidtest_PT_PT.%,bidtest_VI_VN.%,bidtest_ZH_CN.%,boothtest_JA_JP.%,boothtest_DE_DE.%,boothtest_IN_ID.%,boothtest_ES_ES.%,boothtest_KO_KR.%,boothtest_TH_TH.%,boothtest_FR_FR.%,boothtest_AR_SA.%,boothtest_ZH_TW.%,boothtest_IT_IT.%,boothtest_EN_US.%,boothtest_MS_MY.%,boothtest_RU_RU.%,boothtest_PT_PT.%,boothtest_VI_VN.%,boothtest_ZH_CN.%

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 5037482

Relay_Log_Space: 97848

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

1 row in set (0.00 sec)

解决问题：既然sql线程关掉了，那就重启

mysql>stop slave #关掉slave服务也就是关闭主从同步

mysql>change master to master_host='192.168.4.106',master_user='root',master_password='~1~#dev',master_port=3306,master_log_file='log.000456',master_log_pos=4939863; #重新设置master库信息

mysql>start slave #开启slave服务