1，延時從庫
介紹
是我們認為配置的一種特殊從庫.人為配置從庫和主庫延時N小時.
SQL線程延時:數(shù)據(jù)已經(jīng)寫入relaylog中了,SQL線程"慢點"運行
一般企業(yè)建議3-6小時,具體看公司運維人員對于故障的反應(yīng)時間
配置
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;
mysql> show slave status \G
SQL_Delay: 300 延時從庫的設(shè)置時間
SQL_Remaining_Delay: NULL 延時從庫剩余多少時間同步
延時從庫處理邏輯故障
恢復(fù)思路：
1，監(jiān)控到數(shù)據(jù)庫邏輯故障
2，停從庫SQL線程,記錄已經(jīng)回放的位置點(截取日志起點)
stop slave sql_thread ;
show slave status \G
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
3， 截取relaylog
起點:
show slave status \G
Relay_Log_File ,Relay_Log_Pos
終點: drop之前的位置點
show relaylog events in ''
進行截取
4，模擬SQL線程回訪日志
從庫 source
5，恢復(fù)業(yè)務(wù)
情況一: 就一個庫的話
從庫替代主庫工作
情況二:
從庫導(dǎo)出故障庫,還原到主庫中
故障演練
主庫：
create database delay charset utf8mb4;
use delay;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
drop database delay;
從庫
停止從庫sql線程
mysql> stop slave sql_thread;
獲取從庫relaylog的位置點
mysql> show slave status\G
Relay_Log_File: mister_f-relay-bin.000002
Relay_Log_Pos: 501
獲取relaylog截取終點
mysql> show relaylog events in 'mister_f-relay-bin.000002';
+---------------------------+------+----------------+-----------+-------------+----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------------+------+----------------+-----------+-------------+----------------------------------------+
| mister_f-relay-bin.000002 | 4 | Format_desc | 8 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mister_f-relay-bin.000002 | 123 | Previous_gtids | 8 | 154 | |
| mister_f-relay-bin.000002 | 154 | Rotate | 7 | 0 | mysql-bin.000004;pos=1790 |
| mister_f-relay-bin.000002 | 201 | Format_desc | 7 | 0 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mister_f-relay-bin.000002 | 320 | Anonymous_Gtid | 7 | 1855 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mister_f-relay-bin.000002 | 385 | Query | 7 | 1971 | create database yanshi charset utf8mb4 |
| mister_f-relay-bin.000002 | 501 | Anonymous_Gtid | 7 | 2036 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mister_f-relay-bin.000002 | 566 | Query | 7 | 2149 | create database delay charset utf8mb4 |
| mister_f-relay-bin.000002 | 679 | Anonymous_Gtid | 7 | 2214 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mister_f-relay-bin.000002 | 744 | Query | 7 | 2314 | use `delay`; create table t1 (id int) |
| mister_f-relay-bin.000002 | 844 | Anonymous_Gtid | 7 | 2379 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mister_f-relay-bin.000002 | 909 | Query | 7 | 2452 | BEGIN |
| mister_f-relay-bin.000002 | 982 | Table_map | 7 | 2498 | table_id: 150 (delay.t1) |
| mister_f-relay-bin.000002 | 1028 | Write_rows | 7 | 2548 | table_id: 150 flags: STMT_END_F |
| mister_f-relay-bin.000002 | 1078 | Xid | 7 | 2579 | COMMIT /* xid=747 */ |
| mister_f-relay-bin.000002 | 1109 | Anonymous_Gtid | 7 | 2644 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mister_f-relay-bin.000002 | 1174 | Query | 7 | 2739 | drop database delay |
+---------------------------+------+----------------+-----------+-------------+----------------------------------------+
17 rows in set (0.00 sec)
截取relaylog日志
[root@mister_f ~]# mysqlbinlog --start-position=501 --stop-position=1174 /data/3308/data/mister_f-relay-bin.000002 >/tmp/relay.sql
恢復(fù)到從庫
[root@db01 data]# mysql -uroot -p -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql
mysql> set sql_log_bin=1;
2，過濾復(fù)制
需求：假如數(shù)據(jù)庫里有a，b，c這三個庫但是只有a庫是核心庫其他兩個是測試庫，
為了盡可能減少主從復(fù)制的的壓力，在主從復(fù)制的時候復(fù)制其中一個或者幾個這樣的需求，也就是有選擇性的需求，默認是全部的。
兩種方法限制：
主庫：限制binlog
通過show master status查看
binlog_do_db 白名單 ：只記錄白名單里的庫
binlog_ignore_db 黑名單：不記錄黑名單里的庫
在配置文件里添加后重啟生效
例：
binlog_do_db=world
從庫：relaylog
show slave status\G
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table
和主庫的一樣
例子
[root@mister_f ~]# vim /data/3308/my.cnf
replicate_do_db=repl
[root@mister_f ~]# systemctl restart mysqld3308.service
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.21.0.8
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mister_f-relay-bin.000024
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: repl
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
測試：
主庫：
mysql> create database aa charset utf8mb4;
Query OK, 1 row affected (0.01 sec)
從庫：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.21.0.8
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 323
Relay_Log_File: mister_f-relay-bin.000024
Relay_Log_Pos: 489
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: repl
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| bin |
| binlog |
| gtid |
| mysql |
| performance_schema |
| sys |
| test |
| wordpress |
| world |
| yanshi |
+--------------------+
12 rows in set (0.01 sec)
上面闊以看出日志已經(jīng)過來但是沒有同步，只會回放repl這個庫
主庫：
mysql> create database repl charset utf8mb4;
Query OK, 1 row affected (0.01 sec)
從庫：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.21.0.8
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 498
Relay_Log_File: mister_f-relay-bin.000024
Relay_Log_Pos: 664
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: repl
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| bin |
| binlog |
| gtid |
| mysql |
| performance_schema |
| repl |
| sys |
| test |
| wordpress |
| world |
| yanshi |
+--------------------+
13 rows in set (0.00 sec)
可以看出上面的庫已經(jīng)同步過來了，
3，GTID復(fù)制
介紹
GTID(Global Transaction ID)是對于一個已提交事務(wù)的唯一編號，并且是一個全局(主從復(fù)制)唯一的編號。
它的官方定義如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
什么是sever_uuid，和Server-id 區(qū)別？
核心特性: 全局唯一,具備冪等性
準備環(huán)境
三臺機器一主兩從
主庫：
10.0.0.51
從庫
10.0.0.52
10.0.0.53
(1)清理環(huán)境
pkill mysqld
創(chuàng)建目錄：
mkdir -p /data/mysql/data
mkdir -p /data/binlog
mkdir -p /application
上傳軟件到到application
(2) 準備配置文件
主庫db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF
slave1(db02)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF
slave2(db03)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF
(3) 初始化數(shù)據(jù)
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
(4) 啟動數(shù)據(jù)庫
cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld
/etc/init.d/mysqld start
(5) 構(gòu)建主從：
master:51
slave:52,53
主庫：
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
從庫:
change master to
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;
show slave status\G
GTID核心參數(shù)
gtid-mode=on --啟用gtid類型，否則就是普通的復(fù)制架構(gòu)
enforce-gtid-consistency=true --強制GTID的一致性
log-slave-updates=1 --slave更新是否記入日志
GTID 復(fù)制和普通復(fù)制的區(qū)別
普通：
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
GTID復(fù)制：
change master to
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;
（0）在主從復(fù)制環(huán)境中，主庫發(fā)生過的事務(wù)，在全局都是由唯一GTID記錄的，更方便Failover
（1）額外功能參數(shù)（3個）
（2）change master to 的時候不再需要binlog 文件名和position號,MASTER_AUTO_POSITION=1;
（3）在復(fù)制過程中，從庫不再依賴master.info文件，而是直接讀取最后一個relaylog的 GTID號
（4） mysqldump備份時，默認會將備份中包含的事務(wù)操作，以以下方式
SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1-11';
告訴從庫，我的備份中已經(jīng)有以上事務(wù)，你就不用運行了，直接從下一個GTID開始請求binlog就行
4，半同步復(fù)制
介紹
解決主從數(shù)據(jù)一致性問題
半同步工作原理
1. 主庫執(zhí)行新的事務(wù),commit時,更新 show master status\G ,觸發(fā)一個信號給
2. binlog dump 接收到主庫的 show master status\G信息,通知從庫日志更新了
3. 從庫IO線程請求新的二進制日志事件
4. 主庫會通過dump線程傳送新的日志事件,給從庫IO線程
5. 從庫IO線程接收到binlog日志,當(dāng)日志寫入到磁盤上的relaylog文件時,給主庫ACK_receiver線程
6. ACK_receiver線程觸發(fā)一個事件,告訴主庫commit可以成功了
7. 如果ACK達到了我們預(yù)設(shè)值的超時時間,半同步復(fù)制會切換為原始的異步復(fù)制.
配置
加載插件
主:
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';
