MySQL資料庫主從同步配置

標籤：mysql主從配置

Mysql主從安裝配置

 

環境：

主從伺服器上的MySQL資料庫版本同為5.1.34

主機IP：192.168.0.1

從機IP：192.168.0.2
 

一. MySQL主伺服器配置

1.編輯設定檔/etc/my.cnf

# 確保有如下行   

server-id = 1

log-bin=mysql-bin

binlog-do-db=mysql  #需要備份的資料庫名，如果備份多個資料庫，重複設定這個選項即可

binlog-ignore-db=mysql  #不需要備份的資料庫名，如果備份多個資料庫，重複設定這個選項即可

log-slave-updates #這個參數一定要加上，否則不會給更新的記錄些到二進位檔案裡

slave-skip-errors #是跳過錯誤，繼續執行複製操作  
 

2.建立使用者

mysql> grant replication slave on *.* to [email protected] identified by ‘111111′;

# grant replication slave on *.* to ‘使用者名稱’@‘主機’ identified by ‘密碼’;

# 可在Slave上做串連測試: mysql -h 192.168.0.1 -u test -p
 

3.鎖主庫表

mysql> FLUSH TABLES WITH READ LOCK;
 

4.顯示主庫資訊

記錄File和Position，從庫設定將會用到

=====================

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_do_db | Binlog_ignore_db |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 106      |              |                  |

+------------------+----------+--------------+------------------+
 

5.另開一個終端，打包主庫

cd /usr/local/mysql  #mysql庫目錄

tar zcvf var.tar.gz var  

============================
 

二.MySQL從伺服器配置

1、傳輸拿到主庫資料包、解包

# cd /usr/local/mysql

# scp 192.168.0.1:/usr/local/mysql/var.tar.gz .

# tar zxvf var.tar.gz
 

2、查看修改var檔案夾許可權

# chown -R mysql:mysql var

3.編輯 /etc/my.cnf

server-id=2

log-bin=mysql-bin

master-host=192.168.0.1

master-user=slave

master-password=111111

master-port=3306

replicate-do-db=test  #需要備份的資料庫名

replicate-ignore-db=mysql #忽略的資料庫

master-connect-retry=60 #如果從伺服器發現主伺服器斷掉，重新串連的時間差(秒)

log-slave-updates #這個參數一定要加上，否則不會給更新的記錄些到二進位檔案裡

slave-skip-errors #是跳過錯誤，繼續執行複製操作
 

4、驗證串連MASTER

# mysql -h192.168.0.1 -uslave -ppassword

mysql> show grants for [email protected];

5、在SLAVE上設定同步

設定串連MASTER MASTER_LOG_FILE為主庫的File，MASTER_LOG_POS為主庫的Position

============================

mysql> slave stop;

mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.0.1‘,MASTER_USER=‘slave‘,MASTER_PASSWORD=‘111111‘,MASTER_LOG_FILE=‘
mysql-bin.000001‘,MASTER_LOG_POS=106;

6、啟動SLAVE服務

mysql> slave start;
 

7、查看SLAVE狀態

mysql> SHOW SLAVE STATUS\G;

其中 Slave_IO_Running 和 Slave_SQL_Running 兩列的值都為 "Yes"，表明 Slave 的 I/O 和 SQL 線程
都在正常運行。

8、解鎖主庫表

mysql> UNLOCK TABLES;

到此主從庫搭建成功。可以在主庫上插入資料測試同步是否正常。
 

常見錯誤及解決方案：

常見問題的處理：

  www.2cto.com  

1：在從庫上面show slave status\G;出現下列情況，

          Slave_IO_Running: Yes

          Slave_SQL_Running: No

          Seconds_Behind_Master: NULL

 

原因：

a.程式可能在slave上進行了寫操作

b.也可能是slave機器重起後，交易回復造成的.

 

解決方案：

 

進入master

 

mysql> show master status;

+----------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+----------------------+----------+--------------+------------------+

| mysql-bin.000040 | 324 | | |

+----------------------+----------+--------------+------------------+

然後到slave伺服器上執行手動同步

 

