mysql5.5主從同步複製配置，mysql5.5主從同步

mysql5.5主從同步複製配置，mysql5.5主從同步

在上篇文章《爛泥：學習mysql資料庫主從同步複製原理》中，我們介紹了有關mysql主從複製的基本原理。在這篇文章中，我們來實際測試下mysql5.5的主從同步複製功能。

注意mysql5.1.7以前版本與其以後的版本在主從同步部分參數不同。mysql5.1.7以後的版本中不支援master-connect-retry之類的參數。如果在my.cnf檔案中加入該類似的參數，mysql會在下次重啟時報錯。

說明：主庫master與從庫slave都是centos6.5 64bit，如下：

cat /etc/system-release

主庫master與從庫slave都是mysql資料庫版本都為5.5.39。

mysql –V

主庫master與從庫slave的IP分別如下：

master IP：192.168.1.213 slave IP：192.168.1.214

我們以主庫master的ilanni資料庫為實驗對象，如下：

根據《爛泥：學習mysql資料庫主從同步複製原理》文章可知，mysql的主從同步複製過程如下：

1、主庫master在執行SQL語句之後，會把相關的SQL語句記錄到binlog檔案中。

2、從庫slave串連主庫master，並從主庫master擷取該binlog檔案，存於本地relay-log檔案中，然後從master.info檔案讀取上次同步時的pos位置節點起執行SQL語句。

由以上資訊可知，我們的mysql主從同步複製實驗，需要以下幾個步驟：

1、 配置主庫master同步複製時的選項

2、 在主庫master上建立同步複製時的使用者並授權

3、 主庫master鎖表

4、 記錄主庫master的binlog以及pos位置節點

5、 匯出ilanni資料庫

6、 配置slave端同步複製時所需要的選項

7、 在從庫slave上建立資料庫ilanni並匯入備份

8、 解鎖主庫表

9、 設定從庫slave與主庫master同步

10、 在從庫slave上開啟同步

11、 查看從庫slave的relay-log以及master.info

12、 測試主從同步

一、配置主庫master同步複製時的選項

mysql的主從配置，我們只需要修改my.cnf檔案即可。如下：

cat /etc/my.cnf |grep -v ^#|grep -v ^$

log-bin=mysql-bin

max_binlog_size = 500M

server-id=1

binlog-do-db=ilanni

binlog-ignore-db=mysql

其中log-bin=mysql-bin表示啟用mysql二進位日誌，該項必須要啟用，否則mysql主從不會生效。

max_binlog_size=500M表示每個binlog檔案最大大小，當此檔案大小等於500M時，會自動產生一個新的記錄檔。注意：一條記錄不會寫在2個記錄檔中，所以有時記錄檔會超過此大小。

server-id=1表示mysql伺服器ID，該ID必須在該主從中是唯一的，預設是1，該ID可以自行自訂，但必須為數字。

binlog-do-db=ilanni表示需要同步的資料庫名字，如果是多個資料庫，就以此格式再寫一行即可。

binlog-ignore-db=mysql表示不需要同步的資料庫名字，如果是多個資料庫，就以此格式再寫一行即可。

注意：如果binlog-do-db和binlog-ignore-db不加的話，那麼預設是同步複製整個mysql資料庫。

二、在主庫master上建立同步複製時的使用者並授權

登入master端，建立資料庫使用者ilanni，並授權為replication slave許可權。如下：

grant replication slave on *.* to 'ilanni'@'%' identified by '123456';

select user,repl_slave_priv from user where user='ilanni';

通過，我們可以看到目前資料庫使用者ilanni被授予replication slave許可權，在user表中的表現為repl_slave_priv欄位為Y。

注意：replication slave許可權：只有擁有此許可權的使用者才可以查看從伺服器slave以及從主伺服器master讀取二進位日誌的許可權。

授權完畢後，我們需要在slave測試ilanni使用者是否可以串連master。如下：

ifconfig eth0|grep "inet addr"|awk '{print $2}'|cut -d: -f2

mysql -h 192.168.1.213 -uilanni -p123456

通過我們可以看到，目前在從伺服器slave已經可以正常串連master伺服器。

三、 主庫master鎖表

先鎖住主庫master的表，防止資料再寫入，導致主從資料庫不一致。使用如下命令鎖表：

flush tables with read lock;

這樣主庫master只能被讀取，而不能被寫入資料。如下：

通過可以看到，目前主庫已經不能再寫入資料。

注意：目前這個鎖表的終端不要退出，否則這個鎖就失效了。

四、記錄主庫master的binlog檔案名稱以及pos位置節點

為什麼要記錄此時主庫master的binlog檔案名稱以及pos位置節點？

因為當我們把主庫的資料庫遷移或匯入到從庫slave後，我們就會讓從庫slave從這個binlog檔案的該pos位置節點與主庫master同步。

查看主庫master的binlog檔案名稱及pos位置節點，如下：

show master status;

show master status\G;

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000003

通過以上兩張，我們可以很明顯的看到目前主庫的binlog為mysql-bin.000003，pos位置節點為1616。

五、匯出ilanni資料庫

在從庫slave與主庫master第一次同步資料時，有三種方法。

