主從復制原理以及實踐

Mysql主從復制原理及配置

輸入的命令

藍色字體:注釋

×××背景:重要

小標題:藍底白字
1.復制概述

Mysql內建的復制功能是構建大型，高性能應用程序的基礎。將Mysql的數據分布到多個系統上去，這種分布的機制，是通過將Mysql的某一臺主機的數據復制到其它主機（slaves）上，并重新執行一遍來實現的。復制過程中一個服務器充當主服務器，而一個或多個其它服務器充當從服務器。主服務器將更新寫入二進制日志文件，并維護文件的一個索引以跟蹤日志循環。這些日志可以記錄發送到從服務器的更新。當一個從服務器連接主服務器時，它通知主服務器從服務器在日志中讀取的最后一次成功更新的位置。從服務器接收從那時起發生的任何更新，然后封鎖并等待主服務器通知新的更新。

請注意當你進行復制時，所有對復制中的表的更新必須在主服務器上進行。否則，你必須要小心，以避免用戶對主服務器上的表進行的更新與對從服務器上的表所進行的更新之間的沖突。(所以一般會把從服務器設置成只讀read_only= 1)

1.1 mysql支持的復制類型：

1):基于語句的復制:在主服務器上執行的SQL語句，在從服務器上執行同樣的語句。MySQL默認采用基于語句的復制，效率比較高.一旦發現沒法精確復制時,會自動選著基于行的復制。    
2):基于行的復制：把改變的內容復制過去，而不是把命令在從服務器上執行一遍. 從mysql5.0開始支持
3):混合類型的復制:默認采用基于語句的復制,一旦發現基于語句的無法精確的復制時,就會采用基于行的復制。

1.2 . 復制解決的問題

         MySQL復制技術有以下一些特點：
    (1)數據分布 (Data distribution )
    (2)負載平衡(load balancing)
    (3)備份(Backups) 
    (4)高可用性和容錯行 High availability and failover 

 1.3 復制如何工作   > 復制的原理：

       (1) master將改變記錄到二進制日志(binary log)中(這些記錄叫做二進制日志事件，binary log events)；
       (2) slave將master的binary log events拷貝到它的中繼日志(relay log)；
       (3) slave重做中繼日志中的事件，將更改應用到自己的數據上。

下圖描述了復制的過程：

slave復制過程:

第一步:master記錄二進制日志。在每個事務更新數據完成之前，master二進制日志記錄這些改變。MySQL將事務串行的寫入二進制日志，即使事務中的語句都是交叉執行的。在事件寫入二進制日志完成后,master通知存儲引擎提交事務。
       第二步:slave利用I/O thread將master的binary log讀取并拷貝到它自己的中繼日志。首先，slave開始一個工作線程——I/O線程。I/O線程在master上打開一個普通的連接，然后開始binlog dump process。Binlog dump process從master的二進制日志中讀取事件，如果已經跟上master，它會睡眠并等待master產生新的事件。I/O線程將這些事件寫入中繼日志。
       第三步:SQL slave thread（SQL從線程）處理該過程的最后一步。SQL線程從中繼日志讀取事件，并重新執行其中的事件而更新slave的數據，使其與master中的數據一致。該線程與I/O線程保持一致，中繼日志通常會位于OS的緩存中，所以中繼日志的開銷很小。

注意:I/O線程更新的數據的情況記錄在master.info中.(/application/mysql/data)
        此外，在master中也有一個工作線程：和其它MySQL的連接一樣，slave在master中打開一個連接也會使得master開始一個線程。復制過程有一個很重要的限制——復制在slave上是串行化的，也就是說master上的并行更新操作不能在slave上并行操作。實際生產過程中,同步并不是實時的,是異步進行的.

2 .主從實踐配置

   準備兩臺MySQL數據庫服務器Master和slave，Master為主服務器，slave為從服務器，初始狀態時，Master和slave中的數據信息相同，如果數據不相同,應該先把master的數據全備導出,然后導入到slave中,這是為了保證主庫從庫數據一致.(利用多端口實例也可以做)

要點：
負責在主、從服務器傳輸各種修改動作的媒介是主服務器的二進制變更日志，這個日志記載著需要傳輸給從服務器的各種修改動作。因此，主服務器必須激活二進制日志功能。從服務器必須具備足以讓它連接主服務器并請求主服務器把二進制變更日志傳輸給它的權限。        
環境：
Master版本為5.1.17和slave的版本同為5.5.34(其實最好版本一樣,不一樣可能有些函數沒辦法復制)
操作系統：centos 6.9

2.1、創建復制帳號

1、在Master的數據庫中建立一個備份帳戶：每個slave使用標準的MySQL用戶名和密碼連接master。進行復制操作的用戶會授予replication slave權限。用戶名的密碼都會存儲在文本文件master.info中

命令如下：
mysql > grant replication slave,reload,super, on *.* to 'backup'@ ’192.168.52.220’ identified by ‘123456’;

建立一個帳戶backup，并且只能允許從192.168.52.220這個地址上來登陸，密碼是123456。這里@后面的ip地址就是slave的ip

2.2、拷貝數據

（假如是你完全新安裝mysql主從服務器，這個一步就不需要。因為新安裝的master和slave數據完全一樣）

