# MySQL主從復制 [TOC] MySQL的主從復制有利于數據庫架構的健壯性、提升訪問速度和易于維護管理。 * 主從服務器互為備份 * 主從服務器讀寫分離分擔網站壓力 * 根據服務器拆分業務獨立并分擔壓力 ## MySQL主從復制原理過程 以下簡單描述下MySQL Replication的復制過程 1. slave服務器上執行`start slave`命令,開啟主從復制開關； 2. slave服務器的IO線程會通過 在Master上授權的復制用戶權限請求連接Master服務器，并請求從指定binlog日志文件位置(日志文件名和位置在配置主從復制服務時執行change master 命令時指定)之后發送binlog日志內容； 3. master服務器接受來自slave服務器的IO線程的請求后，master服務器上負責復制的IO線程根據slave服務器的IO線程請求的信息讀取指定binlog日志文件指定位置之后的binlog日志信息，然后返回給slave的IO線程，返回的信息中除了binlog日志內容外，還有本次返回日志內容后再master服務器端的新的binlog文件名稱以及在binlog中的下一個指定更新位置； 4. 當slave服務器的IO線程獲取到來自master服務器上IO線程發送日志內容以及日志文件以及位置點后，將binlog日志內容一次寫入到slave端自身的relaylog(**中繼日志**)文件(mysql-relay-bin.xxxxxx)的末尾，并將新的binlog文件名和位置記錄到**master-info**文件中，以便下次讀取master端新binlog日志時能夠告訴master服務器需要從新binlog日志的哪個文件哪個位置開始請求新的binlog日志內容； 5. slave服務器的SQL線程會實時的檢測本地relaylog中新增加的日志內容，然后及時的把log文件中的內容解析成在master端曾經執行的SQL語句的內容，并在自身的slave服務器上按語句的順序執行應用這些SQL語句，應用完畢后清理用過的日志； 6. 經過上面的過程，就可以確保在master端和slave端執行了同樣的SQL語句。當復制狀態正常的情況下，master端和slave端的數據是完全一樣的，MySQL的同步機制是有一些特殊的情況，具體請參考官方的說明，大多數情況下，我們不用擔心。 ### 主數據庫配置步驟 1. 開啟主數據庫的binlog（二進制日志功能），并設置server-id 2. 創建用于同步數據的賬號rep 3. 鎖表并查看當前日志名稱和位置(pos)**[如果備份時使用了master-info參數可不鎖表]** 4. 備份當前主數據庫的全部數據（全備） 5. 解鎖主數據庫，讓主數據庫繼續提供服務 6. 繼續往主數據庫寫數據 ### 從數據庫配置步驟 1. 把主數據庫備份的全備數據恢復到從數據庫 2. 設置從數據庫server-id，并確保這個ID沒有被別的MySQL服務所使用。 3. 在從數據庫上設置主從同步的相關信息，如主數據庫服務器的IP地址、端口號、同步賬號、密碼、binlog文件名、binlog位置(pos)點 4. 開始主從同步start slave； 5. 查看是否同步成功，show slave status\G; * * * * * # MySQL主從復制生產標準實踐過程 ## MySQL主從同步配置步驟 > 1. 準備兩臺數據庫環境，或者單臺多實例環境，保證能正常啟動和登錄； > 2. 配置my.cnf文件,主庫配置log-bin和server-id參數；從庫配置server-id(不能喝主庫和其他從庫一致)，一般不開啟`log-bin`從庫功能。（注意：修改完后重啟生效）； > 3. 登錄主庫使用grant語句增加用于從庫連接主庫同步的賬號，例如：rep并授予`replication slave`同步的權限； > 4. 登錄主庫，使用`flush table with read lock`（在同步的過程中窗口關閉失效，超時參數[ **interactive_timeout** 和 **wait_timeout** ]到了也失效），然后`show master status`查看binlog的位置狀態； > 5. 新增加一個窗口，linux命令行備份或導出原有數據庫數據，并拷貝到從庫所在服務器（如果數據量很大，并且允許停機，可以停機打包，而不用`mysqldump`）； > 6. `unlock tables`解鎖主庫； > 7. 把主庫到處的原有數據備份恢復到從庫； > 8. 根據主庫的`show master； status`查看到的binlog日志文件名和位置信息，在從庫上執行`change master to ...`語句； > 9. 從庫`start slave`開啟同步開關； > 10. 從庫`show slave status\G`檢查同步狀態，并在主庫進行更新測試。 * * * * * > **注意：** > 5.1版本的MySQL版本的鎖表語句是`flush tables with read lock;` > 5.5版本的MySQL的鎖表語句是`flush table with read lock;` ## 定義服務器角色 主庫(mysql master): [ip為192.168.247.129的3306端口] 從庫(mysql slave):[ip為192.168.247.129的3309端口] ## 數據庫環境準備 具備單機單數據庫多實例的環境或者兩臺服務器每臺機器有一個數據的環境 ## 主庫上需要執行的操作 ### 編輯主庫的配置文件 打開binlog日志(從庫不做級聯的話不建議開啟)和配置不重復的server-id ``` [root@curder.centos5 /usr/local/mysql/data] # egrep 'log-bin|server-id' /usr/local/mysql/data/3306/my.cnf log-bin=mysql-bin server-id = 3306 ``` ### 查看開啟結果 ``` # mysql -uroot -paaaaaa -S /usr/local/mysql/data/3306/mysql.sock -e "show variables like 'log_bin'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ ``` ### 建立同步賬號密碼 首先登陸mysql3306實例主數據庫 `mysql -uroot -paaaaaa -S /usr/local/mysql/data/3306/mysql.sock` 執行授權語句 ``` mysql> grant replication slave on *.* to 'rep'@'192.168.0.%' identified by 'reppasswd'; mysql> flush privileges; ``` *授權局域網內 **rep** 用戶通過 **reppasswd** 密碼進行同步* **另外：如果不存在全備** 需要在主庫上執行如下語句獲得全備 ``` mysql> flush table with read lock; #　備份期間禁止用戶寫操作(窗口不允許關閉) ``` **備份語句,獲得全備** `# mysqldump -uroot -paaaaaa -S /usr/local/mysql/data/3306/mysql.sock -B curder --events --master-data=2 > rep.sql` 最后操作完別忘了解除鎖定 **`unlock tables;`** > **注意：** > 5.1版本的MySQL版本的鎖表語句是`flush tables with read lock;` > 5.5版本的MySQL的鎖表語句是`flush table with read lock;` ## 從庫上需要執行的操作 ### 將全備導入到數據庫 ``` mysql -uroot -paaaaaa -S /usr/local/mysql/data/3308/mysql.sock < ~/rep.sql ``` ### 登陸從庫執行change master語句 ``` change master to master_host='192.168.0.15', master_port=3306, master_user='rep', # 賬號信息為主庫建立的用戶信息 master_password='reppasswd', # 在主庫設置同步賬戶時設置 master_log_file='mysql-bin.000003', # 該值通過在mysql master服務器全備前鎖定的值上show master status;查看 master_log_pos=2840; # 該值通過在master服務器上show master status;查看 ``` *上述語句信息以文件的形式存放在從庫的master.info中。* ### 開啟同步 ``` start slave ``` #### 查看是否同步成功語句 ``` show slave status\G ``` ``` Slave_IO_Running: Yes # 從庫IO進程(從master服務器取log的線程) Slave_SQL_Running: Yes # 從庫SQL進程(讀取relaylog 寫數據) Seconds_Behind_Master: 0 # 落后主庫的秒數 ``` **另外** # 一些常用語句 ``` show master status; # 查看master的狀態, 尤其是當前的二進制日志及位置 show slave status; # 查看slave的狀態. reset slave; # 重置slave狀態. start slave; # 啟動slave狀態(一旦啟動 則開始監聽msater的變化) stop slave; # 暫停slave狀態; ``` # MySQL主從復制原理要點 1. 異步方式同步； 2. 邏輯同步模式，多種模式，默認是通過SQL語句執行； 3. 主庫通過記錄binlog實現對從庫的同步； 4. 主庫1個IO線程，從庫1個IO線程和一個SQL線程來完成； 5. 從庫關鍵字文件`master.info`,`relay-log`,`relay-info`功能， 6. 如果從庫還想級聯從庫，需要打開`log-bin`和`log-slave-updates`參數 # 生產場景快速配置MySQL主從復制方案 1. 安裝好要配置的主從的數據庫，配置好log-bin和server-id參數 2. 無需配置主庫my.cnf文件，主庫的log-bin和server-id參數默認就是配置好的 3. 登錄主庫增加用于從庫連接主庫同步的賬號，例如：rep，并授權replication slave同步的權限 4. 使用凌晨`mysqldump`命令帶`--master-data=1`備份的全備數據恢復到從庫 5. 在從庫執行`change master to...`語句，無需binlog文件以及對應位置點(在步驟4中以及通過備份參數指定) 6. 從庫`start slave`開啟同步開關 7. 從庫`show slave status\G`，檢查同步狀態，并在主庫進行更新測試 # MySQL線程狀態 ## 復制主線程狀態`show processlist\G` 1. **sending binlog event to slave** 二進制日志由各種事件組成，一個事件通常為一個更新加一些其他信息。線程已經從二進制日志讀取了一個事件并且正將它發送到從服務器 2. finished reading one binlog;switching to next binlog 線程已經讀完二進制日志文件并且正打開下一個要大宋到服務器的日志文件 3. has send all binlog to slave;waiting for binlog to be updated 線程已經從二進制日志讀取所有主要的更新并已經發送到了從服務器。 現在正空閑，等待由主服務器上新的更新導致的出現在二進制日志中的新事件 4. waiting to finalize termination 線程停止時發生的一個很簡單的狀態 ## 復制從I/O線程狀態 該狀態出現在`Slave_IO_State`列，可以通過`show slave status`顯示。 * `Connection to master` 線程正試圖連接主服務器 * Checking master version 建立同主服務器之間的連接后立即臨時出現的狀態 * Registering slave onmaster 建立同主服務器之間的連接后立即臨時出現的狀態 * Requesting binlog dump 建立同主服務器之間的連接后立即臨時出現的狀態,線程向主服務器發送一條請求，索取從請求的二進制日志文件名和位置開始的二進制日志的內容 * Waiting to reconnect after a failed binlog request 如果二進制日志轉儲請求失敗(由于沒有連接)，線程進入休眠裝填，然后定期嘗試重新連接。可以使用`--master-connect-retry`選項指定重試之間的間隔 * Reconnecting after a failed binlog dump request 線程正嘗試重新連接主服務器 * Waiting for master to send event 線程已經連接上主服務器，正等待二進制日志事件到達。如果主服務器正空閑，會持續較長的時間。如果等到持續`slave_read_timeout`秒，則發生超時。此時線程認為連接被中斷并企圖重新連接。 * Queueing master event to the relay log 線程已經讀取一個事件，正將它復制到中繼日志供SQL線程來處理 * Waiting to reconnect after a failed master event read 讀取時(由于沒有連接)出現錯誤。線程企圖重新連接前將休眠`master-connect-retry`秒 * Reconnectiong after a failed master event read 線程正嘗試重新連接主服務器，當連接重新建立后，狀態變為`Waiting for master to send` * Waiting for slave SQL thread to free enough relay log space 正使用一個非零relay_log_space_limit值，中繼日志已經增長到其組合大小超過該值。I/O線程正等待知道SQL線程處理中繼日志內容并刪除部分中繼日志文件來釋放足夠的空間 * Waiting for slave mutex on exit 線程停止時發生的一個很簡單的狀態，I/O線程的State列也可以顯示語句的文本，這說明已經從中繼日志讀取了一個事件，從中提取了語句，并且正在執行語句。 # 復制從SQL線程狀態 * Reading event from the relay log 線程已經從中繼日志讀取一個事件，可以對事件進行處理 * Has read all relay log;waiting for the slave I/O thread to update it 線程已經處理了中繼日志文件中的所有事件，現在正等待I/O線程將新事件寫入中繼日志