mysql 主從復制

一、mysql復制原理

• 從庫的I/O thread 線程會讀取master info 文件 獲取主庫的 user,password port信息然后還會獲取上次獲取主庫二進制日志的位置 如3640 就是00003這個文件640這個位置，主庫收到從庫的請求后，會驗證用戶名密碼等的合法性，然后問主庫你有沒有比上次00003文件640這個位置更加新的二進制日志，然后主庫就會查看自己的binglog日志，如果發現比640這個新，如已經到達31080這個位置了，主庫就會把00003號文件的640這個位置到1080這個位置的binglog日志截斷，通過dump(i/o)線程返回給從庫的I/O thread線程，到從庫之后，它會先存到tcp/ip緩存當中(tcp/ip cached)，然后從庫會立即發送一個ack給主庫，主庫收到ack后就認為這個過程已經完成，就可以去干別的事情了，此時從庫會更新mast info的信息，把binglog的位置信息更新到1080，下次就從1080開始往下找，然后再把tcp/ip緩存的日志寫入到relaylog當中，最后sql thread線程會讀取relay-log.info,獲取到上次執行binglog日志的位置信息，比如發現上次以及執行到640這個位置了，sql就會讀取relaylog從640的位置開始執行二進制日志，當執行完后，最后更新relay-log.info文件，記錄最后執行的位置，最后，relaylog會自動把已經執行過的二進制日志清理掉這樣一次復制就完成了

• 復制中的線程及文件
  2.1、主庫
  Dump（IO） thread：在復制過程中，主庫發送二進制日志的線程
  2.2、從庫
  IO thread：向主庫請求二進制日志，并且接受二進制日志的線程
  SQL thread：執行請求過來的二進制的線程
  2.3、主庫
  binlog文件：主庫的二進制日志
  2.4、從庫
  relaylog:中繼日志，存儲請求過來的二進制日志
  master.info:
  1、從庫連接主庫的重要參數（user,passwd,ip,port）
  2、上次獲取過的主庫二進制日志的位置
  relay-log.info
  存儲從庫SQL線程已經執行過的relaylog日志位置

  二、mysql 主從搭建

• 主從復制前提

  1、兩臺以上MySQL實例（可以是多臺物理機，也可是mysql實例）
  2、主庫要開啟二進制日志
  3、主庫要提供復制相關的用戶需要用到 replication slave一個比較特殊的權限
  4、從庫需要將和主庫相差的數據進行追加，一般情況下認為備份數據庫，恢復到從庫上
  5、從庫應該從恢復后的時間點開始自動從主庫獲取二進制日志開始自動同步主庫數據，我們需要告訴從庫，從哪兒開始復制二進制日志進行學習

• 主從復制搭建實戰
  1、環境準備
  兩個MySQL實例
  3307：master
  3308：slave
  2、開啟主庫binlog,從庫開啟relay-log(默認在數據目錄下生成)
  vim /data/3307/my.cnf
  log-bin=/data/3307/mysql-bin
  binlog_format=row
  3、server-id不同
  [root@db02 data]# cat /data/3307/my.cnf |grep server-id
  server-id=3307
  [root@db02 data]# cat /data/3308/my.cnf |grep server-id
  server-id=3308
  4、關閉數據庫自動域名解析（沒個節點實例都加）
  skip-name-resolve
  5、啟動多實例
  mysqld_safe --defaults-file=/data/3307/my.cnf &
  mysqld_safe --defaults-file=/data/3308/my.cnf &
  6、主庫創建復制賬戶連接到主庫
  mysql -S /data/3307/mysql.sock
  grant replication slave on . to repl@'10.0.0.%' identified by '123';
  7、從庫數據追加

（1）不需要追加的情況
主和從同時搭建的新環境，就不需要備份主庫數據，恢復到從庫了，直接從第一個binlog(mysql-bin.000001)開頭位置（120）

（2）如果主庫已經工作了很長時間了，我們一般需要備份主庫數據，恢復到從庫，然后從庫從備份的時間點起自動進行復制

mysqldump -S /data/3307/mysql.sock -A -R --triggers --master-data=2 --single-transaction >/tmp/full.sql
sed -n '22p' /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=325
恢復到從庫：
mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql
8、從庫開啟主庫
mysql -S /data/3308/mysql.sock

help change master to

CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=325;
開啟主從（開啟IO/SOL線程）
start slave
9、查看主從狀態
show slave status\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
10、主從重要狀態信息介紹

show slave status\G
Slave_IO_Running: Yes（io線程狀態）
Slave_SQL_Running: Yes（sql線程狀態）
Last_IO_Errno: 0（io線程異常狀態碼）
Last_IO_Error: （io線程異常詳細信息）
Last_SQL_Errno: 0（sql線程狀態碼）
Last_SQL_Error: （sql線程異常詳細信息）

三 、 主從復制常見異常解決思路

• IO線程故障
  1、主庫連接不上
  檢查user，password，port，IP，網絡，防護墻
  stop slave;
  reset slave all;(清空配置)
  chagen master to
  start slave

2、主庫二進制文件丟失或損壞
解決方案；
stop slave;
reset slave all;
從新備份恢復
change master to
start slave;

• SQL線程故障
  執行relaylog日志新新的事件
  1、刪除，修改對象的操作時，沒有這個對象
  2、創建對象時，對象已存在
  3、主鍵沖突
  從庫做寫入操作，會導致以上問題出現

處理方法跳過這個錯誤
stop slave;
set global sql_slave_skip_counter=1;
start slave;
/etc/my.cnf
slave-skip-errors=1032,1062,1007
但是，以上操作有的時候時候是有風險的，最安全的方法是從新構建新的主從
如何預防？
修改從庫為只讀庫
set global read_only=1;
vim /etc/my.cnf
read_only=1(這個參數只能控制普通用戶)

