day10-01-延时从库及过滤复制
1 上节回顾
1.1 binlog_format
SBR 记录单条语句，日志量小，但日志不准确
RBR 修改数据，逐条记录，日质量大。
MBR
1.2 主从故障
IO：
连主库
取日志
存：取到的binlog存到relay-log里
SQL：
回放
1.3 主从延时
主库：
DUMP_T
mysql> show variables like '%group_commit%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
+-----------------------------------------+-------+
2 rows in set (0.01 sec)
mysql>
sync_binlog=1
gtid mode = on
从库：
SQL： 5.7版本 ，GTID，MTS 多线程同步
1.4 主从延时的监控
1）有没有延时的问题？
Seconds_Behind_Master: 0
2）有没有主库原因？
show master status\G
show slave status \G
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1391
Exec_Master_Log_Pos: 1391
Relay_Log_Space: 1646
3）有没有及时回放
[root@mysql-node01 data]# cat relay-log.info
7
./mysql-node01-relay-bin.000003
320
mysql-bin.000004
1391
0
0
1
[root@mysql-node01 data]#
主从复制高级进阶
1.延时从库 *****
为什么要有延时从库
数据库故障?
物理损坏
主从复制非常擅长解决物理损坏.
逻辑损坏
普通主从复制没办法解决逻辑损坏
延时从库，就是控制SQL_THREAD线程，执行relay-log
配置延时从库
SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行
一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间
MASTER_DELAY = 300（秒）
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.2 延时从库处理逻辑故障
1.2.1 延时从库的恢复思路
1）监控到数据库逻辑故障
2）停从库SQL线程，并记录已经回放的位置点(截取日志的起点)
stop slave sql_thread;
show slave status \G
Relay_Log_File: mysql-node01-relay-bin.000002
Relay_Log_Pos: 320
3）截取relaylog
起点：Relay_Log_File，Relay_Log_Pos
终点：故障（drop）之前的position（GTID）
show relaylog events in 'Relay_Log_File';
进行截取
4）模拟SQL线程回放日志
从库：source
5）恢复业务
情况一：就一个库的话，从库替代主库工作
情况二：
从库导出故障库，还原到主库中。
1.2.2 故障演练
主库：
create database delay charset utf8mb4;
use delay;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
drop database delay;
从库：
1.停止SQL_THREAD,获取relay的起点位置
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 2159
Relay_Log_File: mysql-node01-relay-bin.000002
Relay_Log_Pos: 320
Slave_IO_Running: Yes
Slave_SQL_Running: No
Exec_Master_Log_Pos: 1391
SQL_Delay: 300
SQL_Remaining_Delay: NULL
Relay_Log_Pos: 320 起点位置 是 320
2.找到relaylog的截取终点：
mysql> show relaylog events in 'mysql-node01-relay-bin.000002' limit 50;
+-------------------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-node01-relay-bin.000002 | 4 | Format_desc | 3309 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-node01-relay-bin.000002 | 123 | Previous_gtids | 3309 | 154 | |
| mysql-node01-relay-bin.000002 | 154 | Rotate | 3308 | 0 | mysql-bin.000004;pos=1391 |
| mysql-node01-relay-bin.000002 | 201 | Format_desc | 3308 | 0 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-node01-relay-bin.000002 | 320 | Anonymous_Gtid | 3308 | 1456 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-node01-relay-bin.000002 | 385 | Query | 3308 | 1569 | create database delay charset utf8mb4 |
| mysql-node01-relay-bin.000002 | 498 | Anonymous_Gtid | 3308 | 1634 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-node01-relay-bin.000002 | 563 | Query | 3308 | 1734 | use `delay`; create table t1 (id int) |
| mysql-node01-relay-bin.000002 | 663 | Anonymous_Gtid | 3308 | 1799 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-node01-relay-bin.000002 | 728 | Query | 3308 | 1872 | BEGIN |
| mysql-node01-relay-bin.000002 | 801 | Table_map | 3308 | 1918 | table_id: 142 (delay.t1) |
| mysql-node01-relay-bin.000002 | 847 | Write_rows | 3308 | 1968 | table_id: 142 flags: STMT_END_F |
| mysql-node01-relay-bin.000002 | 897 | Xid | 3308 | 1999 | COMMIT /* xid=514 */ |
| mysql-node01-relay-bin.000002 | 928 | Anonymous_Gtid | 3308 | 2064 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-node01-relay-bin.000002 | 993 | Query | 3308 | 2159 | drop database delay |
+-------------------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
mysql>
看到 drop database delay 事件 pos起点是 993，也就是说，993 是上一个事件的终点，那么截取终点是993
截取日志：
[root@mysql-node01 data]# mysqlbinlog --start-position=320 --stop-position=993 ./mysql-node01-relay-bin.000002 > /tmp/delay.sql
[root@mysql-node01 data]#
4.恢复到从库
mysql>
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> source /tmp/delay.sql;
mysql>
mysql>
mysql> select * from delay.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql>
然后可以按需把数据导出恢复到主库，或者切换主从，从库对外提供业务服务，主库offline
快速恢复测试环境：
从库：
drop database delay;
stop slave;
reset slave all;
主库：
reset master;
从库：
CHANGE MASTER TO
MASTER_HOST='10.0.50.61',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_PORT=3308,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
start slave;
2.过滤复制 *****
主库：
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB: #记录binlog白名单，只记录指定数据库
Binlog_Ignore_DB: #记录binlog黑名单，不记录指定数据库
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql>
vi /etc/my.cnf
[mysqld]
binlog_do_db = db_name
binlog_ignore_db = db_name
# 一般情况，只使用其中一个参数，使用白名单或者黑名单，生产很少用。
从库：
show slave status \G
# 库及白/黑名单
Replicate_Do_DB:
Replicate_Ignore_DB:
# 表及白/黑名单
Replicate_Do_Table:
Replicate_Ignore_Table:
# 模糊匹配
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
# 写到从库配置文件
vi /etc/my.cnf
[mysqld]
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
replicate_wild_do_table=
replicate_wild_ignore_table=
# 用哪个写哪个
# 也可以在change master to 指定
vi /etc/my.cnf
[mysqld]
replicate_do_db=repl
[root@mysql-node01 ~]# systemctl restart mysqld3309
[root@mysql-node01 ~]# ss -lntp|grep 3309
LISTEN 0 80 [::]:3309 [::]:* users:(("mysqld",pid=1103,fd=29))
[root@mysql-node01 ~]#
浙公网安备 33010602011771号