????上篇文章介紹了單機(jī)環(huán)境下的MySQL主從異步復(fù)制和主從半同步復(fù)制的搭建過(guò)程。搭建過(guò)程很簡(jiǎn)單，但是在實(shí)際使用過(guò)程中，更多的是解決問題，本篇文章將介紹一下MySQL主從復(fù)制中常見的問題以及如何定位問題和如何解決問題。

1、可能的原因如下

(1)主從服務(wù)器處于不同的網(wǎng)絡(luò)之中，由于網(wǎng)絡(luò)延遲導(dǎo)致；

(2)主從服務(wù)器的硬件配置不同，從服務(wù)器的硬件配置(包括內(nèi)存，CPU，網(wǎng)卡等)遠(yuǎn)低于主服務(wù)器；

(3)主庫(kù)上有大量的寫入操作，導(dǎo)致從庫(kù)無(wú)法實(shí)時(shí)重放主庫(kù)上的binlog；

(4)主庫(kù)上存在著大事務(wù)操作或者慢SQL，導(dǎo)致從庫(kù)在應(yīng)用主庫(kù)binlog的過(guò)程過(guò)慢，形成延遲；

(5)數(shù)據(jù)庫(kù)實(shí)例的參數(shù)配置問題導(dǎo)致，如：從庫(kù)開啟了binlog，或者配置了每次事務(wù)都去做刷盤操作；

2、主從同步延遲問題判斷

2.1、根據(jù)從庫(kù)上的狀態(tài)參數(shù)判斷

mysql-server-3307>?SHOW?SLAVE?STATUS?\G

????在輸出結(jié)果中找到Seconds_Behind_Master參數(shù)，這個(gè)參數(shù)表示的是從庫(kù)上的IO線程和SQL線程相差的時(shí)間，然后根據(jù)該參數(shù)值判斷，這個(gè)值只是初步判斷，不能由這個(gè)值來(lái)下結(jié)論，有如下幾種情況：

0：表示無(wú)延遲，理想狀態(tài)；

NULL：表示從庫(kù)上的IO線程和SQL線程中，有某一個(gè)線程出現(xiàn)問題，可以再次查看Slave_IO_Running和Slave_SQL_Running的值是否都為Yes；

大于0：表示主從已經(jīng)出現(xiàn)延遲，這個(gè)值越大，表示從庫(kù)和主庫(kù)之間的延遲越嚴(yán)重；

小于0：這個(gè)值在官方文檔中沒有說(shuō)明，通常不會(huì)出現(xiàn)。如果出現(xiàn)，那恭喜你中獎(jiǎng)了，撞見MySQL的bug了；

2.2、根據(jù)主從庫(kù)上面當(dāng)前應(yīng)用的二進(jìn)制日志文件名稱或者重放日志的位置來(lái)判斷

2.2.1、同時(shí)打開兩個(gè)MySQL的命令行窗口，分別打開主庫(kù)和從庫(kù)，在第一個(gè)窗口上執(zhí)行查看主庫(kù)當(dāng)前狀態(tài)的命令

mysql-server-3306>?SHOW?MASTER?STATUS?\G
***************************?1.?row?***************************
?????????????File:?mysql-bin.000017
?????????Position:?120
?????Binlog_Do_DB:?
?Binlog_Ignore_DB:?
Executed_Gtid_Set:?
1?row?in?set?(0.00?sec)

(1)在第二個(gè)從庫(kù)的命令行窗口執(zhí)行如下命令

mysql-server-3307>?SHOW?SLAVE?STATUS?\G
***************************?1.?row?***************************
???????????????Slave_IO_State:?Waiting?for?master?to?send?event
???????????????...
????????????????Connect_Retry:?60
??????????????Master_Log_File:?mysql-bin.000017
??????????Read_Master_Log_Pos:?120
???????????????Relay_Log_File:?relay-log.000016
????????????????Relay_Log_Pos:?283
????????Relay_Master_Log_File:?mysql-bin.000017
?????????????Slave_IO_Running:?Yes
????????????Slave_SQL_Running:?Yes
????????????...
???????????????????Last_Errno:?0
???????????????????Last_Error:?
?????????????????Skip_Counter:?0
??????????Exec_Master_Log_Pos:?120
??????????????Relay_Log_Space:?613
??????????????Until_Condition:?None
???????????????Until_Log_File:?
????????????????Until_Log_Pos:?0
????????????????...
????????Seconds_Behind_Master:?0
????????...
??Replicate_Ignore_Server_Ids:?
?????????????Master_Server_Id:?3
??????????????????Master_UUID:?2dbbf79b-5d9f-11e8-8004-000c29e28409
?????????????Master_Info_File:?/mysql_data/3307/data/master.info
????????????????????SQL_Delay:?0
??????????SQL_Remaining_Delay:?NULL

