MySQL主從複製原理及配置

MySQL主從複製原理及配置

1 複製概述

MySQL內建的複製功能是構建大型，高效能應用程式的基礎。將MySQL的資料分布到多個系統上去，這種分布的機制，是通過將MySQL的某一台主機的資料複製到其它主機（slaves）上，並重新執行一遍來實現的。複製過程中一個伺服器充當主伺服器，而一個或多個其它伺服器充當從伺服器。主伺服器將更新寫入二進位記錄檔，並維護檔案的一個索引以追蹤記錄檔迴圈。這些日誌可以記錄發送到從伺服器的更新。當一個從伺服器串連主伺服器時，它通知主伺服器從伺服器在日誌中讀取的最後一次成功更新的位置。從伺服器接收從那時起發生的任何更新，然後封鎖並等待主伺服器通知新的更新。

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

1.1 mysql支援的複製類型：　　

　　（１）：基於語句的複製：  在主伺服器上執行的SQL語句，在從伺服器上執行同樣的語句。MySQL預設採用基於語句的複製，效率比較高。 
            一旦發現沒法精確複製時，  會自動選著基於行的複製。   
　　（２）：基於行的複製：把改變的內容複寫過去，而不是把命令在從伺服器上執行一遍. 從mysql5.0開始支援
　　（３）：混合類型的複製: 預設採用基於語句的複製，一旦發現基於語句的無法精確的複製時，就會採用基於行的複製。
1.2 . 複製解決的問題        MySQL複製技術有以下一些特點：
        (1)    資料分布 (Data distribution )
        (2)    Server Load Balancer(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重做中繼日誌中的事件，將更改應用到自己的資料上。

描述了複製的過程：

                                 
          該過程的第一部分就是master記錄二進位日誌。在每個事務更新資料完成之前，master在二日誌記錄這些改變。MySQL將事務串列的寫入二進位日誌，即使事務中的語句都是交叉執行的。在事件寫入二進位日誌完成後，master通知儲存引擎提交事務。
      下一步就是slave將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的緩衝中，所以中繼日誌的開銷很小。
        此外，在master中也有一個背景工作執行緒：和其它MySQL的串連一樣，slave在master中開啟一個串連也會使得master開始一個線程。複製過程有一個很重要的限制——複製在slave上是序列化的，也就是說master上的並行更新操作不能在slave上並行操作。

2 .複製配置  有兩台MySQL資料庫伺服器Master和slave，Master為主伺服器，slave為從伺服器，初始狀態時，Master和slave中的資料資訊相同，當Master中的資料發生變化時，slave也跟著發生相應的變化，使得master和slave的資料資訊同步，達到備份的目的。
要點：
負責在主、從伺服器傳輸各種修改動作的媒介是主伺服器的二進位變更日誌，這個日誌記載著需要傳輸給從伺服器的各種修改動作。因此，主伺服器必須啟用二進位日誌功能。從伺服器必須具備足以讓它串連主伺服器並請求主伺服器把二進位變更日誌傳輸給它的許可權。

--------------------------------------分割線 --------------------------------------

Ubuntu下Nginx做負載實現高效能WEB伺服器5---MySQL主主同步

生產環境MySQL主主同步主鍵衝突處理

MySQL主從失敗 錯誤Got fatal error 1236

MySQL主從複製，單台伺服器上實施

搭建MySQLProxy 伺服器實現讀寫分離+主從同步

MySQL 5.5 主從雙向同步

MySQL 5.5主從同步排錯

MySQL主從複製非同步半同步執行個體

--------------------------------------分割線 --------------------------------------

       
環境：
Master和slave的MySQL資料庫版本同為5.1.17
作業系統：CentOS 6.5
IP地址：10.100.0.100

2.1、建立複製帳號1、在Master的資料庫中建立一個備份帳戶：每個slave使用標準的MySQL使用者名稱和密碼串連master。進行複製操作的使用者會授予REPLICATION SLAVE許可權。使用者名稱的密碼都會儲存在文字檔master.info中
命令如下：
mysql > GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*
TO backup@’10.100.0.200’
IDENTIFIED BY ‘1234’;
建立一個帳戶backup，並且只能允許從10.100.0.200這個地址上來登陸，密碼是1234。這裡@後面的ip地址就是slave的ip

(如果因為mysql版本新舊密碼演算法不同，可以設定：set password for 'backup'@'10.100.0.200'=old_password('1234')）
2.2、拷貝資料（假如是你完全新安裝mysql主從伺服器，這個一步就不需要。因為新安裝的master和slave有相同的資料）
關停Master伺服器，將Master中的資料拷貝到B伺服器中，使得Master和slave中的資料同步，並且確保在全部設定作業結束前，禁止在Master和slave伺服器中進行寫操作，使得兩資料庫中的資料一定要相同！

2.3、配置master
接下來對master進行配置，包括開啟二進位日誌，指定唯一的servr ID。例如，在設定檔加入如下值：

server-id=1
log-bin=mysql-bin
server-id：為主伺服器A的ID值
log-bin：二進位變更日值
重啟master，運行SHOW MASTER STATUS，輸出如下：

2.4、配置slaveSlave的配置與master類似，你同樣需要重啟slave的MySQL。如下：
log_bin          = mysql-bin
server_id        = 2
relay_log        = mysql-relay-bin
log_slave_updates = 1
read_only        = 1
server_id是必須的，而且唯一。slave沒有必要開啟二進位日誌，但是在一些情況下，必須設定，例如，如果slave為其它slave的master，必須設定bin_log。在這裡，我們開啟了二進位日誌，而且顯示的命名(預設名稱為hostname，但是，如果hostname改變則會出現問題)。
relay_log配置中繼日誌，log_slave_updates表示slave將複製事件寫進自己的二進位日誌(後面會看到它的用處)。
有些人開啟了slave的二進位日誌，卻沒有設定log_slave_updates，然後查看slave的資料是否改變，這是一種錯誤的配置。所以，盡量使用read_only，它防止改變資料(除了特殊的線程)。但是，read_only並是很實用，特別是那些需要在slave上建立表的應用。

2.5、啟動slave
接下來就是讓slave串連master，並開始重做master二進位日誌中的事件。你不應該用設定檔進行該操作，而應該使用CHANGE MASTER TO語句，該語句可以完全取代對設定檔的修改，而且它可以為slave指定不同的master，而不需要停止伺服器。如下：mysql> CHANGE MASTER TO MASTER_HOST='server1',

    -> MASTER_USER='repl',

    -> MASTER_PASSWORD='p4ssword',

    -> MASTER_LOG_FILE='mysql-bin.000001',

  -> MASTER_LOG_POS=0;

    -> MASTER_LOG_POS=0;

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

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

mysql> SHOW SLAVE STATUS\G

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

            Slave_IO_State:

                Master_Host: server1

                Master_User: repl

                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: server1

                Master_User: repl

                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線程狀態。

2.5、添加新slave伺服器假如master已經運行很久了，想對新安裝的slave進行資料同步，甚至它沒有master的資料。
此時，有幾種方法可以使slave從另一個服務開始，例如，從master拷貝資料，從另一個slave複製，從最近的備份開始一個slave。Slave與master同步時，需要三樣東西：
(1)master的某個時刻的資料快照；
(2)master當前的記錄檔、以及產生快照時的位元組位移。這兩個值可以叫做記錄檔座標(log file coordinate)，因為它們確定了一個二進位日誌的位置，你可以用SHOW MASTER STATUS命令找到記錄檔的座標；
(3)master的二進位記錄檔。

可以通過以下幾中方法來複製一個slave：
(1)    冷拷貝(cold copy)
停止master，將master的檔案拷貝到slave；然後重啟master。缺點很明顯。
(2)    熱拷貝(warm copy)
如果你僅使用MyISAM表，你可以使用mysqlhotcopy拷貝，即使伺服器正在運行。
(3)    使用mysqldump
使用mysqldump來得到一個資料快照可分為以下幾步：
<1>鎖表：如果你還沒有鎖表，你應該對錶加鎖，防止其它串連修改資料庫，否則，你得到的資料可以是不一致的。如下：
mysql> FLUSH TABLES WITH READ LOCK;
<2>在另一個串連用mysqldump建立一個你想進行複製的資料庫的轉儲：
shell> mysqldump --all-databases --lock-all-tables >dbdump.db
<3>對錶釋放鎖。
mysql> UNLOCK TABLES;

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 3 下一頁