mysqldump为逻辑备份工具,是mysql数据库自带的备份工具。
一、mysqldump的参数说明
-u 用户
-p 密码
-S 套接字
-h 数据库IP(远程访问使用)
-P 数据库的端口号
本地备份:
mysqldump -uroot -p -S /tmp/mysql.sock
远程备份:
mysqldump -uroot -p -h 10.0.0.51 -P3306
二、备份参数及方式
1、全备参数"-A"
例子1:
[root@db01 ~]# mkdir -p /data/backup
mysqldump -uroot -p -A >/data/backup/full.sql
Enter password:
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
# 补充:
# 1.常规备份是要加 --set-gtid-purged=OFF,解决备份时的警告
# [root@db01 ~]# mysqldump -uroot -p123 -A --set-gtid-purged=OFF >/backup/full.sql
# 2.构建主从时,做的备份,不需要加这个参数
# [root@db01 ~]# mysqldump -uroot -p123 -A --set-gtid-purged=ON >/backup/full.sql
2、单库/多库备份参数"-B"
说明:生产中需要备份,生产相关的库wordpress和MySQL库
例子2 :
mysqldump -B mysql wordpress --set-gtid-purged=OFF >/data/backup/wordpress_mysql.sql
3、数据库下的单表/多表备份
例子3 world数据库下的city,country表
mysqldump -uroot -p world city country >/backup/bak1.sql
以上备份恢复时:必须库事先存在,并且ues进库后才能source恢复
4、高级参数应用
-R 备份存储过程及函数
--triggers 备份触发器
-E 备份事件
例子4:
[root@db01 backup]# mysqldump -uroot -p -A -R -E --triggers >/data/backup/full.sql
#建议在备份时添加以上参数
5、-F 在备份开始时,刷新一个新binlog日志
例子5:
mysqldump -uroot -p -A -R --triggers -F >/tmp/full.sql
#备份时,会刷新一个新的binlog日志
[root@vm01 ~]# mysql -uroot -p -e "show databases;"
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| world |
| ywx |
+--------------------+
[root@vm01 ~]# mysql -uroot -p -e "show master status;"
Enter password:
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
[root@vm01 ~]#
[root@vm01 ~]# mysqldump -uroot -p -A -R --triggers -F >/tmp/full.sql
Enter password:
[root@vm01 ~]# mysql -uroot -p -e "show master status;"
Enter password:
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
[root@vm01 ~]#
#注意:除了sys、information_schema、performance_schema外;-F参数有多好数据库就会刷新几次binlog日志。
6、--master-data=2
以注释的形式,保存备份开始时间点的binlog的状态信息
mysqldump -uroot -p -A -R -E --triggers --master-data=2 >/tmp/full.sql
[root@vm01 ~]# head -30 /tmp/full.sql
......
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
#
......
功能:
(1)在备份时,会自动记录,二进制日志文件名和位置号
0 默认值
1 以change master to命令形式,可以用作主从复制
2 以注释的形式记录,备份时刻的文件名+postion号
(2)自动锁表
(3)如果配合--single-transaction,只对非InnoDB表进行锁表备份,InnoDB表进行“热“”备,实际上是实现快照备份。
7、--single-transaction
innodb 存储引擎开启热备(快照备份)功能
master-data可以自动加锁
(1)在不加--single-transaction ,启动所有表的温备份,所有表都锁定
(1)加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能
例子6: 备份必加参数
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
8、--set-gtid-purged=auto
auto , on
off
使用场景:
1. --set-gtid-purged=OFF,可以使用在日常备份参数中.
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. auto , on:在构建主从复制环境时需要的参数配置,在主从复制时可以不加该参数(默认为auto)
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=ON >/data/backup/full.sql
9、--max-allowed-packet=#
指服务器端和客户端在一次传送数据包的过程中数据包的大小(最大限制)
如果超出这个值,将出现异常
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql
--max-allowed-packet=#
The maximum packet length to send to or receive from server.
10、压缩备份并添加时间戳
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F-%T).sql.gz
11、mysqldump恢复的注意事项
注意:
1、mysqldump在备份和恢复时都需要mysql实例启动为前提。
2、一般数据量级100G以内,大约15-45分钟可以恢复,数据量级很大很大的时候(PB、EB)
3、mysqldump是覆盖形式恢复的方法。
一般我们认为,在同数据量级,物理备份要比逻辑备份速度快.
逻辑备份的优势:
1、可读性强
2、压缩比很高
12、备份时优化参数
(1) max_allowed_packet 最大的数据包大小
mysqldump -uroot -p123 -A -R --triggers --set-gtid-purged=OFF --master-data=2 max_allowed_packet=128M --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
(2) 增加key_buffer_size (临时表有关)
(3) 分库分表并发备份
(4) 架构分离,分别备份 (架构拆分,分布式备份)
三、实验案例
1、数据库恢复思路
时间说明:
(1)每天全备
(2)binlog日志是完整
(3)模拟白天的数据变化
(4)模拟下午两点误删除数据库
恢复思路:
(1)检查备份可用性
(2)从备份中获取二进制日志位置
(3)根据日志位置截取需要的二进制日志
(4)初始化数据库,并启动
(5)恢复全备
(6)恢复二进制日志
2、实现所有表的单独备份
提示:
information_schema.tables
mysqldump -uroot -p123 world city >/backup/world_city.sql
select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," --master-data=2 --single-transaction --set-gtid-purged=0 -R -E --triggers>/backup/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in ('sys','information_schema','performance_schema');
3、模拟企业案例1
1)实验环境
正在运行的网站系统,mysql-5.7.20 数据库,数据量50G,日业务增量1-5M。开启gitd
mysql为二进制安装配置文件如下
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/mysql
server_id=201
port=3306
socket=/tmp/mysql.sock
log_bin=mysql-bin
binlog_format=row
log_error=/tmp/mysqld_err.log
secure-file-priv=/tmp
autocommit=0
gtid_mode=on
enforce_gtid_consistency=true
[mysql]
socket=/tmp/mysql.sock
prompt= [\\d]>
[client]
socket=/tmp/mysql.sock
2) 备份策略
每天23:00点,计划任务调用mysqldump执行全备脚本
3) 故障时间点:
年底故障演练:模拟周三上午10点误删除数据库,并进行恢复.
4) 思路:
1、停业务,避免数据的二次伤害
2、找一个临时库,恢复周三23:00全备
3、截取周二23:00 --- 周三10点误删除之间的binlog,恢复到临时库
4、测试可用性和完整性
5、
5.1 方法一:直接使用临时库顶替原生产库,前端应用割接到新库
5.2 方法二:将误删除的表导出,导入到原生产库
6、开启业务
处理结果:经过20分钟的处理,最终业务恢复正常
模拟故障
1 、准备数据
create database ywx;
use ywx
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
2 、模拟周二 23:00全备
[root@vm01 backup]# mysqldump -uroot -p123 -A -E -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /data/backup/full_$(date +%F).sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@vm01 backup]# ls
full_2020-12-01.sql.gz
3 、模拟周二 23:00到周三 10点之间数据变化
use ywx
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
commit;
4 、模拟故障,删除表(只是模拟,不代表生产操作)
drop database ywx;
模拟恢复过程
1、准备临时数据库(多实例3307)或测试库
[root@vm01 ~]# systemctl start mysqld3307
[root@vm01 ~]# ps -ef |grep 3307
mysql 7201 1 0 Nov28 ? 00:01:48 /app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
root 11039 11007 0 22:50 pts/1 00:00:00 grep --color=auto 3307
2、准备备份
(1)准备全备:
[root@vm01 ~]# cd /data/backup/
[root@vm01 backup]# ls
full_2020-12-01.sql.gz
(2)截取二进制日志
2.1查看使用的bin_log日志开始位子
[root@vm01 backup]# gunzip full_2020-12-01.sql.gz
[root@vm01 backup]# vim full_2020-12-01.sql
。。。。。。
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=732;
。。。。。。
position:开始为:732
gtid:开始为:e271e770-310c-11eb-b220-000c29d16f12:4
2.2查看bin_log结束位子
方案一:
[(none)]>show binlog events in 'mysql-bin.000001';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 201 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 201 | 154 | |
| mysql-bin.000001 | 154 | Gtid | 201 | 219 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:1' |
| mysql-bin.000001 | 219 | Query | 201 | 310 | create database ywx |
| mysql-bin.000001 | 310 | Gtid | 201 | 375 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:2' |
| mysql-bin.000001 | 375 | Query | 201 | 471 | use `ywx`; create table t1 (id int) |
| mysql-bin.000001 | 471 | Gtid | 201 | 536 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:3' |
| mysql-bin.000001 | 536 | Query | 201 | 607 | BEGIN |
| mysql-bin.000001 | 607 | Table_map | 201 | 651 | table_id: 487 (ywx.t1) |
| mysql-bin.000001 | 651 | Write_rows | 201 | 701 | table_id: 487 flags: STMT_END_F |
| mysql-bin.000001 | 701 | Xid | 201 | 732 | COMMIT /* xid=3843 */ |
| mysql-bin.000001 | 732 | Gtid | 201 | 797 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:4' |
| mysql-bin.000001 | 797 | Query | 201 | 868 | BEGIN |
| mysql-bin.000001 | 868 | Table_map | 201 | 912 | table_id: 519 (ywx.t1) |
| mysql-bin.000001 | 912 | Write_rows | 201 | 962 | table_id: 519 flags: STMT_END_F |
| mysql-bin.000001 | 962 | Xid | 201 | 993 | COMMIT /* xid=4302 */ |
| mysql-bin.000001 | 993 | Gtid | 201 | 1058 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:5' |
| mysql-bin.000001 | 1058 | Query | 201 | 1154 | use `ywx`; create table t2 (id int) |
| mysql-bin.000001 | 1154 | Gtid | 201 | 1219 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:6' |
| mysql-bin.000001 | 1219 | Query | 201 | 1290 | BEGIN |
| mysql-bin.000001 | 1290 | Table_map | 201 | 1334 | table_id: 520 (ywx.t2) |
| mysql-bin.000001 | 1334 | Write_rows | 201 | 1384 | table_id: 520 flags: STMT_END_F |
| mysql-bin.000001 | 1384 | Xid | 201 | 1415 | COMMIT /* xid=4305 */ |
| mysql-bin.000001 | 1415 | Gtid | 201 | 1480 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:7' |
| mysql-bin.000001 | 1480 | Query | 201 | 1569 | drop database ywx |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
25 rows in set (0.01 sec)
查看事件,查到drop database ywx之前的结束号
position:1415
gtid:e271e770-310c-11eb-b220-000c29d16f12:7
方案二:查看binLog日志
[root@vm01 backup]# mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/mysql-bin.000001|tail -20
### @1=22 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `ywx`.`t2`
### SET
### @1=33 /* INT meta=0 nullable=1 is_null=0 */
# at 1384
#201201 3:16:44 server id 201 end_log_pos 1415 CRC32 0xb893af34 Xid = 4305
COMMIT/*!*/;
# at 1415
#201201 3:16:55 server id 201 end_log_pos 1480 CRC32 0xfe36418d GTID last_committed=6 sequence_number=7 rbr_only=no
SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:7'/*!*/;
# at 1480
#201201 3:16:55 server id 201 end_log_pos 1569 CRC32 0x1509faeb Query thread_id=28 exec_time=0 error_code=0
SET TIMESTAMP=1606763815/*!*/;
drop database ywx
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
查看事件,查到drop database ywx之前的结束号
position:1415
gtid:e271e770-310c-11eb-b220-000c29d16f12:6
2.3截取bin_log日志
gtid截取
mysqlbinlog --skip-gtids --include-gtids='e271e770-310c-11eb-b220-000c29d16f12:4-6' /data/mysql/mysql-bin.000001 >/data/backup/gtid.sql
position截取
mysqlbinlog --start-position=732 --stop-position=1415 /data/mysql/mysql-bin.000001 >/data/backup/bin.sql
3、恢复备份到临时库
恢复全备
gunzip full_2020-11-30.sql.gz
mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /data/backup/full_2020-11-30.sql;
#gtid截取恢复
source /data/backup/gtid.sql;
#position截取
#source /data/backup/bin.sql;
4、模拟企业案例2
练习:
1、创建一个数据库 ywx
create database ywx charset=utf8;
2、在ywx下创建一张表t1
use ywx;
create table t1(id int);
3、插入5行任意数据
insert into t1(id) values(1),(2),(3),(4),(5);
commit;
4、全备
[root@vm01 backup]# mysqldump -uroot -p123 -A -E -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /data/backup/full_$(date +%F).sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@vm01 backup]# ll
total 208
-rw-r--r-- 1 root root 211036 Dec 1 00:16 full_2020-12-01.sql.gz
5、插入两行数据,任意修改3行数据,删除1行数据
update t1 set id=11 where id=1;
update t1 set id=22 where id=2;
update t1 set id=33 where id=3;
insert into t1(id) values(6),(7);
delete from t1 where id=5;
commit;
6、删除所有数据
delete from t1;
commit;
7、再t1中又插入5行新数据.
insert into t1(id) values(111),(222),(333),(444),(555);
commit;
需求,跳过第六步恢复表数据
写备份脚本和策略
恢复
1、准备临时数据库(多实例3307)或测试库
[root@vm01 ~]# systemctl start mysqld3307
[root@vm01 ~]# ps -ef |grep 3307
mysql 7201 1 0 Nov28 ? 00:01:48 /app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
root 11039 11007 0 22:50 pts/1 00:00:00 grep --color=auto 3307
2、准备全备
[root@vm01 backup]# ll
total 208
-rw-r--r-- 1 root root 211036 Dec 1 00:16 full_2020-12-01.sql.gz
3、截取二进制
1)在全备文件中查找二进制文件的开始位子
[root@vm01 backup]# gunzip full_2020-11-30.sql.gz
[root@vm01 backup]# vim full_2020-11-30.sql
。。。。。。
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=754;
。。。。。。
mysql-bin.000001,MASTER_LOG_POS=754为二进制截取的起点位子
2)确认结束位子
在binlog日志中确认结束位子
[root@vm01 backup]# mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000001
。。。。。。
SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:5'/*!*/;
# at 1429
#201201 2:51:04 server id 201 end_log_pos 1500 CRC32 0xd2a8cc70 Query thread_id=23 exec_time=0 error_code=0
SET TIMESTAMP=1606762264/*!*/;
BEGIN
/*!*/;
# at 1500
#201201 2:51:04 server id 201 end_log_pos 1544 CRC32 0xafbfe599 Table_map: `ywx`.`t1` mapped to number 485
# at 1544
#201201 2:51:04 server id 201 end_log_pos 1609 CRC32 0xd8b246ce Delete_rows: table id 485 flags: STMT_END_F
### DELETE FROM `ywx`.`t1`
### WHERE
### @1=11 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `ywx`.`t1`
### WHERE
### @1=22 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `ywx`.`t1`
### WHERE
### @1=33 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `ywx`.`t1`
### WHERE
### @1=4 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `ywx`.`t1`
### WHERE
### @1=6 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `ywx`.`t1`
### WHERE
### @1=7 /* INT meta=0 nullable=1 is_null=0 */
# at 1609
#201201 2:51:05 server id 201 end_log_pos 1640 CRC32 0x935922a1 Xid = 3817
COMMIT/*!*/;
# at 1640
#201201 2:51:15 server id 201 end_log_pos 1705 CRC32 0xedf7e3c1 GTID last_committed=5 sequence_number=6 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:6'/*!*/;
# at 1705
#201201 2:51:14 server id 201 end_log_pos 1776 CRC32 0x0e0087ba Query thread_id=23 exec_time=0 error_code=0
SET TIMESTAMP=1606762274/*!*/;
BEGIN
/*!*/;
# at 1776
#201201 2:51:14 server id 201 end_log_pos 1820 CRC32 0x4e44226d Table_map: `ywx`.`t1` mapped to number 485
# at 1820
#201201 2:51:14 server id 201 end_log_pos 1880 CRC32 0xd2c8605a Write_rows: table id 485 flags: STMT_END_F
### INSERT INTO `ywx`.`t1`
### SET
### @1=111 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `ywx`.`t1`
### SET
### @1=222 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `ywx`.`t1`
### SET
### @1=333 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `ywx`.`t1`
### SET
### @1=444 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `ywx`.`t1`
### SET
### @1=555 /* INT meta=0 nullable=1 is_null=0 */
# at 1880
#201201 2:51:15 server id 201 end_log_pos 1911 CRC32 0xc1cb6934 Xid = 3819
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@vm01 backup]#
在events事件中确认结束位子
[ywx]>show binlog events in 'mysql-bin.000001';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 201 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 201 | 154 | |
| mysql-bin.000001 | 154 | Gtid | 201 | 219 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:1' |
| mysql-bin.000001 | 219 | Query | 201 | 323 | create database ywx charset=utf8 |
| mysql-bin.000001 | 323 | Gtid | 201 | 388 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:2' |
| mysql-bin.000001 | 388 | Query | 201 | 483 | use `ywx`; create table t1(id int) |
| mysql-bin.000001 | 483 | Gtid | 201 | 548 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:3' |
| mysql-bin.000001 | 548 | Query | 201 | 619 | BEGIN |
| mysql-bin.000001 | 619 | Table_map | 201 | 663 | table_id: 453 (ywx.t1) |
| mysql-bin.000001 | 663 | Write_rows | 201 | 723 | table_id: 453 flags: STMT_END_F |
| mysql-bin.000001 | 723 | Xid | 201 | 754 | COMMIT /* xid=3352 */ |
| mysql-bin.000001 | 754 | Gtid | 201 | 819 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:4' |
| mysql-bin.000001 | 819 | Query | 201 | 890 | BEGIN |
| mysql-bin.000001 | 890 | Table_map | 201 | 934 | table_id: 485 (ywx.t1) |
| mysql-bin.000001 | 934 | Update_rows | 201 | 980 | table_id: 485 flags: STMT_END_F |
| mysql-bin.000001 | 980 | Table_map | 201 | 1024 | table_id: 485 (ywx.t1) |
| mysql-bin.000001 | 1024 | Update_rows | 201 | 1070 | table_id: 485 flags: STMT_END_F |
| mysql-bin.000001 | 1070 | Table_map | 201 | 1114 | table_id: 485 (ywx.t1) |
| mysql-bin.000001 | 1114 | Update_rows | 201 | 1160 | table_id: 485 flags: STMT_END_F |
| mysql-bin.000001 | 1160 | Table_map | 201 | 1204 | table_id: 485 (ywx.t1) |
| mysql-bin.000001 | 1204 | Write_rows | 201 | 1249 | table_id: 485 flags: STMT_END_F |
| mysql-bin.000001 | 1249 | Table_map | 201 | 1293 | table_id: 485 (ywx.t1) |
| mysql-bin.000001 | 1293 | Delete_rows | 201 | 1333 | table_id: 485 flags: STMT_END_F |
| mysql-bin.000001 | 1333 | Xid | 201 | 1364 | COMMIT /* xid=3811 */ |
| mysql-bin.000001 | 1364 | Gtid | 201 | 1429 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:5' |
| mysql-bin.000001 | 1429 | Query | 201 | 1500 | BEGIN |
| mysql-bin.000001 | 1500 | Table_map | 201 | 1544 | table_id: 485 (ywx.t1) |
| mysql-bin.000001 | 1544 | Delete_rows | 201 | 1609 | table_id: 485 flags: STMT_END_F |
| mysql-bin.000001 | 1609 | Xid | 201 | 1640 | COMMIT /* xid=3817 */ |
| mysql-bin.000001 | 1640 | Gtid | 201 | 1705 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:6' |
| mysql-bin.000001 | 1705 | Query | 201 | 1776 | BEGIN |
| mysql-bin.000001 | 1776 | Table_map | 201 | 1820 | table_id: 485 (ywx.t1) |
| mysql-bin.000001 | 1820 | Write_rows | 201 | 1880 | table_id: 485 flags: STMT_END_F |
| mysql-bin.000001 | 1880 | Xid | 201 | 1911 | COMMIT /* xid=3819 */ |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
34 rows in set (0.00 sec)
第一段位子:position从是754开始到1544结束,
开始gtid号为754的下一个gtid号:e271e770-310c-11eb-b220-000c29d16f12:4
结束gtid号:e271e770-310c-11eb-b220-000c29d16f12:5
第二段位子:position从1609开始到1609结束,
gtid为e271e770-310c-11eb-b220-000c29d16f12:6
3)截取二进制文件
从上面可以看到delete from t1的位子为1544
gtid:
第一段:
mysqlbinlog --skip-gtids --include-gtids='e271e770-310c-11eb-b220-000c29d16f12:4' /data/mysql/mysql-bin.000001 >/data/backup/gtid1.sql
第二段:
mysqlbinlog --skip-gtids --include-gtids='e271e770-310c-11eb-b220-000c29d16f12:6' /data/mysql/mysql-bin.000001 >/data/backup/gtid2.sql
合并:
mysqlbinlog --skip-gtids --include-gtids='e271e770-310c-11eb-b220-000c29d16f12:4-6' --exclude-gtids='e271e770-310c-11eb-b220-000c29d16f12:5' /data/mysql/mysql-bin.000001 >/data/backup/gtid3.sql
position:
第一段:
mysqlbinlog --start-position=754 --stop-position=1364 /data/mysql/mysql-bin.000001 >/data/backup/bin1.sql
第二段:
mysqlbinlog --start-position=1609 /data/mysql/mysql-bin.000001 >/data/backup/bin2.sql
4、恢复备份到临时库
恢复全备
gunzip full_2020-12-01.sql.gz
mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /data/backup/full_2020-12-01.sql;
#gtid截取恢复
source /data/backup/gtid1.sql;
source /data/backup/gtid2.sql;
或者:
source /data/backup/gtid3.sql;
#position截取
#source /data/backup/bin1.sql;
#source /data/backup/bin2.sql;
I have a dream so I study hard!!!