MySQL數(shù)據(jù)庫(kù)的主從復(fù)制是一種常見(jiàn)的數(shù)據(jù)備份和高可用性解決方案。通過(guò)配置主從復(fù)制，可以實(shí)現(xiàn)將數(shù)據(jù)從一個(gè)MySQL服務(wù)器（主服務(wù)器）同步到另一個(gè)（從服務(wù)器）。

前言

mysql數(shù)據(jù)庫(kù)的主從同步設(shè)置需要修改主庫(kù)和從庫(kù)的配置文件，并執(zhí)行同步指令，步驟并不復(fù)雜。但是在部署過(guò)程中還是遇到一些問(wèn)題，找了很久好像沒(méi)人遇到相同的問(wèn)題，因此將部署流程與遇到的問(wèn)題分享出來(lái)，希望遇到同樣問(wèn)題時(shí)有參考依據(jù)。

1.主庫(kù)（主服務(wù)器）配置

1.1修改主庫(kù)配置文件，啟用二進(jìn)制日志

數(shù)據(jù)庫(kù)配置文件所在目錄：
CentOS7安裝mysql后，配置文件默認(rèn)路徑為：/etc/my.cnf
Windows安裝mysql后，配置文件默認(rèn)路徑為：C:\ProgramData\MySQL\MySQL Server 5.7
本次安裝的主服務(wù)器CentOS7中，因此需要進(jìn)入到/ect目錄下，修改my.cnf配置文件：

#進(jìn)入etc目錄下
cd /etc
#編輯mysql配置文件
vi my.cnf

在配置文件[mysqld]最后一行，添加以下內(nèi)容：

#mysql主庫(kù)配置
server_id = 88               #集群唯一標(biāo)識(shí)，主庫(kù)從庫(kù)不能重復(fù)(值為數(shù)據(jù)庫(kù)IP)
log_bin = mysql-bin          #開(kāi)啟二進(jìn)制日志
expire_logs_days = 7         #日志有效期（天）

內(nèi)容說(shuō)明：#集群唯一標(biāo)識(shí)，主庫(kù)從庫(kù)不能重復(fù)，建議值取數(shù)據(jù)庫(kù)IP，避免重復(fù)（該項(xiàng)必須配置）
server_id = 88
#開(kāi)啟mysql二進(jìn)制日志（該項(xiàng)必須配置）
log_bin = mysql-bin
#設(shè)置同步日志有效期（天），到期自動(dòng)清理，避免磁盤(pán)占用空間過(guò)大（該項(xiàng)建議配置）
expire_logs_days = 7

1.2重啟數(shù)據(jù)庫(kù)服務(wù)

Mysql數(shù)據(jù)庫(kù)修改my.cnf配置文件后，需要重啟數(shù)據(jù)庫(kù)才能使修改的配置文件生效：

systemctl restart mysqld

重啟數(shù)據(jù)庫(kù)后，可以查看數(shù)據(jù)庫(kù)的運(yùn)行狀態(tài)，確保數(shù)據(jù)庫(kù)正常運(yùn)行(active)：

systemctl status mysqld

1.3創(chuàng)建遠(yuǎn)程連接賬戶

從庫(kù)同步主庫(kù)的數(shù)據(jù)，那么從庫(kù)需要先連接到主庫(kù)。初始的root賬戶只能在主庫(kù)服務(wù)器中登錄，在從庫(kù)所在服務(wù)器中使用該賬戶是無(wú)法登錄的，因此需要?jiǎng)?chuàng)建一個(gè)遠(yuǎn)程連接的賬戶：
登錄數(shù)據(jù)庫(kù)：mysql -uroot -p數(shù)據(jù)庫(kù)密碼在Windows中直接通過(guò)命令行登錄到mysql，需要將mysql添加到環(huán)境變量。本次創(chuàng)建一個(gè)遠(yuǎn)程連接賬戶’slave’，密碼為’test’(可以根據(jù)自己需要?jiǎng)?chuàng)建不同的賬戶密碼)，賦予賬戶只有復(fù)制權(quán)限：

方法一（分步執(zhí)行）：

創(chuàng)建mysql賬戶：

CREATE USER 'slave'@'%' IDENTIFIED BY 'test';

%表示賬戶開(kāi)通遠(yuǎn)程連接，允許所有IP通過(guò)該賬戶登錄數(shù)據(jù)庫(kù)。

授權(quán)該賬戶只有復(fù)制的權(quán)限：

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';

REPLICATION SLAVE 表示僅給該賬戶復(fù)制的權(quán)限，*.*表示可以該賬戶的權(quán)限對(duì)所有的數(shù)據(jù)庫(kù)和數(shù)據(jù)表都有效，可以復(fù)制所有數(shù)據(jù)庫(kù)和表，%表示所有IP都可以通過(guò)該賬戶連接到數(shù)據(jù)庫(kù)。

刷新權(quán)限：

flush privileges; 

方法二（合并執(zhí)行）：

創(chuàng)建一個(gè)賬戶只有復(fù)制權(quán)限(slave)，權(quán)限對(duì)所有數(shù)據(jù)庫(kù)和表生效(*.*)，‘slave’賬號(hào)，密碼為’test’:

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

