MySQL 主從複製

首先：資料量往往直線上升，單獨一臺資料庫伺服器開始出現效能的瓶頸，資料訪問越來越慢。

其次：備份也變得困難了，因為mysqldump是匯出一份文字檔案，而資料量特別大的時候，這樣的備份往往需要很長時間，可能有人會說，我們可以直接透過複製資料檔案來備份資料庫，這樣很方便，快捷，不錯，這樣是比mysqldump方便快捷，但是，直接複製資料檔案備份的方式要求我們必須先關閉mysql服務，然後再複製資料檔案，否則，你複製的檔案很可能是壞的。而實際執行的mysql服務往往要求在任何時候都不可以停止服務，所以這樣的備份方式在此情況下不可行。

如果你遇到了類似上面的問題，你就可以使用建立MySQL主從伺服器的方式來解決，下面先來看看主從伺服器的設定：

前提：MySQL主從伺服器最好使用相同的軟體版本，以避免不不可預期的故障。

首先設定MySQL主伺服器：

在主伺服器上為從伺服器建立一個使用者：

grant replication slave on *.* to '使用者名稱主機' identified by '密碼';

編輯主伺服器的配置檔案：/etc/my.cnf的mysqld處

server-id = 1
log-bin
binlog-do-db=需要備份的資料庫名，如果備份多個資料庫，重複設定這個選項即可
binlog-ignore-db=不需要備份的資料庫苦命，如果備份多個資料庫，重複設定這個選項即可

編輯從伺服器的配置檔案：/etc/my.cnf

server-id=2
master-host=主機
master-user=使用者名稱
master-password=密碼
master-port=埠
replicate-do-db=需要備份的資料庫名，如果備份多個資料庫，重複設定這個選項即可

記得先手動同步一下主從伺服器中要備份的資料庫，然後重啟主，從伺服器。

要驗證主從設定是否已經成功，可以登入從伺服器輸入如下命令：

mysql> show slave statusG

會得到類似下面的列表：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果這兩個選項不全是Yes，那就說明你前面某個步驟配置錯了。

如果你的設定是正確的，嘗試在主伺服器上插入若干條記錄，然後你再轉到從伺服器，會發現相應的新記錄已經自動同步過來了。

如果你的主從伺服器已經配置好了，那麼你在應用程式中，只要保證所有的insert/delete/update操作是在主伺服器上進行的，那麼相應的資料變化會自動同步到從伺服器上，這樣，我們就可以把select操作分擔到多臺從資料庫上，從而降低伺服器的載荷。

如果你想使用複製資料檔案的方式來備份資料庫，只要在從伺服器上的mysql命令列先鍵入slave stop;然後複製資料庫檔案，複製好了，再 在mysql命令列鍵入slave start;啟動從伺服器，這樣就即備份了資料有保證了資料完整性，而且整個過程中主伺服器的mysql無需停止。

-----------------------------------------------------------------------------------

提示：如果修改了主伺服器的配置，記得刪除從伺服器上的master.info檔案。否則從伺服器使用的還是老配置，可能會導致錯誤。

-----------------------------------------------------------------------------------

注意：關於要複製多個資料庫時，binlog-do-db和replicate-do-db選項的設定，網上很多人說是用半形逗號分隔，經過測試，這樣的說法是錯誤的，MySQL官方文件也明確指出，如果要備份多個資料庫，只要重複設定相應選項就可以了。

比如：

binlog-do-db=a
binlog-do-db=b

replicate-do-db=a
replicate-do-db=b

-----------------------------------------------------------------------------------

補充：從伺服器上my.cnf中的master-*的設定僅在第一次生效，後儲存在master.info檔案裡。

-----------------------------------------------------------------------------------

在從伺服器上使用show slave status
Slave_IO_Running,為No,則說明IO_THREAD沒有啟動，請執行slave start [IO_THREAD]
Slave_SQL_Running為No則複製出錯,檢視Last_error欄位排除錯誤後執行slave start [SQL_THREAD]

檢視Slave_IO_State欄位
空 //複製沒有啟動
Connecting to master//沒有連線上master
Waiting for master to send event//已經連上

-----------------------------------------------------------------------------------

可以使用LOAD DATA FROM MASTER語句來建立slave。但有約束條件：
資料表要全部是MyISAM表，必須有SUPER許可權，master的複製使用者必須具備RELOAD和SUPER許可權。
在master端執行RESET MASTER清除已有的日誌變更，
此時slave端會因為找不到master日誌無法啟動IO_THREAD，請清空data目錄下
relay-log.info,hosname-relay-bin*等檔案重新啟動mysql
中繼日誌檔案預設的檔案為hostname-relay-bin.nnn和hostname-relay-bin.index。可用從伺服器的--
relay-log和--relay-log-index選項修改。在從伺服器中還有一個relay-log.info中繼資訊檔案，可用
--relay-log-info-file啟動選項修改檔名。
雙機互備則是兩個mysql同時配置為master及slave

-----------------------------------------------------------------------------------

主伺服器上的相關命令：
show master status
show slave hosts
show {master|binary} logs
show binlog events
purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'
reset master(老版本flush master)
set sql_log_bin={0|1}

-----------------------------------------------------------------------------------

從伺服器上的相關命令:
slave start
slave stop
SLAVE STOP IO_THREAD //此執行緒把master段的日誌寫到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此執行緒把寫到本地的日誌應用於資料庫
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //動態改變master資訊
PURGE MASTER [before 'date'] 刪除master端已同步過的日誌

-----------------------------------------------------------------------------------

--read-only