當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql master-user_【MySQL】MySQL5.6数据库基于binlog主从（Master/Slave）同步安装与配置详解...

發(fā)布時間：2025/3/15 数据库 28 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql master-user_【MySQL】MySQL5.6数据库基于binlog主从（Master/Slave）同步安装与配置详解... 小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

主從數(shù)據(jù)庫同步原理

image.png

主從數(shù)據(jù)庫同步工作原理(流程)：

當(dāng)主庫的數(shù)據(jù)發(fā)生修改時，數(shù)據(jù)更改的記錄將寫入到主庫的二進(jìn)制文件中，從庫此時將會調(diào)用一個IO線程讀取主庫的二進(jìn)制文件，并與中繼日志作對比，并將存在差異的事件寫入到中繼日志中(當(dāng)兩日志內(nèi)容事件一致時，IO線程將處于睡眠狀態(tài))，然后從庫再調(diào)用SQL線程去讀取中繼日志，并將剛寫入的事件數(shù)據(jù)放入到從庫中以保持主從數(shù)據(jù)庫數(shù)據(jù)同步。

配置步驟：

安裝環(huán)境：

操作系統(tǒng)：Cent0S 6.9

數(shù)據(jù)庫版本：MySQL 5.6

主機(jī)A：192.168.206.134(Master)

主機(jī)B：192.168.206.201(Slave)

首先，需要注意幾點問題：

1、互相同步的兩臺mysql的版本必須保證大版本號一致。比如5.5+和5.6+之間同步數(shù)據(jù)，5.6的數(shù)據(jù)同步到5.5就會出現(xiàn)問題。保證大版本號一致很重要。

2、每臺服務(wù)器必須開啟binlog，不開啟binlog則根本無法開始數(shù)據(jù)同步。

3、每臺服務(wù)器必須配置不同的server-id，范圍在1到(2^32-1)之間。

以上三點都可能造成很多奇怪錯誤，請一定注意。

這里強(qiáng)調(diào)的數(shù)據(jù)庫的版本，是因為MySQL在5.6之前和之后的配置方式是不一樣的，后面將會提示到。

首先，要保證防火墻對3306端口的開啟，為了學(xué)習(xí)數(shù)據(jù)庫的主從配置，可以使用service iptables stop 命令直接關(guān)閉防火墻。

#service iptables stop

然后可以在兩臺機(jī)子之間進(jìn)行 ping操作，確保兩臺機(jī)器之間能夠想通

Master部分的配置

在Linux環(huán)境下MySQL的配置文件的位置是在 /etc/my.cnf

，在該文件下指定Master的完整配置如下：

mastercnf文件1.png

mastercnf文件2.png

其中，server-id用于標(biāo)識唯一的數(shù)據(jù)庫，取值為1到(2^32-1)

binlog-ignore-db:表示不需要同步的數(shù)據(jù)庫

binlog-do-db:表示需要同步的數(shù)據(jù)庫

然后重啟MySQL

service mysqld restart

輸入用戶名密碼進(jìn)入MySQL

#mysql -uroot -p

賦予從庫權(quán)限帳號，允許用戶在主庫上讀取日志，賦予192.168.206.201也就是Slave機(jī)器有File權(quán)限，只賦予Slave機(jī)器有File權(quán)限還不行，還要給它REPLICATION SLAVE的權(quán)限才可以

在Master數(shù)據(jù)庫命令行中輸入：

mysql>GRANT FILE ON *.* TO '數(shù)據(jù)庫用戶名'@'Slave地址' IDENTIFIED BY '數(shù)據(jù)庫登錄密碼'；//賦予File權(quán)限格式

mysql>GRANT FILE ON *.* TO 'root'@'192.168.206.201' IDENTIFIED BY 'mysql password';//賦予File權(quán)限

mysql>GRANT REPLICATION SLAVE ON *.* TO '數(shù)據(jù)庫用戶名'@‘Slave地址’ IDENTIFIED BY '數(shù)據(jù)庫登錄密碼';//賦予REPLICATION SLAVE權(quán)限格式

mysql>GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.206.201' IDENTIFIED BY 'mysql password';//賦予REPLICATION SLAVE權(quán)限

mysql>FLUSH PRIVILEGES;//刷新權(quán)限

重啟mysql，登錄mysql，顯示主庫信息：