(2)比較從庫(kù)上的Master_Log_File和Relay_Master_Log_File文件之間是否有差異

有差異，則說(shuō)明主從延遲很嚴(yán)重；

如果沒有差異，則比較Read_Master_Log_Pos和Exec_Master_Log_Pos的差異，這倆參數(shù)分別表示從庫(kù)當(dāng)前讀取到的主庫(kù)的二進(jìn)制日志文件位置點(diǎn)和已經(jīng)執(zhí)行到的位置點(diǎn)；

如果上述輸出都沒有差異，可以通過(guò)主庫(kù)上"show master status"和從庫(kù)上"show slave status"的結(jié)果作比較。主要比較主庫(kù)的"File"和從庫(kù)的"Master_Log_File"，主庫(kù)上的"Position"和從庫(kù)上的"Read_Master_Log_Pos"；

3、主從延遲解決辦法

3.1、判斷是否由于網(wǎng)絡(luò)導(dǎo)致

????方法：測(cè)試主從庫(kù)之間的網(wǎng)絡(luò)延遲，比如測(cè)試ping延遲。同時(shí)可以檢查主從同步的時(shí)候是否使用了主庫(kù)的域名來(lái)同步，而域名解析速度可能會(huì)特別慢。或者使用其他測(cè)試工具；

3.2、判斷是否由于硬件環(huán)境導(dǎo)致

????方法：確認(rèn)主從庫(kù)的硬件配置是否相差較大，如果配置參數(shù)相差較大，可以排查從庫(kù)上的CPU，內(nèi)存，IO使用率來(lái)判斷是否因?yàn)橛布渲脤?dǎo)致；

3.3、判斷是否在主庫(kù)上有大量的DML操作

????方法：可以在主庫(kù)上通過(guò)"show full processlist"命令查看當(dāng)前正在執(zhí)行的sql，查看是否有大量正在執(zhí)行的SQL，或者觀察主庫(kù)的CPU和內(nèi)存使用率，判斷是否有高并發(fā)操作；

3.4、判斷是否有慢SQl，可以在主庫(kù)上臨時(shí)打開慢SQL記錄，臨時(shí)打開方法如下

#開啟慢SQL功能并查看是否生效
mysql-server-3306>?SET?@@GLOBAL.slow_query_log?=?ON;
mysql-server-3306>?SHOW?VARIABLES?LIKE?'slow_query_log';
#設(shè)置慢SQL的時(shí)間并查看是否生效，單位為s，表示大于多少秒的SQL會(huì)被記錄
mysql-server-3306>?SET?@@GLOBAL.long_query_time?=?5;
mysql-server-3306>?SHOW?VARIABLES?LIKE?'long_query_time';
#設(shè)置慢SQL記錄日志路徑并查看是否生效。注意，這個(gè)目錄必須對(duì)MySQL用戶有讀寫權(quán)限
mysql-server-3306>?SET?@@GLOBAL.slow_query_log_file?=?'/mysql_data/mysql-slow.log';
mysql-server-3306>?SHOW?VARIABLES?LIKE?'slow_query_log_file';

3.5、檢查從服務(wù)器參數(shù)配置是否合理

(1)查看從庫(kù)是否開啟了binlog日志，從庫(kù)上執(zhí)行如下命令查看

mysql-server-3307>?SHOW?VARIABLES?LIKE?'log_bin';

????如果開啟了binlog日志，而且從庫(kù)未充當(dāng)其他庫(kù)的主庫(kù)時(shí)，可以將從庫(kù)上的binlog關(guān)閉，否則會(huì)增加從庫(kù)負(fù)擔(dān)，每次重放完成主庫(kù)的binlog還要記錄到自身的binlog

