mysql主從複製淺析（一）

標籤：

複製概述

mysql內建的複製功能是構建大規模、高效能應用的基礎。複製解決的基本問題是讓一台伺服器的資料與其他的伺服器同步，以主備的方式構建資料系統。

常見用途

• 資料分布：在不同的地理位置分布資料，進行備份。
• 負載平衡：將讀操作分布到多個伺服器上（使用dns輪詢或lvs）
• 備份：是傳統備份的補充，不能完全替代備份
• 故障切換：避免單點失效
• 升級測試：使用一個更高版本的mysql作為備庫作為主庫的升級測試

工作原理

1. 在主庫上把更改記錄到二進位日誌（Bin Log）
2. 備庫上將主庫的日誌複製到自己的中繼日誌（Relay Log）上。
3. 備庫讀取中繼日誌的事件，寫入資料

配置複製範例1) 配置主庫和備庫

vi /etc/my.cnflog_bin = mysql-bin #啟動二進位日誌server_id = 128 #伺服器id,一般取ip的最後一段

2）重啟mysql

service mysql restart

3）主庫上建立複製帳號

mysql>GRANT REPLICATION SLAVE ON *.* to ‘replication‘@‘%‘ identified by ‘123456‘;

4) 主庫上查詢master狀態

mysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000015 |      107 |              |                  |+------------------+----------+--------------+------------------+

註：執行完此步驟後不要再操作主伺服器MYSQL，防止主伺服器狀態值變化

5）備庫配置slave

mysql> CHANGE MASTER TO    -> MASTER_HOST=‘192.168.75.128‘,    -> MASTER_USER=‘replication‘,    -> MASTER_PASSWORD=‘123456‘,    -> MASTER_PORT=3306,    -> MASTER_LOG_FILE=‘mysql-bin.000015‘,    -> MASTER_LOG_POS=107,    -> MASTER_CONNECT_RETRY=10；//啟動複製功能mysql>  start slave;

6) 查看備伺服器複製功能狀態

mysql> show slave status\G************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.75.128                  Master_User: replication                  Master_Port: 3306                Connect_Retry: 10              Master_Log_File:           Read_Master_Log_Pos: 4               Relay_Log_File: t1-relay-bin.000015                Relay_Log_Pos: 4        Relay_Master_Log_File:              Slave_IO_Running: Yes  //此狀態必須YES            Slave_SQL_Running: Yes  //此狀態必須YES          .............

備伺服器中途複製

前面的設定是主備伺服器都剛建立，並且知道當前主庫的起始日誌，實際工作中的情境大多是已經運行了一段時間的主庫，然後用一台新安裝的備庫與之同步。下面是備伺服器中途複製的常用方法：

• 冷備份：關閉主庫，把資料複製到備庫。重啟主庫後在備庫執行 CHANGE MASTER TO指向主庫新的記錄檔
• 使用mysqldump：如果只包含InnoDB表，可以使用mysqldump傳輸複製資料，再指定二進位日誌座標 $ mysqldump --single-transaction --all-databases --master-data=1 --host=server1 | mysql --host=server2
• 使用Percona Xtabackup開源的熱備份工具

複製的工作模式

• 基於語句的複製：備庫記錄和執行主庫運行過的SQL
• 基於行的複製：備庫複製主庫每一行資料（推薦）

常用的主備架構

• 一主多備：在少量寫大量讀時，該配置十分有效，可以將讀操作分攤多個備庫上，直到主備之間的頻寬成為瓶頸。
• 雙主複製：包含兩台伺服器，每一個都配置成對方的主庫和備庫。使用情境是兩個不同的應用伺服器都需要串連一個可寫的資料庫。該配置主要有2個問題，兩伺服器同時修改一行資料和AUTO_INCREMENT問題。
• 雙主複製的被動模式：雙主複製的變體，主要區別是一台伺服器是唯讀被動伺服器。由於是有一台唯讀伺服器，所以沒有上面的問題，而且該模式下伺服器的配置是一樣的，所以容錯移轉和故障恢複很容易解決。

複製的管理和維護1)測量備庫延遲

SHOW SLAVE STATUS輸出的secondsbehindmaster理論上顯示了備庫的延時。

2)確定主備是都一致

• mysql內建工具CHECKSUM TABLE,當複製進行中時，該方法不可行。
• pt-talble-checksum 比較好的開源工具。

3)備庫從新同步備庫資料

備庫忽略了一些資料或人為的修改備庫的資料時就需要從新同步備庫，方法有： - 關閉備庫，重新從主庫複製一份資料，簡單但資料量大時不推薦 - 使用mysqldump轉儲受影響的資料，當主庫資料沒有發生改變時，該方法很好，但只同步一百萬行資料中的一小部分時效率不高。 - 開源工具pt-table-sync,比較主備之間不同的資料從而進行同步，效率很高

4)資料損毀解決方案

• 主庫意外關閉：備庫從主庫一個新的日誌開始複製資料，再用pt-talle-checksum重新同步主備。
• 備庫意外關閉：正常情況備庫重啟會去mastter.info尋找上次複製的位置，如果該檔案損壞或檔案本身資料是錯誤的，備庫可能會重新執行一些二進位日誌事件，造成如惟一索引這樣的錯誤，解決方案使用開源工具pt-slave-restart

mysql主從複製淺析（一）