十二、mysql主從復制
1、主從復制原理
1.主從復制的前提：
1.1兩臺mysql實體（多臺物理機，或者多實體）
1.2主庫要開啟二進制日志
1.3主庫要提供復制相關用戶，replication slave,一個比較特殊的權限，
grant replication slave on * . * to repl@'10.0.0.%' identified by '123';
1.4從庫需要將和主庫相差的資料，進行追加
一般情況下可以人為備份主庫資料，恢復到從庫上，
1.5應該從恢復之后的時間點，開始自動從主庫獲取二進制日志開始應用
需要人為告訴從庫，從哪里開始自動開始復制二進制日志(file+position)，另外還需要告訴從庫user,password,ip,port
2.復制中的執行緒及檔案
2.1主庫
dump（IO） thread:在復制程序中，主庫發送二進制日志的執行緒，
2.2從庫
IO thread:向主庫請求二進制日志，并且接受二進制日志的執行緒，
SQL thread:執行請求過來的二進制的執行緒
2.3在主庫的檔案
binlog檔案，主庫的二進制日志
2.4從庫檔案
relaylog：中繼日志，存盤請求過來的二進制日志，
master.info:
? 1.從庫連接主庫的重要引數（user，password，ip，port）
? 2.上次獲取過的主庫二進制日志的位置
relay-log.info
? 存盤從庫SQL執行緒已經執行過的relaylog日志位置，
3.主從復制的作業原理
3.1從庫，IO執行緒，讀取master.info中的資訊，獲取到連接引數（user，password，ip，port），和上次用過的主庫的binlog的位置（mysqlbin-0000,position），
3.2IO執行緒使用連接到主庫，拿著上次從主庫獲取到的binlog的位置，問主庫有沒有比這個更新的二進制日志，
3.3主庫查詢二進制日志，并對比從庫發送過來的位置資訊，如果有新的二進制日子，就通過dump thread發送給我從庫，
3.4從庫通過IO執行緒，接受主庫發來的二進制日志，存盤到TCP/IP快取中，并且回傳ACK確認給主庫，這時主庫認為復制完成了，可以繼續其他作業了，
3.5從庫更新master.info，二進制日志的為新的位置資訊，
3.6從庫IO執行緒會將TCP/IP快取中的日志，存盤到relay-log中繼日志檔案中，
3.7從庫SQL執行緒，讀取relay-log.info，獲取到上次執行到的relay-log日志位置，以這個位置為起點，往后繼續執行中繼日志，
3.8SQL執行緒執行完成所有relay之后，會更新relay-log.info資訊為新位置資訊，
到此位置，一次完成的復制程序完成，
4、搭建主從復制
1、準備環境
思路：
1、兩個以上節點（多實體）
3307：master
3308：slave1
3309：slave2
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
[root@db02 data]# cat /data/3309/my.cnf |grep server-id
server-id=3309
4、關閉資料庫的自動域名決議
每個節點都加入以下配置：
skip-name-resolve
5、啟動多實體
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/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=https://www.cnblogs.com/cuiyongchao007/p/2 --single-transaction >/tmp/full.sql
sed -n'22p' /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', 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.203',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=325;
開啟主從（開啟IO和SQL執行緒）：
start slave;
9、查看主從狀態：
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
10、主從重要狀態資訊介紹
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
IO執行緒故障：
1、主庫連接不上
user、password、port、ip 錯誤
解決方案：
stop slave;
reset slave all;
change master to
start slave;
防火墻
網路不通
skip-name-resolve
stop slave;
start slave;
2、主庫二進制日志丟失或損壞
解決方案：
stop slave;
reset slave all;
重新備份恢復
change master to
start slave;
5、SQL執行緒故障
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 ---->只能控制普通用戶
6、主從例外--主從延時過長
show slave status \G
Seconds_Behind_Master:0
默認的主從復制機制是異步的一個程序，
主庫原因:
1、主庫做修改操作之后，才會記錄二進制日志，
sync_binlog=0/1
If the value of this variable is greater than 0,
the MySQL server synchronizes its binary log to disk (using fdatasync())
after sync_binlog commit groups are written to the binary log.
The default value of sync_binlog is 0, which does no synchronizing to disk—in this case,
the server relies on the operating system to flush the binary log's contents from time to time as for any other file.
A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log.
However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast)
---------------------
1：表示：每次事務commit，重繪binlog到磁盤
0：系統決定binlog什時候重繪到磁盤
2、主庫的壓力特別大（大事務、多事務）
3、從庫數量多，導致dump執行緒繁忙
-------------------
從庫原因：
1、relay-log寫入慢
2、SQL執行緒慢（主從硬體差異比較大）
-----------------------------
盡可能的避免主從延時
1、sync_binlog=1
2、大事務拆成小事務，多事務進行分離
3、使用多級主從，分庫分表架構
4、將binlog放到ssd或者flash上，高性能存盤
5、將relay放到ssd或者flash上
6、盡量選擇和主庫一致硬體和配置
7、主從復制高級功能--半同步復制
出發點：保證主從資料一致性的問題，安全的考慮
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，這次半同步就完成了，
8、主從復制高級特性--延時從庫
會專門找一個節點，配置成延時節點，盡可能防止邏輯損壞，一般情況下這個節點會被用備份
我們配置的是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;
9、主從復制高級功能--復制過濾
主庫方面控制（不建議使用）：
白名單:只記錄白名單中列出的庫的二進制日志
binlog-do-db
黑名單：不記錄黑名單列出的庫的二進制日志
binlog-ignore-db
從庫方面控制：
show slave status\G;查看相關引數，
白名單：只執行白名單中列出的庫或者表的中繼日志
--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資料庫的資料
10、主從復制新特性--GTID復制
GTID
5.6新特性
GTID(Global Transaction ID)是對于一個已提交事務的編號，并且是一個全域唯一的編號，
它的官方定義如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
每一臺mysql實體中，都會有一個唯一的uuid，標識實體的唯一性
auto.cnf，存放在資料目錄下
重要引數：
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
gtid-mode=on --啟用gtid型別，否則就是普通的復制架構
enforce-gtid-consistency=true --強制GTID的一致性
log-slave-updates=1 --slave更新是否記入日志
-----------------
構建1主2從的GTID復制環境：
3臺虛擬機，
db02 克隆兩臺虛擬機環境，分別命名為db01、db03，在生產中準備3臺真實的物理機，不用多實體
要求：
1、IP地址、主機名
db01:10.0.0.51/24
db03:10.0.0.53/24
2、清理所有之前3306的相關資料，只留軟體
db01:
cd /application/mysql/data/
\rm -rf *
cd /data/binlog/
\rm -rf *
db02:
cd /application/mysql/data/
\rm -rf *
cd /data/binlog/
\rm -rf *
db03:
cd /application/mysql/data/
\rm -rf *
cd /data/binlog/
\rm -rf *
3、準備組態檔
規劃：
主庫： 10.0.0.51/24
從庫1: 10.0.0.52/24
從庫2：10.0.0.53/24
主庫：
加入以下配置資訊
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/44474.html
標籤：MySQL
上一篇：第六天MySQL