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

image.png

主從數(shù)據(jù)庫(kù)同步工作原理（流程）：
當(dāng)主庫(kù)的數(shù)據(jù)發(fā)生修改時(shí)墨状，數(shù)據(jù)更改的記錄將寫入到主庫(kù)的二進(jìn)制文件中愁溜，從庫(kù)此時(shí)將會(huì)調(diào)用一個(gè)IO線程讀取主庫(kù)的二進(jìn)制文件咱旱，并與中繼日志作對(duì)比，并將存在差異的事件寫入到中繼日志中（當(dāng)兩日志內(nèi)容事件一致時(shí)魂那，IO線程將處于睡眠狀態(tài)）隙笆，然后從庫(kù)再調(diào)用SQL線程去讀取中繼日志锌蓄，并將剛寫入的事件數(shù)據(jù)放入到從庫(kù)中以保持主從數(shù)據(jù)庫(kù)數(shù)據(jù)同步升筏。

配置步驟：

安裝環(huán)境：

操作系統(tǒng)：Cent0S 6.9
數(shù)據(jù)庫(kù)版本：MySQL 5.6
主機(jī)A：192.168.206.134（Master）
主機(jī)B：192.168.206.201（Slave）

首先，需要注意幾點(diǎn)問(wèn)題：

1瘸爽、互相同步的兩臺(tái)mysql的版本必須保證大版本號(hào)一致仰冠。比如5.5+和5.6+之間同步數(shù)據(jù)，5.6的數(shù)據(jù)同步到5.5就會(huì)出現(xiàn)問(wèn)題蝶糯。保證大版本號(hào)一致很重要。
2辆沦、每臺(tái)服務(wù)器必須開啟binlog昼捍，不開啟binlog則根本無(wú)法開始數(shù)據(jù)同步。
3肢扯、每臺(tái)服務(wù)器必須配置不同的server-id妒茬，范圍在1到（2^32-1）之間。

以上三點(diǎn)都可能造成很多奇怪錯(cuò)誤蔚晨，請(qǐng)一定注意乍钻。

這里強(qiáng)調(diào)的數(shù)據(jù)庫(kù)的版本，是因?yàn)镸ySQL在5.6之前和之后的配置方式是不一樣的铭腕，后面將會(huì)提示到银择。

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

#service iptables stop

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

Master部分的配置

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

mastercnf文件1.png

mastercnf文件2.png

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

然后重啟MySQL

service mysqld restart

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

#mysql -uroot -p

賦予從庫(kù)權(quán)限帳號(hào)袜瞬，允許用戶在主庫(kù)上讀取日志，賦予192.168.206.201也就是Slave機(jī)器有File權(quán)限身堡，只賦予Slave機(jī)器有File權(quán)限還不行邓尤，還要給它REPLICATION SLAVE的權(quán)限才可以
在Master數(shù)據(jù)庫(kù)命令行中輸入：

mysql>GRANT FILE ON *.* TO '數(shù)據(jù)庫(kù)用戶名'@'Slave地址' IDENTIFIED BY '數(shù)據(jù)庫(kù)登錄密碼'；//賦予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ù)庫(kù)用戶名'@‘Slave地址’ IDENTIFIED BY '數(shù)據(jù)庫(kù)登錄密碼';//賦予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裁赠，顯示主庫(kù)信息：

mysql>show master status;

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

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

另外提示：如果執(zhí)行這個(gè)步驟始終為Empty set(0.00 sec)，那說(shuō)明前面的my.cnf沒(méi)配置對(duì)蕾哟，需要仔細(xì)檢查一忱。

Slave部分的配置

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

4slave配置文件1.png

5slave配置文件2.png

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

在配置文件中帘营，MySQL5.6之后的版本中沒(méi)有指定：

master-host=192.168.206.134 #Master的主機(jī)IP
master-user=root
master-password=mysql password #Master的MySQL密碼

