参考文献：
https://www.cnblogs.com/clsn/p/8138015.html#_label2
新建数据库和数据(全量恢复)
drop database clsn;
CREATE DATABASE clsn;
USE `clsn`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `test` VALUES (1,'clsn'),(2,'znix'),(3,'inca'),(4,'zuma'),(5,'kaka');
查看数据
SELECT * FROM `clsn`.`test` LIMIT 0, 1000
全量备份sql
生成的clsn_2022-04-07.sql.gz是clsn的全量数据，数据截止到(5,‘kaka’)
mysqldump -uroot -p'123456' -B --master-data=2 --single-transaction clsn|gzip>/home/mysqlbak/clsn_$(date +%F).sql.gz
新增数据(增量恢复)
insert into test values(6,'haha');
insert into test values(7,'hehe');
查看数据
SELECT * FROM `clsn`.`test` LIMIT 0, 1000
发现数据有问题，禁用数据库准备恢复
采用iptables防火墙屏蔽所有应用程序的写入
firewall-cmd --zone=public --remove-port=3306/tcp --permanent
firewall-cmd --reload
或采用mysql 配置参数，但是需要重启数据库
#修改配置文件
skip-networking
#重启
service mysqld restart
删除数据库
drop database clsn;
恢复
误删数据库第一时间发现立即去找最后一个binlog文件，查看第23行binlog的位置。
#解压全备sql
zcat clsn_2022-04-07.sql.gz >clsn_2022-04-07.sql
#查看pos和binlog文件
[root@129 mysqlbak]# sed -n '23p' clsn_2022-04-07.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000026', MASTER_LOG_POS=1025;
#提取clsn库的操作 起点1025上面sed -n '23p' clsn_2022-04-07.sql查询的结果
#-d 指定数据库
#多个binlog
#mysqlbinlog -d clsn --start-position=1025 mysql-bin.000026 mysql-bin.0000027 -r bin1.sql
mysqlbinlog -d clsn --start-position=1025 mysql-bin.000026 -r bin.sql
打开bin.sql文件，第78行是删除库的语句
第15行 # at 1025
第75行 # at 1706(避开第78行的删除语句)
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 157
#220407 21:01:25 server id 129 end_log_pos 126 CRC32 0xd1475ec5 Start: binlog v 4, server v 8.0.28 created 220407 21:01:25 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
peBOYg+BAAAAegAAAH4AAAABAAQAOC4wLjI4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACl4E5iEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
CigAAcVeR9E=
'/*!*/;
# at 1025
#220407 21:27:35 server id 129 end_log_pos 1104 CRC32 0xfdcd43fc Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no original_committed_timestamp=1649338055258785 immediate_commit_timestamp=1649338055258785 transaction_length=302
# original_commit_timestamp=1649338055258785 (2022-04-07 21:27:35.258785 CST)
# immediate_commit_timestamp=1649338055258785 (2022-04-07 21:27:35.258785 CST)
/*!80001 SET @@session.original_commit_timestamp=1649338055258785*//*!*/;
/*!80014 SET @@session.original_server_version=80028*//*!*/;
/*!80014 SET @@session.immediate_server_version=80028*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1104
#220407 21:27:35 server id 129 end_log_pos 1186 CRC32 0x1713bab0 Query thread_id=25 exec_time=0 error_code=0
SET TIMESTAMP=1649338055/*!*/;
SET @@session.pseudo_thread_id=25/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 1186
#220407 21:27:35 server id 129 end_log_pos 1296 CRC32 0x75802f37 Query thread_id=25 exec_time=0 error_code=0
use `clsn`/*!*/;
SET TIMESTAMP=1649338055/*!*/;
insert into test values(6,'haha')
/*!*/;
# at 1296
#220407 21:27:35 server id 129 end_log_pos 1327 CRC32 0xe5cb2bd2 Xid = 62309
COMMIT/*!*/;
# at 1327
#220407 21:27:47 server id 129 end_log_pos 1406 CRC32 0xa1a2344d Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=no original_committed_timestamp=1649338067910695 immediate_commit_timestamp=1649338067910695 transaction_length=302
# original_commit_timestamp=1649338067910695 (2022-04-07 21:27:47.910695 CST)
# immediate_commit_timestamp=1649338067910695 (2022-04-07 21:27:47.910695 CST)
/*!80001 SET @@session.original_commit_timestamp=1649338067910695*//*!*/;
/*!80014 SET @@session.original_server_version=80028*//*!*/;
/*!80014 SET @@session.immediate_server_version=80028*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1406
#220407 21:27:47 server id 129 end_log_pos 1488 CRC32 0x299c3925 Query thread_id=25 exec_time=0 error_code=0
SET TIMESTAMP=1649338067/*!*/;
BEGIN
/*!*/;
# at 1488
#220407 21:27:47 server id 129 end_log_pos 1598 CRC32 0x3a279f80 Query thread_id=25 exec_time=0 error_code=0
SET TIMESTAMP=1649338067/*!*/;
insert into test values(7,'hehe')
/*!*/;
# at 1598
#220407 21:27:47 server id 129 end_log_pos 1629 CRC32 0x737aebcf Xid = 62316
COMMIT/*!*/;
# at 1629
#220407 21:42:02 server id 129 end_log_pos 1706 CRC32 0x32070fe6 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=no original_committed_timestamp=1649338922066652 immediate_commit_timestamp=1649338922066652 transaction_length=183
# original_commit_timestamp=1649338922066652 (2022-04-07 21:42:02.066652 CST)
# immediate_commit_timestamp=1649338922066652 (2022-04-07 21:42:02.066652 CST)
/*!80001 SET @@session.original_commit_timestamp=1649338922066652*//*!*/;
/*!80014 SET @@session.original_server_version=80028*//*!*/;
/*!80014 SET @@session.immediate_server_version=80028*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1706
#220407 21:42:02 server id 129 end_log_pos 1812 CRC32 0x1bf18ae7 Query thread_id=26 exec_time=0 error_code=0 Xid = 87206
SET TIMESTAMP=1649338922/*!*/;
DROP DATABASE `clsn`
/*!*/;
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*/;
全量恢复
mysql -uroot -p'123456' < clsn_2022-04-07.sql
增量恢复
根据position位置
第15行 # at 1025
第75行 # at 1706
mysqlbinlog --start-position=1025 --stop-position=1706 mysql-bin.000026 | mysql -uroot -p'123456'
到此以恢复全部数据，这里是特殊场景，实际应用灵活变通。
