MySQL階段五——主從複制原理、主從延遲原理與解決

MySQL主從複制原理、主從延遲原理與解決

MySQL主從複制畫圖描述：

MySQL主從複制原理上圖詳解：

①　使用者做crud操作，寫入資料庫，更新結果記錄到binlog中；

②　主從同步是主找從的，從庫IO發起請求，主庫的主程序看從庫的master change中給的參數是否合法，如果合法主程序交給IO程序進行3操作，否則拒絕；

③　主庫根據master的位置點，從這個位置點的binlog日志一直到binlog最後，将其準備發送給從庫；

④　将找到的binlog日志發給從庫，并且還會發送新的日志點；

⑤　從庫收到binlog日志，将其寫入relay-log（中繼日志）中；

⑥　從庫IO程序再向master info儲存主庫傳過來的最後的binlog日志的位置點；

⑦　從庫IO是循環發起請求的，發了再要，不會顧及SQL讀取中繼的操作。

從庫IO根據新的日志點，向主庫發起請求，主庫執行3操作再，再發送新的binlog給從庫，從庫再執行5操作；

⑧　其實當第一次向relay-log中放資料時，SQL程序就已經知道，SQL程序将relay-log中的sql語句轉換成資料，寫入從庫，進而實作同步；（relay-log和master info也不會互動）

⑨　SQL讀取中繼日志，并不會一次性全部讀完，會把讀取到的日志點存放到relay-log.info中。

主從同步實作之前應該具備的條件和做的準備：

①　從庫有IO和SQL兩個線程，主庫有IO一個線程

②　開啟主從同步之前，主從庫相對與一個日志點之前的資料是一緻的；

（即先要将主庫全備，并且記錄全備的binlog：show master status;然後将全備的内容放入從庫，即可完成）

③　開啟主從同步之前，要在主庫建立從庫進行同步的賬号；

（3306mysql>grant replication slave on *.* to ‘rep’@’192.168.168.101’ identified by ‘123’;）

④　主庫要打開binlog開關；

⑤　從庫要與主庫進行主從同步，要做一下配置

（

3307mysql>CHANGE MASTER TO

MASTER_HOST=’192.168.168.101’，

MASTER_PORT=3306，

MASTER_USER=’rep’，

MASTER_PASSWORD=’123’,

MASTER_LOG_FILE=’mysql-bin.000002,’

MASTER_LOG_POS=238;

）

注：master_host參數裡面最好不要是域名或者localhost，最好是IP

⑥　在從庫mysql>start slave;開啟從庫的IO和SQL程序，并且檢視mysql>show slave status\G；檢視（slave_IO_Running:yes slave_SQL_Rnning:yes scends_behind_master:0）如果這三個參數是這樣，基本上，主從複制配置完成。

環境：多執行個體環境（主：3306、從：3307）

主：確定logbin開啟，server-id唯一，my.cnf中參數不能重複。

在主資料庫中建立用于主從同步的賬号：

grant replication slave on *.* to rep@'192.168.168.109' identified by '123';

備份腳本：rep3306

<code>[root@qinbinPC rep]</code><code># cat rep3306</code>

<code>MYUSER=root</code>

<code>MYPASS=</code><code>"qb123"</code>

<code>MYSOCK=</code><code>/data/3306/mysql</code><code>.sock</code>

<code>MAIN_PATH=</code><code>/server/backup</code>

<code>DATA_PATH=</code><code>/server/backup</code>

<code>LOG_FILE=${DATA_PATH}</code><code>/mysqllogs_</code><code>`</code><code>date</code> <code>+%F`.log</code>

<code>DATA_FILE=${DATA_PATH}</code><code>/mysql_backup_</code><code>`</code><code>date</code> <code>+%F`.sql.gz</code>

<code>MYSQL_PATH=</code><code>/application/mysql/bin</code>

<code>MYSQL_CMD=</code><code>"$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK"</code>

<code>MYSQL_DUMP=</code><code>"$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -A -B --master-data=2 --single-transaction -e"</code>

<code>cat</code> <code>|$MYSQL_CMD <<EOF</code>

<code>flush table with </code><code>read</code> <code>lock;</code>

<code>system </code><code>echo</code> <code>"--show master status result--"</code><code>>> $LOG_FILE;</code>

<code>system $MYSQL_CMD -e </code><code>"show master status"</code><code>|</code><code>tail</code> <code>-l>>$LOG_FILE;</code>

<code>system ${MYSQL_DUMP} |</code><code>gzip</code> <code>>$DATA_FILE;</code>

<code>$MYSQL_CMD -e </code><code>"unlock tables;"</code>

然後檢查：

<code>[root@qinbinPC rep]</code><code># cd /server/backup/</code>

<code>[root@qinbinPC backup]</code><code># ls</code>

<code>mysql_backup_2017-05-13.sql mysqllogs_2017-05-13.log</code>

