mysql5.5主從同步復(fù)制配置_MySQL

在上篇文章《爛泥：學(xué)習(xí)mysql數(shù)據(jù)庫主從同步復(fù)制原理》中，我們介紹了有關(guān)mysql主從復(fù)制的基本原理。在這篇文章中，我們來實(shí)際測試下mysql5.5的主從同步復(fù)制功能。

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

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

cat /etc/system-release

主庫master與從庫slave都是mysql數(shù)據(jù)庫版本都為5.5.39。

mysql –V

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

master IP：192.168.1.213 slave IP：192.168.1.214

我們以主庫master的ilanni數(shù)據(jù)庫為實(shí)驗(yàn)對象，如下：

根據(jù)《爛泥：學(xué)習(xí)mysql數(shù)據(jù)庫主從同步復(fù)制原理》文章可知，mysql的主從同步復(fù)制過程如下：

1、主庫master在執(zhí)行SQL語句之后，會把相關(guān)的SQL語句記錄到binlog文件中。

2、從庫slave連接主庫master，并從主庫master獲取該binlog文件，存于本地relay-log文件中，然后從master.info文件讀取上次同步時的pos位置節(jié)點(diǎn)起執(zhí)行SQL語句。

由以上信息可知，我們的mysql主從同步復(fù)制實(shí)驗(yàn)，需要以下幾個步驟：

1、 配置主庫master同步復(fù)制時的選項

2、 在主庫master上創(chuàng)建同步復(fù)制時的用戶并授權(quán)

3、 主庫master鎖表

4、 記錄主庫master的binlog以及pos位置節(jié)點(diǎn)

5、 導(dǎo)出ilanni數(shù)據(jù)庫

6、 配置slave端同步復(fù)制時所需要的選項

7、 在從庫slave上創(chuàng)建數(shù)據(jù)庫ilanni并導(dǎo)入備份

8、 解鎖主庫表

9、 設(shè)置從庫slave與主庫master同步

10、 在從庫slave上開啟同步

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

12、 測試主從同步

一、配置主庫master同步復(fù)制時的選項

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

其中l(wèi)og-bin=mysql-bin表示啟用mysql二進(jìn)制日志，該項必須要啟用，否則mysql主從不會生效。

max_binlog_size=500M表示每個binlog文件最大大小，當(dāng)此文件大小等于500M時，會自動生成一個新的日志文件。注意：一條記錄不會寫在2個日志文件中，所以有時日志文件會超過此大小。

server-id=1表示mysql服務(wù)器ID，該ID必須在該主從中是唯一的，默認(rèn)是1，該ID可以自行自定義，但必須為數(shù)字。

binlog-do-db=ilanni表示需要同步的數(shù)據(jù)庫名字，如果是多個數(shù)據(jù)庫，就以此格式再寫一行即可。

binlog-ignore-db=mysql表示不需要同步的數(shù)據(jù)庫名字，如果是多個數(shù)據(jù)庫，就以此格式再寫一行即可。

注意：如果binlog-do-db和binlog-ignore-db不加的話，那么默認(rèn)是同步復(fù)制整個mysql數(shù)據(jù)庫。

二、在主庫master上創(chuàng)建同步復(fù)制時的用戶并授權(quán)

登錄master端，創(chuàng)建數(shù)據(jù)庫用戶ilanni，并授權(quán)為replication slave權(quán)限。如下：

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

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

通過上圖，我們可以看到目前數(shù)據(jù)庫用戶ilanni被授予replication slave權(quán)限，在user表中的表現(xiàn)為repl_slave_priv字段為Y。

注意：replication slave權(quán)限：只有擁有此權(quán)限的用戶才可以查看從服務(wù)器slave以及從主服務(wù)器master讀取二進(jìn)制日志的權(quán)限。

授權(quán)完畢后，我們需要在slave測試ilanni用戶是否可以連接master。如下：

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

mysql -h 192.168.1.213 -uilanni -p123456

通過上圖我們可以看到，目前在從服務(wù)器slave已經(jīng)可以正常連接master服務(wù)器。

三、 主庫master鎖表

先鎖住主庫master的表，防止數(shù)據(jù)再寫入，導(dǎo)致主從數(shù)據(jù)庫不一致。使用如下命令鎖表：

flush tables with read lock;

這樣主庫master只能被讀取，而不能被寫入數(shù)據(jù)。如下：

通過上圖可以看到，目前主庫已經(jīng)不能再寫入數(shù)據(jù)。

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

四、記錄主庫master的binlog文件名以及pos位置節(jié)點(diǎn)

為什么要記錄此時主庫master的binlog文件名以及pos位置節(jié)點(diǎn)？

因?yàn)楫?dāng)我們把主庫的數(shù)據(jù)庫遷移或?qū)氲綇膸靤lave后，我們就會讓從庫slave從這個binlog文件的該pos位置節(jié)點(diǎn)與主庫master同步。

查看主庫master的binlog文件名及pos位置節(jié)點(diǎn)，如下：

show master status;

show master status\G;

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

通過以上兩張截圖，我們可以很明顯的看到目前主庫的binlog為mysql-bin.000003，pos位置節(jié)點(diǎn)為1616。

五、導(dǎo)出ilanni數(shù)據(jù)庫

在從庫slave與主庫master第一次同步數(shù)據(jù)時，有三種方法。

