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

前言

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

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

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

數(shù)據(jù)庫配置文件所在目錄：
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主庫配置
server_id = 88               #集群唯一標(biāo)識，主庫從庫不能重復(fù)(值為數(shù)據(jù)庫IP)
log_bin = mysql-bin          #開啟二進(jìn)制日志
expire_logs_days = 7         #日志有效期（天）

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

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

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

systemctl restart mysqld

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

systemctl status mysqld

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

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

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

創(chuàng)建mysql賬戶：

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

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

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

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

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

刷新權(quán)限：

flush privileges; 

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

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

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

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

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

SHOW MASTER STATUS;

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

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

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

2.1修改從庫配置文件

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

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

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

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

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

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

systemctl restart mysqld

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

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

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

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

2.4開始主從同步

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

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

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

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

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

start slave;

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

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

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

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

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

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

2.6同步報錯解決辦法一

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

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

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

start slave;

2.7同步報錯解決辦法二

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

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

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

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

#該方法用于跳過所有1032錯誤
slave-skip-errors=1032

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

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

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

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

start slave;

3.同步測試

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

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

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

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

最新評論