MySQL主從複制架構

一、前言

  現如今，資料庫發展越來越快，雖然主從架構已經落伍了，取而代之的是更加複雜的資料庫叢集。但是作為一個小型公司，資料庫的主從架構是最基本的架構，是以了解完主從架構，也就能看懂更加複雜的架構。下面介紹一下該架構。

二、資料庫的讀寫分離

  為什麼要讀寫分離？

對于一個小型網站，可能單台資料庫伺服器就能滿足需求，但是在一些大型的網站或者應用中，單台的資料庫伺服器可能難以支撐大的通路壓力，更新伺服器性能，成本又太高，必須要橫向擴充。還有就是，單庫的話，讀、寫都是操作一個資料庫，資料多了之後，對資料庫的讀、寫性能就會有很大影響。同時對于資料安全性，和系統的穩定性，也是挑戰。

  資料庫的讀寫分離的好處？

1. 将讀操作和寫操作分離到不同的資料庫上，避免主伺服器出現性能瓶頸；

2. 主伺服器進行寫操作時，不影響查詢應用伺服器的查詢性能，降低阻塞，提高并發；

3. 資料擁有多個容災副本，提高資料安全性，同時當主伺服器故障時，可立即切換到其他伺服器，提高系統可用性；

  讀寫分離的基本原理？

就是讓主資料庫處理事務性增、改、删操作（INSERT、UPDATE、DELETE）操作，而從資料庫處理SELECT查詢操作。資料庫複制被用來把事務性操作導緻的變更同步到其他從資料庫。以SQL為例，主庫負責寫資料、讀資料。讀庫僅負責讀資料。每次有寫庫操作，同步更新到讀庫。寫庫就一個，讀庫可以有多個，采用日志同步的方式實作主庫和多個讀庫的資料同步。

三、MySQL複制描述

  主從複制是指一台伺服器充當主資料庫伺服器，另一台或多台伺服器充當從資料庫伺服器，主伺服器中的資料自動複制到從伺服器之中。對于多級複制，資料庫伺服器即可充當主機，也可充當從機。MySQL主從複制的基礎是主伺服器對資料庫修改記錄二進制日志，從伺服器通過主伺服器的二進制日志自動執行更新。

請注意：當你進行複制時，所有對複制中的表的更新必須在主伺服器上進行。否則，你必須要小心，以避免使用者對主伺服器上的表進行的更新與對從伺服器上的表所進行的更新之間的沖突。

  3.1、mysql支援的複制類型：

　　（1）：基于語句的複制：在主伺服器上執行的SQL語句，在從伺服器上執行同樣的語句。MySQL預設采用基于語句的複制，效率比較高。  

       一旦發現沒法精确複制時，會自動選着基于行的複制。    

（2）：基于行的複制：把改變的内容複制過去，而不是把指令在從伺服器上執行一遍. 從mysql5.0開始支援

（3）：混合類型的複制: 預設采用基于語句的複制，一旦發現基于語句的無法精确的複制時，就會采用基于行的複制。

 3.2、複制解決的問題：

   MySQL複制技術有以下一些特點：

    (1)資料分布 (Data distribution )

    (2)負載平衡(load balancing)

    (3)備份(Backups) 

    (4)高可用性和容錯行 High availability and failover

3.3、mysql資料庫存儲資料的過程：

主mysql：

1、客戶發起事務動作，mysql服務将事務記錄到事務日志（Binary log）中，将事務執行的一些操作記錄在記憶體中，如果事務沒有完成，記憶體斷電，可以從事務日志中将沒有完成的事務進行復原。

2、将記憶體中完成的操作記錄到二進制日志中

3、将二進制日志中的完整事務操作，定期寫到磁盤檔案中

（mysql資料庫中有個dump的程序，會将二進制日志改變的部分提取出來，交給從伺服器的I/O程序進行讀取）

從mysql

4、如果從mysql想要同步資料庫的話，可以監聽主mysql上的二進制日志，将主mysql二進制日志中發生變化的部分提出到從mysql上，放入從mysql的relay log日志（中繼日志）中

5、再将中繼日志中的操作讀入記憶體，由記憶體将操作記錄到二進制日志中，再定期寫到磁盤檔案中。

