mysql 完全备份+二进制日志备份

首先确保服务器开启二进制日志

准备工作

查看二进制文件

[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)

• 二进制日志恢复数据

1. 通过位置点恢复

   查看二进制日志关于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)
   

2. 通过时间来恢复

   查看二进制日志关于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)