1.4查看master狀態(tài)，記錄二進(jìn)制文件名(File)和位置(Position)：

復(fù)制主機(jī)的數(shù)據(jù)庫(kù)，需要先查詢(xún)主機(jī)數(shù)據(jù)庫(kù)二進(jìn)制日志的文件名和文件所在位置的，命令如下：

SHOW MASTER STATUS;

1.5停止主機(jī)數(shù)據(jù)庫(kù)的寫(xiě)操作

注意：從機(jī)開(kāi)始同步之前，主機(jī)不能再進(jìn)行寫(xiě)操作，如果主機(jī)仍在進(jìn)行寫(xiě)操作，會(huì)導(dǎo)致同步失敗，導(dǎo)致同步無(wú)法繼續(xù)執(zhí)行。因此，建議在執(zhí)行同步之前，先把所有連接到mysql數(shù)據(jù)庫(kù)的jar、tomcat、中間件、exe程序全部停止，停止程序往主機(jī)繼續(xù)寫(xiě)入數(shù)據(jù)，同步時(shí)確保數(shù)據(jù)的一致性。

2.從庫(kù)（從機(jī)）配置

2.1修改從庫(kù)配置文件

本次從機(jī)安裝在Windows系統(tǒng)中，需要進(jìn)入mysql的配置文件目錄下，修改配置文件
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
{如果從機(jī)安裝在CentOS7下，則需要修改/etc/my.cnf配置文件，添加以下內(nèi)容}

簡(jiǎn)單的配置只需要需改從機(jī)的id即可完成。修改server-id，改成與主機(jī)不沖突的值（建議取從機(jī)的IP，避免沖突）。
進(jìn)階配置如下圖所示：

#mysql從機(jī)配置
#從機(jī)唯一標(biāo)識(shí)，與主庫(kù)不能重復(fù)(值取IP地址)
server-id=66
#設(shè)置日志保存時(shí)長(zhǎng)
expire_logs_days=7
#數(shù)據(jù)庫(kù)宕機(jī)后自動(dòng)恢復(fù)日志，從庫(kù)建議開(kāi)啟，有利于數(shù)據(jù)一致性
relay_log_recovery=1

2.2重啟從機(jī)Mysql服務(wù)

本次從機(jī)安裝在Windows系統(tǒng)中，安裝Mysql后會(huì)出現(xiàn)在系統(tǒng)服務(wù)中。修改配置文件后，需要重啟系統(tǒng)服務(wù)才能適用新的改動(dòng)：

如果從機(jī)安裝在CentOS7下，則通過(guò)命令重啟Mysql:

systemctl restart mysqld

2.3導(dǎo)入數(shù)據(jù)庫(kù)

注意：如果已停止所有程序?qū)?shù)據(jù)庫(kù)的寫(xiě)入操作，則可以直接將數(shù)據(jù)庫(kù)全部同步過(guò)來(lái)，無(wú)需創(chuàng)建導(dǎo)入數(shù)據(jù)庫(kù)，此步驟可忽略進(jìn)入下一步。直接看下一個(gè)步驟：2.4開(kāi)始主從同步。

由于本次同步時(shí)，未停止程序?qū)?shù)據(jù)庫(kù)寫(xiě)入操作，導(dǎo)致在使用中的數(shù)據(jù)庫(kù)無(wú)法同步到從機(jī)中，因此需要手動(dòng)導(dǎo)入數(shù)據(jù)庫(kù)。雖然設(shè)置了主從同步，但是如果主庫(kù)未停止寫(xiě)入，從庫(kù)并不會(huì)將主機(jī)在使用中的數(shù)據(jù)庫(kù)直接同步復(fù)制到從機(jī)。因此必須先將主機(jī)的數(shù)據(jù)庫(kù)導(dǎo)出，再導(dǎo)入到從機(jī)的數(shù)據(jù)庫(kù)中。從機(jī)先創(chuàng)建數(shù)據(jù)庫(kù)，新創(chuàng)建的數(shù)據(jù)庫(kù)名字、字符集、排序規(guī)則必須和主庫(kù)原有的數(shù)據(jù)庫(kù)相同。然后再執(zhí)行下一步。

數(shù)據(jù)庫(kù)創(chuàng)建好后，將主機(jī)中導(dǎo)出的數(shù)據(jù)庫(kù)導(dǎo)入到從機(jī)中：

2.4開(kāi)始主從同步

重啟Mysql后，可以設(shè)置從機(jī)的數(shù)據(jù)庫(kù)同步到主機(jī)。設(shè)置同步到的主機(jī)信息（連接的IP和賬戶），執(zhí)行從機(jī)執(zhí)行同步命令。

登錄從機(jī)數(shù)據(jù)庫(kù)：mysql -uroot -p數(shù)據(jù)庫(kù)密碼

登錄成功后，在從機(jī)數(shù)據(jù)庫(kù)命令行執(zhí)行以下指令，設(shè)置主機(jī)的連接參數(shù)。使從機(jī)可以連接到主機(jī)：