（mysql從資料庫中有I/O線程和SQL線程，I/O線程會監控主mysql伺服器上的二進制檔案，如果二進制檔案發生改變後就去讀取發生改變的部分，加載到自己的relay log中繼日志中，并由SQL線程讀取中繼日志中的資料，加載到記憶體中，再寫入二進制檔案中。）

四、MySQL主從複制過程

MySQL主從複制的兩種情況：同步複制和異步複制，實際複制架構中大部分為異步複制。複制的基本過程如下：

1、Slave上面的IO程序連接配接上Master，并請求從指定日志檔案的指定位置（或者從最開始的日志）之後的日志内容。

2、Master接收到來自Slave的IO程序的請求後，負責複制的IO程序會根據請求資訊讀取日志指定位置之後的日志資訊，傳回給Slave的IO程序。傳回資訊中除了日志所包含的資訊之外，還包括本次傳回的資訊已經到Master端的bin-log檔案的名稱以及bin-log的位置。

3、Slave的IO程序接收到資訊後，将接收到的日志内容依次添加到Slave端的relay-log檔案的最末端，并将讀取到的Master端的 bin-log的檔案名和位置記錄到master-info檔案中，以便在下一次讀取的時候能夠清楚的告訴Master“我需要從某個bin-log的哪個位置開始往後的日志内容，請發給我”。

4、Slave的Sql程序檢測到relay-log中新增加了内容後，會馬上解析relay-log的内容成為在Master端真實執行時候的那些可執行的内容，并在自身執行。

五、MySQL資料庫複制配置

最簡單的複制模式就是一主一從的複制模式了，下面開始操作：

首先使用yum方式安裝Mysql服務，要求版本一緻，并啟動服務

yum install mariadb

yum install  mariadb-server

注意：保證伺服器在同一網段

在Master伺服器上：

1、修改主MySQL的主配置檔案

vim /etc/my.cnf

server_id=1    #配置server-id，讓主伺服器有唯一ID号

log-bin=mysql-bin  #打開Mysql日志，日志格式為二進制

skip-name-resolve  #關閉名稱解析，（非必須）

systemctl start mariadb     #啟動服務

cd /var/lib/mysql-->ll-->mysqlbinlog mysql-bin.000001 檢視二進制日志，生成mysql-bin.000001和mysql-bin.index檔案

2、在主MySQL上給從MySQL添加登入權限

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.%.%' IDENTIFIED BY 'magedu';

3、檢視Master伺服器狀态

在Master的資料庫執行mysql>show master status;檢視主伺服器二進制日志狀态

--------------+

| File                         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------------+----------+--------------+------------------+-------------------

+mysql-bin.000004 |      416 |              |                  |                   |

+------------------------+----------+--------------+------------------+-----

mysql> show binlog events \G;  檢視二進制日志狀态

在從伺服器上：

4、配置slave從伺服器

對slave進行配置，打開中繼日志，指定唯一的servr ID，設定隻讀權限。在配置檔案加入如下值：

server-id=2   #配置server-id，讓從伺服器有唯一ID号

relay_log = mysql-relay-bin   #打開Mysql日志，日志格式為二進制

read_only = 1   #設定隻讀權限

log_bin = mysql-bin   #開啟從伺服器二進制日志

log_slave_updates = 1 #使得更新的資料寫進二進制日志中

5．啟動從伺服器複制線程

在master和slave都已經配置好後，隻需要把slave指向master即可，并開始重做master二進制日志中的事件。

mysql -uroot -p

CHANGE MASTER TO MASTER_HOST='192.168.159.151', 

MASTER_USER='slave',

MASTER_PASSWORD='magedu', 

MASTER_LOG_FILE='mysql-bin.000004',

MASTER_LOG_POS=416;

注意：上面的sql語句是從頭開始複制第一個binlog，如果想從某個位置開始複制binlog，就需要在change master to時指定要開始的binlog檔案名和語句在檔案中的起點位置，參數為：master_log_file和master_log_pos。

執行start slave； # 啟動複制線程

6、檢視從伺服器狀态

可使用SHOW SLAVE STATUS\G檢視從伺服器狀态，如下所示，也可用show processlist \G檢視目前複制狀态：

Slave_IO_Running: Yes #IO線程正常運作

Slave_SQL_Running: Yes #SQL線程正常運作

7、測試

在主伺服器上：show databases;-->create database magedu;

在從伺服器上檢視：show databases;

