如果您對數(shù)據(jù)庫感興趣，可以添加 DBA解決方案QQ群：895979329
1. 延時從庫
1.1介紹
是我們認(rèn)為配置的一種特殊從庫.人為配置從庫和主庫延時N小時.
1.2 為什么要有延時從
數(shù)據(jù)庫故障?
物理損壞
主從復(fù)制非常擅長解決物理損壞.
邏輯損壞
普通主從復(fù)制沒辦法解決邏輯損壞
1.3 配置延時從庫
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
SQL_Remaining_Delay: NULL
1.4 延時從庫應(yīng)用
1.4.1 故障恢復(fù)思路
1主1從,從庫延時5分鐘,主庫誤刪除1個庫
1. 5分鐘之內(nèi) 偵測到誤刪除操作
2. 停從庫SQL線程
3. 截取relaylog
起點 :停止SQL線程時,relay最后應(yīng)用位置
終點:誤刪除之前的position(GTID)
4. 恢復(fù)截取的日志到從庫
5. 從庫身份解除,替代主庫工作
1.4.2 故障模擬及恢復(fù)
1.主庫數(shù)據(jù)操作
db01 [(none)]>create database relay charset utf8;
db01 [(none)]>use relay
db01 [relay]>create table t1 (id int);
db01 [relay]>insert into t1 values(1);
db01 [relay]>drop database relay;
2. 停止從庫SQL線程
stop slave sql_thread;
3. 找relaylog的截取起點和終點
起點:
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 482
終點:
show relaylog events in 'db01-relay-bin.000002'
| db01-relay-bin.000002 | 1046 | Xid | 7 | 2489 | COMMIT /* xid=144 */ |
| db01-relay-bin.000002 | 1077 | Anonymous_Gtid | 7 | 2554 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
mysqlbinlog --start-position=482 --stop-position=1077 /data/3308/data/db01-relay-bin.000002>/tmp/relay.sql
- 從庫恢復(fù)relaylog
source /tmp/relay.sql
5.從庫身份解除
db01 [relay]>stop slave;
db01 [relay]>reset slave all
2. 半同步 ***
解決主從數(shù)據(jù)一致性問題
2.1 半同步復(fù)制工作原理的變化
1. 主庫執(zhí)行新的事務(wù),commit時,更新 show master status\G ,觸發(fā)一個信號給
2. binlog dump 接收到主庫的 show master status\G信息,通知從庫日志更新了
3. 從庫IO線程請求新的二進(jìn)制日志事件
4. 主庫會通過dump線程傳送新的日志事件,給從庫IO線程
5. 從庫IO線程接收到binlog日志,當(dāng)日志寫入到磁盤上的relaylog文件時,給主庫ACK_receiver線程
6. ACK_receiver線程觸發(fā)一個事件,告訴主庫commit可以成功了
7. 如果ACK達(dá)到了我們預(yù)設(shè)值的超時時間,半同步復(fù)制會切換為原始的異步復(fù)制.
2.2 配置半同步復(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';
3 . 過濾復(fù)制
3.1 說明
主庫：
show master status;
Binlog_Do_DB
Binlog_Ignore_DB
從庫：
show slave status\G
Replicate_Do_DB:
Replicate_Ignore_DB:
3.2 實現(xiàn)過程
mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction -R --triggers >/backup/full.sql
vim /backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
[root@db01 ~]# mysql -S /data/3309/mysql.sock
source /backup/full.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
start slave;
[root@db01 ~]# vim /data/3309/my.cnf
replicate_do_db=ppt
replicate_do_db=word
[root@db01 ~]# systemctl restart mysqld3309
主庫：
Master [(none)]>create database word;
Query OK, 1 row affected (0.00 sec)
Master [(none)]>create database ppt;
Query OK, 1 row affected (0.00 sec)
Master [(none)]>create database excel;
Query OK, 1 row affected (0.01 sec)
4. GTID復(fù)制
4.1 GTID引入
4.2 GTID介紹
GTID(Global Transaction ID)是對于一個已提交事務(wù)的唯一編號，并且是一個全局(主從復(fù)制)唯一的編號希痴。
它的官方定義如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
什么是sever_uuid，和Server-id 區(qū)別裙戏？
核心特性: 全局唯一,具備冪等性
4.3 GTID核心參數(shù)
重要參數(shù)：
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
gtid-mode=on --啟用gtid類型缴川，否則就是普通的復(fù)制架構(gòu)
enforce-gtid-consistency=true --強制GTID的一致性
log-slave-updates=1 --slave更新是否記入日志
4.4 GTID復(fù)制配置過程：
4.4.1 清理環(huán)境
pkill mysqld
\rm -rf /data/mysql/data/*
\rm -rf /data/binlog/*
4.4.2 準(zhǔn)備配置文件
主庫db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/data/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=/data/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=/data/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
4.4.3 初始化數(shù)據(jù)
mysqld --initialize-insecure --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data
4.4.4 啟動數(shù)據(jù)庫
/etc/init.d/mysqld start
4.4.5 構(gòu)建主從：
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;
4.5 GTID 從庫誤寫入操作處理
查看監(jiān)控信息:
Last_SQL_Error: Error 'Can't create database 'oldboy'; database exists' on query. Default database: 'oldboy'. Query: 'create database oldboy'
Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3
Executed_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,
7ca4a2b7-4aae-11e9-859d-000c298720f6:1
注入空事物的方法：
stop slave;
set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3';
begin;commit;
set gtid_next='AUTOMATIC';
這里的xxxxx:N 也就是你的slave sql thread報錯的GTID，或者說是你想要跳過的GTID额湘。
最好的解決方案：重新構(gòu)建主從環(huán)境
4.6 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;
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備份時毯焕，默認(rèn)會將備份中包含的事務(wù)操作衍腥，以以下方式
SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1';
告訴從庫，我的備份中已經(jīng)有以上事務(wù)纳猫，你就不用運行了紧阔，直接從下一個GTID開始請求binlog就行。
