MySQL主從復(fù)制原理、半同步操作步驟及原理
轉(zhuǎn)載?2016年09月21日 11:51:23
7195
1.1 企業(yè)Linux運(yùn)維場(chǎng)景數(shù)據(jù)同步方案
1、scp/sftp/nc 命令可以實(shí)現(xiàn)遠(yuǎn)程數(shù)據(jù)同步。
2、搭建ftp/http/svn/nfs 服務(wù)器，然后在客戶端上也可以把數(shù)據(jù)同步到服務(wù)器。
3、搭建samba文件共享服務(wù)，然后在客戶端上也可以把數(shù)據(jù)同步到服務(wù)器。
http://taokey.blog.51cto.com/4633273/1203553
4、利用rsync/csync2/union等均可以實(shí)現(xiàn)數(shù)據(jù)同步
提示：union可實(shí)現(xiàn)雙同步，csync2可實(shí)現(xiàn)多機(jī)同步。
以上文件同步方式如果結(jié)合定時(shí)任何或者inotify，sersync等功能，可以實(shí)現(xiàn)定時(shí)以及定時(shí)的數(shù)據(jù)同步。
5、文件級(jí)別也可以利用mysql，mongodb等軟件作為容器實(shí)現(xiàn)。
6、程序向兩個(gè)服務(wù)器同時(shí)寫入數(shù)據(jù)，雙寫就是一個(gè)同步機(jī)制
特點(diǎn)：簡單、方便、效率和文件系統(tǒng)級(jí)別要差一點(diǎn)，但是同步的節(jié)點(diǎn)可以提供訪問。
軟件的自身同步機(jī)制（mysql，oracle，mongdb，ttserver，redis….）文件放到數(shù)據(jù)庫，同步到從庫，再把文件拿出來。
7、DRBD文件系統(tǒng)級(jí)別（基于塊設(shè)備復(fù)制，直接復(fù)制block）
1.1.2 文件系統(tǒng)級(jí)別的異機(jī)同步方案
1、drbd基于文件系統(tǒng)同步，相當(dāng)于網(wǎng)絡(luò)RAID1，可以同步幾乎任何業(yè)務(wù)數(shù)據(jù)。
mysql數(shù)據(jù)庫的官方推薦drbd同步數(shù)據(jù)，所有單點(diǎn)服務(wù)例如：NFS、MFS（DRBD）MySQL等都可以用drbd做復(fù)制，效率很高，缺點(diǎn)：備機(jī)服務(wù)不可用
2、數(shù)據(jù)庫同步方案：
a.自身同步機(jī)制：
? mysql replication，mysql主從復(fù)制（邏輯的SQL重寫）物理復(fù)制方法<===drbd（從庫不提供讀寫）
? oracle dataguard（物理的磁盤塊，邏輯的SQL語句重寫）9i從庫不提供腹瀉的，11g的從庫實(shí)現(xiàn)了readonly
b.第三方drbd，參考URL
Heartbeat+DRBD+MySQL高可用架構(gòu)方案與實(shí)施過程細(xì)節(jié)?
http://oldboy.blog.51cto.com/2561410/1240412
? MySQL的主從復(fù)制方案，和上述文件及文件系統(tǒng)級(jí)別同步是類似的，都輸數(shù)據(jù)的傳輸。只不過MySQL無需借助第三方工具，而是其自帶的同步復(fù)制功能，另外一點(diǎn)，MySQL的主從復(fù)制并不是從硬盤給上文件直接同步，而是邏輯的binlog日志同步到本地的應(yīng)用執(zhí)行的過程
提示：官方說主從不要超過9臺(tái)，推薦不超過5臺(tái)。
1、單向主從復(fù)制邏輯圖
2、雙向主主同步邏輯圖，此架構(gòu)可以在Master1端或Master2端進(jìn)行數(shù)據(jù)寫入
3、線性級(jí)聯(lián)單向雙主同步邏輯圖，此架構(gòu)只能在Master1端進(jìn)行數(shù)據(jù)寫入
4、環(huán)狀級(jí)聯(lián)單向多主同步邏輯圖，任何一個(gè)點(diǎn)都可以寫入數(shù)據(jù)
5、環(huán)狀級(jí)聯(lián)單向多主多從同步邏輯圖，此架構(gòu)只能在任意一個(gè)Master端進(jìn)行數(shù)據(jù)寫入
MySQL官方的同步架構(gòu)圖
工作中第一種方案和第二種方案最常用。
? MySQL的主從復(fù)制是一個(gè)異步的復(fù)制過程（雖然一般情況下感覺是實(shí)時(shí)的），數(shù)據(jù)將從一個(gè)Mysql數(shù)據(jù)庫（我們稱之為Master）復(fù)制到另一個(gè)Mysql數(shù)據(jù)庫（我們稱之為Slave），在Master與Slave之間實(shí)現(xiàn)整個(gè)主從復(fù)制的過程是由三個(gè)線程參與完成的。其中有兩個(gè)線程（SQL線程和IO線程）在Slave端，另一個(gè)線程（I/O線程）在Master端。
? 要實(shí)現(xiàn)MySQL的主從復(fù)制，首先必須打開Master端的binlog記錄功能，否則就無法實(shí)現(xiàn)。因?yàn)檎麄€(gè)復(fù)制過程實(shí)際上就是Slave從aster端獲取binlog日志，然后再在Slave上以相同順序執(zhí)行獲取的binlog日志中的記錄的各種SQL操作
畫圖：
1）在Slave?服務(wù)器上執(zhí)行sart slave命令開啟主從復(fù)制開關(guān)，開始進(jìn)行主從復(fù)制。
2）此時(shí)，Slave服務(wù)器的IO線程會(huì)通過在master上已經(jīng)授權(quán)的復(fù)制用戶權(quán)限請(qǐng)求連接master服務(wù)器，并請(qǐng)求從執(zhí)行binlog日志文件的指定位置（日志文件名和位置就是在配置主從復(fù)制服務(wù)時(shí)執(zhí)行change
master命令指定的）之后開始發(fā)送binlog日志內(nèi)容
3）Master服務(wù)器接收到來自Slave服務(wù)器的IO線程的請(qǐng)求后，其上負(fù)責(zé)復(fù)制的IO線程會(huì)根據(jù)Slave服務(wù)器的IO線程請(qǐng)求的信息分批讀取指定binlog日志文件指定位置之后的binlog日志信息，然后返回給Slave端的IO線程。返回的信息中除了binlog日志內(nèi)容外，還有在Master服務(wù)器端記錄的IO線程。返回的信息中除了binlog中的下一個(gè)指定更新位置。
4）當(dāng)Slave服務(wù)器的IO線程獲取到Master服務(wù)器上IO線程發(fā)送的日志內(nèi)容、日志文件及位置點(diǎn)后，會(huì)將binlog日志內(nèi)容依次寫到Slave端自身的Relay Log（即中繼日志）文件（Mysql-relay-bin.xxx）的最末端，并將新的binlog文件名和位置記錄到master-info文件中，以便下一次讀取master端新binlog日志時(shí)能告訴Master服務(wù)器從新binlog日志的指定文件及位置開始讀取新的binlog日志內(nèi)容
5）Slave服務(wù)器端的SQL線程會(huì)實(shí)時(shí)檢測(cè)本地Relay Log?中IO線程新增的日志內(nèi)容，然后及時(shí)把Relay LOG?文件中的內(nèi)容解析成sql語句，并在自身Slave服務(wù)器上按解析SQL語句的位置順序執(zhí)行應(yīng)用這樣sql語句，并在relay-log.info中記錄當(dāng)前應(yīng)用中繼日志的文件名和位置點(diǎn)
1、開啟Binlog功能
2、主庫要建立賬號(hào)
3、從庫要配置master.info（CHANGE MASTER to…相當(dāng)于配置密碼文件和Master的相關(guān)信息）
4、start slave 開啟復(fù)制功能
1.3個(gè)線程，主庫IO，從庫IO和SQL及作用
2.master.info（從庫）作用
3.relay-log 作用
4.異步復(fù)制
5.binlog作用（如果需要級(jí)聯(lián)需要開啟Binlog）
主從復(fù)制是異步的邏輯的SQL語句級(jí)的復(fù)制
復(fù)制時(shí)，主庫有一個(gè)O/O線程，從庫有兩個(gè)線程，I/O和SQL線程
實(shí)現(xiàn)主從復(fù)制的必要條件是主庫要開啟記錄binlog功能
作為復(fù)制的所有Mysql節(jié)點(diǎn)的server-id都不能相同
binlog文件只記錄對(duì)數(shù)據(jù)庫有更改的SQL語句（來自主庫內(nèi)容的變更），不記錄任何查詢（select，show）語句
我們準(zhǔn)備的是多實(shí)例，一臺(tái)服務(wù)器開啟3個(gè)服務(wù)端口不同
環(huán)境準(zhǔn)備：
[root@db02 3307]# netstat -lntup|grep 330
tcp??????? 0????? 0 0.0.0.0:3306??????????????? 0.0.0.0:*?????????????????? LISTEN????? 3074/mysqld????????
tcp??????? 0????? 0 0.0.0.0:3307??????????????? 0.0.0.0:*?????????????????? LISTEN????? 33364/mysqld???????
tcp??????? 0????? 0 0.0.0.0:3308????? ??????????0.0.0.0:*?????????????????? LISTEN????? 34084/mysqld
主庫開啟Binlog功能
[root@db02 3307]# grep log-bin /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
設(shè)置server-id，此處ID不可以相同否則最后出現(xiàn)IO錯(cuò)誤
[root@db02 3307]# grep server-id /data/3306/my.cnf
server-id = 1
[root@db02 3307]# grep server-id /data/3307/my.cnf
server-id = 3
[root@db02 3307]# grep server-id /data/3308/my.cnf
server-id = 2
================================================
[root@db02 3307]# grep server-id /data/{3306,3307,3308}/my.cnf
/data/3306/my.cnf:server-id = 1
/data/3307/my.cnf:server-id = 3
/data/3308/my.cnf:server-id = 2
主庫需要授權(quán)slave訪問的用戶
mysql>grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
mysql> flush privileges;
mysql>show grants for rep@'172.16.1.%';
mysql>select user,host from mysql.user
#replication slave 為mysql同步的必須權(quán)限，此處不要授權(quán)all權(quán)限
因?yàn)閺膸飕F(xiàn)在還沒有數(shù)據(jù)，或者數(shù)據(jù)不統(tǒng)一我們需要導(dǎo)入數(shù)據(jù)
鎖表、查看binlog文件及位置點(diǎn)，主庫導(dǎo)出全備，需要鎖表（-x –master-date=2）? ??
flush table with read lock;?? 鎖表，窗口不能退出，退出失效
root@oldboy 05:16:22->show master status;? 臨界點(diǎn)，將來恢復(fù)就從0025開始
+------------------+----------+--------------+------------------+
| File???????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000025 |???? 9155 |????????????? |??????????? ??????|
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
備份
mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --events|gzip >/server/backup/rep_bak$(date +%F).sql.gz
[root@db02 oldboy]# ls -lrt /server/backup/
total 308
-rw-r--r-- 1 root root???? 20 Dec 23? 2015 bak_2015-12-23.sql.gz
-rw-r--r-- 1 root root 152214 Dec 23? 2015 bak.sql.gz
-rw-r--r-- 1 root root 152238 Jun 29 17:20 rep_bak2016-06-29.sql.gz
解鎖：unlock table;
root@oldboy 05:22:00->show master status;
+------------------+----------+--------------+------------------+
| File???????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000025 |???? 9155 |?????????? ???|????????????????? |
+------------------+----------+--------------+------------------+
#如果解鎖之后還是mysql-bin.000025?說明是正確的，如果動(dòng)了說明沒有鎖住表
#如果mysqldump?加了-F?他就會(huì)更改刷新binlog
1.確保server-id不同
2.把主庫的備份導(dǎo)入到從庫
[root@db02 backup]# gzip -d rep_bak2016-06-29.sql.gz
[root@db02 backup]# mysql -uroot -p123456 -S /data/3307/mysql.sock
3.查找位置點(diǎn)，配置master.info
mysql-bin.000025 |? ???9155
|? ?主庫的位置點(diǎn)
Mysql從庫連接主庫的配置信息如下：
CHANGE?MASTER?TO?
MASTER_HOST='172.16.1.52',#這是主庫的IP（域名也可以需要做解析）
MASTER_PORT=3306,#主庫的端口，從庫端口和主庫不可以相同
MASTER_USER='rep',#這是主庫上創(chuàng)建用來復(fù)制的用戶rep
MASTER_PASSWORD='123456'#rep的密碼
MASTER_LOG_FILE='mysql-bin.000025',#這里是show master status時(shí)看到的查詢二進(jìn)制日志文件名稱，這里不能多空格
MASTER_LOG_POS=9155;#這里是show master status時(shí)看到的二進(jìn)制日志偏移量，不能多空格
提示：3307操作此步：會(huì)在/data/3307/data下面產(chǎn)生master.info文件
開啟從庫復(fù)制開關(guān)
root@oldboy 07:47:44->show slave status\G
*************************** 1. row ***************************
?????????????? Slave_IO_State: Waiting for master to send event
????????????????? Master_Host: www.etiantian.org
????????????????? Master_User: rep
????????????????? Master_Port: 3306
??????????????? Connect_Retry: 60
????????? ????Master_Log_File: mysql-bin.000025
????????? Read_Master_Log_Pos: 9706
?????????????? Relay_Log_File: relay-bin.000002
??????????????? Relay_Log_Pos: 453
??????? Relay_Master_Log_File: mysql-bin.000025
???????????? Slave_IO_Running: Yes?????? IO線程代表IO正常
??????????? Slave_SQL_Running: Yes??????????????? SQL線程
????????????? Replicate_Do_DB:
????????? Replicate_Ignore_DB: mysql
?????????? Replicate_Do_Table:
?????? Replicate_Ignore_Table:
????? Replicate_Wild_Do_Table:
? Replicate_Wild_Ignore_Table:
?????????????????? Last_Errno: 0
?????????????????? Last_Error:
???????????????? Skip_Counter: 0
????????? Exec_Master_Log_Pos: 9706
????????????? Relay_Log_Space: 603
????????????? Until_Condition: None
?????????????? Until_Log_File:
??????????????? Until_Log_Pos: 0
?????????? Master_SSL_Allowed: No
?????????? Master_SSL_CA_File:
?????????? Master_SSL_CA_Path:
????????????? Master_SSL_Cert:
??????????? Master_SSL_Cipher:
?????????????? Master_SSL_Key:
??????? Seconds_Behind_Master: 0??? 延遲
Master_SSL_Verify_Server_Cert: No
??????????????? Last_IO_Errno: 0
??????????????? Last_IO_Error:
?????????????? Last_SQL_Errno: 0
?????????????? Last_SQL_Error:
? Replicate_Ignore_Server_Ids:
???????????? Master_Server_Id: 1
1 row in set (0.00 sec)
查看檢查結(jié)果：在主庫創(chuàng)建目錄查看從庫是否存在即可
提示：如果出現(xiàn)show master status里面沒有東西說明bin-log日志沒有開啟
Slave_IO_Running:Yes，這是I/O線程狀態(tài)，I/O線程負(fù)載從從庫去主庫讀取binlog日志，并寫入從庫的中繼日志中，狀態(tài)為Yes表示I/O線程工作正常。
Slave_SQL_Running:Yes?這個(gè)是SQL線程狀態(tài)，SQL線程負(fù)載讀取中繼日志（relay-log）中的數(shù)據(jù)并轉(zhuǎn)換為SQL語句應(yīng)用到從庫數(shù)據(jù)庫中，狀態(tài)為Yes表示I/O線程工作正常
Seconds_Behind_Master:0?這個(gè)是在復(fù)制過程中，從庫比主庫延遲的描述，這個(gè)參數(shù)很重要，但企業(yè)里更準(zhǔn)確地判斷主從延遲的方法為：在主庫寫時(shí)間戳，然后從庫讀取時(shí)間戳進(jìn)行比較，從而認(rèn)定是否延遲。
mysql主從復(fù)制中，需要將備庫（從庫）提升為主庫，需要取消其從庫角色，可以通過執(zhí)行以下命令：
stop slave;
reset slave all;
RESET SLAVE ALL是清除從庫的同步復(fù)制信息，包括連接信息和二進(jìn)制文件名、位置
從庫上執(zhí)行這個(gè)命令后，使用show slave status將不會(huì)有輸出
1.5 生產(chǎn)場(chǎng)景下輕松部署MySQL主從復(fù)制
快速步驟MySQL主從復(fù)制
1）安裝好配置從庫的數(shù)據(jù)庫，配置好log-bin和server-id參數(shù)
2）無需配置主庫my.cnf，主庫的log-bin和server-id參數(shù)默認(rèn)就是配置好的。
3）登錄主庫，增加從庫連接同步的賬戶，例如：rep，并授權(quán)replication同步的權(quán)限
4）使用mysqldump命令帶-x和–master-data=2的命令及參數(shù)全備數(shù)據(jù)，把它恢復(fù)到從庫
5）從庫執(zhí)行CHANGE MASTER TO….語句，需要binlog文件及對(duì)應(yīng)點(diǎn)（因?yàn)楱Cmaster-data=2已經(jīng)帶了）
6）從庫開啟同步開關(guān)，start slave
7）從庫show slave status\G，檢查同步狀態(tài)，并在主庫更新測(cè)試
步驟：
主庫：
shell>mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B -F -R -x --master-data=1 -A --events|gzip >/server/backup/rep3307_(date +%F).sql.gz
因?yàn)樘砑恿薽aster-data=1 已經(jīng)為我們寫好了位置點(diǎn)
從庫：
shell>mysql -uroot -p123456 -S /data/3307/mysql.sock <./repo3307_2016-07-03.sql
mysql>CHANGE MASTER TO
MASTER_HOST='www.etiantian.org',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000025',
MASTER_LOG_POS=9815;
mysql>start slave
mysql>show slave status\G
Slave_SQL_Running：NO 下面會(huì)有提示，如果提示這個(gè)庫已經(jīng)創(chuàng)建無法創(chuàng)建等是可以跳過的
解決辦法
stop slave;
set global sql_slave_skip_counter =1; #將同步指針向下移動(dòng)，如果多次不同步，可以添加移動(dòng)的數(shù)量
start slave;
查看連接的線程，每一個(gè)線程代表一個(gè)從庫
root@oldboy 08:51:37->show processlist;
+----+------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host ? ? ? ? ? ? ?| db ? | Command ? ? | Time | State ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | Info ? ? ? ? ? ? |
+----+------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| ?2 | rep ?| 172.16.1.52:51317 | NULL | Binlog Dump | ? 68 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL ? ? ? ? ? ? |
| ?3 | root | localhost ? ? ? ? | NULL | Query ? ? ? | ? ?0 | NULL ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| show processlist |
+----+------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
主庫I/O 線程工作狀態(tài)解釋說明
Sending binlog event to slave線程已經(jīng)從二進(jìn)制binlog日志讀取了一個(gè)事件并且正將它發(fā)送到從服務(wù)器
Finished reading one binlog；switching to next binlog線程已經(jīng)讀完二進(jìn)制binlog日志文件，并且整打開下一個(gè)要發(fā)送到從服務(wù)器的binlog日志文件
Has sent all binlog to slave；waiting for binlog to be updated線程已經(jīng)從binlog日志讀取所有更新并已經(jīng)發(fā)送到了從數(shù)據(jù)庫服務(wù)器。線程現(xiàn)在為空閑狀態(tài)，等待由主服務(wù)器上二進(jìn)制binlog日志中的新事件更新。
Waiting to finalize termination線程停止時(shí)發(fā)送的一個(gè)很簡單的狀態(tài)
從庫I/O線程工作狀態(tài)show processlist;
從庫I/O線程工作狀態(tài)解釋說明
Connecting to master線程正試圖連接主服務(wù)器
Checking master version同步主服務(wù)器之間建立后臨時(shí)出現(xiàn)的狀態(tài)
Registering slave to master
Requesting binlog dump建庫同主服務(wù)器之間的連接后立即臨時(shí)出現(xiàn)的狀態(tài)，線程向主服務(wù)器發(fā)送一條請(qǐng)求，索取從請(qǐng)求的二進(jìn)制binlog日志文件名和位置開始的二進(jìn)制binlog日志的內(nèi)容
Waiting to reconnect after a failed binlog dump request如果二進(jìn)制binlog日志轉(zhuǎn)存儲(chǔ)請(qǐng)求失敗，線程進(jìn)行睡眠狀態(tài)，嘗試重新連接
Reading event from the relay log線程已經(jīng)從中繼日志讀取了一個(gè)事件，可以對(duì)事件進(jìn)行處理了。
Has read all relay log；waiting for the slave I/O thread to update it線程已經(jīng)處理了中繼日志文件中的所有事件，現(xiàn)在等待I.O線程將新事件寫入中繼日志
Waiting for slave mutex on exit線程停止時(shí)發(fā)生了一個(gè)很簡單的狀態(tài)
1.6 MySQL主從復(fù)制更多應(yīng)用技巧實(shí)踐
1. 工作中MySQL從庫停止復(fù)制的故障案例
? 模擬重現(xiàn)故障的鞥能力是運(yùn)維人員最重要的能力。下面就進(jìn)行模擬操作。先在從庫創(chuàng)建一個(gè)庫，然后去主庫創(chuàng)建同名的庫來模擬數(shù)據(jù)沖突
mysql>show slave status;報(bào)錯(cuò)：且show slave status\G;
? ? ? ? ? ? Slave_IO_Running: Yes
? ? ? ? ? ? Slave_SQL_Running: No
? ? ? ? ? ? ? Replicate_Do_DB:?
? ? ? ? ? Replicate_Ignore_DB: mysql
? ? ? ? ? ?Replicate_Do_Table:?
? ? ? ?Replicate_Ignore_Table:?
? ? ? Replicate_Wild_Do_Table:?
? Replicate_Wild_Ignore_Table:?
? ? ? ? ? ? ? ? ? ?Last_Errno: 1007
? ? ? ? ? ? ? ? ? ?Last_Error: Error ‘Can’t create database ‘cyh’; database exists’ on query. Default database: ‘cyh’. Query: ‘create database cyh’
stop slave；#臨時(shí)停止同步開關(guān)
set global sql_slave_skip_counter =1； #將同步指針向下移動(dòng)一個(gè)
start slave；
或可以根據(jù)錯(cuò)誤號(hào)事先在配置文件中配置，跳過指定的不影響業(yè)務(wù)的數(shù)據(jù)的錯(cuò)誤，例如：
[root@db02 oldboy]# grep slave-skip /data/3306/my.cnf
slave-skip-errors = 1032,1062
提示：類似由于入庫重復(fù)導(dǎo)致的失敗可以忽略，其他情況是不可以忽略需要根據(jù)公司不同業(yè)務(wù)來評(píng)估。
錯(cuò)誤2
MYSQL ERROR 139 (HY000)錯(cuò)誤的解決辦法
標(biāo)簽：mysql error 1396 it
創(chuàng)建用戶的時(shí)候報(bào)這個(gè)錯(cuò)誤，原因是MYSQL中已經(jīng)有了這個(gè)用戶，可以用mysql.user中直接刪除，然后刷新權(quán)限，在創(chuàng)建用戶就不會(huì)有這個(gè)問題了。如果是drop user先，那么mysql內(nèi)部應(yīng)該會(huì)自動(dòng)刷新一下，那么在創(chuàng)建就不會(huì)這個(gè)問題看
其他可能引起復(fù)制故障的問題：
MySQL自身的原因以及人為重復(fù)插入數(shù)據(jù)
不同的數(shù)據(jù)版本會(huì)引起不同步，低版本到高版本可以，但是高版本不能往低版本同步
MySQL的運(yùn)行錯(cuò)誤或者程序BUG
binlog記錄模式，例如：row level模式就比默認(rèn)的語句要好
1.7 讓MySQL從庫記錄binlog日志方法
??從庫需要記錄binlog的場(chǎng)景為：當(dāng)前從庫還要作為其他從庫的主庫，例如：級(jí)聯(lián)復(fù)制或者雙主互為主從場(chǎng)景的情況下。
在從庫的my.cnf中加入如下參數(shù)，然后重啟服務(wù)生效
log-slave-updates#必須要有這個(gè)參數(shù)
log-bin = /data/3307/mysql-bin
expire_logs_days = 7#相當(dāng)于刪除7天之后的日志
問題一：一個(gè)主庫的從庫太多，導(dǎo)致復(fù)制延遲。
建議從庫數(shù)量3-5 為宜，要復(fù)制的從節(jié)點(diǎn)數(shù)量過多，會(huì)導(dǎo)致復(fù)制延遲
問題二：從庫硬件比主庫差，導(dǎo)致復(fù)制延遲
??查看master和slave的系統(tǒng)配置，可能會(huì)因?yàn)闄C(jī)器配置的問題，包括磁盤IO、CPU、內(nèi)存等各方面因素造成復(fù)制的延遲，一般發(fā)生在高并發(fā)大數(shù)據(jù)量寫入場(chǎng)景。
問題三：慢SQL語句過多
??假如一條SQL語句，執(zhí)行時(shí)間是20秒，那么從庫執(zhí)行完畢，到從庫上能查到數(shù)據(jù)也至少是20秒，這樣就延遲20秒了
??SQL語句的優(yōu)化一般要作為常規(guī)工作不斷的監(jiān)控和優(yōu)化，如果是單個(gè)SQL的寫入時(shí)間長，可以修改后分多次寫入，通過查看慢查詢?nèi)罩净騭how full processlist 命令找出執(zhí)行時(shí)間長的查詢語句或者打的事務(wù)。
問題四：主從復(fù)制的設(shè)計(jì)問題
?例如，主從復(fù)制單線程，因?yàn)橹鲙鞂懖l(fā)太大，來不及傳送到從庫就會(huì)導(dǎo)致延遲。
更高版本的MySQL可以支持多線程復(fù)制，門戶網(wǎng)站會(huì)開發(fā)自己多線程同步功能。
問題五：主從庫之間的網(wǎng)絡(luò)延遲。
??主庫的網(wǎng)卡、網(wǎng)線、連接的交換機(jī)等網(wǎng)絡(luò)設(shè)備都可能成為復(fù)制的瓶頸，導(dǎo)致復(fù)制延遲，另外，跨公網(wǎng)主從復(fù)制很容易導(dǎo)致主庫復(fù)制延遲
問題六：主庫讀寫壓力大，導(dǎo)致復(fù)制延遲
主庫硬件要搞好一點(diǎn)，架構(gòu)的前端要加buffer以及緩存層。
通過read-only參數(shù)讓從庫只讀訪問
? read-only參數(shù)選項(xiàng)可以讓從服務(wù)器只允許來自服務(wù)器線程或具有SUPER權(quán)限的數(shù)據(jù)庫用戶進(jìn)行更新，可以確保從服務(wù)器不接受來自用戶端的非法用戶更新。
? read-only參數(shù)具有允許數(shù)據(jù)庫更新的條件為：
具有SUPER權(quán)限的用戶可以更新，不受read-only參數(shù)影響，例如：管理員root。
來自從服務(wù)器線程可以更新，不受read-only參數(shù)影響，例如：rep用戶
? 在生產(chǎn)環(huán)境中，可以在從庫Slave中使用read-only參數(shù)，確保從庫數(shù)據(jù)不被非法更新。
read-only參數(shù)的配置如下：
方法一：啟動(dòng)數(shù)據(jù)庫時(shí)直接帶–read-only參數(shù)啟動(dòng)或重啟，使用
mysqladmin -uroot -p123456 -S /data/3307/mysql.sock shutdown
mysql_safe --defaults-file=/data/3307/my.cnf --read-only &
方法二：在my.cnf里[mysqld]模塊下加read-only參數(shù)，然后重啟數(shù)據(jù)庫
[mysqld]
read-only
1.8 Web用戶專業(yè)設(shè)置方案：MySQL主從復(fù)制讀寫分離集群
? 專業(yè)的運(yùn)維人員提供給開發(fā)人員的讀寫分離的賬戶設(shè)置如下：
1）訪問主庫和從庫時(shí)使用一套用戶密碼，例如，用戶名：web，密碼：123456
2）即使訪問IP不同，端口也盡量相同（3306）。例如:寫庫VIP為10.0.0.1 ，讀庫VIP為10.0.0.2
? 除了IP沒辦法修改之外，要盡量為開發(fā)人員提供方便，如果數(shù)據(jù)庫前端有DAL層（DBPROXY代理）還可以只給開發(fā)人員一套用戶、密碼、IP、端口，這樣就更專業(yè)了，剩下的都是由運(yùn)維搞定。
方法1：主庫和從庫使用不同的用戶，授權(quán)不同的權(quán)限。
主庫上對(duì)web_w用戶授權(quán)如下：
用戶：web_w 密碼：123456 端口3306 ?主庫VIP：10.0.0.1
權(quán)限：SELECT,INSERT,UPDATE,DELETE
命令：GRANT SELECT,INSERT,UPDATE,DELETE ON wen.* to 'web_w'@10.0.0.% identified by '123456';
從庫對(duì)web_r用戶授權(quán)如下：
用戶：web_r 密碼:123456 端口：3306 從庫VIP：10.0.0.2
權(quán)限：SELECT
命令：GRANT SELECT ON web.* TO web@10.0.0.% identfied by '123456';
提示：此方法顯得不夠?qū)I(yè)，但是可以滿發(fā)開發(fā)需求。
方法2：主庫和從庫使用相同的用戶，但授予不同的權(quán)限。（由于主從同步 有一些可能無法同步）
主庫上對(duì)web用戶授權(quán)如下：
用戶：web 密碼：123456 端口：3306 主庫VIP：10.0.0.1
權(quán)限：SELECT,INSERT,UPDATE,DELETE
命令：GRANT SELECT,INSERT,UPDATE,DELETE ON web.* TO web@10.0.0.% identified by '123456';
從庫上對(duì)web用戶授權(quán)如下：
用戶：web ?密碼：123456 端口：3306 ?從庫VIP：10.0.0.2
權(quán)限：SELECT
#由于主庫和從庫是同步復(fù)制的，所以從庫上的Web用戶會(huì)自動(dòng)和主庫一直，既無法實(shí)現(xiàn)只讀select的權(quán)限。
方法3：在從庫上設(shè)置read-only參數(shù)，讓從庫只讀
主庫和從庫：主庫和從庫使用相同的用戶，授予相同的權(quán)限（非ALL權(quán)限）
用戶：web 密碼：123546 端口：3306 主庫VIP:10.0.0.8
權(quán)限：SELECT,INSERT,UPDATE,DELETE
命令：GRANT SELECT,INSERT,UPDATE,DELETE ON web.* to web_w@10.0.0.% identified by '123546';
由于從庫設(shè)置了read-only，非super權(quán)限是無法寫入的，因?yàn)橥ㄟ^read-only參數(shù)就可以
忽略授權(quán)庫Mysql同步，主庫配置參數(shù)如下：
binlog-ignore-db = mysql
replicate-ignore-db = mysql
1.9 MySQL主從復(fù)制集群架構(gòu)的數(shù)據(jù)備份策略
? 有了主從復(fù)制，還需要做定時(shí)全量備份
因?yàn)?，如果主庫有語句誤操作（例如：drop database oldboy;）從庫也會(huì)執(zhí)行drop，這樣MySQL從庫就都刪除了該數(shù)據(jù)。
把從庫作為數(shù)據(jù)備份服務(wù)器時(shí)，備份策略如下：
高并發(fā)業(yè)務(wù)場(chǎng)景備份時(shí)，可以選擇在一臺(tái)從庫上備份（Slave）把從庫作為數(shù)據(jù)備份服務(wù)器時(shí)需要在從庫binlog功能。
1）選擇一個(gè)不對(duì)外提供服務(wù)的從庫，這樣可以確保和主庫更新最接近，專門做數(shù)據(jù)備份用
2）開啟從庫的binlog功能。
? 備份時(shí)可以選擇只停止SQL線程，停止應(yīng)用SQL語句到數(shù)據(jù)庫，I/O線程保留工作狀態(tài)，執(zhí)行命令為stop slave sql_thread；備份方式可以采取mysqldump邏輯備份或者直接物理備份，例如使用cp、tar（針對(duì)目錄）工具，或xtrabackup（第三方的物理備份軟件）進(jìn)行備份，邏輯備份和物理備份的選擇，一般是根據(jù)總的備份數(shù)據(jù)量的多少進(jìn)行選擇，數(shù)據(jù)庫低于20G，建議選擇mysqldump邏輯備份方法，安全穩(wěn)定，最后把全量和binlog數(shù)據(jù)發(fā)送到備份服務(wù)器上留存
M –S1
? –S2
? –S3
? –S4
是否事先選擇好從庫，從庫如何選擇
—S1作為備庫
第一:主庫插入數(shù)據(jù)后，同時(shí)寫入到S1，成功返回。
優(yōu)點(diǎn):兩臺(tái)庫會(huì)同時(shí)寫入數(shù)據(jù)。
缺點(diǎn)：寫入會(huì)慢，網(wǎng)絡(luò)不穩(wěn)定，主庫持續(xù)等待。
解決措施：
1.連不上S1的時(shí)候會(huì)自動(dòng)轉(zhuǎn)為異步
2.設(shè)置10秒超時(shí)，超時(shí)10秒轉(zhuǎn)為異步
3.S1網(wǎng)絡(luò)，硬件要好，不提供服務(wù)，只能接管。
總結(jié)：
半同步就是就是用戶向mysql寫入數(shù)據(jù)，先寫入到主庫，然后生成binlog日志。主庫等待從庫來取binlog日志，如果從庫超過10秒沒有來獲取binlog日志。主庫自動(dòng)轉(zhuǎn)換為異步，以后用戶寫入數(shù)據(jù)生成binlog日志，等待用戶自己來取，沒有取到主庫也不在管理。
查看的位置在數(shù)據(jù)里的安裝目錄下可以找到
[root@db02 oldboy]# ll /application/mysql-5.5.49/lib/plugin/
-rwxr-xr-x 1 root root 173428 Jun 16 12:57 semisync_master.so
-rwxr-xr-x 1 root root ?94098 Jun 16 12:57 semisync_slave.so
優(yōu)點(diǎn)：可以立即接管主庫
缺點(diǎn)：浪費(fèi)資源 不推薦使用
當(dāng)主庫宕機(jī)后，mysql 臨時(shí)選擇一個(gè)最接近主庫的slave
確定主之后，角色切換S1提升為新主M1
1、如果主庫可以SSH連接，bin-log數(shù)據(jù)沒丟，要把主庫的bin-log補(bǔ)全到所有庫
a.提升S1為M1的操作
? 1）調(diào)配置read-only，授權(quán)用戶select，變成增刪改查，開啟binlog
? 2）rm -rf master.info relay-log*
? 3）登錄數(shù)據(jù)庫reset master
? 4）重啟數(shù)據(jù)庫，提升S1為M1完畢
b.所有從庫:CHANGE MASTER TO,MASTER_LOG_FILE=’mysql-bin.000001′,MASTER_LOG_POS=107
2、如果數(shù)據(jù)庫連接不上
??a.半同步從庫提升主庫，半同步數(shù)據(jù)，補(bǔ)全到所有從庫
半同步從庫提升主庫的操作1-a，所有從庫執(zhí)行同1-b
? b.S1 只當(dāng)做備庫的方法
提升S1為主庫，操作見1-a步驟，所有從庫CHANGE MASTER 同1-b
? c.主庫宕機(jī)實(shí)現(xiàn)沒有指定從庫為主庫
選主的過程:
1.登錄國有從庫show processlist;里面有2個(gè)線程，查看2個(gè)線程狀態(tài)
登錄所有從庫，分別查看master.info
[root@db02 data]# cat master.info
18
mysql-bin.000028
188
www.etiantian.org
rep
123456
3306
60
0
確保更新完畢，查看4個(gè)從庫那個(gè)更快，經(jīng)過測(cè)試沒有延遲的情況POS差距很小，甚至一直的
選擇文件及位置點(diǎn)最大的為主庫，補(bǔ)全所有其他從庫，和當(dāng)前準(zhǔn)備為主的數(shù)據(jù)一直。
提升的主庫操作
1.確保所有relay log全部更新完畢
在每個(gè)庫執(zhí)行seop slave in_thread(sql線程)；show processlit
直到看到Has read all relay log；表示從庫更新都執(zhí)行完畢；
2.登錄從庫提升為主庫
登錄：mysql -uroot -p123456 -S /data/3306/mysql.sock
stop slave;
retset master;
quit;
3.進(jìn)到數(shù)據(jù)庫數(shù)據(jù)目錄，刪除master.info relay-log.info
cd /data/3306/data
rm -rf master.info relay-log.info
檢查mysql授權(quán)表（web用戶權(quán)限以及從庫同步的權(quán)限）是不是正確的
read-only等參數(shù)
確認(rèn)bin-log是否開啟
4.開啟binlog
vim /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
//如果存在log-slave-updates read-only等一定要注釋掉它。
/data/3306/mysql restart
到此為止，提升主庫完畢
其他從庫操作
登錄從庫
stop slave；
CHANGE MASTER TO MASTER_HOST ='192.168.1.1'; #如果不同步，就指定位置點(diǎn)。
start slave；
show slave status\G
==========================主庫宕機(jī)切換成功
修改程序配置文件從主數(shù)據(jù)庫32指定32
平時(shí)訪問數(shù)據(jù)庫用域名，則直接可以修改hosts解析。
HMA高可用根據(jù)就是利用以上原理實(shí)現(xiàn)的。
以上是mysql主庫意外宕機(jī)。
假如：我們有計(jì)劃切換，如何從操作？
1.主庫鎖表
2.登錄所有的庫查看同步狀態(tài)，是否完成
步驟和前面相同
mysql+HA+DRBD高可用場(chǎng)景
mysql+MMM
mysql+MHA（日本人開發(fā)）
實(shí)現(xiàn)原理：把所有服務(wù)器之間做了一個(gè)SSH免密鑰登錄，控制臺(tái)登錄到主庫分發(fā)binlog到所有從庫，在上從庫比對(duì)哪一個(gè)更快更全
PXC
mysql+cluster （企業(yè)很少使用）
很多企業(yè)常用M-S