如果不是則需要:關停Master服務器，將Master中的數據拷貝到B服務器中，使得Master和slave中的數據同步，并且確保在全部設置操作結束前，禁止在Master和slave服務器中進行寫操作，使得兩數據庫中的數據一定要相同！

其中在進行master數據庫全備的時候,請使用參數--master-data=1進行備份如下:

[root@localhost]#mysqldump -uroot -p123456 -A -B -F --master-data=1 -x --events|gzip>備份的文件.

有這個參數,那么在做主從同步的時候,執行change master就不用單獨指定mysql-bin的位置,和文件,因為已經在全備里面了,而且是執行語句,在從庫進行全備恢復的時候已經完成恢復.和相關文件的指定. 

2.3、配置master

接下來對master進行配置，包括打開二進制日志，指定唯一的servr ID。例如，在配置文件加入如下值：

server-id=1          #為主服務器A的ID值
log-bin=mysql-bin     #二進制變更日值

重啟master，運行SHOW MASTER STATUS，輸出如下則代表二進制日志已經成功打開：

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000002 |      106 |              |                  |

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

1 row in set (0.00 sec)

2.4、配置slave

Slave的配置與master類似，你同樣需要重啟slave的MySQL。如下：
log_bin = mysql-bin
server_id= 2
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only= 1
1):server_id是必須的，而且唯一。

2):slave沒有必要開啟二進制日志，但是在一些情況下，必須設置，例如，如果slave為其它slave的master，必須設置bin_log。在這里，我們開啟了二進制日志，而且顯示的命名(默認名稱為hostname，但是，如果hostname改變則會出現問題)。
3):relay_log配置中繼日志，log_slave_updates表示slave將復制事件寫進自己的二進制日志(后面會看到它的用處)。
有些人開啟了slave的二進制日志，卻沒有設置log_slave_updates，然后查看slave的數據是否改變，這是一種錯誤的配置。
4):盡量使用read_only，它防止改變數據(除了特殊的線程)。但是需要在slave上創建表的應用read_only并是很實用.

配置也需要重啟才生效. 

2.5、啟動slave

到此,接下來就是讓slave連接master，并開始重做master二進制日志中的事件。你不應該用配置文件進行該操作，而應該使用CHANGE MASTER TO語句，該語句可以完全取代對配置文件的修改，而且它可以為slave指定不同的master，而不需要停止服務器。如下：

mysql> CHANGE MASTER TO MASTER_HOST='主機IP',

-> MASTER_USER='rep',

-> MASTER_PASSWORD='123456',

-> MASTER_LOG_FILE='mysql-bin.000001',#恢復備份用--master-data=1的不需指定.自動在備份中指定

-> MASTER_LOG_POS=0;##恢復備份用--master-data=1的不需要指定.自動在備份中指定

MASTER_LOG_POS的值為0，因為它是日志的開始位置。

如果沒有用--master-data則可以需要登錄數據庫,利用show master status進行MASTER_LOG_FILE

和MASTER_LOG_POS的尋找.

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000002 |      106 |              |                  |

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

1 row in set (0.00 sec)

你可以用SHOW SLAVE STATUS語句查看slave的設置是否正確：

mysql> SHOW SLAVE STATUS\G

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

Slave_IO_State:

Master_Host: 192.168.52.220

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 4

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: No

Slave_SQL_Running: No

               ...omitted...

Seconds_Behind_Master: NULL

Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No

表明slave還沒有開始復制過程。日志的位置為4而不是0，這是因為0只是日志文件的開始位置，并不是日志位置。實際上，MySQL知道的第一個事件的位置是4。

開始復制，你可以運行：

mysql> START SLAVE;

運行SHOW SLAVE STATUS查看輸出結果：

mysql> SHOW SLAVE STATUS\G

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

 Slave_IO_State: Waiting for master to send event

Master_Host:192.168.52.220

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 164

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 164

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

                          ...omitted...

Seconds_Behind_Master: 0

在這里主要是看:
             Slave_IO_Running=Yes
             Slave_SQL_Running=Yes

slave的I/O和SQL線程都已經開始運行，而且Seconds_Behind_Master不再是NULL。日志的位置增加了，意味著一些事件被獲取并執行了。

驗證:如果你在master上進行修改，你可以在slave上看到各種日志文件的位置的變化，同樣，你也可以看到數據庫中數據的變化。

你可查看master和slave上線程的狀態。在master上，你可以看到slave的I/O線程創建的連接：

在master上輸入show processlist\G;

mysql> show processlist \G

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

Id: 1

User: root

Host: localhost:2096

db: test

Command: Query

Time: 0

State: NULL

Info: show processlist

*************************** 2. row ***************************

Id: 2

User: repl

Host: localhost:2144

db: NULL

Command: Binlog Dump

Time: 1838

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

2 rows in set (0.00 sec)

行2為處理slave的I/O線程的連接。

在slave服務器上運行該語句：

mysql> show processlist \G

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

Id: 1

User: system user

Host:

db: NULL

Command: Connect

Time: 2291

State: Waiting for master to send event

Info: NULL

*************************** 2. row ***************************

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 1852

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

*************************** 3. row ***************************

Id: 5

User: root

Host: localhost:2152

db: test

Command: Query

Time: 0

State: NULL

Info: show processlist

3 rows in set (0.00 sec)

行1為I/O線程狀態，行2為SQL線程狀態。

來源于網絡整理