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

image.png

主從數(shù)據(jù)庫同步工作原理（流程）：
當主庫的數(shù)據(jù)發(fā)生修改時，數(shù)據(jù)更改的記錄將寫入到主庫的二進制文件中，從庫此時將會調(diào)用一個IO線程讀取主庫的二進制文件，并與中繼日志作對比，并將存在差異的事件寫入到中繼日志中（當兩日志內(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
主機A：192.168.206.134（Master）
主機B：192.168.206.201（Slave）

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

1、互相同步的兩臺mysql的版本必須保證大版本號一致。比如5.5+和5.6+之間同步數(shù)據(jù)，5.6的數(shù)據(jù)同步到5.5就會出現(xiàn)問題。保證大版本號一致很重要。
2、每臺服務器必須開啟binlog，不開啟binlog則根本無法開始數(shù)據(jù)同步。
3、每臺服務器必須配置不同的server-id，范圍在1到（2^32-1）之間。

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

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

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

#service iptables stop

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

Master部分的配置

在Linux環(huán)境下MySQL的配置文件的位置是在 /etc/my.cnf
，在該文件下指定Master的完整配置如下：

mastercnf文件1.png

mastercnf文件2.png

其中，server-id用于標識唯一的數(shù)據(jù)庫，取值為1到（2^32-1）
binlog-ignore-db:表示不需要同步的數(shù)據(jù)庫
binlog-do-db:表示需要同步的數(shù)據(jù)庫

然后重啟MySQL

service mysqld restart

輸入用戶名密碼進入MySQL

#mysql -uroot -p

賦予從庫權(quán)限帳號，允許用戶在主庫上讀取日志，賦予192.168.206.201也就是Slave機器有File權(quán)限，只賦予Slave機器有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沒配置對，需要仔細檢查。

Slave部分的配置

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

4slave配置文件1.png

5slave配置文件2.png

相關(guān)的字段的含義可以參考另一篇文章：http://m.itdecent.cn/p/ac8b0892d621

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

master-host=192.168.206.134 #Master的主機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

登錄進入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 選項，這里要進行對應。
更多關(guān)于change master語句的信息可參考：http://m.itdecent.cn/p/ada9f34d8563
然后可以通過mysql> show slave status \G; 查看配置的信息：

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

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

當完成配置后
查看master的狀態(tài)：

mysql >show master status;  //Position不應該為0
mysql>show processlist;  

//state狀態(tài)應該為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;

//應該有兩行state值為：
Has read all relay log; waiting for the slave I/O thread to update it
Waiting for master to send event

image.png

下面開始真機測試：
測試環(huán)境：windows10+Navicat11
以下測試是使用真機連接虛擬機中的主從庫
首先在Navicat中創(chuàng)建連接（物理機連接虛擬機的方法參考：http://m.itdecent.cn/p/fa4db03db9ca）：

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

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

連接的地址為虛擬機中主/從庫的地址
連接上去之后，分別在Master和Slave中創(chuàng)建數(shù)據(jù)庫，該數(shù)據(jù)庫名字為剛剛配置的需要同步的數(shù)據(jù)庫名字
接著開始在該數(shù)據(jù)庫中新建數(shù)據(jù)表
首先在主庫中創(chuàng)建

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

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

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

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

以上是一個完整的配置過程，是查找過多篇資料和通過實踐之后的總結(jié)。
同時感謝啟發(fā)我的兩個作者：
http://blog.csdn.net/xlgen157387/article/details/51331244/
http://m.itdecent.cn/p/67d8355315c6

另：
從主服務器得到一個快照版本

如果你的是MYISAM或者既有MYISAM又有INNODB的話就在主服務器上使用如下命令導出服務器的一個快照：
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

將快照版本還原到從服務器上
mysqldump -uroot -p -h 10.1.1.76 test < db.sql

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

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

可以先查看一下進程是否sleep太多

mysql> show processlist;

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

mysql>show master status;

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

mysql>show slave status \G;

1、可能slave服務未開啟
數(shù)據(jù)庫控制臺輸入show slave status \G;打印出slave的狀態(tài)

mysql> show slave status \G;

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

mysql> service slave restart;//重啟

如果服務未開啟，則開啟：

mysql> service slave start;

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

mysql> show slave status \G;

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

mysql> slave stop;//停止從服務
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;//不要濫用
mysql>slave start;//啟動從服務

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

mysql5.6之后的版本（包含5.6）
mysql> stop slave; #關(guān)閉Slavemysql> change master to master_host='主服務器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、先進入主庫，進行鎖表，防止數(shù)據(jù)寫入

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

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

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

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

mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 |     3260 |              | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

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

使用scp命令

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

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

mysql> stop slave;

6.然后到從庫執(zhí)行mysql命令，導入數(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

二、主從復制，中繼日志不斷增長

解決方案：
設(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;來快速恢復主從架構(gòu)，但是此時主從架構(gòu)的數(shù)據(jù)可能已經(jīng)不一致了。set global sql_slave_skip_counter=N; 當N等于1時，表示跳過若干個event，直到當前事務結(jié)束，而當N大于1時，每跳過一個event，都要N--設(shè)置--slave-skip-errors=[ err_code1[,err_code2][,all]] 跳過出現(xiàn)指定錯誤的SQL.如果要斷開主從架構(gòu)，應先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;

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

1. 檢查跳過的event是否在一個事物中
2. 跳過slave上的event進行后續(xù)處理后要檢查數(shù)據(jù)的一致性。
3. 最好能在master的binglog上查看一下跳過的evnet到底做了寫什么。

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

主從架構(gòu)中，從庫的io_thread一直保持connecting狀態(tài)。先理解Slave_IO_Running 為connecting,的含義。造成的主要有三個：
1、網(wǎng)絡不通 (是否打開防火墻)
2、復制用戶的密碼不對 (主從同步指定的用戶密碼主機名限制)
3、pos不對 (指定的position不正確

五、主鍵沖突，報1062錯誤

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

六、從庫同步慢

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

1. 主從同步延遲與系統(tǒng)時間的關(guān)系，查看主從兩臺機器間系統(tǒng)時間差
2. 主從同步延遲與壓力、網(wǎng)絡、機器性能的關(guān)系，查看從庫的io，cpu，mem及網(wǎng)絡壓力
3. 主從同步延遲與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)這個問題的原因是之前曾做過主從復制!需要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ā)

后記：
關(guān)于配置時導數(shù)據(jù)：
http://m.itdecent.cn/p/5233202ff307
關(guān)于SECONDS_BEHIND_MASTER：
http://m.itdecent.cn/p/56fad1fbe08a
需要注意的一些問題和一些常見故障（先貼出參考，后面有空再整理總結(jié)）
http://www.cnblogs.com/wangxin37/p/6398755.html
http://www.educity.cn/shujuku/1178774.html
https://www.douban.com/note/354334490/
http://www.3lian.com/edu/2014/01-23/126344.html