slave stop;

change master to 

master_host=‘10.14.0.140‘,

master_user=‘repl‘,

master_password=‘111111‘,

master_port=3306,

master_log_file=‘mysql-bin.000040‘,

master_log_pos=324;

slave start;

show slave status\G;

 

2、現象：從資料庫無法同步，show slave status顯示Slave_IO_Running為No,Seconds_Behind_Master
為null

 

       解決：重啟主要資料庫

 

             service mysql restart

 

             mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 98 | | |

+------------------+----------+--------------+------------------+

slave stop;

change master to Master_Log_File=‘mysql-bin.000001‘,Master_Log_Pos=98

slave start;

或是這樣：

stop slave;

set global sql_slave_skip_counter =1;

start slave;

 

這個現象主要是master資料庫存在問題，我在實際的操作中先重啟master後重啟slave即可解決這問題，
出現此問題，必須要要重啟master資料庫。
 

1.主輔庫同步主要是通過二進位日誌來實現同步的。

2.在啟動輔庫的時候必須先把資料同步，並刪除日誌目錄下的：master.info檔案。因為master.info記錄
了上次要串連主庫的資訊，如果不刪除，即使my.cnf裡進行了修改，也不起作用。因為讀取的還是
master.info檔案裡的資訊。

 

在mysql複製環境中,有8個參數可以讓我們控制,需要複製或需要忽略不進行複製的DB或table分別為: 

下面二項需要在Master上設定： 

Binlog_Do_DB:設定哪些資料庫需要記錄Binlog 

Binlog_Ignore_DB:設定哪裡資料庫不需要記錄Binlog 

  

優點是Master端的Binlog記錄所帶來的Io量減少，網路IO減少，還會讓slave端的IO線程,SQL線程減少，
從而大幅提高複製效能, 

缺點是mysql判斷是否需要複製某個事件不是根據產生該事件的查詢所在的DB,而是根據執行查詢時刻所在
的預設資料庫（也就是登入時指定的庫名或運行"use database"中指定的DB）,只有當前預設DB和配置中
所設定的DB完全吻合時IO線程才會將該事件讀取給slave的IO線程.所以,如果在預設 DB和設定須要複製的
DB不一樣的情況下改變了須要複製的DB中某個Table中的資料,該事件是不會被複製到Slave中去的,這樣就
會造成Slave端的資料和Master的資料不一致.同樣,在預設的資料庫下更改了不須要複製的資料庫中的資料,
則會被複製到slave端,當slave端並沒有該資料庫時,則會造成複製出錯而停止. 

  

下面六項需要在slave上設定： 

Replicate_Do_DB:設定需要複製的資料庫,多個DB用逗號分隔 

Replicate_Ignore_DB:設定可以忽略的資料庫. 

Replicate_Do_Table:設定需要複製的Table 

Replicate_Ignore_Table:設定可以忽略的Table 

Replicate_Wild_Do_Table:功能同Replicate_Do_Table,但可以帶萬用字元來進行設定。 

Replicate_Wild_Ignore_Table:功能同Replicate_Do_Table,功能同Replicate_Ignore_Table,可以帶萬用字元。  

  

優點是在slave端設定複製過濾機制,可以保證不會出現因為預設的資料庫問題而造成Slave和Master資料
不一致或複製出錯的問題. 

缺點是效能方面比在Master端差一些.原因在於:不管是否須要複製,事件都會被IO線程讀取到Slave端,
這樣不僅增加了網路IO量,也給Slave端的IO線程增加了Relay Log的寫入量.  

同步原理說明 

MySQL的Replication基於主伺服器在二進位日誌中跟蹤所有對資料庫的更改(更新、刪除等)。

MySQL使用3個線程來完成Replication工作，具體分布是主上1個相關線程、從上2個相關線程；

主的相關線程可以理解為主伺服器上SHOW PROCESSLIST的輸出中的Binlog Dump線程、從伺服器分別為IO和
SQL線程;

