首先确保服务器开启二进制日志
准备工作
查看二进制文件
[root@localhost mysql]# ls
auto.cnf client-key.pem ib_logfile1 localhost-bin.000004 localhost-slow.log private_key.pem server-key.pem wp
backup game ibtmp1 localhost-bin.index mysql public_key.pem sys wxj
ca-key.pem ib_buffer_pool localhost-bin.000001 localhost.localdomain.err mysql.sock sanchuang TENNIS
ca.pem ibdata1 localhost-bin.000002 localhost.localdomain.pid mysql.sock.lock sanchuang123 test
client-cert.pem ib_logfile0 localhost-bin.000003 localhost.log performance_schema server-cert.pem w
[root@localhost mysql]# mysqlbinlog localhost-bin.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210401 17:02:45 server id 1 end_log_pos 123 CRC32 0xa84b7737 Start: binlog v 4, server v 5.7.33-log created 210401 17:02:45
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
NYxlYA8BAAAAdwAAAHsAAAABAAQANS43LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATd3S6g=
'/*!*/;
# at 123
#210401 17:02:45 server id 1 end_log_pos 154 CRC32 0x3d6a0e33 Previous-GTIDs
# [empty]
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@(none) 20:53 mysql>show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000004 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1.创建完全备份
[root@localhost mysql]# mysqldump -uroot -p'wp123456' --databases wp >/backup/wp.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
2.模拟数据发生变化
root@(none) 20:53 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wp |
+--------------------+
root@game 21:01 mysql>use wp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root@wp 21:02 mysql>insert into trans_demo(id,name) values(4,'lisa');
Query OK, 1 row affected (0.53 sec)
root@wp 21:03 mysql>insert into trans_demo(id,name) values(5,'jack');
Query OK, 1 row affected (0.11 sec)
3.查看位置号
root@wp 21:03 mysql>show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000004 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.09 sec)
4.模拟出现意外，数据库数据丢失
root@wp 21:05 mysql>drop database wp;
Query OK, 3 rows affected (1.21 sec)
查看数据库，确认库已被删除
root@(none) 21:06 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| |
+--------------------+
5.恢复数据
- 恢复完全备份
[root@localhost mysql]# mysql -uroot -p'wp123456' </backup/wp.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
此时的数据还没有完全恢复
root@wp 21:38 mysql>select * from trans_demo;
+------+------+
| id | name |
+------+------+
| 3 | cali |
| 1 | bob |
| 2 | ROSE |
+------+------+
3 rows in set (0.00 sec)
- 二进制日志恢复数据
-
通过位置点恢复
查看二进制日志关于drop数据库的部分
[root@localhost mysql]# mysqlbinlog localhost-bin.000004|egrep -C 5 'drop database' #210401 21:06:44 server id 1 end_log_pos 749 CRC32 0x4232993e Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 749 #210401 21:06:44 server id 1 end_log_pos 835 CRC32 0x39092363 Query thread_id=60 exnec_time=1 error_code=0 SET TIMESTAMP=1617282404/*!*/; drop database wp /*!*/; # at 835 #210401 21:35:54 server id 1 end_log_pos 900 CRC32 0x8ebec48f Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 900获得drop数据的位置点
# at 749 #210401 21:06:44 server id 1 end_log_pos 835 CRC32 0x39092363 Query thread_id=60 exec_time=1 error_code=0 SET TIMESTAMP=1617282404/*!*/; drop database wp位置点为 # at 749
恢复
[root@localhost mysql]# mysqlbinlog --start-position=154 --stop-position=749 localhost-bin.000004 |mysql -uroot -p'wp123456' mysql: [Warning] Using a password on the command line interface can be insecure.恢复成功
root@wp 21:49 mysql>select * from trans_demo; +------+------+ | id | name | +------+------+ | 3 | cali | | 1 | bob | | 2 | ROSE | | 4 | lisa | | 5 | jack | +------+------+ 5 rows in set (0.00 sec) -
通过时间来恢复
查看二进制日志关于drop数据库的部分
[root@localhost mysql]# mysqlbinlog localhost-bin.000004|egrep -C 5 'drop database' # at 31294 #210402 13:01:12 server id 1 end_log_pos 31380 CRC32 0xae8350ad Query thread_id=77 exec_time=0 error_code=0 SET TIMESTAMP=1617339672/*!*/; SET @@session.pseudo_thread_id=77/*!*/; SET @@session.sql_mode=1436549152/*!*/; drop database wp /*!*/; # at 31380 #210403 16:20:54 server id 1 end_log_pos 31431 CRC32 0xb277e494 Rotate to localhost-bin.000005 pos: 4 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ;- 获得drop数据的时间点
#210402 13:01:12通过时间点恢复
[root@localhost mysql]# mysqlbinlog --stop-datetime='2021-04-02 13:01:12' localhost-bin.000004|mysql -uroot -p'wp123456' mysql: [Warning] Using a password on the command line interface can be insecure.恢复成功
root@wp 16:24 mysql>select * from trans_demo; +------+------+ | id | name | +------+------+ | 3 | cali | | 1 | bob | | 2 | ROSE | | 4 | lisa | | 5 | jack | +------+------+ 5 rows in set (0.00 sec)