新版本的配置這種方式是不適用的票渠。如果，在MySQL5.6和之后的版本中配置從庫(kù)的時(shí)候芬迄，設(shè)置到了上邊的內(nèi)容问顷，即指定了master-host、master-user等信息的話禀梳，重啟MySQL的時(shí)候就會(huì)報(bào)錯(cuò)杜窄，查看數(shù)據(jù)庫(kù)的報(bào)錯(cuò)信息（數(shù)據(jù)庫(kù)的目錄, /data/mysqldb/VM_128_194_centos.err ），可以看到master-host 被檢測(cè)數(shù)是一個(gè)未知的變量算途，因此會(huì)出現(xiàn)錯(cuò)誤

在5.6以及后續(xù)版本的配置如下：
修改完/etc/my.cnf 文件之后塞耕，重啟一下MySQL

service mysqld restart

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

#mysql -uroot -p

在控制臺(tái)輸入

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的時(shí)候的File選項(xiàng)嘴瓤， master_log_pos是在配置Master的Position 選項(xiàng)扫外，這里要進(jìn)行對(duì)應(yīng)。
更多關(guān)于change master語(yǔ)句的信息可參考：http://www.reibang.com/p/ada9f34d8563
然后可以通過(guò)mysql> show slave status \G; 查看配置的信息：

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

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

當(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ī)測(cè)試：
測(cè)試環(huán)境：windows10+Navicat11
以下測(cè)試是使用真機(jī)連接虛擬機(jī)中的主從庫(kù)
首先在Navicat中創(chuàng)建連接（物理機(jī)連接虛擬機(jī)的方法參考：http://www.reibang.com/p/fa4db03db9ca）：

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

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

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

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

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

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

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

以上是一個(gè)完整的配置過(guò)程瞎嬉，是查找過(guò)多篇資料和通過(guò)實(shí)踐之后的總結(jié)蝎毡。
同時(shí)感謝啟發(fā)我的兩個(gè)作者：
http://blog.csdn.net/xlgen157387/article/details/51331244/
http://www.reibang.com/p/67d8355315c6

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

如果你的是MYISAM或者既有MYISAM又有INNODB的話就在主服務(wù)器上使用如下命令導(dǎo)出服務(wù)器的一個(gè)快照：
mysqldump -uroot -p --lock-tables --events --triggers --routines --flush-logs --master-data=2 --databases test > db.sql
試過(guò)只有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)于一些常見問(wèn)題及解決方案

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

