mysql5.6,5.7 主從不同步解決辦法

<code>MySQL Binlog 【ROW】和【STATEMENT】選擇</code>

<code>對比Row和Statement：R比S産生的日志量大5.5倍，網卡流量高4~5倍，cpu稍微忙了10個百分點。在複制過程中，從均沒有延遲。因為SQL過濾條件WHERE 後面的字段利用好索引，ROW和STATEMENT模式下效果一樣。要是沒有利用好索引，則：</code>

<code>STATEMENT下：在主上執行（3~5s）一條，從上也是需要這個時間，并且出現延遲。（Seconds_Behind_Master）。本來就單線程的，導緻從的可用性更差。</code>

<code>ROW下：在主上執行（3~5s）一條，正常情況下每張表都有主鍵，是以按照ROW的記錄的SQL格式，不會出現對這類sql的延遲。除非極端情況下更新一張沒有主鍵甚至沒有任何索引的表。</code>

<code>對比發現：在執行此類sql的時候，在STATEMENT下面，（利用好索引）主和從的各個開銷都很小，網絡流量都不大。而在ROW下面：因為日志産生量就很大，導緻在複制期間網卡流量就很大：12M。網卡流量：【1：10000】，日志大小：【1：2000000】，CPU空閑：【80：20】。這個隻限于這個例子，看範圍大小和表字段的大小。總之在網絡和磁盤開銷上面比較，他們差距了好幾個數量級。</code>

<code>小結2：</code>

<code>    </code><code>對于更新單條的sql語句，在STATEMENT和ROW下</code>

<code>1，CPU消耗差距不大，都需要執行這麼sql。消耗 R=S</code>

<code>2，磁盤寫和網絡傳輸上，因為ROW記錄的格式的原因。消耗 R&gt;S</code>

<code>3，SQL效率來看，合理利用索引的更新，效率差距不大，不合理利用索引的更新，效率 R&gt;S</code>

<code>4，日志檔案大小上，因為都需要記錄這麼多SQL，但是由于R和S的記錄格式不一樣，大小 R&gt;S</code>

<code>    </code><code>對于執行一個大範圍的sql語句，在STATEMENT和ROW下</code>

<code>1，CPU上，主上隻要執行一條SQL，而從上需要執行N條，消耗 R&gt;S</code>

<code>2，磁盤寫和網絡傳輸上，因為ROW記錄的格式的原因。消耗R&gt;S，看範圍條件，大的話，差距巨大。</code>

<code>3，日志檔案大小上，主記錄一條，從記錄N條，并且還由于R和S的記錄格式不一樣，R&gt;S，差距巨大。</code>

<code>從上面的分析得出，STATEMENT要比ROW劃算。要是使用STATEMENT沒有任何問題的話，就推薦使用STATEMENT</code><code>/MIXED</code><code>格式記錄二進制日志</code>

<code>http:</code><code>//www</code><code>.cnblogs.com</code><code>/zhoujinyi/archive/2013/01/15/2836131</code><code>.html?utm_source=tuicool&amp;utm_medium=referral</code>

<code>mysql5.7 主從不同步GTID_NEXT</code>

<code>5.6的解決方案</code>

<code>http:</code><code>//suifu</code><code>.blog.51cto.com</code><code>/9167728/1845457</code>

<code>end_log_pos 有了它，根據pos值，直接就能找到，找到delete那條資料，反做（變成insert）</code>

<code>原因</code>

<code>我在從庫上操作了create語句，然後主從不同步了，是以解決辦法就是跳過已經執行的sql</code>

<code>Last_SQL_Errno: 1050</code>

<code>Last_SQL_Error: Coordinator stopped because there were error(s) </code><code>in</code> <code>the worker(s). The most recent failure being: Worker 0 failed executing transaction </code><code>'b30dcce8-3395-11e6-902b-0050569d58f6:38435158'</code> <code>at master log mysql-bin.001313, end_log_pos 19583512. See error log and</code><code>/or</code> <code>performance_schema.replication_applier_status_by_worker table </code><code>for</code> <code>more</code> <code>details about this failure or others, </code><code>if</code> <code>any.</code>

<code>解決 </code>

<code>1.master</code>