第一種方法就是在主庫鎖表後，使用tar把master庫直接打包，然後使用scp或者rsync把該打包檔案弄到從庫slave上。這種情況一般適用於網站或者業務在初始化，抑或在資料庫大於100G時建議使用。

第二種方法就是在主庫鎖表後，我們直接使用mysqldump命令匯出資料庫，然後在從庫上進行恢複。這個方法比較常見，所以我們一般是使用這個方法。

注意以上兩種方法，我們都需要進行在主庫master鎖表後進行操作。

第三種方法，其實我們不需要做其他工作。只需開啟同步複製即可。但是這個有一個前提就是，mysql的binlog必須齊全，這個齊全就是要包括該資料建立時的binlog也要存在。並且同步時，還必須要從最初始的binlog開始。所以，這種方法，我們一般不使用。

下面我們是通過第二種方法來匯出ilanni資料庫，如下：

mysqldump -uroot -p123456 ilanni>ilanni.sql

ll -h |grep ilanni.sql

現在我們再把ilanni資料庫的備份檔案ilanni.sql，通過scp命令複製到從庫slave上，如下：

scp ilanni.sql root@192.168.1.214:/root

六、配置從庫slave同步複製時所需要的選項

從庫slave上我們只需要在my.cnf檔案中，修改server-id值為唯一即可。如下：

cat /etc/my.cnf|grep -v ^#|grep -v ^$

七、在從庫slave上建立資料庫ilanni並匯入備份

我們在第五步已經把ilanni的備份檔案通過scp命令複製到從庫，現在我們需要在從庫slave上，然後把備份的資料匯入進去。如下：

create database ilanni;

mysql –uroot –p123456 ilanni</root/ilanni.sql

通過，我們可以看到目前從庫已經完全恢複ilanni資料庫。

八、解鎖主庫master表

從庫slave的ilanni資料庫建立並匯入備份後，我們現在來解鎖主庫master表，使用如下命令：

unlock tables;

九、設定從庫slave與主庫master同步

在第七步中我們已經恢複ilanni資料庫的資料，我們來開始設定從庫slave與主庫master同步，使用如下命令：

change master to master_host='192.168.1.213',master_user='ilanni',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=1616, master_connect_retry=100;

其中：master_host表示是主庫的IP

master_user表示主庫master上允許同步的使用者

maser_password表示同步處理的使用者的密碼

master_log_file表示從哪個binlog檔案開始同步

master_log_pos表示從該binlog檔案的那個pos節點位置開始同步

master_connect_retry表示從庫slave與主庫master同步周期，預設是60s。

注意：master_log_file和master_log_pos，就是我們在第四步記錄的binlog檔案名稱和pos位置節點。

十、在從庫slave上開啟同步

以上配置完畢後，我們現在來開啟主從同步。開啟主從同步，我們需要在從庫上開啟。開啟主從同步非常簡單，只需一條命令即可，如下：

start slave;

以上就開啟了mysql主從同步複製的開關。我們使用如下命令檢查，同步是否正常。如下：

show slave status\G;

我們查看同步是否主要是查看Slave_IO_Running與Slave_SQL_Running選項。如果正常同步，這兩選必須同時為YES。

如果Slave_IO_Running為NO，說明可能是從庫與主庫的網路不通。

如果Slave_SQL_Running為NO，說明很可能是從庫與主庫的資料不一致。

通過，我們可以看到目前Slave_IO_Running和Slave_SQL_Running都為YES。說明現在主從同步是正常的。

並且通過，我們也可以看到從庫slave與主庫master剛開始同步時的binlog檔案名稱以及開始同步時的pos位置節點。

十一、查看從庫slave的relay-log以及master.info

現在我們來查看從庫relay-log以及master.info資訊，我們首先看relay-log資訊，如下：

mysqlbinlog ilanni-relay-bin.000002|more

通過這張圖，我們可以在relay-log日誌中看到，從庫slave開始同步主庫的binlog檔案名稱以及同步複製時的pos位置節點。

現在我們來查看master.info，如下：

cat master.info |more

可以看到這個檔案儲存了從庫slave同步主庫master時的相關資訊：IP、使用者、密碼、binlog檔案名稱、pos位置節點、同步周期。

十二、測試主從同步

現在我們來測試下，mysql的主從同步。先在主庫master上給ilanni資料庫插入和刪除一條資料。如下：

insert into ilannitable values(6);

delete from ilannitable where id=2;

select id from ilannitable;

此時主庫master的ilanni資料庫中只有1、3、4、5、6，這個5條資料。

現在我們登入從庫slave，查看下ilanni資料庫的情況。如下：

通過，我們可以看到從庫slave中的ilanni資料庫和主庫master中的ilanni資料庫，資料是一致。說明主從已經同步複製已經成功。

現在我們再來查看master.info檔案的資訊，如下：

mysqlbinlog /usr/local/mysql/data/mysql-bin.000003

cat master.info |more

通過中，主庫的binlog檔案的pos位置節點與master.info對比。我們可以發現master.info檔案中確實記錄了mysql在同步複製時的binlog檔案名稱以及pos位置節點。

至此，有關mysql主從同步複製的實驗暫時告一段落。

歡迎大家訪問我的個人網站 萌萌的IT人