第一種方法就是在主庫鎖表后，使用tar把master庫直接打包，然后使用scp或者rsync把該打包文件弄到從庫slave上。這種情況一般適用于網(wǎng)站或者業(yè)務(wù)在初始化，抑或在數(shù)據(jù)庫大于100G時建議使用。

第二種方法就是在主庫鎖表后，我們直接使用mysqldump命令導(dǎo)出數(shù)據(jù)庫，然后在從庫上進(jìn)行恢復(fù)。這個方法比較常見，所以我們一般是使用這個方法。

注意以上兩種方法，我們都需要進(jìn)行在主庫master鎖表后進(jìn)行操作。

第三種方法，其實(shí)我們不需要做其他工作。只需開啟同步復(fù)制即可。但是這個有一個前提就是，mysql的binlog必須齊全，這個齊全就是要包括該數(shù)據(jù)創(chuàng)建時的binlog也要存在。并且同步時，還必須要從最初始的binlog開始。所以，這種方法，我們一般不使用。

下面我們是通過第二種方法來導(dǎo)出ilanni數(shù)據(jù)庫，如下：

mysqldump -uroot -p123456 ilanni>ilanni.sql

ll -h |grep ilanni.sql

現(xiàn)在我們再把ilanni數(shù)據(jù)庫的備份文件ilanni.sql，通過scp命令復(fù)制到從庫slave上，如下：

scp ilanni.sql root@192.168.1.214:/root

六、配置從庫slave同步復(fù)制時所需要的選項

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

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

七、在從庫slave上創(chuàng)建數(shù)據(jù)庫ilanni并導(dǎo)入備份

我們在第五步已經(jīng)把ilanni的備份文件通過scp命令復(fù)制到從庫，現(xiàn)在我們需要在從庫slave上，然后把備份的數(shù)據(jù)導(dǎo)入進(jìn)去。如下：

create database ilanni;

mysql –uroot –p123456 ilanni

通過上圖，我們可以看到目前從庫已經(jīng)完全恢復(fù)ilanni數(shù)據(jù)庫。

八、解鎖主庫master表

從庫slave的ilanni數(shù)據(jù)庫創(chuàng)建并導(dǎo)入備份后，我們現(xiàn)在來解鎖主庫master表，使用如下命令：

unlock tables;

九、設(shè)置從庫slave與主庫master同步

在第七步中我們已經(jīng)恢復(fù)ilanni數(shù)據(jù)庫的數(shù)據(jù)，我們來開始設(shè)置從庫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節(jié)點(diǎn)位置開始同步

master_connect_retry表示從庫slave與主庫master同步周期，默認(rèn)是60s。

注意：master_log_file和master_log_pos，就是我們在第四步記錄的binlog文件名和pos位置節(jié)點(diǎn)。

十、在從庫slave上開啟同步

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

start slave;

以上就開啟了mysql主從同步復(fù)制的開關(guān)。我們使用如下命令檢查，同步是否正常。如下：

show slave status\G;

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

如果Slave_IO_Running為NO，說明可能是從庫與主庫的網(wǎng)絡(luò)不通。

如果Slave_SQL_Running為NO，說明很可能是從庫與主庫的數(shù)據(jù)不一致。

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

并且通過上圖，我們也可以看到從庫slave與主庫master剛開始同步時的binlog文件名以及開始同步時的pos位置節(jié)點(diǎn)。

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

現(xiàn)在我們來查看從庫relay-log以及master.info信息，我們首先看relay-log信息，如下：

mysqlbinlog ilanni-relay-bin.000002|more

通過這張圖，我們可以在relay-log日志中看到，從庫slave開始同步主庫的binlog文件名以及同步復(fù)制時的pos位置節(jié)點(diǎn)。

現(xiàn)在我們來查看master.info，如下：

cat master.info |more

可以看到這個文件保存了從庫slave同步主庫master時的相關(guān)信息：IP、用戶、密碼、binlog文件名、pos位置節(jié)點(diǎn)、同步周期。

十二、測試主從同步

現(xiàn)在我們來測試下，mysql的主從同步。先在主庫master上給ilanni數(shù)據(jù)庫插入和刪除一條數(shù)據(jù)。如下：

insert into ilannitable values(6);

delete from ilannitable where id=2;

select id from ilannitable;

此時主庫master的ilanni數(shù)據(jù)庫中只有1、3、4、5、6，這個5條數(shù)據(jù)。

現(xiàn)在我們登錄從庫slave，查看下ilanni數(shù)據(jù)庫的情況。如下：

通過上圖，我們可以看到從庫slave中的ilanni數(shù)據(jù)庫和主庫master中的ilanni數(shù)據(jù)庫，數(shù)據(jù)是一致。說明主從已經(jīng)同步復(fù)制已經(jīng)成功。

現(xiàn)在我們再來查看master.info文件的信息，如下：

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

cat master.info |more

通過上圖中，主庫的binlog文件的pos位置節(jié)點(diǎn)與master.info對比。我們可以發(fā)現(xiàn)master.info文件中確實(shí)記錄了mysql在同步復(fù)制時的binlog文件名以及pos位置節(jié)點(diǎn)。

至此，有關(guān)mysql主從同步復(fù)制的實(shí)驗(yàn)暫時告一段落。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com