change master to master_host='主機(jī)IP',master_user='數(shù)據(jù)庫(kù)賬戶',master_password='數(shù)據(jù)庫(kù)密碼',master_log_file='數(shù)據(jù)庫(kù)二進(jìn)制文件',master_log_pos=文件位置參數(shù);

從機(jī)連接到主機(jī)后，開(kāi)始同步，從機(jī)復(fù)制主機(jī)的二進(jìn)制日志：

start slave;

2.5查看從機(jī)狀態(tài)

注意：從機(jī)開(kāi)始同步之前，主機(jī)不能再進(jìn)行寫(xiě)操作，如果主機(jī)仍在進(jìn)行寫(xiě)操作，會(huì)導(dǎo)致同步失敗，Slave_SQL_Running欄顯示為NO，Last_Error會(huì)重復(fù)出現(xiàn)報(bào)錯(cuò)，導(dǎo)致同步無(wú)法繼續(xù)執(zhí)行。

#查看從機(jī)狀態(tài)
show slave status;

如果在數(shù)據(jù)庫(kù)命令行中執(zhí)行該命令，回顯的信息是沒(méi)有分行的，行顯示的數(shù)據(jù)錯(cuò)亂，可以通過(guò)增加“\G”分行展示從機(jī)狀態(tài)：

#分行查看從機(jī)狀態(tài)
show slave status\G;

如果從機(jī)的狀態(tài)如下圖所示，則說(shuō)明同步是成功的：

2.6同步報(bào)錯(cuò)解決辦法一

注意：從機(jī)開(kāi)始同步之前，主機(jī)不能再進(jìn)行寫(xiě)操作。如果主機(jī)仍在進(jìn)行寫(xiě)操作，會(huì)導(dǎo)致同步失敗，Slave_SQL_Running欄顯示為NO，Last_Error會(huì)重復(fù)出現(xiàn)報(bào)錯(cuò)代碼1032，導(dǎo)致同步無(wú)法繼續(xù)執(zhí)行。

那么怎么停止主機(jī)的寫(xiě)操作呢？停止主機(jī)的寫(xiě)操作，一定要在主機(jī)上停止所有的連接到數(shù)據(jù)庫(kù)的程序和中間件，讓程序不再往數(shù)據(jù)庫(kù)中寫(xiě)入數(shù)據(jù)即可。

主機(jī)中往數(shù)據(jù)庫(kù)寫(xiě)的操作停止后，從機(jī)再重新執(zhí)行同步操作：

start slave;

2.7同步報(bào)錯(cuò)解決辦法二

查閱了許多資料，大佬們的做法是這樣的，從機(jī)登錄數(shù)據(jù)庫(kù)后執(zhí)行以下命令：

 #sql_slave_skip_counter =1表示跳過(guò)1步錯(cuò)誤，后面的數(shù)字可變，出現(xiàn)多少個(gè)報(bào)錯(cuò)則把數(shù)字改成這個(gè)數(shù)量可以跳過(guò)報(bào)錯(cuò)繼續(xù)同步
stop slave;
set global sql_slave_skip_counter =1;  
start slave;

但是我按照大佬們的操作，還是沒(méi)解決問(wèn)題。如果錯(cuò)誤比較多，或者中途還是會(huì)出現(xiàn)報(bào)錯(cuò)，導(dǎo)致無(wú)法完成主從同步，那么建議修改從機(jī)的數(shù)據(jù)庫(kù)配置文件，跳過(guò)所有1032代碼的錯(cuò)誤：

在配置文件中[mysqld]添加以下內(nèi)容：

#該方法用于跳過(guò)所有1032錯(cuò)誤
slave-skip-errors=1032

如果想跳過(guò)其他的錯(cuò)誤代碼，可以把slave-skip-errors后面的代碼替換為出現(xiàn)的錯(cuò)誤代碼。更粗暴的是，可以跳過(guò)所有的錯(cuò)誤代碼：

#該方法用于跳過(guò)所有錯(cuò)誤代碼
slave-skip-errors=all

修改配置文件之后，重新啟動(dòng)mysql數(shù)據(jù)庫(kù)。

重啟數(shù)據(jù)庫(kù)之后，從機(jī)中重新執(zhí)行主從同步命令：

start slave;

3.同步測(cè)試

在主機(jī)中新建數(shù)據(jù)庫(kù)，在數(shù)據(jù)庫(kù)中添加數(shù)據(jù)表，查看從庫(kù)中是否將主庫(kù)修改的內(nèi)容同步過(guò)來(lái)：

主機(jī)新增了“ibms_ksy”數(shù)據(jù)庫(kù)，同時(shí)導(dǎo)入了數(shù)據(jù)庫(kù)報(bào)表。我們查看從機(jī)是否同步了新增的數(shù)據(jù)庫(kù)：

可以看到，從機(jī)同步了主機(jī)新增的數(shù)據(jù)庫(kù)，并將主庫(kù)中的數(shù)據(jù)表也同步了過(guò)來(lái)。

到此這篇關(guān)于Mysql數(shù)據(jù)庫(kù)主從同步的實(shí)現(xiàn)示例的文章就介紹到這了,更多相關(guān)Mysql 主從同步內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論