<code>[root@qinbinPC backup]</code><code># cat mysqllogs_2017-05-13.log </code>

<code> </code><code>Slave_IO_State: Queueing master event to the relay log</code>

<code> </code><code>Master_Host: 192.168.168.109</code>

<code> </code><code>Master_User: rep</code>

<code> </code><code>Master_Port: 3306</code>

<code> </code><code>Connect_Retry: 60</code>

<code> </code><code>Master_Log_File: mysql-bin.000020</code>

<code> </code><code>Read_Master_Log_Pos: 332</code>

<code> </code><code>Relay_Log_File: relay-bin.000002</code>

<code> </code><code>Relay_Log_Pos: 253</code>

<code> </code><code>Relay_Master_Log_File: mysql-bin.000020</code>

<code> </code><code>Slave_IO_Running: Yes</code>

<code> </code><code>Slave_SQL_Running: Yes</code>

<code> </code><code>Replicate_Do_DB: </code>

<code> </code><code>Replicate_Ignore_DB: mysql</code>

<code> </code><code>Replicate_Do_Table: </code>

<code> </code><code>Replicate_Ignore_Table: </code>

<code> </code><code>Replicate_Wild_Do_Table: </code>

<code> </code><code>Replicate_Wild_Ignore_Table: </code>

<code> </code><code>Last_Errno: 0</code>

<code> </code><code>Last_Error: </code>

<code> </code><code>Skip_Counter: 0</code>

<code> </code><code>Exec_Master_Log_Pos: 332</code>

<code> </code><code>Relay_Log_Space: 403</code>

<code> </code><code>Until_Condition: None</code>

<code> </code><code>Until_Log_File: </code>

<code> </code><code>Until_Log_Pos: 0</code>

<code> </code><code>Master_SSL_Allowed: No</code>

<code> </code><code>Master_SSL_CA_File: </code>

<code> </code><code>Master_SSL_CA_Path: </code>

<code> </code><code>Master_SSL_Cert: </code>

<code> </code><code>Master_SSL_Cipher: </code>

<code> </code><code>Master_SSL_Key: </code>

<code> </code><code>Seconds_Behind_Master: 0</code>

<code>Master_SSL_Verify_Server_Cert: No</code>

<code> </code><code>Last_IO_Errno: 0</code>

<code> </code><code>Last_IO_Error: </code>

<code> </code><code>Last_SQL_Errno: 0</code>

<code> </code><code>Last_SQL_Error: </code>

<code> </code><code>Replicate_Ignore_Server_Ids: </code>

<code> </code><code>Master_Server_Id: 1</code>

用于複制備份的腳本：

<code>[root@qinbinPC rep]</code><code># cat rep3307</code>

<code>MYSOCK=</code><code>/data/3307/mysql</code><code>.sock</code>

<code>#RECOVER</code>

<code>gzip</code> <code>-d mysql_backup_`</code><code>date</code> <code>+%F`.sql.gz</code>

<code>$MYSQL_CMD<mysql_backup_`</code><code>date</code> <code>+%F`.sql</code>

<code>#config slave</code>

<code>cat</code> <code>|$MYSQL_CMD<<EOF</code>

<code>CHANGE MASTER TO</code>

<code>MASTER_HOST=</code><code>'192.168.168.109'</code><code>,</code>

<code>MASTER_PORT=3306,</code>

<code>MASTER_USER=</code><code>'rep'</code><code>,</code>

<code>MASTER_PASSWORD=</code><code>'123'</code><code>,</code>

<code>MASTER_LOG_FILE=</code><code>'mysql-bin.000020'</code><code>,</code>

<code>MASTER_LOG_POS=332;</code>

<code>$MYSQL_CMD -e </code><code>"start slave;"</code>

<code>$MYSQL_CMD -e </code><code>"show slave status\G"</code> <code>>$LOG_FILE</code>

<code>#mail -s "mysql slave result" [email protected] <$LOG_FILE</code>

-三、生産場景讀寫分離授權方案

方案一：

主庫：grant select,insert,update,delete on 'blog'.* to 'blog'@'10.0.0.%' identified by '123';

從庫：主庫賬号同步到從庫，然後再回收一些權限：revoke insert,update,delete on blog.* from 'blog'@'10.0.0.%';

從庫也可以不收回權限，在my.cnf中的[mysqld]下加read-only也可以，但是需要注意：read-only參數對有授權super或all peivileges的權限的使用者不起作用。

方案二：

主庫：web_w 123 10.0.0.1 3306 (select,insert,delete,update);

從庫：web_r 123 10.0.0.2 3306 (select);

風險：使用web_w連接配接從庫時，權限比較大。

方案三：

mysql庫不同步，在主庫和從庫建立權限不一樣的使用者。

風險：從庫切換主庫時，連接配接使用者權限問題。

解決：保留一個從庫專門準備接替從庫。

-四、主庫當機，從庫換主，繼續同步