<code>mysqlbinlog mysql-bin.001313 (end_log_pos 19583512 對應</code><code>#170314 19:28:02 server id 1  end_log_pos 19583512 CRC32 0xed572feb  Quer)</code>

<code>mysqlbinlog mysql-bin.001313|</code><code>grep</code> <code>-C 10 </code><code>"end_log_pos 19583512"</code>

<code>說明這行有問題</code>

<code>SET @@SESSION.GTID_NEXT= </code><code>'b30dcce8-3395-11e6-902b-0050569d58f6:38435158'</code><code>/*!*/;</code>

<code># at 19581673</code>

<code>#170314 19:28:02 server id 1  end_log_pos 19583512 CRC32 0xed572feb   Query   thread_id=23586111  exec_time=0 error_code=0</code>

<code>2.slave</code>

<code>stop slave;  </code>

<code>set</code> <code>@@session.gtid_next=</code><code>'b30dcce8-3395-11e6-902b-0050569d58f6:38435158'</code><code>;</code>

<code>begin;</code>

<code>commit;</code>

<code>set</code> <code>@@session.gtid_next=automatic;</code>

<code>start slave;</code>

<code>3.show slave status\G;</code>

<code>因為拖到早上解決，是以看下落後多少資料</code>

<code>------Master_Log_File: mysql-bin.001319    1</code>

<code>--Read_Master_Log_Pos: 2532411</code>

<code>Relay_Log_Pos: 27411621</code>

<code>------Relay_Master_Log_File: mysql-bin.001313   1(明顯不同)</code>

<code>--Exec_Master_Log_Pos: 27411408</code>

<code>Retrieved_Gtid_Set: b30dcce8-3395-11e6-902b-0050569d58f6:38411883-38616860</code>

<code>Executed_Gtid_Set: 9b59f303-3433-11e6-8a48-0050569d2d94:1-146,</code>

<code>b30dcce8-3395-11e6-902b-0050569d58f6:1270-38443937</code>

<code>4.是以有必要利用pt工具強制同步</code>

<code>4.1</code>

<code>pt-table-checksum h=</code><code>'masterip'</code><code>,u=</code><code>'xx'</code><code>,p=</code><code>'xx'</code><code>,P=3306 --nocheck-replication-filters --replicate=</code><code>test</code><code>.checksums --no-check-binlog-</code><code>format</code> <code>--ignore-databases=mysql --chunk-size-limit=5</code>

<code>4.2</code>

<code>pt-table-</code><code>sync</code>  <code>--execute --replicate </code><code>test</code><code>.checksums  --</code><code>sync</code><code>-to-master h=</code><code>'slaveip'</code><code>,P=3306,u=</code><code>'xx'</code><code>,p=</code><code>'xx'</code>

<code>5.show slave status變化</code>

<code>[MySQL FAQ]系列 — MySQL複制中slave延遲監控</code>

<code>Read_Master_Log_Pos: 445167889</code>

<code>Exec_Master_Log_Pos: 445167889</code>

<code>Seconds_Behind_Master: 0</code>

<code>好了，最後我們說下如何正确判斷SLAVE的延遲情況：</code>

<code>1、首先看 Relay_Master_Log_File 和 Master_Log_File 是否有差異；</code>

<code>2、如果Relay_Master_Log_File 和 Master_Log_File 是一樣的話，</code>

<code>再來看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差異，</code>

<code>對比SQL線程比IO線程慢了多少個binlog事件；</code>

<code>3、如果Relay_Master_Log_File 和 Master_Log_File 不一樣，那說明延遲可能較大，</code>

<code>需要從MASTER上取得binlog status，判斷目前的binlog和MASTER上的差距；</code>

<code>在第三方監控節點上，對MASTER和SLAVE同時發起SHOW BINARY LOGS和SHOW SLAVE STATUS\G</code>

<code>的請求，最後判斷二者binlog的差異，以及 Exec_Master_Log_Pos 和 Read_Master_Log_Pos </code>

<code>的差異。</code>

<code>UserParameter=mysql.slave.Relay_Log_Pos</code>

<code>UserParameter=mysql.slave.Exec_Master_Log_Pos</code>

<code>UserParameter=mysql.slave.Read_Master_Log_Pos</code>

<code>監控項目</code>

<code>Read_Master_Log_Pos: 5374182</code>

<code>Relay_Log_Pos: 27113212</code>

<code>Exec_Master_Log_Pos: 27112999</code>

<code>Relay_Master_Log_File 和 Master_Log_File(需要增加)</code>

<code>mysql5.7 主從不同步 gtid_purged</code>

<code>set</code> <code>global gtid_purged=</code><code>'xxxx'</code>

<code>MySQL5.6 GTID新特性實踐</code>

<code>MySQL5.7殺手級新特性：GTID原理與實戰</code>

<code>GTID跳過SQL錯誤的腳本</code>

參考 http://blog.itpub.net/29510932/viewspace-1736132/

GTID_PURGE() 當同步發生大量的錯誤時，使用flush table with read lock鎖住主庫，記錄GTID的事務編号（最後那個，

例如後面示例裡面的142787），然後資料同步到從庫，在參數中加上UUID(空格)起始事務編号(空格)中止事務編号

原理：purge掉master log中，同步資料的SCN之前的事務，從同步時間點以後開始讀取binlog； 這樣做的好處是不用去master操作，清理binlog（手抖清理了其他東西就不好了~） 

5.6

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v --start-position="594374863" \

binlog.000283 &gt; /XXX/binlog.sql

mysqlbinlog --base64-output=DECODE-ROWS -v --start-datetime="2017-05-02 20:00:00" --stop-datetime="2017-05-03 00:00:00" mysql-bin.001600&gt; /home/back/test.sql

從以上輸出中，我們可以知道，從夯住的那個點開始，binlog 記錄的資訊就出現了異常，可以推測在主庫有大操作。另外，針對出現問題庫，檢視主庫和從庫的表數量，發現從庫的表數量多于主庫，有幾個臨時表出現。可以推測的，主庫有删表的操作，從庫同步夯住，導緻同步異常，主庫删表的操作還沒來得及同步到從庫。

經過和研發溝通，确認了兩點。第一，确實有大操作，程式有大量的批量插入，而且是用的 LOAD DATA LOCAL INFILE；第二，主庫确實有删表的操作，這幾張表都是臨時表

slave優化點

slave_parallel_worker

Master_Log_File: mysql-bin.003842

Read_Master_Log_Pos: 15198736

Relay_Master_Log_File: mysql-bin.003842

Exec_Master_Log_Pos: 15198736   

Relay_Log_Space: 15199242

3.2017.12 資料遷移，做多源複制的時候，出現了問題

1236 error

解決辦法

去2個主上，show GTID_PURGED

從好像是把兩個主PURGED結合，然後SET @@GLOBAL.GTID_PURGED = 'b30dcce8-3395-11e6-902b-0050569d58f6:1-129112350';

再開啟主從。

本文轉自 liqius 51CTO部落格，原文連結：http://blog.51cto.com/szgb17/1906790，如需轉載請自行聯系原作者