MySQL replication

MySQL Master/Slave原理分析:

   單向同步是基于MySQL的二進制日志的(binlog)，需要開啟二進制日志功能。MySQL将所有更新的

内容記錄到二進制日志中，Master/Slave通過三個線程來完成同步。在Master上有一個線程，用來發

送二進制日志，在Master上show processlist\G可以檢視線程的名字(Binlog Dump)。在Slave上有兩

個線程，I/O線程接受來自Master的更新二進制日志并把它儲存到中繼日志(relay log)中，SQL線程用

來讀取中繼日志中SQL語句并執行更新操作。流程圖如下圖：

Master/Slave優點:

系統服務穩定：當Master當機時，可以切換Slave服務。

系統服務速率加快：更新操作在Master上進行，查詢操作在Slave上進行，達到了負載均衡的效果，減輕了Master的負載，加快服務響應時間。

系統資料易備份、恢複：資料備份可以在Slave上進行而不幹擾Master的服務。

Master：192.168.10.21   Slave：192.168.10.22

Master：建立同步賬戶，并給予同步權限

grant replication slave on  test.*  to 'mysql'@'192.168.10.22'(slave ip) identified  by  'mysql';

flush   privileges;

配置/etc/my.cnf，在[mysqld]下配置參數

[mysqld]
server-id = 1 (id必須唯一)
log-bin = mysql-bin (二進制檔案字首名)
log-bin-index = mysql-bin.index(二進制日志索引檔案)
sync-binlog = 1
innodb-flush-log-at-trx-commit = 1
innodb-support-xa = 1      

注：後面三個參數是為了安全目的考慮的，伺服器出現當機時確定資料一緻性

Slave：配置/etc/ my.cnf檔案，在[mysqld]下添加一下參數後重新開機服務

[mysqld]
server-id = 2 (id必須唯一)
log-bin = mysql-bin (二進制檔案字首名)

log-bin-index = mysql-bin.index(二進制日志索引檔案)

relay-log = mysql-relay-bin(中繼日志檔案字首名)

relay-log-index = mysql-relay-bin.index(中繼日志索引檔案)

read-only = 1(保證隻從master更新,除非登入使用者具有super權限)

skip-slave-start = 1(執行slave start才開始更新)

log-slave-updates = 1(SQL線程執行的更新寫入自己的二進制日志,slave充當master時有用)

replicate-wild-ignore-table=mysql.% (屏蔽對mysql庫的同步)
replicate-wild-do-table=test.%  (同步資料庫名稱)
      

注：replicate-ignore-db和replicate-do-db不允許跨資料庫同步。

例：use mysql;insert into test values(...);

此時這個語句忽略執行，導緻資料不同步。

沒有slave，初級操作步驟，盡量在不影響業務情況下執行備份(比如夜間執行)

如果資料庫使用myisam，可按下面方式操作：

shell> mysqldump --all-databases --master-data=1 > data.sql

注：--master-data參數會開啟--lock-all-tables并寫入change master to

如果資料庫使用的是InnoDB表類型的話，則應該使用single-transcation：

shell> mysqldump --all-databases --single-transaction --master-data=1 > data.sql

在從伺服器上導入資料

shell> mysql < data.sql

因為開始設定了master-data=1，是以系統會自動設定參數：MASTER_LOG_FILE和MASTER_LOG_POS，我們還需要設定剩下的參數：

mysql> CHANGE MASTER TO
       MASTER_HOST='<MASTER_HOST>',
       MASTER_USER='<SLAVE_USER>',
       MASTER_PASSWORD='<SLAVE_PASSWORD>';      

如果資料量很大的話，mysqldump會非常慢，此時直接拷貝資料檔案能節省不少時間：

在拷貝之前要先鎖定資料，然後再獲得相關的日志資訊（File & Position）：

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;      

接下來拷貝資料檔案時，如果是MyISAM表類型的話，直接拷貝即可；如果是InnoDB表類型的話，一定要先停止MySQL服務再拷貝，否則拷貝檔案可能無法使用。把拷貝的資料檔案直接複制到從伺服器的資料目錄。

最後還需要再指定一下日志資訊：

