MySQL学习笔记延时从库
介绍
是我们人为配置的一种特殊从库，人为配置从库和主库延时N小时
为什么要有延时
主从复制非常擅长解决数据库物理损坏
SQL线程延时：数据已经写入relaylog中了，SQL线程‘慢点’执行
配置
db02 [(none)]>stop slave;
Query OK, 0 rows affected (0.00 sec)
db02 [(none)]>change master to
-> master_delay=300; 设置延时300秒
Query OK, 0 rows affected (0.01 sec)
db02 [(none)]>start slave;
Query OK, 0 rows affected (0.01 sec)
db02 [(none)]>show slave status \G 检查
*************************** 1. row ***************************
SQL_Delay: 300
SQL_Remaining_Delay: NULL
五分钟内侦测到误操作
1.停掉从库stop slave
2.截取relaylog，恢复到从库
3.从库替代主库工作
两库情况
db01 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
5 rows in set (0.00 sec)
db02 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
5 rows in set (0.00 sec)
主库产生数据并删除，利用延时从库恢复数据
db01 [(none)]>create database test charset utf8;
Query OK, 1 row affected (0.01 sec)
db01 [(none)]>use test
Database changed
db01 [test]>create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)
db01 [test]>insert into t1 values(1);
Query OK, 1 row affected (0.03 sec)
db01 [test]>drop database test;
Query OK, 1 row affected (0.01 sec)
检测到错误操作，停掉主从
db02 [(none)]>stop slave;
Query OK, 0 rows affected (0.01 sec)
截取relaylog
起点
db02 [(none)]>show slave status \G
*************************** 1. row ***************************
Master_Log_File: mysql-bin.000016
Read_Master_Log_Pos: 1696
Relay_Log_File: db02-relay-bin.000006
Relay_Log_Pos: 360 360是从库当前状态的position号，如果要恢复，以当前状态的position号当起点
终点
db02 [(none)]>db02 [(none)]>show relaylog events in 'db02-relay-bin.000006';
+-----------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| db02-relay-bin.000006 | 4 | Format_desc | 7 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| db02-relay-bin.000006 | 123 | Previous_gtids | 7 | 194 | 8d397b30-8791-11e9-acbd-000c2902fc28:55-62 |
| db02-relay-bin.000006 | 194 | Rotate | 6 | 0 | mysql-bin.000016;pos=950 |
| db02-relay-bin.000006 | 241 | Format_desc | 6 | 0 | Server ver: 5.7.20-log, Binlog ver: 4 |
| db02-relay-bin.000006 | 360 | Gtid | 6 | 1015 | SET @@SESSION.GTID_NEXT= '8d397b30-8791-11e9-acbd-000c2902fc28:63' |
| db02-relay-bin.000006 | 425 | Query | 6 | 1123 | create database test charset utf8 |
| db02-relay-bin.000006 | 533 | Gtid | 6 | 1188 | SET @@SESSION.GTID_NEXT= '8d397b30-8791-11e9-acbd-000c2902fc28:64' |
| db02-relay-bin.000006 | 598 | Query | 6 | 1286 | use `test`; create table t1 (id int) |
| db02-relay-bin.000006 | 696 | Gtid | 6 | 1351 | SET @@SESSION.GTID_NEXT= '8d397b30-8791-11e9-acbd-000c2902fc28:65' |
| db02-relay-bin.000006 | 761 | Query | 6 | 1423 | BEGIN |
| db02-relay-bin.000006 | 833 | Table_map | 6 | 1468 | table_id: 224 (test.t1) |
| db02-relay-bin.000006 | 878 | Write_rows | 6 | 1508 | table_id: 224 flags: STMT_END_F |
| db02-relay-bin.000006 | 918 | Xid | 6 | 1539 | COMMIT /* xid=44 */ |
| db02-relay-bin.000006 | 949 | Gtid | 6 | 1604 | SET @@SESSION.GTID_NEXT= '8d397b30-8791-11e9-acbd-000c2902fc28:66' |
| db02-relay-bin.000006 | 1014 | Query | 6 | 1696 | drop database test |
+-----------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
15 rows in set (0.00 sec)
1014是relay-log中记录的主库最新的日志，所以误删除之前的position就是截取的终点
[root@db02 ~]# mysqlbinlog --start-position=360 --stop-position=1014 /data/mysql/db02-relay-bin.000006>/tmp/relay.sql
进行恢复
db02 [(none)]>source /tmp/relay.sql
db02 [test]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| world |
+--------------------+
6 rows in set (0.00 sec)
db02 [test]>select * from t1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
恢复成功，必要时从库恢复完成后顶替主库工作
复制过滤
从库有针对的复制主库
主库参数
Binlog_Do_DB
Binlog_Ignore_DB
从库
库级别：
Replicate_Do_DB:
Replicate_Ignore_DB:
表级别：
Replicate_Do_Table:
Replicate_Ignore_Table:
模糊级别：
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
一般库级别就够用了
配置
[root@db02 ~]# vim /etc/my.cnf
replicate_do_db=aaa
replicate_do_db=bbb
只复制aaa库和bbb库
[root@db02 ~]# systemctl restart mysqld.service
db02 [(none)]>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: aaaa
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000016
Read_Master_Log_Pos: 2005
Relay_Log_File: db02-relay-bin.000004
Relay_Log_Pos: 360
Relay_Master_Log_File: mysql-bin.000016
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: aaa,bbb
测试
主库创建aaa,bbb,ccc,ddd库，aaa库中创建t1表
db01 [(none)]>create database aaa;
Query OK, 1 row affected (0.00 sec)
db01 [(none)]>create database bbb;
Query OK, 1 row affected (0.00 sec)
db01 [(none)]>create database ccc;
Query OK, 1 row affected (0.00 sec)
db01 [(none)]>create database ddd;
Query OK, 1 row affected (0.00 sec)
db01 [(none)]>use aaa;
Database changed
db01 [aaa]>create table t1 (id int);
Query OK, 0 rows affected (0.02 sec)
查看从库都复制什么了
db02 [aaa]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| bbb |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
7 rows in set (0.00 sec)
db02 [aaa]>show tables from aaa;
+---------------+
| Tables_in_aaa |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
只复制了我们规定的aaa库和bbb库
GTID复制
GTID（Global Transaction ID）是对于一个已提交事务的唯一编号，并且是一个全局（主从复制）唯一的编号
GTID = source_id ： transaction_id
8d397b30-8791-11e9-acbd-000c2902fc28:1-2
GTID核心参数
gtid-mode=on 启用GTID类型
enforce-gtid-condidtency=true 强制GTID的一致性
log-slave-updates=1 slave更新是否记入日志
GTID主从配置
db01+db02修改配置文件
[root@db01 ~]# vim /etc/my.cnf
gtid-mode=on
enforce-gtid-condidtency=true
log-slave-updates=1
[root@db01 ~]# systemctl restart mysqld.service
[root@db02 ~]# vim /etc/my.cnf
[root@db02 ~]# systemctl restart mysqld.service
搭建主从
db02 [(none)]>change master to
-> master_host='10.0.0.51',
-> master_user='aaaa',
-> master_password='123',
-> master_suto_position=1;
db02 [(none)]>start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
db01产生数据测试
db01 [(none)]>create database aaa;
Query OK, 1 row affected (0.09 sec)
db01 [(none)]>use aaa;
Database changed
db01 [aaa]>create table t1(id int);
Query OK, 0 rows affected (0.02 sec)
db01 [aaa]>show master status;
+------------------+----------+--------------+------------------+--------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set
+------------------+----------+--------------+------------------+--------------------------
| mysql-bin.000001 | 470 | | | 8d397b30-8791-11e9-acbd-0
+------------------+----------+--------------+------------------+--------------------------
1 row in set (0.00 sec)
db02检查
db02 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
6 rows in set (0.00 sec)
db02 [(none)]>show tables from aaa;
+---------------+
| Tables_in_aaa |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
db02 [(none)]>show slave status \G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Retrieved_Gtid_Set: 8d397b30-8791-11e9-acbd-000c2902fc28:1-2
Executed_Gtid_Set: 8d397b30-8791-11e9-acbd-000c2902fc28:1-2
配置成功
GTID和普通复制的区别
1.在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便fileover
2.change master to时不再需要binlog文件名和position号，master_auto_position=1
3.在复制过程中，不再依赖master.info文件，而是直接读取最后一个relaylog的GTID号
4.mysqldump备份时，默认会将备份中包含的事务操作记录成
SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1';形式
告诉从库，我的备份中已经已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行
从库有针对的复制主库 主库参数 Binlog_Do_DB Binlog_Ignore_DB 从库 库级别： Replicate_Do_DB: Replicate_Ignore_DB: 表级别： Replicate_Do_Table: Replicate_Ignore_Table: 模糊级别： Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: 一般库级别就够用了
配置 [root@db02 ~]# vim /etc/my.cnf replicate_do_db=aaa replicate_do_db=bbb 只复制aaa库和bbb库 [root@db02 ~]# systemctl restart mysqld.service db02 [(none)]>show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.51 Master_User: aaaa Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000016 Read_Master_Log_Pos: 2005 Relay_Log_File: db02-relay-bin.000004 Relay_Log_Pos: 360 Relay_Master_Log_File: mysql-bin.000016 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: aaa,bbb 测试 主库创建aaa,bbb,ccc,ddd库，aaa库中创建t1表 db01 [(none)]>create database aaa; Query OK, 1 row affected (0.00 sec) db01 [(none)]>create database bbb; Query OK, 1 row affected (0.00 sec) db01 [(none)]>create database ccc; Query OK, 1 row affected (0.00 sec) db01 [(none)]>create database ddd; Query OK, 1 row affected (0.00 sec) db01 [(none)]>use aaa; Database changed db01 [aaa]>create table t1 (id int); Query OK, 0 rows affected (0.02 sec) 查看从库都复制什么了 db02 [aaa]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | aaa | | bbb | | mysql | | performance_schema | | sys | | world | +--------------------+ 7 rows in set (0.00 sec) db02 [aaa]>show tables from aaa; +---------------+ | Tables_in_aaa | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec) 只复制了我们规定的aaa库和bbb库
GTID复制
GTID（Global Transaction ID）是对于一个已提交事务的唯一编号，并且是一个全局（主从复制）唯一的编号
GTID = source_id ： transaction_id
8d397b30-8791-11e9-acbd-000c2902fc28:1-2
GTID核心参数
gtid-mode=on 启用GTID类型
enforce-gtid-condidtency=true 强制GTID的一致性
log-slave-updates=1 slave更新是否记入日志
GTID主从配置
db01+db02修改配置文件
[root@db01 ~]# vim /etc/my.cnf
gtid-mode=on
enforce-gtid-condidtency=true
log-slave-updates=1
[root@db01 ~]# systemctl restart mysqld.service
[root@db02 ~]# vim /etc/my.cnf
[root@db02 ~]# systemctl restart mysqld.service
搭建主从
db02 [(none)]>change master to
-> master_host='10.0.0.51',
-> master_user='aaaa',
-> master_password='123',
-> master_suto_position=1;
db02 [(none)]>start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
db01产生数据测试
db01 [(none)]>create database aaa;
Query OK, 1 row affected (0.09 sec)
db01 [(none)]>use aaa;
Database changed
db01 [aaa]>create table t1(id int);
Query OK, 0 rows affected (0.02 sec)
db01 [aaa]>show master status;
+------------------+----------+--------------+------------------+--------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set
+------------------+----------+--------------+------------------+--------------------------
| mysql-bin.000001 | 470 | | | 8d397b30-8791-11e9-acbd-0
+------------------+----------+--------------+------------------+--------------------------
1 row in set (0.00 sec)
db02检查
db02 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
6 rows in set (0.00 sec)
db02 [(none)]>show tables from aaa;
+---------------+
| Tables_in_aaa |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
db02 [(none)]>show slave status \G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Retrieved_Gtid_Set: 8d397b30-8791-11e9-acbd-000c2902fc28:1-2
Executed_Gtid_Set: 8d397b30-8791-11e9-acbd-000c2902fc28:1-2
配置成功
GTID和普通复制的区别
1.在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便fileover
2.change master to时不再需要binlog文件名和position号，master_auto_position=1
3.在复制过程中，不再依赖master.info文件，而是直接读取最后一个relaylog的GTID号
4.mysqldump备份时，默认会将备份中包含的事务操作记录成
SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1';形式
告诉从库，我的备份中已经已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行
gtid-mode=on 启用GTID类型 enforce-gtid-condidtency=true 强制GTID的一致性 log-slave-updates=1 slave更新是否记入日志
GTID主从配置
db01+db02修改配置文件
[root@db01 ~]# vim /etc/my.cnf
gtid-mode=on
enforce-gtid-condidtency=true
log-slave-updates=1
[root@db01 ~]# systemctl restart mysqld.service
[root@db02 ~]# vim /etc/my.cnf
[root@db02 ~]# systemctl restart mysqld.service
搭建主从
db02 [(none)]>change master to
-> master_host='10.0.0.51',
-> master_user='aaaa',
-> master_password='123',
-> master_suto_position=1;
db02 [(none)]>start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
db01产生数据测试
db01 [(none)]>create database aaa;
Query OK, 1 row affected (0.09 sec)
db01 [(none)]>use aaa;
Database changed
db01 [aaa]>create table t1(id int);
Query OK, 0 rows affected (0.02 sec)
db01 [aaa]>show master status;
+------------------+----------+--------------+------------------+--------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set
+------------------+----------+--------------+------------------+--------------------------
| mysql-bin.000001 | 470 | | | 8d397b30-8791-11e9-acbd-0
+------------------+----------+--------------+------------------+--------------------------
1 row in set (0.00 sec)
db02检查
db02 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
6 rows in set (0.00 sec)
db02 [(none)]>show tables from aaa;
+---------------+
| Tables_in_aaa |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
db02 [(none)]>show slave status \G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Retrieved_Gtid_Set: 8d397b30-8791-11e9-acbd-000c2902fc28:1-2
Executed_Gtid_Set: 8d397b30-8791-11e9-acbd-000c2902fc28:1-2
配置成功
GTID和普通复制的区别
1.在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便fileover
2.change master to时不再需要binlog文件名和position号，master_auto_position=1
3.在复制过程中，不再依赖master.info文件，而是直接读取最后一个relaylog的GTID号
4.mysqldump备份时，默认会将备份中包含的事务操作记录成
SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1';形式
告诉从库，我的备份中已经已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行
1.在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便fileover 2.change master to时不再需要binlog文件名和position号，master_auto_position=1 3.在复制过程中，不再依赖master.info文件，而是直接读取最后一个relaylog的GTID号 4.mysqldump备份时，默认会将备份中包含的事务操作记录成 SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1';形式 告诉从库，我的备份中已经已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行