本文由秀依林楓提供友情贊助，首發於爛泥行天下。
在上篇文章《爛泥：學習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 [email protected]:/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主從同步復制的實驗暫時告一段落。