mysql> CHANGE MASTER TO
       MASTER_HOST='<MASTER_HOST>',
       MASTER_USER='<SLAVE_USER>',
       MASTER_PASSWORD='<SLAVE_PASSWORD>',
       MASTER_LOG_FILE='<File>',
       MASTER_LOG_POS=<Position>;      

有slave伺服器，操作步驟：

stop slave sql_thread;

show slave status擷取Relay_Master_Log_File和Exec_Master_Log_Pos的值

執行上面的步驟擷取資料

接着在新的從伺服器上設定日志資訊：

mysql> CHANGE MASTER TO
       MASTER_HOST='<MASTER_HOST>',
       MASTER_USER='<SLAVE_USER>',
       MASTER_PASSWORD='<SLAVE_PASSWORD>',
       MASTER_LOG_FILE='<Relay_Master_Log_File>',
       MASTER_LOG_POS=<Exec_Master_Log_Pos>;      

接着在新從伺服器上啟動複制，舊伺服器上啟動sql線程。

show slave status;

Slave_IO_State: Waiting for master to send event    (I/O線程運作狀态)

Master_Host: 192.168.110.128     (master ip)

Master_User: repl     (授權同步使用者)

Master_Port: 3306    (連接配接端口)

Connect_Retry: 60    (連接配接失敗重連間隔時間)

Master_Log_File: mysql-bin.000020    (I/O線程正在讀取的master二進制日志)

Read_Master_Log_Pos: 240752   (I/O線程正在讀取目前master二進制日志的位置)

Relay_Log_File: liu1-relay-bin.000007    (SQL線程正在讀取的中繼日志)

Relay_Log_Pos: 123892    (SQL線程正在讀取目前中繼日志的位置)

Relay_Master_Log_File: mysql-bin.000020    (包含最新事件由SQL線程執行的master二進制日志)

Slave_IO_Running: Yes    (I/O線程是否運作，運作)

Slave_SQL_Running: No    (SQL線程是否運作，不運作)

Replicate_Do_DB:     (同步資料庫)

Replicate_Ignore_DB:     (忽略資料庫)

Replicate_Do_Table:     (同步表)

Replicate_Ignore_Table:     (忽略表)

Replicate_Wild_Do_Table: test.%    (跨資料庫同步資料庫)

Replicate_Wild_Ignore_Table: %.%    (跨資料庫忽略資料庫)

Last_Errno: 0    (最近的狀态碼)

Last_Error:     (最近的錯誤資訊)

Skip_Counter: 0    (跳過N個events)

Exec_Master_Log_Pos: 123747    (由SQL線程執行的master二進制日志的位置)

Relay_Log_Space: 124089    (所有中繼日志的大小)

Until_Condition: None    (設定until，沒有設定)

Until_Log_File: (同步到哪個日志)

Until_Log_Pos: 0    (讀取到哪個位置)

Master_SSL_Allowed: No    (允許SSL，下面的都是ssl設定)

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0    (最近I/O線程傳回狀态碼)

Last_IO_Error:     (最近I/O線程傳回錯誤資訊)

Last_SQL_Errno: 0    (最近SQL線程傳回狀态碼)

Last_SQL_Error:     (最近SQL線程傳回錯誤資訊)

Master_Log_File和Read_Master_Log_Pos記錄了I/O線程正在讀取的master日志和位置

Relay_Master_Log_File和Exec_Master_Log_Pos記錄了SQL線程正在執行的master日志和位置

Relay_Log_File和Relay_Log_Pos記錄了SQL線程正在讀取的中繼日志和位置(對人資料沒有太大作用)

注：Relay_Log_File和Relay_Log_Pos的值是中繼日志中的值(程式需要的資料)，SQL線程将這兩個值及相應的SQL語句通過程式換算成在master二進制日志檔案中的位置，即Relay_Master_Log_File和Exec_Master_Log_Pos的值(人需要的資料)，即slave SQL線程正在同步的master日志位置。

判斷I/O和SQL是否同步，看Master_Log_File和Relay_Master_Log_File、Read_Master_Log_Pos和Exec_Master_Log_Pos的值是否相同。這裡I/O已經讀取所有master日志，但并沒有完全同步資料，因為SQL線程滞後了。

參考文章