mysql利用binlog日志恢复数据库小实验
1、my.cnf配置
log_bin = /MySQL-bin/mysql-bin
max_binlog_size = 100M
2、测试
create database bp; use bp create table bptest(id int ,name varchar(20)); show tables; insert into bptest values(1,'a'); insert into bptest values(2,'b'); insert into bptest values(3,'c'); select * from bptest; insert into bptest values(4,'d'); insert into bptest values(5,'e'); delete from bptest where id=1; truncate table bptest; select * from bptest;
3、查看mysqlbinlog
mysqlbinlog /MySQL-bin/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #181203 9:44:04 server id 1 end_log_pos 256 CRC32 0xbaf1b3b8 Start: binlog v 4, server v 10.2.19-MariaDB-log created 181203 9:44:04 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' ZIoEXA8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABkigRcEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAEEwQADQgICAoKCgG4s/G6 '/*!*/; # at 256 #181203 9:44:04 server id 1 end_log_pos 299 CRC32 0x1443c41e Gtid list [0-1-18] # at 299 #181203 9:44:04 server id 1 end_log_pos 342 CRC32 0x58c2c378 Binlog checkpoint mysql-bin.000001 # at 342 #181203 9:44:30 server id 1 end_log_pos 384 CRC32 0xe5c81d38 GTID 0-1-19 ddl /*!100101 SET @@session.skip_parallel_replication=0*//*!*/; /*!100001 SET @@session.gtid_domain_id=0*//*!*/; /*!100001 SET @@session.server_id=1*//*!*/; /*!100001 SET @@session.gtid_seq_no=19*//*!*/; # at 384 #181203 9:44:30 server id 1 end_log_pos 467 CRC32 0x1fe94f9e Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1543801470/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=1411383296/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create database bp /*!*/; # at 467 #181203 9:44:30 server id 1 end_log_pos 509 CRC32 0x8edf2d7a GTID 0-1-20 ddl /*!100001 SET @@session.gtid_seq_no=20*//*!*/; # at 509 #181203 9:44:30 server id 1 end_log_pos 619 CRC32 0x4824ae44 Query thread_id=9 exec_time=0 error_code=0 use `bp`/*!*/; SET TIMESTAMP=1543801470/*!*/; create table bptest(id int ,name varchar(20)) /*!*/; # at 619 #181203 9:44:30 server id 1 end_log_pos 661 CRC32 0xbbae54a3 GTID 0-1-21 trans /*!100001 SET @@session.gtid_seq_no=21*//*!*/; BEGIN /*!*/; # at 661 #181203 9:44:30 server id 1 end_log_pos 758 CRC32 0xe65b525c Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1543801470/*!*/; insert into bptest values(1,'a') /*!*/; # at 758 #181203 9:44:30 server id 1 end_log_pos 789 CRC32 0xa12a734a Xid = 9 COMMIT/*!*/; # at 789 #181203 9:44:30 server id 1 end_log_pos 831 CRC32 0x38b47da8 GTID 0-1-22 trans /*!100001 SET @@session.gtid_seq_no=22*//*!*/; BEGIN /*!*/; # at 831 #181203 9:44:30 server id 1 end_log_pos 928 CRC32 0x1c8b22d1 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1543801470/*!*/; insert into bptest values(2,'b') /*!*/; # at 928 #181203 9:44:30 server id 1 end_log_pos 959 CRC32 0xb3853908 Xid = 10 COMMIT/*!*/; # at 959 #181203 9:44:30 server id 1 end_log_pos 1001 CRC32 0xca9d1c72 GTID 0-1-23 trans /*!100001 SET @@session.gtid_seq_no=23*//*!*/; BEGIN /*!*/; # at 1001 #181203 9:44:30 server id 1 end_log_pos 1098 CRC32 0xb98e03d9 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1543801470/*!*/; insert into bptest values(3,'c') /*!*/; # at 1098 #181203 9:44:30 server id 1 end_log_pos 1129 CRC32 0x5ee65007 Xid = 11 COMMIT/*!*/; # at 1129 #181203 9:44:30 server id 1 end_log_pos 1171 CRC32 0x5763fa6a GTID 0-1-24 trans /*!100001 SET @@session.gtid_seq_no=24*//*!*/; BEGIN /*!*/; # at 1171 #181203 9:44:30 server id 1 end_log_pos 1268 CRC32 0xeb740489 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1543801470/*!*/; insert into bptest values(4,'d') /*!*/; # at 1268 #181203 9:44:30 server id 1 end_log_pos 1299 CRC32 0x7d47e9db Xid = 13 COMMIT/*!*/; # at 1299 #181203 9:44:30 server id 1 end_log_pos 1341 CRC32 0x5b709f6b GTID 0-1-25 trans /*!100001 SET @@session.gtid_seq_no=25*//*!*/; BEGIN /*!*/; # at 1341 #181203 9:44:30 server id 1 end_log_pos 1438 CRC32 0xe55328ae Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1543801470/*!*/; insert into bptest values(5,'e') /*!*/; # at 1438 #181203 9:44:30 server id 1 end_log_pos 1469 CRC32 0x76442c82 Xid = 14 COMMIT/*!*/; # at 1469 #181203 9:44:30 server id 1 end_log_pos 1511 CRC32 0x0a5b210c GTID 0-1-26 trans /*!100001 SET @@session.gtid_seq_no=26*//*!*/; BEGIN /*!*/; # at 1511 #181203 9:44:30 server id 1 end_log_pos 1605 CRC32 0x40a65c3c Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1543801470/*!*/; delete from bptest where id=1 /*!*/; # at 1605 #181203 9:44:30 server id 1 end_log_pos 1636 CRC32 0x0d0a9bed Xid = 15 COMMIT/*!*/; # at 1636 #181203 9:44:30 server id 1 end_log_pos 1678 CRC32 0x9aa1c8db GTID 0-1-27 ddl /*!100001 SET @@session.gtid_seq_no=27*//*!*/; # at 1678 #181203 9:44:30 server id 1 end_log_pos 1764 CRC32 0x4fbf3c67 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1543801470/*!*/; truncate table bptest /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
mysqlbinlog --start-position=661 --stop-position=1511 /MySQL-bin/mysql-bin.000001|mysql -uroot -p
恢复完成
每次重启mysql都会重新生成binlog
使用stop-datetime生成要恢复数据的sql，以时间恢复
mysqlbinlog --no-defaults --database=kevin --skip-gtids --stop-datetime='2023-10-20 17:30:51' /var/lib/mysql/mysql-bin.000006 > /root/binlog.sql
使用stop-position生成要恢复数据的sql，以标志位恢复
mysqlbinlog --no-defaults --database=kevin --skip-gtids --stop-position=521 /var/lib/mysql/mysql-bin.000006 > /root/binlog.sql
执行要恢复的sql
mysql> source /root/binlog.sql
