十六、mysql的备份与恢复（二）--mysqldump
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: [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
说明：生产中需要备份，生产相关的库wordpress和MySQL库
例子2 :
mysqldump -B mysql wordpress --set-gtid-purged=OFF >/data/backup/wordpress_mysql.sql
例子3 world数据库下的city,country表 mysqldump -uroot -p world city country >/backup/bak1.sql 以上备份恢复时:必须库事先存在,并且ues进库后才能source恢复
-R 备份存储过程及函数 --triggers 备份触发器 -E 备份事件 例子4: [root@db01 backup]# mysqldump -uroot -p -A -R -E --triggers >/data/backup/full.sql #建议在备份时添加以上参数
例子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日志。
以注释的形式,保存备份开始时间点的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表进行“热“”备，实际上是实现快照备份。
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
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
指服务器端和客户端在一次传送数据包的过程中数据包的大小(最大限制) 如果超出这个值，将出现异常 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.
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
注意： 1、mysqldump在备份和恢复时都需要mysql实例启动为前提。 2、一般数据量级100G以内，大约15-45分钟可以恢复，数据量级很大很大的时候（PB、EB） 3、mysqldump是覆盖形式恢复的方法。 一般我们认为，在同数据量级，物理备份要比逻辑备份速度快. 逻辑备份的优势: 1、可读性强 2、压缩比很高
(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）恢复二进制日志
提示： 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');
1）实验环境
正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量1-5M。开启gitd
[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;
[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
use ywx insert into t1 values(11),(22),(33); commit; create table t2 (id int); insert into t2 values(11),(22),(33); commit;
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
（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
恢复全备 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;
练习： 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!!!