(2)查看從庫(kù)上的sync_binlog參數(shù)的值，這個(gè)參數(shù)表示的是事務(wù)提交多少次之后，由MySQL來(lái)將binlog_cache中的數(shù)據(jù)刷新到磁盤，有以下幾種值：

0：表示事務(wù)提交之后，MySQL不做刷新binlog_cache到磁盤的操作，而是由操作系統(tǒng)來(lái)定時(shí)自動(dòng)完成刷盤操作，這種操作對(duì)性能損耗最少，但是也最不安全；

n：表示提交n次事務(wù)之后，由MySQL將binlog_cache中的數(shù)據(jù)刷新到磁盤，如果開啟，會(huì)對(duì)性能有一定程度的損耗。所以，從庫(kù)上如果延遲很嚴(yán)重，可以考慮將該參數(shù)的值設(shè)為0；

mysql-server-3307>?SET?@@GLOBAL.sync_binlog?=?0;
mysql-server-3307>?SHOW?VARIABLES?LIKE?'sync_binlog';
+---------------+-------+
|?Variable_name?|?Value?|
+---------------+-------+
|?sync_binlog???|?0?????|
+---------------+-------+
1?row?in?set?(0.00?sec)

(3)如果從庫(kù)中要同步的數(shù)據(jù)庫(kù)使用的是InnoDB存儲(chǔ)引擎，可以查看innodb_flush_log_at_trx_commit參數(shù)。這個(gè)參數(shù)表示事務(wù)執(zhí)行完成之后，多久的頻率刷新一次日志到磁盤上，可用的值有如下幾種：

0：表示MySQL會(huì)將日志緩沖區(qū)中的數(shù)據(jù)每秒一次地寫入日志文件中，并且日志文件的刷盤操作同時(shí)進(jìn)行。該模式下在事務(wù)提交的時(shí)候，不會(huì)主動(dòng)觸發(fā)寫入磁盤的操作，效率最高，但是安全性也比較低，可能會(huì)丟失數(shù)據(jù)；

1：每一次事務(wù)提交都需要把日志寫入磁盤，這個(gè)過(guò)程是特別耗時(shí)的操作；

2：每一次事務(wù)提交之后，不會(huì)自動(dòng)觸發(fā)日志刷盤的操作，而是由操作系統(tǒng)來(lái)決定什么時(shí)候來(lái)做刷新日志的操作，在操作系統(tǒng)掛了的情況下才會(huì)丟失數(shù)據(jù)；如果在主從延遲非常嚴(yán)重的情況下，可以將從庫(kù)的該參數(shù)設(shè)置為0，以提高從庫(kù)上重放主庫(kù)二進(jìn)制日志的效率。

mysql-server-3307>?SET?@@GLOBAL.innodb_flush_log_at_trx_commit?=?0;
mysql-server-3307>?SHOW?VARIABLES?LIKE?'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
|?Variable_name??????????????????|?Value?|
+--------------------------------+-------+
|?innodb_flush_log_at_trx_commit?|?0?????|
+--------------------------------+-------+
1?row?in?set?(0.00?sec)

注意：上述涉及到修改MySQL數(shù)據(jù)庫(kù)實(shí)例的操作中，修改之后會(huì)立刻生效，但是重啟實(shí)例之后，會(huì)失效，如果要永久修改，則需要編輯mysql配置文件，然后重啟。

近期精彩回顧：

《FactoryBean和BeanFactory是個(gè)啥？》

《聊聊Spring的自定義標(biāo)簽》

《Spring標(biāo)簽解析過(guò)程源碼分析》

《Spring容器初始化可真不容易》

《SPI機(jī)制及使用場(chǎng)景》

《聊聊MySQL的索引》

《MySQL主從同步》

常駐內(nèi)容：

源碼搭建：《Spring5.1.x源碼環(huán)境搭建》

注釋版源碼傳送門：進(jìn)入公眾號(hào)點(diǎn)擊底部"獲取源碼"資料即可獲取

關(guān)注菜鳥封神記，定期分享技術(shù)干貨！

點(diǎn)贊和在看是最大的支持，感謝↓↓↓

總結(jié)

以上是生活随笔為你收集整理的mysql 写入慢_MySQL主从，你遇到过哪些问题？的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。