使用mysqldump对全库进行完全备份 mysqldump -h127.0.0.1 -uroot -pJimstars -P3306 --single-transaction --master-data=2 --events --routines --all-databases > /data/backup/20200820-1352-all_bak.sql 创建测试库和测试表 mysql -h127.0.0.1 -uroot -pJimstars -P3306 -e "create database test_drop_db;" mysql -h127.0.0.1 -uroot -pJimstars -P3306 -e "create table test_drop_db.test_drop_tab_100(id int,name varchar(200)); " mysql -h127.0.0.1 -uroot -pJimstars -P3306 -e "insert into test_drop_db.test_drop_tab_100 values (10000,'full_bak-0');" mysql -h127.0.0.1 -uroot -pJimstars -P3306 -e "insert into test_drop_db.test_drop_tab_100 values (10001,'full_bak-1');" mysql -h127.0.0.1 -uroot -pJimstars -P3306 -e "insert into test_drop_db.test_drop_tab_100 values (10002,'full_bak-2');" 多切换几次 binlog日志 mysql -h127.0.0.1 -uroot -pJimstars -P3306 -e "flush logs;" mysql -h127.0.0.1 -uroot -pJimstars -P3306 -e "insert into test_drop_db.test_drop_tab_100 values (10003,'full_bak-3');" mysql -h127.0.0.1 -uroot -pJimstars -P3306 -e "insert into test_drop_db.test_drop_tab_100 values (10004,'full_bak-4');" 假设在这里误删除了表 mysql -h127.0.0.1 -uroot -pJimstars -P3306 -e "drop table test_drop_db.test_drop_tab_100;" 多切换几次 binlog日志 mysql -h127.0.0.1 -uroot -pJimstars -P3306 -e "flush logs;" 恢复备份的到测试库 3308，3308是新搭建的，没有任何数据，仅做恢复用途 mysql -h127.0.0.1 -uroot -pJimstars -P3308 < /data/backup/20200820-1352-all_bak.sql 确认mysqldump备份到的最终位置 head -n 40 /data/backup/20200820-1352-all_bak.sql 找到如下信息： CHANGE MASTER TO MASTER_LOG_FILE='VM_0_15_centos-bin.000012', MASTER_LOG_POS=26808; 查找删除恢复时的所在binlog日志 mysqlbinlog -v VM_0_15_centos-bin.000001 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000002 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000003 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000004 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000005 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000006 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000007 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000008 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000009 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000010 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000011 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000012 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000013 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000014 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000015 | grep -i "drop table" | grep -i "test_drop_tab_100" mysqlbinlog -v VM_0_15_centos-bin.000016 | grep -i "drop table" | grep -i "test_drop_tab_100" 通过多次查找，发现所在的binlog日志为：VM_0_15_centos-bin.000014 mysqlbinlog -v VM_0_15_centos-bin.000014 | grep -i "drop table" | grep -i "test_drop_tab_100" DROP TABLE `test_drop_db`.`test_drop_tab_100` /* generated by server */ 把 binlog 为14的保存到另外的地方 mysqlbinlog -v VM_0_15_centos-bin.000014 > /tmp/VM_0_15_centos-bin.000014.log 查找对应的pos点 grep -C 30 -i "drop table" /tmp/VM_0_15_centos-bin.000014.log /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 506 #200820 13:54:37 server id 2003306 end_log_pos 574 CRC32 0xcc80d2be Query thread_id=1163 exec_time=0 error_code=0 SET TIMESTAMP=1597902877/*!*/; BEGIN /*!*/; # at 574 #200820 13:54:37 server id 2003306 end_log_pos 645 CRC32 0x81dc364b Table_map: `test_drop_db`.`test_drop_tab_100` mapped to number 1005 # at 645 #200820 13:54:37 server id 2003306 end_log_pos 697 CRC32 0x4e6ac31f Write_rows: table id 1005 flags: STMT_END_F BINLOG ' HRA+XxNqkR4ARwAAAIUCAAAAAO0DAAAAAAEADHRlc3RfZHJvcF9kYgARdGVzdF9kcm9wX3RhYl8x MDAAAgMPAiADA0s23IE= HRA+Xx5qkR4ANAAAALkCAAAAAO0DAAAAAAEAAgAC//wUJwAACgBmdWxsX2Jhay00H8NqTg== '/*!*/; ### INSERT INTO `test_drop_db`.`test_drop_tab_100` ### SET ### @1=10004 ### @2='full_bak-4' # at 697 #200820 13:54:37 server id 2003306 end_log_pos 728 CRC32 0x5e8414ed Xid = 6052 COMMIT/*!*/; # at 728 #200820 13:54:41 server id 2003306 end_log_pos 793 CRC32 0x051dd8b3 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 793 #200820 13:54:41 server id 2003306 end_log_pos 942 CRC32 0x98257be2 Query thread_id=1164 exec_time=0 error_code=0 SET TIMESTAMP=1597902881/*!*/; DROP TABLE `test_drop_db`.`test_drop_tab_100` /* generated by server */ /*!*/; # at 942 #200820 13:54:43 server id 2003306 end_log_pos 998 CRC32 0xe63ee3e7 Rotate to VM_0_15_centos-bin.000015 pos: 4 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*/; 从上述日志，可以确认恢复的结束binlog为 VM_0_15_centos-bin.000014 pos点为 793 而开始恢复的binlog为 VM_0_15_centos-bin.000012 pos点为 26808 最开始需要恢复开始binlog日志文件 mysqlbinlog --start-position=26808 /data/mysql/VM_0_15_centos-bin.000012 | mysql -h127.0.0.1 -uroot -pJimstars -P3308 接着需要恢复的binlog日志文件 mysqlbinlog /data/mysql/VM_0_15_centos-bin.000013 | mysql -h127.0.0.1 -uroot -pJimstars -P3308 如果中间需要恢复的binlog日志文件很多，也可以这样写： mysqlbinlog /data/mysql/VM_0_15_centos-bin.000013 /data/mysql/VM_0_15_centos-bin.000014 /data/mysql/VM_0_15_centos-bin.000015 | mysql -h127.0.0.1 -uroot -pJimstars -P3308 最后需要恢复的binlog日志文件 mysqlbinlog --stop-position=793 /data/mysql/VM_0_15_centos-bin.000014 | mysql -h127.0.0.1 -uroot -pJimstars -P3308 查看3308数据库，确定所删除的表是否已经恢复。 说明：如果删除的是数据库，步骤和上面的步骤完全一样，此处不再赘述。