01.確定所有relay log全部更新完畢。

在沒有從庫上執行stop slave;show processlist;

直到看到Has read all relay log;表示從庫更新都執行完畢：

(找一個資料庫中master日志點最近的)

02.登入

#mysql -uroot -p'123' -S /data/3306/mysql.sock

>stop slave;

>retset master;

>quit;

03.進到資料庫目錄，删除master.info relay-log.info

檢查授權表，read-only等參數。

04.提升為主庫

vim /data/3306/my.cnf

開啟log-bin

如果存在log-slave-updates read-only等一定注釋。

然後重新開機服務，提升主庫完畢。

05.其他從庫操作

先檢查（用于同步賬号是否都還在）

登入從庫：

>stop slave;

>change master to master_host='新從庫IP'；

>start slave;

>show slave status\G

-五、主從複制常見故障總結

01.show master status；沒有位置點

原因：binlog沒有打開

（my.cnf裡面檢視binlog是log-bin,登入show variables like 'log_bin'）

02.MASTER_HOST=不能是域名或者localhost

03.鎖表，解鎖受interactive_timeout和wait_timeout兩個參數控制，過了時間會自動解鎖。

04.錯誤：last_IO_Error,...,'Could not find first log file name in binary log index file'

原因：master_log_file=' mysql.bin.000001 ';加了空格

05.多執行個體連接配接從庫的時候不能啟動一直提示running，原因是非正常關閉資料庫，導緻腳本出錯。

解決：rm -f /data/3306/mysql.sock /data/3306/*.pid

06.當從庫已經建立一個資料庫，進行主從複制的時候報錯，這種sql錯誤是可以接受的，可以：

>set global sql_slave_skip_counter=1;

或者根據錯誤号，跳過錯誤，slave-skip-errors=1032,1062,1007

之前見過一個說法：“使用半夜mysqldump帶--master-data=1全備恢複到從庫，從庫執行change master to,無須加位置點”

我在虛拟機，多執行個體環境做主從同步，做主庫備份的時候加上參數--master-data=1（沒有鎖表），在從庫進行連接配接的時候沒有加MASTER_LOG_FILE=’mysql-bin.000002,’MASTER_LOG_POS=’238’;這兩個參數，master.info裡面有位置點（如果沒有鎖表備份，之後又操作主庫資料），但是實際上是從頭同步。

希望與大家一起交流！

/////////////////////////////////////////////////////////////

一、MySQL資料庫主從同步延遲

要了解MySQL資料庫主從同步延遲原理，我們先從MySQL的資料庫主從複制原理說起：

MySQL的主從複制都是單線程的操作，主庫對所有DDL和DML産生的日志寫進binlog，由于binlog是順序寫，是以效率很高。

Slave的IO Thread線程從主庫中bin log中讀取取日志。

Slave的SQL Thread線程将主庫的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随即的，不是順序的，成本高很多。

由于SQL Thread也是單線程的，如果slave上的其他查詢産生lock争用，又或者一個DML語句（大事務、大查詢）執行了幾分鐘，那麼所有之後的DML會等待這個DML執行完才會繼續執行，這就導緻了延時。

二、MySQL資料庫主從同步延遲産生原因

1、Master負載

2、Slave負載

3、網絡延遲

4、機器配置（cpu、記憶體、硬碟）

總之，當主庫的并發較高時，産生的DML數量超過slave的SQL Thread所能處理的速度，或者當slave中有大型query語句産生了鎖等待那麼延時就産生了。

三、MySQL資料庫主從同步延遲解決方案

1、salve較高的機器配置

2、Slave調整參數

為了保障較高的資料安全性，配置sync_binlog=1，innodb_flush_log_at_trx_commit = 1 等設定。而Slave可以關閉binlog，innodb_flush_log_at_trx_commit也可以設定為0來提高sql的執行效率

3、并行複制

MySQL的複制延遲是一直被诟病的問題之一，欣喜的是，MySQL 5.7版本已經支援”真正”的并行複制功能。MySQL 5.7并行複制的思想簡單易懂，簡而言之，就是”一個組送出的事務都是可以并行回放的”，因為這些事務都已進入到事務的prepare階段，則說明事務之間沒有任何沖突（否則就不可能送出）。MySQL 5.7以後，複制延遲問題永不存在。

這裡需要注意的是，為了相容MySQL 5.6基于庫的并行複制，5.7引入了新的變量slave-parallel-type，該變量可以配置成DATABASE（預設）或LOGICAL_CLOCK。可以看到，MySQL的預設配置是庫級别的并行複制，為了充分發揮MySQL 5.7的并行複制的功能，我們需要将slave-parallel-type配置成LOGICAL_CLOCK。

本文轉自叫我北北 51CTO部落格，原文連結:http://blog.51cto.com/qinbin/1929063

MySQL階段五——主從複制原理、主從延遲原理與解決

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述