上面就是最簡單的主從複制模式，不過有時候随着時間的推進，binlog會變得非常龐大，如果新增加一台slave，從頭開始複制master的binlog檔案是非常耗時的，是以我們可以從一個指定的位置開始複制binlog日志，可以通過其他方法把以前的binlog檔案進行快速複制，例如copy實體檔案。在change master to中有兩個參數可以實作該功能，master_log_file和master_log_pos，通過這兩個參數指定binlog檔案及其位置。我們可以從master上複制也可以從slave上複制，假如我們是從master上複制，具體操作過程如下：

（1）為了防止在操作過程中資料更新，導緻資料不一緻，是以需要先重新整理資料并鎖定資料庫：flush tables with read lock。

（2）檢查目前的binlog檔案及其位置：show master status。

| File                                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------------+----------+--------------+------------------+-------------------+

| mysqlmaster-bin.000001 |      630   |              |                  |                   |

（3）主伺服器上另開一連接配接（另開一視窗，不退出第一個視窗，退出後上面的鎖表将失效）

通過mysqldump指令建立資料庫的邏輯備份：

mysqldump -uroot -p -P3306 --all-databases  --triggers --routines --events >mysqlall.sql。

（4）有了master的邏輯備份後，對資料庫進行解鎖：unlock tables。

（5）把mysqlall.sql複制到新的slave上，執行：

scp mysqlall.sql  192.168.X.X:/tmp/

mysql -uroot -p -h 127.0.0.1 -P3306 < /tmp/mysqlall.sql  把master的邏輯備份插入slave的資料庫中。

（6）現在可以把新的slave連接配接到master上了，隻需要在change master to中多設定兩個參數master_log_file='mysql-bin.000003'和master_log_pos='107'即可，然後啟動slave：start slave，這樣slave就可以接着107的位置進行複制了。

登陸從資料庫執行:

CHANGE MASTER TO MASTER_HOST='192.168.159.120',

MASTER_USER='systop2',

MASTER_PASSWORD='systop',

MASTER_LOG_FILE='mysqlmaster-bin.000003',

MASTER_LOG_POS=107;

（7）start slave；

show slave status \G;

檢視Slave_IO_Running: Yes

   Slave_SQL_Running: Yes都顯示yes表示啟動正常。

若報Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' 錯誤，Slave_IO_Running為no

執行flush logs;（清空緩沖區資料，此時緩沖區資料都會被寫到資料庫裡）

mysql> show master status;

重新執行第6步。

有時候master并不能讓你鎖住表進行複制，因為可能跑一些不間斷的服務，如果這時master已經有了一個slave，我們則可以通過這個slave進行再次擴充一個新的slave。原理同在master上進行複制差不多，關鍵在于找到binlog的位置，你在複制的同時可能該slave也在和master進行同步，操作如下：

（1）為了防止資料變動，還是需要停止slave的同步：stop slave。

（2）然後重新整理表，并用mysqldump邏輯備份資料庫。

（3）使用show slave status檢視slave的相關資訊，記錄下兩個字段的值Relay_Master_Log_File和Exec_Master_Log_Pos，這個用來确定從後面哪裡開始複制。

（4）對slave解鎖，把備份的邏輯資料庫導入新的slave的資料庫中，然後設定change master to，這一步和複制master一樣。

實驗中出現的問題：

1、在同步後出現：

Slave_IO_Running: Yes

Slave_SQL_Running: NO

原因：1.程式可能在slave上進行了寫操作，是以從伺服器上要在配置檔案中寫入：read_only=1

  2.也可能是slave機器重起後，事務復原造成的.

解決辦法：

方法1、檢視master伺服器中的二進制日志記錄show master status;

   重新在從伺服器上進行配置，change master to .....

方法2、先在從伺服器上停掉slave：slave stop |stop slave

   set global sql_slave_skip_counter=1;

   #以上是用來跳過幾個事件，隻有當同步程序出現錯誤而停止的時候才可以執行。 

   重新開機slave：slave start |start slave

2、在啟動資料庫後檢視slave status

show slave status\G;

出現：

Slave_IO_Running: No

    Slave_SQL_Running: Yes

    這是因為slave 開啟了，但是沒有指向master，或沒有指向成功,或防火牆沒關。

    需要重新檢視master的資料，確定change master to master....指向成功。

本文轉自 優果馥思 51CTO部落格，原文連結:http://blog.51cto.com/youguofusi/2051302