mysql>show master status;

3查看master庫狀態(tài).png

這里的File、Position是在后面配置Slave的時候需要用到的，Binlog_Do_DB表示需要同步的數(shù)據(jù)庫，Binlog_Ignore_DB 表示Ignore的數(shù)據(jù)庫

另外提示：如果執(zhí)行這個步驟始終為Empty set(0.00 sec)，那說明前面的my.cnf沒配置對，需要仔細(xì)檢查。

Slave部分的配置

與Master的配置一樣，首先需要配置my.cnf文件，如下

4slave配置文件1.png

5slave配置文件2.png

在配置文件中，MySQL5.6之后的版本中沒有指定：

master-host=192.168.206.134 #Master的主機(jī)IP

master-user=root

master-password=mysql password #Master的MySQL密碼

新版本的配置這種方式是不適用的。如果，在MySQL5.6和之后的版本中配置從庫的時候，設(shè)置到了上邊的內(nèi)容，即指定了master-host、master-user等信息的話，重啟MySQL的時候就會報錯，查看數(shù)據(jù)庫的報錯信息(數(shù)據(jù)庫的目錄, /data/mysqldb/VM_128_194_centos.err )，可以看到master-host 被檢測數(shù)是一個未知的變量，因此會出現(xiàn)錯誤

在5.6以及后續(xù)版本的配置如下：

修改完/etc/my.cnf 文件之后，重啟一下MySQL

service mysqld restart

登錄進(jìn)入mysql控制臺

#mysql -uroot -p

在控制臺輸入

mysql> stop slave; //關(guān)閉Slave

mysql> change master to master_host='192.168.206.134',master_user='root',master_password='123456',master_log_file='mysql-bin.000003', master_log_pos=120;//指定master信息

mysql> start slave; //開啟Slave

在這里指定Master的信息，master_log_file是在配置Master的時候的File選項， master_log_pos是在配置Master的Position 選項，這里要進(jìn)行對應(yīng)。

更多關(guān)于change master語句的信息可參考：http://www.jianshu.com/p/ada9f34d8563

然后可以通過mysql> show slave status \G; 查看配置的信息：

6slave狀態(tài)文件.png

7slave狀態(tài)文件.png

在途中的Slave_IO_Running和Slave_SQL_Running都為yes，那么表示配置成功

各個字段含義可參考這篇文章：http://www.jianshu.com/p/3c4d7c6c6205

當(dāng)完成配置后

查看master的狀態(tài)：

mysql >show master status; //Position不應(yīng)該為0

mysql>show processlist;

//state狀態(tài)應(yīng)該為Has sent all binlog to slave; waiting for binlog to be updated

image.png

查看slave狀態(tài)：

mysql>show slave status;

//Slave_IO_Running 與 Slave_SQL_Running 狀態(tài)都要為Yes

mysql>show processlist;

//應(yīng)該有兩行state值為：

Has read all relay log; waiting for the slave I/O thread to update it

Waiting for master to send event

image.png

下面開始真機(jī)測試：

測試環(huán)境：windows10+Navicat11

以下測試是使用真機(jī)連接虛擬機(jī)中的主從庫

