一.主從復(fù)制簡(jiǎn)介
2015年5月28日11時(shí)，12小時(shí)后恢復(fù)，損失：平均每小時(shí)106.48W$
1）高可用
2）輔助備份
3）分擔(dān)負(fù)載
復(fù)制是 MySQL 的一項(xiàng)功能，允許服務(wù)器將更改從一個(gè)實(shí)例復(fù)制到另一個(gè)實(shí)例。
1）主服務(wù)器將所有數(shù)據(jù)和結(jié)構(gòu)更改記錄到二進(jìn)制日志中。
2）從屬服務(wù)器從主服務(wù)器請(qǐng)求該二進(jìn)制日志并在本地應(yīng)用其內(nèi)容。
3）IO：請(qǐng)求主庫(kù)，獲取上一次執(zhí)行過(guò)的新的事件，并存放到relaylog
4）SQL：從relaylog中將sql語(yǔ)句翻譯給從庫(kù)執(zhí)行
二.主從復(fù)制原理
主從復(fù)制的前提(保證數(shù)據(jù)的一致性,最好打點(diǎn)備份,寫入的位置點(diǎn)為打點(diǎn)備份的位置點(diǎn)或者cat /application/mysql/data/relay-log.info )
1）兩臺(tái)或兩臺(tái)以上的數(shù)據(jù)庫(kù)實(shí)例
2）主庫(kù)要開(kāi)啟二進(jìn)制日志
3）主庫(kù)要有復(fù)制用戶
4）主庫(kù)的server_id和從庫(kù)不同
5）從庫(kù)需要在開(kāi)啟復(fù)制功能前，要獲取到主庫(kù)之前的數(shù)據(jù)（主庫(kù)備份，并且記錄binlog當(dāng)時(shí)位置）
6）從庫(kù)在第一次開(kāi)啟主從復(fù)制時(shí)，時(shí)必須獲知主庫(kù)：ip，port，user，password，logfile，pos
IP：10.0.0.51
Port：3306
User：rep
Password：oldboy123
logFile：mysql-bin.000002
Pos：120
7）從庫(kù)要開(kāi)啟相關(guān)線程：IO、SQL
8）從庫(kù)需要記錄復(fù)制相關(guān)用戶信息，還應(yīng)該記錄到上次已經(jīng)從主庫(kù)請(qǐng)求到哪個(gè)二進(jìn)制日志
9）從庫(kù)請(qǐng)求過(guò)來(lái)的binlog，首先要存下來(lái)，并且執(zhí)行binlog，執(zhí)行過(guò)的信息保存下來(lái)
主從復(fù)制涉及到的文件和線程
主庫(kù)：
1）主庫(kù)binlog：記錄主庫(kù)發(fā)生過(guò)的修改事件
2）dump thread：給從庫(kù)傳送（TP）二進(jìn)制日志線程
從庫(kù)：
1）relay-log（中繼日志）：存儲(chǔ)所有主庫(kù)TP過(guò)來(lái)的binlog事件
2）master.info：存儲(chǔ)復(fù)制用戶信息，上次請(qǐng)求到的主庫(kù)binlog位置點(diǎn)
3）IO thread：接收主庫(kù)發(fā)來(lái)的binlog日志，也是從庫(kù)請(qǐng)求主庫(kù)的線程
4）SQL thread：執(zhí)行主庫(kù)TP過(guò)來(lái)的日志
原理
1）通過(guò)change master to語(yǔ)句告訴從庫(kù)主庫(kù)的ip，port，user，password，file，pos
2）從庫(kù)通過(guò)start slave命令開(kāi)啟復(fù)制必要的IO線程和SQL線程
3）從庫(kù)通過(guò)IO線程拿著change master to用戶密碼相關(guān)信息，連接主庫(kù)，驗(yàn)證合法性
4）從庫(kù)連接成功后，會(huì)根據(jù)binlog的pos問(wèn)主庫(kù)，有沒(méi)有比這個(gè)更新的
5）主庫(kù)接收到從庫(kù)請(qǐng)求后，比較一下binlog信息，如果有就將最新數(shù)據(jù)通過(guò)dump線程給從庫(kù)IO線程
6）從庫(kù)通過(guò)IO線程接收到主庫(kù)發(fā)來(lái)的binlog事件，存儲(chǔ)到TCP/IP緩存中，并返回ACK更新master.info
7）將TCP/IP緩存中的內(nèi)容存到relay-log中
8）SQL線程讀取relay-log.info，讀取到上次已經(jīng)執(zhí)行過(guò)的relay-log位置點(diǎn)，繼續(xù)執(zhí)行后續(xù)的relay-log日志，執(zhí)行完成后，更新relay-log.info
主從復(fù)制搭建實(shí)戰(zhàn)
主庫(kù)操作:
1）修改配置文件
#編輯mysql配置文件
[root@db01 ~]# vim /etc/my.cnf
#在mysqld標(biāo)簽下配置
[mysqld]
#主庫(kù)server-id為1，從庫(kù)不等于1
server_id =1
#開(kāi)啟binlog日志
log_bin=mysql-bin
2）創(chuàng)建主從復(fù)制用戶
#登錄數(shù)據(jù)庫(kù)
[root@db01 ~]# mysql -uroot -poldboy123
#創(chuàng)建rep用戶
mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by 'oldboy123';#replication全局,不能給單庫(kù)授權(quán)
從庫(kù)操作:
1）修改配置文件
#修改db02配置文件
[root@db02 ~]# vim /etc/my.cnf
#在mysqld標(biāo)簽下配置
[mysqld]
#主庫(kù)server-id為1，從庫(kù)不等于1
server_id =5
#重啟mysql
[root@db02 ~]# /etc/init.d/mysqld restart
#記錄主庫(kù)binlog及位置點(diǎn)(主庫(kù)沒(méi)數(shù)據(jù))
mysql> show master status;
#登陸數(shù)據(jù)庫(kù)
[root@db02 ~]# mysql -uroot -poldboy123
#執(zhí)行change master to 語(yǔ)句
change master to
master_host='10.0.0.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000004',
master_log_pos=68192
master_delay=
#主庫(kù)有數(shù)據(jù)時(shí):找起始位置點(diǎn)
1.邏輯備份 zcat +備份文件 |head 25 或者查看relay-log.info
2.物理備份
[root@db01 script]# cat /backup/full_2019-12-12-00/xtrabackup_binlog_info
mysql-bin.00000115724
master_log_file=
master_log_pos=
#恢復(fù)數(shù)據(jù),找截止點(diǎn)
邏輯:mysqlbinlog --base64-output=decode-rows -vvv +mysql-bin.000001的絕對(duì)路徑
物理:合并,--copy-back 先備份.在刪除data
四.主從復(fù)制基本故障處理
IO線程
從庫(kù)上檢測(cè)
1.網(wǎng)路不通
ping 10.0.0.51
1.硬件層.路由,交換機(jī),網(wǎng)路設(shè)備
2.網(wǎng)路
3.安全組規(guī)則
4.差錯(cuò)網(wǎng)線口
2.端口不通
telnet 10.0.0.51 3306
yum install -y tcping
tcping 172.16.1.51 3306
mysql -urep -p1 -h172.16.1.51
原因:1.防火墻 2.selinux
解決:
1.針對(duì)端口開(kāi)放firewalld-cmd --add-service=mysql --add-port=3306/tcp 2.關(guān)閉selinux
3.用戶名錯(cuò)誤
4.密碼錯(cuò)誤
5.反向解析
skip_name_resolve
sql線程是NO
1.從庫(kù)有該數(shù)據(jù),主庫(kù)要?jiǎng)?chuàng)建.操作對(duì)象已存在
從庫(kù):a庫(kù)
主庫(kù):要?jiǎng)?chuàng)建a庫(kù)
解決辦法: set global sql_slave_skip_counter=1 (生產(chǎn)環(huán)境不能交戶)
2.主庫(kù)有該數(shù)據(jù),從庫(kù)沒(méi)有,操作對(duì)象不存在(insert update delete drop truncate alter)
解決辦法:保證數(shù)據(jù)一致性,
連接主庫(kù)
1）user password ip port
2）網(wǎng)絡(luò)：不通，延時(shí)高，防火墻
請(qǐng)求binlog
1）binlog不存在或者損壞
更新relay-log和master.info
SQL線程
1）relay-log出現(xiàn)問(wèn)題
2）從庫(kù)做寫入了
- 操作對(duì)象已存在（create）
- 操作對(duì)象不存在（insert update delete drop truncate alter）
- 約束問(wèn)題、數(shù)據(jù)類型、列屬性
處理方法一：
#臨時(shí)停止同步
mysql> stop slave;
#將同步指針向下移動(dòng)一個(gè)（可重復(fù)操作）
mysql> set global sql_slave_skip_counter=1;
#開(kāi)啟同步
mysql> start slave;
處理方法二：
#編輯配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]標(biāo)簽下添加以下參數(shù)
slave-skip-errors=1032,1062,1007
但是以上操作都是有風(fēng)險(xiǎn)存在的
處理方法三：
1）重新備份數(shù)據(jù)庫(kù)，恢復(fù)到從庫(kù)
2）給從庫(kù)設(shè)置為只讀
#在命令行臨時(shí)設(shè)置
set global read_only=1;
#在配置文件中永久生效
read_only=1
#grant all 中有(supper),可以創(chuàng)建,只能給其它權(quán)限,
五.延時(shí)從庫(kù)
普通的主從復(fù)制可能存在不足
1）邏輯損壞怎么辦？
2）不能保證主庫(kù)的操作，從庫(kù)一定能做
3）高可用？自動(dòng)failover？
4）過(guò)濾復(fù)制
企業(yè)中一般會(huì)延時(shí)3-6小時(shí)
延時(shí)從庫(kù)配置方法
#停止主從
mysql>stop slave;
#設(shè)置延時(shí)為180秒
mysql>CHANGE MASTER TO MASTER_DELAY = 180;
#開(kāi)啟主從
mysql>start slave;
#查看狀態(tài)
mysql> show slave status \G
SQL_Delay: 60
3.延時(shí)從庫(kù)停止方法
#停止主從
mysql> stop slave;
#設(shè)置延時(shí)為0,取消延時(shí)
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
#開(kāi)啟主從
mysql> start slave;
沒(méi)有主從
1.修改主庫(kù)從庫(kù)的配置文件
主:server_id
開(kāi)啟:binlog
從庫(kù):配置server_id
2.保證從庫(kù)和主庫(kù)的數(shù)據(jù)一致
3.執(zhí)行chang master to
master_host='',
master_user='',
master_password='',
master_log_file='',
master_log_pos= ,#找尋全量備份的點(diǎn)
master_delay= ;
思考問(wèn)題：
總數(shù)據(jù)量級(jí)500G，正常備份去恢復(fù)需要1.5-2小時(shí)
1）配置延時(shí)3600秒
mysql>CHANGE MASTER TO MASTER_DELAY = 3600;
2）主庫(kù)
drop database db;
3）怎么利用延時(shí)從庫(kù)，恢復(fù)數(shù)據(jù)？
提示：
1、從庫(kù)relaylog存放在datadir目錄下
2、mysqlbinlog 可以截取relaylog內(nèi)容
3、show relay log events in 'db01-relay-bin.000001';
處理的思路：1）停止SQL線程
mysql> stop slave sql_thread;
2）截取relaylog到誤刪除之前點(diǎn)
-
relay-log.info 獲取到上次運(yùn)行到的位置點(diǎn)，作為恢復(fù)起點(diǎn)
1）停止SQL線程
mysql> stop slave sql_thread;
2）截取relaylog到誤刪除之前點(diǎn)
relay-log.info 獲取到上次運(yùn)行到的位置點(diǎn)，作為恢復(fù)起點(diǎn)
全備
mysqldump -uroot -p1 -A |gzip>/tmp/full1.sql
起始點(diǎn)
[root@db04 data]# cat relay-log.info
7
./db04-relay-bin.000005
283
mysql-bin.000005
截取截止點(diǎn)relay_log 文件
[root@db04 data]# mysqlbinlog -uroot -p1 --start-position=59503 --stop-position=59691 db04-relay-bin.000002 >/tmp/yanchi3.sql
將數(shù)據(jù)導(dǎo)入生產(chǎn)環(huán)境
先導(dǎo)全備,在導(dǎo)增備
六.半同步復(fù)制
從MYSQL5.5開(kāi)始，支持半自動(dòng)復(fù)制。之前版本的MySQL Replication都是異步（asynchronous）的，主庫(kù)在執(zhí)行完一些事務(wù)后，是不會(huì)管備庫(kù)的進(jìn)度的。如果備庫(kù)不幸落后，而更不幸的是主庫(kù)此時(shí)又出現(xiàn)Crash（例如宕機(jī)），這時(shí)備庫(kù)中的數(shù)據(jù)就是不完整的。簡(jiǎn)而言之，在主庫(kù)發(fā)生故障的時(shí)候，我們無(wú)法使用備庫(kù)來(lái)繼續(xù)提供數(shù)據(jù)一致的服務(wù)了。
半同步復(fù)制（Semi synchronous Replication）則一定程度上保證提交的事務(wù)已經(jīng)傳給了至少一個(gè)備庫(kù)。
出發(fā)點(diǎn)是保證主從數(shù)據(jù)一致性問(wèn)題，安全的考慮。
5.5 出現(xiàn)概念，但是不建議使用，性能太差
5.6出現(xiàn)group commit 組提交功能，來(lái)提升開(kāi)啟半同步復(fù)制的性能
5.7更加完善了，在group commit基礎(chǔ)上出現(xiàn)了MGR
5.7的增強(qiáng)半同步復(fù)制的新特性：after commit； after sync；
半同步復(fù)制開(kāi)啟方法(等待ack返回時(shí),主庫(kù)處于鎖表狀態(tài))
1）安裝（主庫(kù)）
#登錄數(shù)據(jù)庫(kù)
[root@db01 ~]# mysql -uroot -poldboy123
#查看是否有動(dòng)態(tài)支持
mysql> show global variables like 'have_dynamic_loading';
#安裝自帶插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';
#啟動(dòng)插件
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
#設(shè)置超時(shí)
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
#修改配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]標(biāo)簽下添加如下內(nèi)容（不用重啟庫(kù)）
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
檢查安裝：
mysql> show variables like'rpl%';
mysql> show global status like 'rpl_semi%';
2）安裝（從庫(kù)）
#登錄數(shù)據(jù)庫(kù)
[root@mysql-db02 ~]# mysql -uroot -poldboy123
#安裝slave半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';
#啟動(dòng)插件
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
#重啟io線程使其生效
mysql> stop slave io_thread;
mysql> start slave io_thread;
#編輯配置文件（不需要重啟數(shù)據(jù)庫(kù)）
[root@mysql-db02 ~]# vim /etc/my.cnf
#在[mysqld]標(biāo)簽下添加如下內(nèi)容
[mysqld]
rpl_semi_sync_slave_enabled =1
注：相關(guān)參數(shù)說(shuō)明
rpl_semi_sync_master_timeout=milliseconds
設(shè)置此參數(shù)值（ms）,為了防止半同步復(fù)制在沒(méi)有收到確認(rèn)的情況下發(fā)生堵塞，如果Master在超時(shí)之前沒(méi)有收到任何確認(rèn)，將恢復(fù)到正常的異步復(fù)制，并繼續(xù)執(zhí)行沒(méi)有半同步的復(fù)制操作。
rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一個(gè)事務(wù)被提交,但Master沒(méi)有任何Slave的連接，這時(shí)不可能將事務(wù)發(fā)送到其它地方保護(hù)起來(lái)。默認(rèn)情況下，Master會(huì)在時(shí)間限制范圍內(nèi)繼續(xù)等待Slave的連接，并確認(rèn)該事務(wù)已經(jīng)被正確的寫到磁盤上。
可以使用此參數(shù)選項(xiàng)關(guān)閉這種行為，在這種情況下，如果沒(méi)有Slave連接，Master就會(huì)恢復(fù)到異步復(fù)制。
測(cè)試半同步
#創(chuàng)建兩個(gè)數(shù)據(jù)庫(kù)，test1和test2
mysql> create database test1;
Query OK, 1 row affected (0.04 sec)
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
#查看復(fù)制狀態(tài)
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
#此行顯示2，表示剛才創(chuàng)建的兩個(gè)庫(kù)執(zhí)行了半同步
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.06 sec)
#從庫(kù)查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test1 |
| test2 |
+--------------------+
#關(guān)閉半同步（1:開(kāi)啟 0:關(guān)閉）
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 0;
#查看半同步狀態(tài)
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF | #狀態(tài)為關(guān)閉
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
#再一次創(chuàng)建兩個(gè)庫(kù)
mysql> create database test3;
Query OK, 1 row affected (0.00 sec)
mysql> create database test4;
Query OK, 1 row affected (0.00 sec)
#再一次查看半同步狀態(tài)
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
#此行還是顯示2，則證明，剛才的那兩條并沒(méi)有執(zhí)行半同步否則應(yīng)該是4
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
注:不難發(fā)現(xiàn)，在查詢半同步狀態(tài)是，開(kāi)啟半同步，查詢會(huì)有延遲時(shí)間，關(guān)閉之后則沒(méi)有
七.過(guò)濾復(fù)制
主庫(kù)：
白名單:只記錄白名單中列出的庫(kù)的二進(jìn)制日志
黑名單：不記錄黑名單列出的庫(kù)的二進(jìn)制日志
從庫(kù)：
白名單：只執(zhí)行白名單中列出的庫(kù)或者表的中繼日志
- --replicate-do-db=test
- --replicate-do-table=test.t1
- --replicate-wild-do-table=test.t*#支持通配符
黑名單：不執(zhí)行黑名單中列出的庫(kù)或者表的中繼日志
- --replicate-ignore-db
- --replicate-ignore-table
- --replicate-wild-ignore-table
復(fù)制過(guò)濾配置：
[root@db01 data]# vim /data/3307/my.cnf
#在[mysqld]標(biāo)簽下添加
replicate-do-db=world
#關(guān)閉MySQL
mysqladmin -S /data/3307/mysql.sock shutdown
#啟動(dòng)MySQL
mysqld_safe --defaults-file=/data/3307/my.cnf &
測(cè)試復(fù)制過(guò)濾：
第一次測(cè)試：
1）主庫(kù)：
[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock
mysql> use world
mysql> create table t1(id int);
2）從庫(kù)查看結(jié)果：
[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock
mysql> use world
mysql> show tables;
第二次測(cè)試：
1）主庫(kù)：
[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock
mysql> use test
mysql> create table tb1(id int);
2）從庫(kù)查看結(jié)果：
[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock
mysql> use test
mysql> show tables;
過(guò)濾復(fù)制小結(jié)
- 在主庫(kù)上設(shè)置白名單:只記錄白名單設(shè)置的庫(kù)和表,及相關(guān)的sql語(yǔ)句到binlog中
- 在主庫(kù)上設(shè)置黑名單:不記錄黑名單設(shè)置的庫(kù)和表,及相關(guān)的sql語(yǔ)句到binlog中
- 在從庫(kù)上設(shè)置白名單:IO線程會(huì)拿所有的binlog.但是sql線程只執(zhí)行白名單設(shè)置的庫(kù)或者表相關(guān)的sql語(yǔ)句
- 再?gòu)膸?kù)上設(shè)置黑名單:IO線程會(huì)拿 所有的binlog,但是sql線程不執(zhí)行黑名單設(shè)置的庫(kù)或者表相關(guān)的sql語(yǔ)句
|