可以先查看一下進(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ù)庫(kù)控制臺(tái)輸入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ù)庫(kù)連接失敗
在正常情況下毁靶，配置好服務(wù)器后，從庫(kù)不進(jìn)行修改操作逊移，即從庫(kù)只有讀的權(quán)限预吆，如果修改了從庫(kù)數(shù)據(jù)表，則會(huì)造成同步失敗胳泉，如發(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時(shí)，表示從庫(kù)連接失敗凤瘦，在Last_Error處會(huì)打印錯(cuò)誤日志信息宿礁。出現(xiàn)該問(wèn)題主要是事務(wù)回滾問(wèn)題，解決方法有兩種蔬芥。
1梆靖、

mysql> slave stop;//停止從服務(wù)
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;//不要濫用
mysql>slave start;//啟動(dòng)從服務(wù)

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

mysql5.6之后的版本（包含5.6）
mysql> stop slave; #關(guān)閉Slavemysql> change master to master_host='主服務(wù)器IP地址',master_user='主庫(kù)用戶名',master_password='主庫(kù)登錄密碼',
master_log_file='mysql-bin.000004', master_log_pos=28125;//master_log_file是在配置master的時(shí)候的file選項(xiàng)笔诵，master_log_pos是在配置master時(shí)候position選項(xiàng)的  mysql> show master status;可查看
mysql> start slave;  #開啟Slave
mysql5.6之前的版本：

mysql>change master to
master_host='主庫(kù)IP地址',
master_user='主庫(kù)用戶名',
master_password='主庫(kù)登錄密碼',
master_log_file='主庫(kù)文件信息',//同上
master_log_pos=' 主庫(kù)的position信息 ';//同上

如果數(shù)據(jù)庫(kù)相差較大涤姊，或者要求數(shù)據(jù)完全統(tǒng)一的情況：
1、先進(jìn)入主庫(kù)嗤放，進(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;
+-------------------+----------+--------------+-------------------------------+
| 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備份文件傳到從庫(kù)機(jī)器次酌，進(jìn)行數(shù)據(jù)恢復(fù)

使用scp命令

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

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

mysql> stop slave;

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

mysql> source /tmp/mysql.bak.sql

7.設(shè)置從庫(kù)同步舆乔，注意該處的同步點(diǎn)岳服，就是主庫(kù)show master status信息里的| File| Position兩項(xiàng)

#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ù)制希俩，中繼日志不斷增長(zhǎng)

解決方案：
設(shè)置中繼日志自動(dòng)刪除
vi 配置文件my.cnf吊宋，在mysqld下添加（位置：/etc/my.cnf）
relay_log-purge=1//(自動(dòng)清除中繼日志打開)
然后重啟MySQL， 這樣SQL Thread每執(zhí)行完一個(gè)events時(shí)才會(huì)判斷該relay-log是否需要颜武，已經(jīng)不再需要?jiǎng)t自動(dòng)刪除

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

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

mysql>stop slave;

跳過(guò)slave上的一個(gè)錯(cuò)誤：

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

啟動(dòng)slave

mysql>start slave;

使用此方法需要注意的問(wèn)題：

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

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

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

五旱易、主鍵沖突，報(bào)1062錯(cuò)誤

主從架構(gòu)中腿堤，從庫(kù)復(fù)制報(bào)1062錯(cuò)誤阀坏，主鍵沖突。如果binlog是基于語(yǔ)句級(jí)復(fù)制笆檀，很容易出現(xiàn)上面的問(wèn)題忌堂。設(shè)置innodb_autoincr_lock_mode=0或是1或修改binlog_format=mixed|row

六、從庫(kù)同步慢

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

1. 主從同步延遲與系統(tǒng)時(shí)間的關(guān)系，查看主從兩臺(tái)機(jī)器間系統(tǒng)時(shí)間差
2. 主從同步延遲與壓力樱衷、網(wǎng)絡(luò)棋嘲、機(jī)器性能的關(guān)系，查看從庫(kù)的io矩桂，cpu沸移，mem及網(wǎng)絡(luò)壓力
3. 主從同步延遲與lock鎖的關(guān)系(myisam表讀時(shí)會(huì)堵塞寫)，盡量避免使用myisam表侄榴。一個(gè)實(shí)例里面盡量減少數(shù)據(jù)庫(kù)的數(shù)量雹锣。

七、change master時(shí)報(bào)錯(cuò)ERROR 1201（HY000）

表現(xiàn)：在搭建主從時(shí)癞蚕，報(bào)1201錯(cuò)誤 笆制。ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
解決方法：出現(xiàn)這個(gè)問(wèn)題的原因是之前曾做過(guò)主從復(fù)制!需要reset slave后再change

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

1 主從都存在trigger時(shí)涣达，主庫(kù)會(huì)記錄sql語(yǔ)句在辆，不包含trigger的操作，從庫(kù)上數(shù)據(jù)和主庫(kù)一致..
2 主有trigger,從庫(kù)上沒(méi)有trigger時(shí)度苔，從庫(kù)上沒(méi)有trigger時(shí)匆篓，觸發(fā)器不會(huì)被執(zhí)行
3 主上無(wú)trigger,從上有trigger時(shí) ，主從數(shù)據(jù)不一致寇窑，從庫(kù)上的trigger被觸發(fā)

后記：
關(guān)于配置時(shí)導(dǎo)數(shù)據(jù)：
http://www.reibang.com/p/5233202ff307
關(guān)于SECONDS_BEHIND_MASTER：
http://www.reibang.com/p/56fad1fbe08a
需要注意的一些問(wèn)題和一些常見故障（先貼出參考鸦概，后面有空再整理總結(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