首先在Navicat中創(chuàng)建連接(物理機(jī)連接虛擬機(jī)的方法參考：http://www.jianshu.com/p/fa4db03db9ca)：

7創(chuàng)建連接.png

7創(chuàng)建連接2.png

連接的地址為虛擬機(jī)中主/從庫的地址

連接上去之后，分別在Master和Slave中創(chuàng)建數(shù)據(jù)庫，該數(shù)據(jù)庫名字為剛剛配置的需要同步的數(shù)據(jù)庫名字

接著開始在該數(shù)據(jù)庫中新建數(shù)據(jù)表

首先在主庫中創(chuàng)建

9刷新從表之后從表數(shù)據(jù)表自動創(chuàng)建.png

創(chuàng)建好之后右鍵刷新從庫，從庫自動創(chuàng)建表

8Navicat中新建數(shù)據(jù)表.png

同樣的，在主表添加數(shù)據(jù)

10主表添加數(shù)據(jù).png

添加完數(shù)據(jù)后有點刷新從表，從表的數(shù)據(jù)也自動刷新

另：

從主服務(wù)器得到一個快照版本

如果你的是MYISAM或者既有MYISAM又有INNODB的話就在主服務(wù)器上使用如下命令導(dǎo)出服務(wù)器的一個快照：

mysqldump -uroot -p --lock-tables --events --triggers --routines --flush-logs --master-data=2 --databases test > db.sql

試過只有INNODB的話就是用如下命令：

mysqldump -uroot -p --single-transaction --events --triggers --routines --flush-logs --master-data=2 --databases test > db.sql

將快照版本還原到從服務(wù)器上

mysqldump -uroot -p -h 10.1.1.76 test < db.sql

關(guān)于一些常見問題及解決方案

一、主從數(shù)據(jù)庫不同步問題

可以先查看一下進(jìn)程是否sleep太多

mysql> show processlist;

然后可以看看master的狀態(tài)

mysql>show master status;

如果都為正常，那就到slave上看看狀態(tài)

mysql>show slave status \G;

1、可能slave服務(wù)未開啟

數(shù)據(jù)庫控制臺輸入show slave status \G;打印出slave的狀態(tài)

mysql> show slave status \G;

主要看slave_IO_Running和Slave_SQL_Running，結(jié)果都需要為yes，如果不為yes，需要重啟slave服務(wù)

mysql> service slave restart;//重啟

如果服務(wù)未開啟，則開啟：

mysql> service slave start;

2、從數(shù)據(jù)庫連接失敗

在正常情況下，配置好服務(wù)器后，從庫不進(jìn)行修改操作，即從庫只有讀的權(quán)限，如果修改了從庫數(shù)據(jù)表，則會造成同步失敗，如發(fā)現(xiàn)失敗，用show slave status \G;查看服務(wù)器狀態(tài)信息：

mysql> show slave status \G;

查看Slave_SQL_Running狀態(tài)，如果出現(xiàn)slave_SQL_Running為NO時，表示從庫連接失敗，在Last_Error處會打印錯誤日志信息。出現(xiàn)該問題主要是事務(wù)回滾問題，解決方法有兩種。

1、

mysql> slave stop;//停止從服務(wù)

mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;//不要濫用

mysql>slave start;//啟動從服務(wù)

2、手動重設(shè)從服務(wù)器

mysql5.6之后的版本(包含5.6)

mysql> stop slave; #關(guān)閉Slavemysql> change master to master_host='主服務(wù)器IP地址',master_user='主庫用戶名',master_password='主庫登錄密碼',

master_log_file='mysql-bin.000004', master_log_pos=28125;//master_log_file是在配置master的時候的file選項，master_log_pos是在配置master時候position選項的 mysql> show master status;可查看

mysql> start slave; #開啟Slave

mysql5.6之前的版本：

mysql>change master to

master_host='主庫IP地址',

master_user='主庫用戶名',

master_password='主庫登錄密碼',

master_log_file='主庫文件信息',//同上

master_log_pos=' 主庫的position信息 ';//同上

如果數(shù)據(jù)庫相差較大，或者要求數(shù)據(jù)完全統(tǒng)一的情況：

1、先進(jìn)入主庫，進(jìn)行鎖表，防止數(shù)據(jù)寫入

mysql>flush tables with read lock;//此處是鎖定為只讀狀態(tài)

2、進(jìn)行數(shù)據(jù)備份

將數(shù)據(jù)備份到mysql.bak.sql文件

# mysqldump -uroot -p -hlocalhost > mysql.bak.sql

3.查看master 狀態(tài)

mysql> show master status;

+-------------------+----------+--------------+-------------------------------+

+-------------------+----------+--------------+-------------------------------+

+-------------------+----------+--------------+-------------------------------+

1 row in set (0.00 sec)

4.把mysql備份文件傳到從庫機(jī)器，進(jìn)行數(shù)據(jù)恢復(fù)

使用scp命令

[root@server01 mysql]# scp mysql.bak.sql root@192.168.128.101:/tmp/

5.停止從庫的狀態(tài)

mysql> stop slave;

6.然后到從庫執(zhí)行mysql命令，導(dǎo)入數(shù)據(jù)備份

mysql> source /tmp/mysql.bak.sql

7.設(shè)置從庫同步，注意該處的同步點，就是主庫show master status信息里的| File| Position兩項

#change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;

8.重新開啟從同步

mysql> start slave;

9.查看同步狀態(tài)

mysql> show slave status; 查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

二、主從復(fù)制，中繼日志不斷增長

解決方案：

設(shè)置中繼日志自動刪除

vi 配置文件my.cnf，在mysqld下添加(位置：/etc/my.cnf)

relay_log-purge=1//(自動清除中繼日志打開)

然后重啟MySQL，這樣SQL Thread每執(zhí)行完一個events時才會判斷該relay-log是否需要，已經(jīng)不再需要則自動刪除

三、主從同步失敗，如何快速同步

跳過錯誤，繼續(xù)同步。設(shè)置SQL_slave_skip_counter=1;來快速恢復(fù)主從架構(gòu)，但是此時主從架構(gòu)的數(shù)據(jù)可能已經(jīng)不一致了。set global sql_slave_skip_counter=N; 當(dāng)N等于1時，表示跳過若干個event，直到當(dāng)前事務(wù)結(jié)束，而當(dāng)N大于1時，每跳過一個event，都要N--設(shè)置--slave-skip-errors=[ err_code1[,err_code2][,all]] 跳過出現(xiàn)指定錯誤的SQL.如果要斷開主從架構(gòu)，應(yīng)先stop slave io_thread;等待執(zhí)行完relay log里的內(nèi)容再stop slave;

如果有與字符編碼問題：

先停止slave

mysql>stop slave;

跳過slave上的一個錯誤：

mysql>set global sql_slave_skip_counter=1;//不要濫用，用之前最好上網(wǎng)查找資料

啟動slave

mysql>start slave;

使用此方法需要注意的問題：

檢查跳過的event是否在一個事物中

跳過slave上的event進(jìn)行后續(xù)處理后要檢查數(shù)據(jù)的一致性。

最好能在master的binglog上查看一下跳過的evnet到底做了寫什么。

四、IO線程(Slave_IO_Running)始終保持為connecting狀態(tài)

主從架構(gòu)中，從庫的io_thread一直保持connecting狀態(tài)。先理解Slave_IO_Running 為connecting,的含義。造成的主要有三個：

1、網(wǎng)絡(luò)不通 (是否打開防火墻)

2、復(fù)制用戶的密碼不對 (主從同步指定的用戶密碼主機(jī)名限制)

3、pos不對 (指定的position不正確

五、主鍵沖突，報1062錯誤

主從架構(gòu)中，從庫復(fù)制報1062錯誤，主鍵沖突。如果binlog是基于語句級復(fù)制，很容易出現(xiàn)上面的問題。設(shè)置innodb_autoincr_lock_mode=0或是1或修改binlog_format=mixed|row

六、從庫同步慢

主從架構(gòu)中，從庫的同步數(shù)據(jù)非常慢。出現(xiàn)主從同步慢的原因有：

主從同步延遲與系統(tǒng)時間的關(guān)系，查看主從兩臺機(jī)器間系統(tǒng)時間差

主從同步延遲與壓力、網(wǎng)絡(luò)、機(jī)器性能的關(guān)系，查看從庫的io，cpu，mem及網(wǎng)絡(luò)壓力

主從同步延遲與lock鎖的關(guān)系(myisam表讀時會堵塞寫)，盡量避免使用myisam表。一個實例里面盡量減少數(shù)據(jù)庫的數(shù)量。

七、change master時報錯ERROR 1201(HY000)

表現(xiàn)：在搭建主從時，報1201錯誤。ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

解決方法：出現(xiàn)這個問題的原因是之前曾做過主從復(fù)制!需要reset slave后再change

八、關(guān)于在主從的時候使用觸發(fā)器的問題

1 主從都存在trigger時，主庫會記錄sql語句，不包含trigger的操作，從庫上數(shù)據(jù)和主庫一致..

2 主有trigger,從庫上沒有trigger時，從庫上沒有trigger時，觸發(fā)器不會被執(zhí)行

3 主上無trigger,從上有trigger時，主從數(shù)據(jù)不一致，從庫上的trigger被觸發(fā)

總結(jié)

以上是生活随笔為你收集整理的mysql master-user_【MySQL】MySQL5.6数据库基于binlog主从（Master/Slave）同步安装与配置详解...的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇：因子分析——python
下一篇： gen2服务器只显示spbc,gen2-