主伺服器建立將binlog中的內容發送到從伺服器。從伺服器I/O線程讀取主伺服器Binlog Dump線程發送的
內容並將該資料拷貝到從伺服器資料目錄中的中繼記錄檔（relay-log）裡，SQL線程用於讀取中繼日誌
並執行日誌中包含的更新。 

MySQL的Replication是單向，非同步同步 

MySQL同步機制基於master把所有對資料庫的更新、刪除等)都記錄在二進位日誌裡。因此，想要啟用同步
機制，在master就必須啟用二進位日誌。每個slave接受來自master上在二進位日誌中記錄的更新操作，
因此在slave上執行了這個操作的一個拷貝。應該非常重要地意識到，二進位日誌只是從啟用二進位日誌開
始的時刻才記錄更新操作的。所有的 slave必須在啟用二進位日誌時把master上已經存在的資料拷貝過來。
如果運行同步時slave上的資料和master上啟用二進位日誌時的資料不一致的話，那麼slave同步就會失敗。
把master上的資料拷貝過來的方法之一實在slave上執行 LOAD DATA FROM MASTER 語句。不過要注意，LOAD DATA FROM MASTER是從MySQL 4.0.0之後才開始可以用的，而且只支援master上的 MyISAM 類型表。同樣地，
這個操作需要一個全域的讀鎖，這樣的話傳送記錄到slave的時候在master上就不會有更新操作了。當實現了
自由鎖表熱備份時(在 MySQL 5.0中)，全域讀鎖就沒必要了。由於有這些限制，因此我們建議只在master上
相關資料比較小的時候才執行 LOAD DATA FROM MASTER 語句，或者在master上允許一個長時間的讀鎖。
由於每個系統之間 LOAD DATA FROM MASTER 的速度各不一樣，一個比較好的衡量規則是每秒能拷貝1MB資料。
這隻是的粗略的估計，不過master和slave都是奔騰700MHz的機器且用 100MBit/s網路連接時就能達到這個
速度了。slave上已經完整拷貝master資料後，就可以串連到master上然後等待處理更新了。如果 master
當機或者slave串連斷開，slave會定期嘗試串連到master上直到能重連並且等待更新。重試的時間間隔由 –master-connect-retry 選項來控制，它的預設值是60秒。每個slave都記錄了它關閉時的日誌位置。
master是不知道有多少個slave串連上來或者哪個slave從什麼時候開始更新。 
 

MySQL同步功能由3個線程(master上1個，slave上2個)來實現。執行 START SLAVE 語句後，slave就建立
一個I/O線程。I/O線程串連到master上，並請求master發送二進位日誌中的語句。master建立一個線程來把
日誌的內容發送到slave上。這個線程在master上執行 SHOW PROCESSLIST 語句後的結果中的 Binlog Dump
線程便是。slave上的I/O線程讀取master的 Binlog Dump 線程發送的語句，並且把它們拷貝到其資料目錄
下的中繼日誌(relay logs)中。第三個是SQL線程，salve用它來讀取中繼日誌，然後執行它們來更新資料。
如上所述，每個mster/slave上都有3個線程。每個 master上有多個線程，它為每個slave串連都建立一個線程，每個slave只有I/O和SQL線程。在MySQL 4.0.2以前，同步只需2個線程(master和slave各一個)。slave上的I/O
和SQL線程合并成一個了，它不使用中繼日誌。slave上使用2個線程的優點是，把讀日誌和執行分開成2個
獨立的任務。執行任務如果慢的話，讀日誌任務不會跟著慢下來。例如，如果slave停止了一段時間，那麼
I/O線程可以在slave啟動後很快地從master上讀取全部日誌，儘管SQL線程可能落後I/O線程好幾的小時。
如果slave在SQL線程沒全部執行完就停止了，但I/O線程卻已經把所有的更新日誌都讀取並且儲存在本地的中
繼日誌（relay-log）中了，因此在slave再次啟動後就會繼續執行它們了。這就允許在 master上清除二進位
日誌，因為slave已經無需去master讀取更新日誌了。執行 SHOW PROCESSLIST 語句就會告訴我們所關心的master和slave上發生的情況

MySQL資料庫主從同步配置