• 主從延時過長
  show slave status \G
  Seconds_Behind_Master:0

默認的主從復制是異步的過程

主庫原因
1、主庫做修改操作之后，才會記錄二進制日志
2、主庫的壓力特別大（大事務，多事物）
3、從庫數量多，導致domp線程繁忙
從庫原因：
1、relay-log寫入慢
2、sql線程慢（主從硬件差異較大）
解決思路
主庫
1、sync_binlog=1(1表示只要主庫做了一次commit，二進制日志就會立刻刷新到磁盤，如果等于0要根據系統binlog決定)
2、大事物拆分成小事物，多事物進行分離
3、使用多級主從，分庫分表架構
4、將binlog放在ssd或者flash上，高性能存儲
從庫
1、將relay放到ssd或者flash上
2、盡量選擇和主庫一樣的硬件配置

四、主從復制其他特性

• 半同步復制
  1、出現原因
  保證數據一致性問題，安全考慮
  默認情況下MySQL主從備份是通過異步的方式進行備份，當從庫向主庫請求二進制日志，從庫會把請求過來的二進制日志存到tcp/ip緩存當中，返回一個ack告訴主庫接收到了二進制日志，試想一下當這個時候從庫突然斷電了，存到tcp/ip緩存當中的二進制日志就會丟失，而主庫還也以為從庫正常接收到了二進制日志，從庫的數據就會丟失，半同步的實現方法就是當從庫接收到二進制日志后必須要存到relaylog落地到磁盤當中從庫才會發送ack給主庫，這樣就保證了數據的一致性
  半同步復制是在5.5出現的概念，但5.5的半同步復制性能太差不建議使用，在5.6以后出現group commit 組提交功能，來提升開啟版同步復制的性能，5.7 增強半同步復制的新特性：after sync;
• 具體實現

加載插件

主:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

從:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
查看是否加載成功:
show plugins;

啟動:
主:
SET GLOBAL rpl_semi_sync_master_enabled = 1;

從:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

重啟從庫上的IO線程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

查看是否在運行
主:
show status like 'Rpl_semi_sync_master_status';
從:
show status like 'Rpl_semi_sync_slave_status';

補充：
rpl_semi_sync_master_timeout | 10000
默認情況先,到達10秒鐘還沒有ack，主從關系自動切換為普通復制
如果是1主多從的半同步復制，只要有一臺落地relaylog，返回ack，這次半同步就完成了。

• 從庫延時
  防止數據庫的邏輯損壞，假設在主庫不小心誤刪除了一個表，這個事件會同樣記錄到二進制日志當中發送到從庫，這時候從庫不會立即執行這個 操作，比如配置了延時3小時，從庫會在3小時后才執行刪除表的操作，這樣就給我們運維人員一些緩沖的時間，延時從庫是對sql線程的延時
  會專門找一個節點，配置成延時節點，盡可能防止邏輯損壞，一般情況下這個節點會被用備份

• 配置實現
  SQL_thread的延時

mysql>stop slave;

mysql>CHANGE MASTER TO MASTER_DELAY = 60;

mysql>start slave;

mysql> show slave status \G
SQL_Delay: 300

取消延時：
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
mysql> start slave;

• 復制過濾
  出現原因，有得時候我們不想復制主庫的所有數據，只想復制一個庫或者一個庫某些表
  主庫方面控制（不建議使用）：
  白名單:只記錄白名單中列出的庫的二進制日志
  binlog-do-db
  黑名單：不記錄黑名單列出的庫的二進制日志
  binlog-ignore-db

從庫方面控制：
白名單：只執行白名單中列出的庫或者表的中繼日志

--replicate-do-db=test （哪個庫）
--replicate-do-table=test.t1 （哪個庫的哪個表）
--replicate-wild-do-table=test.x* （模糊的匹配）

黑名單：不執行黑名單中列出的庫或者表的中繼日志
--replicate-ignore-db
--replicate-ignore-table
--replicate-wild-ignore-table

只復制world數據庫的數據
在從庫配置
vi /etc/my.cnf
replicate-do-db=world
重啟從庫

查詢：show slave status \G
replicate-do-db = world

• 主從復制新模式-GTID復制

• GTID是從5.6之后新的復制特性，以前的復制模式是當主庫發生了任何方式的變化，會已事件的形式記錄到binlog日志當中，每個事件生成一個position號，然后從庫去獲取這些二進制事件，而GTID是把每個完整的事物單獨的生成一個全局唯一的編號，這樣就簡化了主從復制的配置，實現過程相對也簡單寫
  它的官方定義如下：
  GTID = source_id ：transaction_id
  7E11FA47-31CA-19E1-9E56-C43AA21293967:29
  前半段為UUID，后半段為事物的編號，存放在auto.cnf下

• 配置實現
  1、重要參數
  gtid-mode=on （是否開啟GTID）
  enforce-gtid-consistency=true （強制GTID的一致性）
  log-slave-updates=1 (slave更新是否寫入日志)
  2、規劃
  主庫： 10.0.0.51/24
  從庫1: 10.0.0.52/24
  從庫2：10.0.0.53/24
  3、寫入配置文件
  主庫：
  加入以下配置信息

db01:10.0.0.51/24

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=51
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

slave1：
db02:10.0.0.52/24

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

slave2：
db02:10.0.0.53/24

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=53
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

三臺節點分別初始化數據：

/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data/

分別啟動三個節點mysql：

/etc/init.d/mysqld start

測試啟動情況：
mysql -e "show variables like 'server_id'"

master:51
slave:52,53

51:
grant replication slave on . to repl@'10.0.0.%' identified by '123';

52\53:

change master to master_host='10.0.0.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;

start slave;