一、概述
1.简介
实时的将一个节点的binlog，传输到另一个结点并快速恢复。
2.作用
- 辅助备份
- 架构演变
二、主从复制部署
1.主从复制准备环境
- 至少2个及以上的MySQL实例，并定义主从角色(server_id)
- 主库开启二进制日志
- 主库需要开启专门的复制用户
- 从库提前进行数据补偿
- 告知从库其复制主库的相关信息(ip,port,user,password,复制的起点[change master to])
- 从库启动专用线程
2.主从复制搭建过程
step1 准备多个数据库节点
多实例搭建流程
详见第三章第五节
[root@db01 ~]# systemctl start mysqld3308.service
[root@db01 ~]# ps -ef |grep mysqld
mysql 37560 1 1 11:24 ? 00:00:00 /application/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf
mysql 37594 1 1 11:24 ? 00:00:00 /application/mysql/bin/mysqld --defaults-file=/data/mysql/3308/my.cnf
root 37626 36582 0 11:25 pts/3 00:00:00 grep --color=auto mysqldstep2 主库创建专用复制用户
| mysql -u用户 -p密码 -S socket文件 -e "grant replication slave on *.* to 用户@'白名单' identified by '密码'" |
[root@db01 ~]# mysql -S /data/mysql/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '456';"
[root@db01 ~]# mysql -S /data/mysql/3307/mysql.sock -e "select user,host from mysql.user;"
+---------------+-----------+
| user | host |
+---------------+-----------+
| repl | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+step3 主库开启二进制日志
[root@db01 ~]# vim /data/mysql/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/mysql.sock
server_id=7
port=3307
log_error=/data/mysql/3307/data/mysql.log
log_bin=/data/mysql/3307/mysql-bin
binlog_format=row
[root@db01 ~]# systemctl restart mysqld3307step4 备份主库数据，恢复到从库
[root@db01 ~]# mysqldump -S /data/mysql/3307/mysql.sock -A -R -E --triggers --master-data=2 --single-transaction >/tmp/master_DB_bak.sql
[root@db01 ~]# head -22 /tmp/master_DB_bak.sql |tail -1
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=154;
[root@db01 ~]# mysql -S /data/mysql/3307/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock </tmp/master_DB_bak.sql
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+step5 告知从库主库相关信息
| change master to |
|
CHANGE MASTER TO MASTER_HOST='主库地址', MASTER_USER='主从复制用户', MASTER_PASSWORD='用户登录密码', MASTER_PORT=端口号, MASTER_LOG_FILE='所请求的二进制文件', MASTER_LOG_POS=起始位置, MASTER_CONNECT_RETRY=失败重试次数; |
mysql> change master to
-> MASTER_HOST='10.0.0.151',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='456',
-> MASTER_PORT=3307,
-> MASTER_LOG_FILE='mysql-bin.000005',
-> MASTER_LOG_POS=154,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.01 sec)step6 启动专用线程
| start slave |
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)step7 验证
| show slave status |
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G" |grep 'Running'
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates三、主从复制原理
1.相关文件
主库
- binlog(二进制日志)
从库
- relaylog(中继日志)：保存主库同步到从库的二进制日志文件
[root@db01 ~]# mysqlbinlog /data/mysql/3308/data/db01-relay-bin.000001 |head -5
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190902 12:51:15 server id 8 end_log_pos 123 CRC32 0x36697afc Start: binlog v 4, server v 5.7.26-log created 190902 12:51:15 at startup- Master.info：保存主库相关信息
[root@db01 ~]# head /data/mysql/3308/data/master.info
25
mysql-bin.000005
154
10.0.0.151
repl
456
3307
10
0- relaylog.info：保存relaylog的相关信息(记录relaylog的回放过的位置点)
[root@db01 ~]# cat /data/mysql/3308/data/relay-log.info
7
./db01-relay-bin.000002
320
mysql-bin.000005
154
0
0
12.相关线程
主库
- binlog dump thread(二进制日志投递线程)
| show processlist #查看数据库现有进程 |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 7 | system user | | NULL | Connect | 568 | Waiting for master to send event | NULL |
| 8 | system user | | NULL | Connect | 568 | Slave has read all relay log; waiting for more updates | NULL |
| 12 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)从库
- I/O线程
- SQL进程
3.工作原理
step1
从库执行change master to时，将主库的IP PORT USER PASSWORD BINLOG POSITION记录到master info文件中；
step2
从库执行start slaves时，会启动自身的slave_IO线程和slave_SQL线程
step3
slave_IO线程读取master info信息，获取主库信息并连接主库
step4
主库启动BINLOG DUMP线程，相应从库请求
step5
slave_IO线程根据master.info记录的BINLOG文件名与POSITION号，向主库DUMP线程请求最新日志
step6
DUMP线程检查主库的binlog日志，如果存在更新，将日志发送给从库的I/O线程
step7
slave_IO线程将收到的日志保存于了TCP/IP 缓存，并立即返回TCP ACK报文给主库；此时主库工作完成
step8
slave_IO线程将缓存中的数据，存储到relay-log日志文件，更新master.info文件BINLOG 文件名和POSITION；slave_IO线程工作完成
step9
slave_SQL线程读取relay-log.info文件，获取已执行的relay-log的位置，并以此作为起点，回放relay-log；
step10
slave_SQL线程完成日志回放之后，更新relay-log.info文件。
step11
relay-log会有自动清理的功能
附：主库一旦生成新的日志，会主动向BINLOG DUMP线程发送“信号”，通知从库slave_IO线程请求日志
| 主从复制在数据库启动之时自动重构，不需手动重启； |
4. 从库状态信息
|
从库当前I/O线程请求状态 Slave_IO_State: Waiting for master to send event |
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G"|grep 'IO_State'
Slave_IO_State: Waiting for master to send event|
主库相关信息(Master.info) Master_Host: 主库IP地址 Master_User: 主库主从复制用户 Master_Port: 主库端口 Connect_Retry: 时间 #重试时间(单位:s) Master_Log_File: 当前请求的主库二进制日志 Read_Master_Log_Pos: 主从复制起点位置 |
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G;"|grep -E '(^[ ]+Master_(Host|User|Port|Log))|(Connect_Retry)|(Read_Master_Log_Pos)'
Master_Host: 10.0.0.151
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 154|
从库中继日志信息(relay.info) Relay_Log_File: 从库中继日志 Relay_Log_Pos: SQL线程当前回放位置 Relay_Master_Log_File: 中继日志对应的二进制日志 Exec_Master_Log_Pos: 中继日志已执行位置 |
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G;"|grep -E '(^[ ]+Relay_)|Exec_Master'|head -4
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000005
Exec_Master_Log_Pos: 154|
从库的线程状态以及异常信息提示 Slave_IO_Running: Yes #正常状态为Yes; Slave_SQL_Running: Yes #正常状态为Yes; Last_IO_Errno: I/O线程错误号 Last_IO_Error: I/O线程错误提示信息 Last_SQL_Errno: SQL线程错误号 Last_SQL_Error: I/O线程错提示信息> |
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G;"|grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error: |
过滤复制相关信息 Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: |
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G;"|grep -E '^[ ]+Replicate_'|head -6
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: |
主从延时判定 Seconds_Behind_Master: 0 |
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G;"|grep 'Seconds_Behind'
Seconds_Behind_Master: 0 |
延时从库相关信息 SQL_Delay: 0 SQL_Remaining_Delay: NULL |
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G;"|grep -E '^[ ]+SQL'
SQL_Delay: 0
SQL_Remaining_Delay: NULL|
GTID复制信息 Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 |
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G;"|grep -E 'Gtid|Auto'
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G;"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.151
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 154
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 526
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 7
Master_UUID: 6ca5e638-bfcc-11e9-a36e-000c2938d306
Master_Info_File: /data/mysql/3308/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:三、主从复制维护
1.故障监控(手动)
|
mysql -u用户名 -p密码 -S socket文件 -e "show slave status \G" |grep Running: #监控从库I/O和SQL线程状态信息 mysql -u用户名 -p密码 -S socket文件 -e "show slave status \G" |grep Last |head -6 |tail -4 |
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G"|grep 'Running:'
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G"|grep 'Last'| head -6| tail -4
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G;"|grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:2.故障原因
I/O线程故障
解决思路：重建主从关系；
|
I/O线程主要工作：
连接主库
请求日志
接收日志
写入日志
|
- 连接主库相关故障
连接信息错误
网络故障
主库宕机
防火墙阻挡
主库连接数超过上限(set global max_connections)
| 排除方法：手工连接主库 |
- 请求日志相关故障
主库二进制日志完整性错误
|
reset slave all #重置master.info信息(需先停止从库I/O和SQL线程后) show master status #查看主库状态 |
#模拟故障
[root@db01 ~]# mysql -S /data/mysql/3307/mysql.sock -e "show master status\G;"
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
[root@db01 ~]# mysql -S /data/mysql/3307/mysql.sock -e "reset master"
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G"| grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000005' at 154, the last event read from '/data/mysql/3307/mysql-bin.000005' at 123, the last byte read from '/data/mysql/3307/mysql-bin.000005' at 154.'
Last_SQL_Errno: 0
Last_SQL_Error:#模拟恢复
mysql> stop slave;
Query OK, 0 rows affected (0.41 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.28 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.151',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='456',
-> MASTER_PORT=3307,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.14 sec)
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G"| grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error: -
接收日志相关故障
I/O线程故障 -
写入日志相关故障
relay-log损坏
SQL线程故障
| SQL线程主要工作：回放relay-log |
- 回放relay-log相关故障
relay-log损坏
回放SQL语句失败(创建的对象已存在，修改或删除的对象不存在，约束条件冲突)
| SQL层主要工作：语法检查，SQL_mode检查，语义检查，语句解析，语句优化，语句执行 |
回放SQL失败的主要原因为主从数据不一致；
|
主从数据不一致原因
从库人为写入数据(解决方法：设置只读从库或使用中间件)
主从本身原因造成数据不一致
|
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show variables like '%read_only%'"
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+|
设置只读从库 read_only super_read_only |
主从数据辅助工具
- pt-table-checksum 主从数据校验工具
- pt-table-sync 主从数据修复工具
四、主从延时
1.状态信息
Seconds_Behind_Master
2.延时因素
主库原因
- 二进制日志书写不及时
| 解决方法：sync_binlog 默认值为1；事务提交立即刷写日志。 |
- DUMP线程串行传输问题
| 解决方法：GTID |
从库原因
- SQL线程串行问题
| 解决方法：GTID和逻辑时钟(Logical_clock) |
大事务原因
| 解决方法：定位日志点，查看主库二进制日志； |
五、延时从库
1.概述
定义：延时从库是我们人为定制的一种的从库角色，与主从延时无关。
作用：数据库逻辑损坏时，可快速恢复数据。
2.配置
step 0 当前数据库状态
[root@db01 ~]# mysql -S /data/mysql/3309/mysql.sock -e "show slave status\G"| grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
[root@db01 ~]# mysql -S /data/mysql/3309/mysql.sock -e "show slave status\G;"|grep -E '^[ ]+SQL'
SQL_Delay: 0
SQL_Remaining_Delay: NULLstep 1 停止主从关系
| stop slave; |
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3309 |
+--------+
1 row in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)step 2 设置延时时间(单位：秒)
| change master to MASTER_DELAY = n |
| 实际生产环境中一般延时3~6个小时； |
mysql> change master to MASTER_DELAY = 1800;
Query OK, 0 rows affected (0.00 sec)step 3 开启主从关系
| start slave |
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)step 4 验证延时从库
| mysql -u用户名 -p密码 -S socket文件 -e "show slave status"|grep -E '^[ ]+SQL_' |
[root@db01 ~]# mysql -S /data/mysql/3309/mysql.sock -e "show slave status\G;"|grep -E '^[ ]+SQL'
SQL_Delay: 1800
SQL_Remaining_Delay: NULL
[root@db01 ~]# mysql -S /data/mysql/3309/mysql.sock -e "show slave status\G"| grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:3.模拟演练
|
恢复思路 1.停止从库SQL线程(stop slave sql_thread;) 2.上线维护页面 3.截取从库relaylog日志(起点：SQL线程停止时relay-log位置点；终点：误操作点)/将日志中误操作剔除； 4.恢复日志到从库 |
step 0 数据模拟
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3307 |
+--------+
1 row in set (0.00 sec)
mysql> create database delay_db charset=utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> use delay_db;
Database changed
mysql> create table t1(id int primary key auto_increment, k1 char(5) not null default 'NA' )engine=innodb,charset=utf8mb4;
Query OK, 0 rows affected (0.37 sec)
mysql> insert into t1(k1) values('aa'),('bb'),('cc');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 for update;
+----+----+
| id | k1 |
+----+----+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+----+----+
3 rows in set (0.00 sec)
mysql> drop database delay_db;
Query OK, 1 row affected (0.04 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)step 1 停止从库SQL线程
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3309 |
+--------+
1 row in set (0.00 sec)
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
[root@db01 /data/mysql/3309/data]# mysql -S /data/mysql/3309/mysql.sock -e "show slave status\G;"|grep -E '^[ ]+SQL'
SQL_Delay: 1800
SQL_Remaining_Delay: NULL
[root@db01 /data/mysql/3309/data]# mysql -S /data/mysql/3309/mysql.sock -e "show slave status\G"| grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:step 2 截取relay-log日志
| show relaylog events in '中继日志' |
[root@db01 /data/mysql/3309/data]# mysql -S /data/mysql/3309/mysql.sock -e "show slave status\G"| grep -E 'Relay_Log'|head -2
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 1051
[root@db01 /data/mysql/3309/data]# mysql -S /data/mysql/3309/mysql.sock -e "show relaylog events in 'db01-relay-bin.000002';"
+-----------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| db01-relay-bin.000002 | 4 | Format_desc | 9 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| db01-relay-bin.000002 | 123 | Previous_gtids | 9 | 154 | |
| db01-relay-bin.000002 | 154 | Rotate | 7 | 0 | mysql-bin.000002;pos=154 |
| db01-relay-bin.000002 | 201 | Format_desc | 7 | 0 | Server ver: 5.7.26-log, Binlog ver: 4 |
| db01-relay-bin.000002 | 320 | Anonymous_Gtid | 7 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000002 | 385 | Query | 7 | 341 | create database delay_db charset=utf8mb4 |
| db01-relay-bin.000002 | 507 | Anonymous_Gtid | 7 | 406 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000002 | 572 | Query | 7 | 602 | use `delay_db`; create table t1(id int primary key auto_increment, k1 char(5) not null default 'NA' )engine=innodb,charset=utf8mb4 |
| db01-relay-bin.000002 | 768 | Anonymous_Gtid | 7 | 667 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000002 | 833 | Query | 7 | 743 | BEGIN |
| db01-relay-bin.000002 | 909 | Table_map | 7 | 795 | table_id: 222 (delay_db.t1) |
| db01-relay-bin.000002 | 961 | Write_rows | 7 | 854 | table_id: 222 flags: STMT_END_F |
| db01-relay-bin.000002 | 1020 | Xid | 7 | 885 | COMMIT /* xid=2092 */ |
| db01-relay-bin.000002 | 1051 | Anonymous_Gtid | 7 | 950 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000002 | 1116 | Query | 7 | 1054 | drop database delay_db |
+-----------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------+
[root@db01 /data/mysql/3309/data]# mysqlbinlog --start-position=1051 --stop-position=1116 db01-relay-bin.000002 >/tmp/recover.sql;
[root@db01 /data/mysql/3309/data]# ls /tmp/recover.sql -l
-rw-r--r-- 1 root root 1139 Sep 6 13:14 /tmp/recover.sqlstep 3 恢复日志
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3309 |
+--------+
1 row in set (0.00 sec)
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/recover.sql;
......
Query OK, 0 rows affected (0.00 sec)
mysql> use delay_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t1;
+----+----+
| id | k1 |
+----+----+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+----+----+
3 rows in set (0.00 sec)[root@db01 /data/mysql/3309/data]# mysqldump -S /data/mysql/3309/mysql.sock -B delay_db -E -R --triggers --master-data=2 --single-transaction >/tmp/3309.sql
[root@db01 /data/mysql/3309/data]# ls /tmp/3309.sql -l
-rw-r--r-- 1 root root 2293 Sep 6 13:23 /tmp/3309.sql
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3307 |
+--------+
1 row in set (0.00 sec)
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/3309.sql;
......
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| delay_db |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> use delay_db;
Database changed
mysql> select * from t1;
+----+----+
| id | k1 |
+----+----+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+----+----+
3 rows in set (0.00 sec)六、过滤复制
| 通常白名单和黑名单仅使用一个； |
1、主库过滤(实际环境中基本不使用)
- Binlog_Do_DB 记录日志白名单
- Binlog_Ignore_DB 记录日志黑名单
| show master status; |
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1054 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)2、从库过滤
| 从库会接收主库的全部日志，但只会回放白名单内的相关库和表的日志； |
|
基于库的过滤 replicate_do_db=库1 replicate_ignore_db=库2 基于表的精确过滤 replicate_do_table=库.表1 replicate_ignore_table=库.表2 基于表的模糊过滤 replicate_wild_do_table=库.表1 replicate_wild_ignore_table=库.表2 |
[root@db01 /data/mysql/3309/data]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G;"|grep -E '^[ ]+Replicate_'|head -6
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: | 验证 |
[root@db01 /data/mysql/3309/data]# vim /data/mysql/3309/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3309/data
socket=/data/mysql/3309/mysql.sock
server_id=9
port=3309
log_error=/data/mysql/3309/data/mysql.log
log_bin=/data/mysql/3309/mysql-bin
replicate_do_db=aspen
[root@db01 /data/mysql/3309/data]# systemctl restart mysqld3309.service mysql> select @@port;
+--------+
| @@port |
+--------+
| 3307 |
+--------+
1 row in set (0.01 sec)
mysql> create database aspen;
Query OK, 1 row affected (0.00 sec)
mysql> create database stu;
Query OK, 1 row affected (0.00 sec)
mysql> create database aspen_han;
Query OK, 1 row affected (0.00 sec)mysql> select @@port;
+--------+
| @@port |
+--------+
| 3309 |
+--------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aspen |
| delay_db |
| mysql |
| performance_schema |
| relay_db |
| sys |
| test |
+--------------------+
8 rows in set (0.00 sec)
mysql> show relaylog events in 'db01-relay-bin.000006';
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| db01-relay-bin.000006 | 4 | Format_desc | 9 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| db01-relay-bin.000006 | 123 | Previous_gtids | 9 | 154 | |
| db01-relay-bin.000006 | 154 | Rotate | 7 | 0 | mysql-bin.000001;pos=1106 |
| db01-relay-bin.000006 | 201 | Format_desc | 7 | 0 | Server ver: 5.7.26-log, Binlog ver: 4 |
| db01-relay-bin.000006 | 320 | Anonymous_Gtid | 7 | 1171 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000006 | 385 | Query | 7 | 1268 | create database aspen |
| db01-relay-bin.000006 | 482 | Anonymous_Gtid | 7 | 1333 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000006 | 547 | Query | 7 | 1424 | create database stu |
| db01-relay-bin.000006 | 638 | Anonymous_Gtid | 7 | 1489 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000006 | 703 | Query | 7 | 1598 | create database aspen_han |
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
10 rows in set (0.00 sec)附：思维导图
