mysql数据库（完全备份，增量备份，差异备份）

碳烤小肥杨..

已于 2023-09-05 23:52:01 修改

阅读量2.5k

点赞数 9

文章标签：数据库 mysql 运维 linux

于 2023-09-05 23:49:33 首次发布

本文链接：https://blog.csdn.net/m0_64505752/article/details/132702850

版权

mysql数据库（完全备份，增量备份，差异备份）

1. mysql数据库备份与恢复

1.1 数据库常用备份方案

数据库备份方案：

全量备份
增量备份
差异备份

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据备份时间短恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

1.2 mysql备份工具mysqldump

//语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
//常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
  
    
//备份整个数据库(全备)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FTX                |
| ftx                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use ftx;
Database changed
mysql> show tables;
+---------------+
| Tables_in_ftx |
+---------------+
| course        |
| student       |
+---------------+
2 rows in set (0.00 sec)

[root@ftx ~]# ls
anaconda-ks.cfg

在做备份操作之前需要先将指定ip授权
mysql> grant all on *.* to 'root'@192.168.195.136 identified by '12345678';
Query OK, 0 rows affected, 1 warning (0.00 sec)

重新读取授权表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@ftx ~]# mysqldump -uroot -p12345678 -h192.168.195.136 --all-databases > all-2023-09-04-19-55.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.  //如果在命令行中输入了密码，则会出现此行告警，告诉我们在命令行界面输入密码是不安全的

我们应该用这种方式安全输入密码
[root@ftx ~]# mysqldump -uroot -p -h192.168.195.136 --all-databases > all-2023-09-04-19-55.sql
Enter password: 
[root@ftx ~]# ls
all-2023-09-04-19-55.sql anaconda-ks.cfg



//备份ftx库的student表和course表
[root@ftx ~]# mysqldump -uroot -p -h192.168.195.136 ftx student course > table-2023-09-04-20-45.sql
Enter password: 
[root@ftx ~]# ls
all-2023-09-04-19-55.sql  table-2023-09-04-20-45.sql anaconda-ks.cfg


//备份ftx库
[root@ftx ~]# mysqldump -uroot -p -h192.168.195.136 --databases ftx > ftx-2023-09-04-23-16.sql
Enter password: 
[root@ftx ~]# ls
all-2023-09-04-19-55.sql   ftx-2023-09-04-23-16.sql   table-2023-09-04-20-45.sql anaconda-ks.cfg


//模拟误删ftx数据库
mysql> drop database ftx;
Query OK, 2 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FTX                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

1.3 mysql数据恢复

//恢复ftx数据库
[root@ftx ~]# ls
all-2023-09-04-19-55.sql anaconda-ks.cfg   ftx-2023-09-04-23-16.sql   table-2023-09-04-20-45.sql
[root@ftx ~]# mysql -uroot -p -h192.168.195.136 < ftx-2023-09-04-23-16.sql 
Enter password:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FTX                |
| ftx                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)



//恢复ftx数据库的student表和course表
mysql> use ftx;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> source table-2023-09-04-20-45.sql     //此操作在库里面进行执行
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

......

......

mysql> show tables;
+---------------+
| Tables_in_ftx |
+---------------+
| course        |
| student       |
+---------------+
2 rows in set (0.00 sec)

//同时我们也可以将备份的数据库中的表恢复到其他的数据库当中
首先我们新创建一个数据库
mysql> create database yyr;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FTX                |
| ftx                |
| mysql              |
| performance_schema |
| sys                |
| yyr                |
+--------------------+
7 rows in set (0.00 sec)

[root@ftx ~]# mysql -uroot -p yyr < table-2023-09-04-20-45.sql    //此操作在命令行执行
Enter password: 

mysql> use yyr
Database changed
mysql> show tables;
+---------------+
| Tables_in_yyr |
+---------------+
| course        |
| student       |
+---------------+
2 rows in set (0.00 sec)


//模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FTX                |
| ftx                |
| mysql              |
| performance_schema |
| sys                |
| yyr                |
+--------------------+
7 rows in set (0.00 sec)

在此之前我们新建了一个yyr的数据库，所以我们重新做一次完全备份
[root@ftx ~]# mysqldump -uroot -p -h192.168.195.136 --all-databases > all-databases.sql
Enter password: 
[root@ftx ~]# ls
all-2023-09-04-19-55.sql    ftx-2023-09-04-23-16.sql   all-databases.sql     table-2023-09-04-20-45.sql   anaconda-ks.cfg 

mysql> drop database FTX;
Query OK, 1 row affected (0.00 sec)

mysql> drop database ftx;
Query OK, 2 rows affected (0.01 sec)

mysql> drop database mysql;
Query OK, 31 rows affected, 2 warnings (0.01 sec)

mysql> drop database performance_schema;
Query OK, 88 rows affected, 2 warnings (0.00 sec)

mysql> drop database sys;
Query OK, 101 rows affected, 2 warnings (0.03 sec)

mysql> drop database yyr;
Query OK, 2 rows affected, 2 warnings (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

//恢复整个数据库
[root@localhost ~]# ls
all-201808131500.sql  table-201808131500.sql
anaconda-ks.cfg       wq-201808131500.sql
[root@ftx ~]# mysql -uroot -p -h192.168.195.136 < all-databases.sql 
Enter password: 
[root@ftx ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FTX                |
| ftx                |
| mysql              |
| yyr                |
+--------------------+

由于performance_schema和sys这两个库在备份的文件中没有，所以在恢复数据库的时候就不会恢复这两个库，想要恢复这两个库的话，可以初始化数据库，但我们那些有数据的数据库则又会删除，同时由于这两个库没有对我的数据造成任何影响，所以我们可以继续下面的操作

1.4 差异备份与恢复

1.4.1. mysql差异备份

开启MySQL服务器的二进制日志功能

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

server-id = 10        //设置服务器标识符
log-bin = mysql_bin   //开启二进制日志功能

[root@ftx ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS!

对数据库进行完全备份

[root@localhost ~]# mysql -uroot -pwangqing123!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wangqing           |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from wangqing;
+--------------------+
| Tables_in_wangqing |
+--------------------+
| runtime            |
| student            |
+--------------------+
2 rows in set (0.01 sec)

mysql> select * from wangqing.runtime;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | tom   |   10 |
|    2 | jerry |   30 |
+------+-------+------+
2 rows in set (0.01 sec)

mysql> select * from wangqing.student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)



//完全备份
[root@ftx ~]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202309051400.sql
Enter password: 
[root@ftx ~]# ls
all-2023-09-04-19-55.sql  anaconda-ks.cfg  ftx-2023-09-04-23-16.sql  table-2023-09-04-20-45.sql  all-202309051340.sql   all-202309051400.sql  all-databases.sql


//增加新内容
mysql> use yyr
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
mysql> show tables;
+---------------+
| Tables_in_yyr |
+---------------+
| course        |
| student       |
+---------------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | m    |    160 |         1 |
|  2 | Green  |   23 | m    |    158 |         2 |
|  3 | Henry  |   23 | f    |    185 |         1 |
|  4 | Jane   |   22 | m    |    162 |         3 |
|  5 | Jim    |   24 | f    |    175 |         2 |
|  6 | John   |   21 | f    |    172 |         4 |
|  7 | Lily   |   22 | m    |    165 |         4 |
|  8 | Susan  |   23 | m    |    170 |         5 |
|  9 | Thomas |   22 | f    |    178 |         5 |
| 10 | Tom    |   23 | f    |    165 |         5 |
| 11 | LiMing |   22 | m    |    180 |         7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)

mysql> insert student(name,age,sex,height,course_id) value('yyr',18,'f',160,8);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | m    |    160 |         1 |
|  2 | Green  |   23 | m    |    158 |         2 |
|  3 | Henry  |   23 | f    |    185 |         1 |
|  4 | Jane   |   22 | m    |    162 |         3 |
|  5 | Jim    |   24 | f    |    175 |         2 |
|  6 | John   |   21 | f    |    172 |         4 |
|  7 | Lily   |   22 | m    |    165 |         4 |
|  8 | Susan  |   23 | m    |    170 |         5 |
|  9 | Thomas |   22 | f    |    178 |         5 |
| 10 | Tom    |   23 | f    |    165 |         5 |
| 11 | LiMing |   22 | m    |    180 |         7 |
| 12 | yyr    |   18 | f    |    160 |         8 |
+----+--------+------+------+--------+-----------+
12 rows in set (0.00 sec)

mysql> update student set course_id = 6 where name = 'LiMing';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | m    |    160 |         1 |
|  2 | Green  |   23 | m    |    158 |         2 |
|  3 | Henry  |   23 | f    |    185 |         1 |
|  4 | Jane   |   22 | m    |    162 |         3 |
|  5 | Jim    |   24 | f    |    175 |         2 |
|  6 | John   |   21 | f    |    172 |         4 |
|  7 | Lily   |   22 | m    |    165 |         4 |
|  8 | Susan  |   23 | m    |    170 |         5 |
|  9 | Thomas |   22 | f    |    178 |         5 |
| 10 | Tom    |   23 | f    |    165 |         5 |
| 11 | LiMing |   22 | m    |    180 |         6 |
| 12 | yyr    |   18 | f    |    160 |         8 |
+----+--------+------+------+--------+-----------+
12 rows in set (0.00 sec)

1.4.2. mysql差异备份恢复

应用场景：误删数据

[root@ftx ~]# mysql -uroot -e 'drop database yyr;'
[root@ftx ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FTX                |
| ftx                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

注：当我们误删数据库后，应该立即设置读锁，不允许再有人往数据库当中写东西，因为在我们恢复数据库的时候是将数据库恢复到我们删除数据库前的时间，在此期间如果还有人在写入的话，那他写入的东西就会在我们恢复数据库的同时消失。

刷新创建新的二进制日志

[root@ftx ~]# ll /opt/data
total 123036
-rw-r-----. 1 mysql mysql       56 Sep  5 13:40 auto.cnf
-rw-------. 1 mysql mysql     1676 Sep  5 13:40 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Sep  5 13:40 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Sep  5 13:40 client-cert.pem
-rw-------. 1 mysql mysql     1680 Sep  5 13:40 client-key.pem
drwxr-x---. 2 mysql mysql       94 Sep  5 13:40 ftx
drwxr-x---. 2 mysql mysql       58 Sep  5 13:40 FTX
-rw-r-----. 1 mysql mysql    74003 Sep  5 14:09 ftx.err
-rw-r-----. 1 mysql mysql      486 Sep  5 13:52 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Sep  5 14:23 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Sep  5 14:23 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Sep  5 13:40 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep  5 14:09 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Sep  5 13:39 mysql
-rw-r-----. 1 mysql mysql      895 Sep  5 14:21 mysql_bin.000006  //在没有刷新二进制日志之前执行的所有动作都在这个文件里面
-rw-r-----. 1 mysql mysql       19 Sep  5 14:09 mysql_bin.index   //这个文件中写的是我们的日志都写到那个地方去了
-rw-r-----. 1 mysql mysql        5 Sep  5 13:53 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Sep  5 13:39 performance_schema
-rw-------. 1 mysql mysql     1680 Sep  5 13:40 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Sep  5 13:40 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Sep  5 13:40 server-cert.pem
-rw-------. 1 mysql mysql     1680 Sep  5 13:40 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Sep  5 13:39 sys

[root@ftx ~]# cat /opt/data/mysql_bin.index
./mysql_bin.000006

//刷新创建新的二进制日志
[root@ftx ~]# mysqladmin -uroot -p flush-logs
Enter password: 
[root@ftx ~]# ll /opt/data
total 123040
-rw-r-----. 1 mysql mysql       56 Sep  5 13:40 auto.cnf
-rw-------. 1 mysql mysql     1676 Sep  5 13:40 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Sep  5 13:40 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Sep  5 13:40 client-cert.pem
-rw-------. 1 mysql mysql     1680 Sep  5 13:40 client-key.pem
drwxr-x---. 2 mysql mysql       94 Sep  5 13:40 ftx
drwxr-x---. 2 mysql mysql       58 Sep  5 13:40 FTX
-rw-r-----. 1 mysql mysql    74003 Sep  5 14:09 ftx.err
-rw-r-----. 1 mysql mysql      486 Sep  5 13:52 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Sep  5 14:23 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Sep  5 14:23 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Sep  5 13:40 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep  5 14:09 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Sep  5 13:39 mysql
-rw-r-----. 1 mysql mysql      942 Sep  5 14:34 mysql_bin.000006
-rw-r-----. 1 mysql mysql      154 Sep  5 14:34 mysql_bin.000007  //刷新二进制日志之后则会生成一个新的这样的文件，我们后续的动作也都会存放在这个文件中
-rw-r-----. 1 mysql mysql       38 Sep  5 14:34 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Sep  5 13:53 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Sep  5 13:39 performance_schema
-rw-------. 1 mysql mysql     1680 Sep  5 13:40 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Sep  5 13:40 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Sep  5 13:40 server-cert.pem
-rw-------. 1 mysql mysql     1680 Sep  5 13:40 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Sep  5 13:39 sys

[root@ftx ~]# cat /opt/data/mysql_bin.index
./mysql_bin.000006
./mysql_bin.000007

恢复完全备份

[root@ftx ~]# mysql -uroot -p < all-202309051400.sql
Enter password: 
[root@ftx ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FTX                |
| ftx                |
| mysql              |
| performance_schema |
| sys                |
| yyr                |
+--------------------+
[root@ftx ~]# mysql -uroot -e 'show tables from yyr;'
+---------------+
| Tables_in_yyr |
+---------------+
| course        |
| student       |
+---------------+
[root@ftx ~]# mysql -uroot -e 'select * from yyr.course;'
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MYSQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
[root@ftx ~]# mysql -uroot -e 'select * from yyr.student;'
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | m    |    160 |         1 |
|  2 | Green  |   23 | m    |    158 |         2 |
|  3 | Henry  |   23 | f    |    185 |         1 |
|  4 | Jane   |   22 | m    |    162 |         3 |
|  5 | Jim    |   24 | f    |    175 |         2 |
|  6 | John   |   21 | f    |    172 |         4 |
|  7 | Lily   |   22 | m    |    165 |         4 |
|  8 | Susan  |   23 | m    |    170 |         5 |
|  9 | Thomas |   22 | f    |    178 |         5 |
| 10 | Tom    |   23 | f    |    165 |         5 |
| 11 | LiMing |   22 | m    |    180 |         7 |
+----+--------+------+------+--------+-----------+

恢复差异备份

[root@ftx ~]# ll /opt/data
total 123884
-rw-r-----. 1 mysql mysql       56 Sep  5 13:40 auto.cnf
-rw-------. 1 mysql mysql     1676 Sep  5 13:40 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Sep  5 13:40 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Sep  5 13:40 client-cert.pem
-rw-------. 1 mysql mysql     1680 Sep  5 13:40 client-key.pem
drwxr-x---. 2 mysql mysql       94 Sep  5 19:40 ftx
drwxr-x---. 2 mysql mysql       58 Sep  5 19:40 FTX
-rw-r-----. 1 mysql mysql    74277 Sep  5 19:39 ftx.err
-rw-r-----. 1 mysql mysql      486 Sep  5 13:52 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Sep  5 19:41 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Sep  5 19:41 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Sep  5 13:40 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep  5 14:09 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Sep  5 19:40 mysql
-rw-r-----. 1 mysql mysql      942 Sep  5 14:34 mysql_bin.000006
-rw-r-----. 1 mysql mysql   867770 Sep  5 19:40 mysql_bin.000007
-rw-r-----. 1 mysql mysql       38 Sep  5 14:34 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Sep  5 13:53 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Sep  5 13:39 performance_schema
-rw-------. 1 mysql mysql     1680 Sep  5 13:40 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Sep  5 13:40 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Sep  5 13:40 server-cert.pem
-rw-------. 1 mysql mysql     1680 Sep  5 13:40 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Sep  5 13:39 sys
drwxr-x---. 2 mysql mysql       94 Sep  5 19:40 yyr

//检查误删数据库的位置在什么地方
[root@ftx ~]# mysql -uroot -p12345678
mysql> show binlog events in 'mysql_bin.000006';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000006 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.39-log, Binlog ver: 4 |
| mysql_bin.000006 | 123 | Previous_gtids |        10 |         154 |                                       |
| mysql_bin.000006 | 154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000006 | 219 | Query          |        10 |         290 | BEGIN                                 |
| mysql_bin.000006 | 290 | Table_map      |        10 |         348 | table_id: 182 (yyr.student)           |
| mysql_bin.000006 | 348 | Write_rows     |        10 |         403 | table_id: 182 flags: STMT_END_F       |
| mysql_bin.000006 | 403 | Xid            |        10 |         434 | COMMIT /* xid=1062 */                 |
| mysql_bin.000006 | 434 | Anonymous_Gtid |        10 |         499 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000006 | 499 | Query          |        10 |         570 | BEGIN                                 |
| mysql_bin.000006 | 570 | Table_map      |        10 |         628 | table_id: 182 (yyr.student)           |
| mysql_bin.000006 | 628 | Update_rows    |        10 |         710 | table_id: 182 flags: STMT_END_F       |
| mysql_bin.000006 | 710 | Xid            |        10 |         741 | COMMIT /* xid=1064 */                 |
| mysql_bin.000006 | 741 | Anonymous_Gtid |        10 |         806 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000006 | 806 | Query          |        10 |         895 | drop database yyr                     |     //可以看到此行就是误删yyr数据库的动作
| mysql_bin.000006 | 895 | Rotate         |        10 |         942 | mysql_bin.000007;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)	

//使用mysqlbinlog恢复差异备份，此处我们要将执行动作恢复到执行删除数据库之前，也就是上表的806之前
[root@ftx ~]# mysqlbinlog --stop-position=806 /opt/data/mysql_bin.000006 | mysql -uroot -p12345678
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@ftx ~]# mysql -uroot -e 'select * from yyr.student;'
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | m    |    160 |         1 |
|  2 | Green  |   23 | m    |    158 |         2 |
|  3 | Henry  |   23 | f    |    185 |         1 |
|  4 | Jane   |   22 | m    |    162 |         3 |
|  5 | Jim    |   24 | f    |    175 |         2 |
|  6 | John   |   21 | f    |    172 |         4 |
|  7 | Lily   |   22 | m    |    165 |         4 |
|  8 | Susan  |   23 | m    |    170 |         5 |
|  9 | Thomas |   22 | f    |    178 |         5 |
| 10 | Tom    |   23 | f    |    165 |         5 |
| 11 | LiMing |   22 | m    |    180 |         6 |
| 12 | yyr    |   18 | f    |    160 |         8 |
+----+--------+------+------+--------+-----------+

//我们可以看到，表中数据恢复到了我们完全备份后再次修改的时候，证明恢复差异备份成功

在此之后我们的执行动作则都会存放在刷新的二进制日志里面

//可以通过此命令进行查询
mysql> show binlog events in 'mysql_bin.000007'\G

举例说明：
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FTX                |
| ftx                |
| mysql              |
| performance_schema |
| sys                |
| test               |
| yyr                |
+--------------------+
8 rows in set (0.00 sec)

mysql> show binlog events in 'mysql_bin.000007'\G
......
......
*************************** 472. row ***************************
   Log_name: mysql_bin.000007
        Pos: 868416
 Event_type: Query
  Server_id: 10
End_log_pos: 868510
       Info: create database test
472 rows in set (0.00 sec)

mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)

mysql> show binlog events in 'mysql_bin.000007'\G
......
......
*************************** 474. row ***************************
   Log_name: mysql_bin.000007
        Pos: 868575
 Event_type: Query
  Server_id: 10
End_log_pos: 868660
       Info: drop database test
474 rows in set (0.00 sec)

错误排查报告

[root@ftx ~]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202309051320.sql
Enter password: 
mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Table 'performance_schema.session_variables' doesn't exist (1146)

出现此类状况是因为我们将performance_schema这个库给删了

//解决方法：
初始化数据库
[root@ftx ~]# service mysqld stop  ##首先关闭服务
Shutting down MySQL.. SUCCESS!

[root@ftx ~]# cd /opt/data/       ##进入到我们存放数据库的目录当中
[root@ftx data]# ls
auto.cnf         client-key.pem  ib_buffer_pool  ibtmp1            mysql_bin.index  server-cert.pem
ca-key.pem       ftx             ibdata1         mysql             mysql.pid        server-key.pem
ca.pem           FTX             ib_logfile0     mysql_bin.000001  private_key.pem  yyr
client-cert.pem  ftx.err         ib_logfile1     mysql_bin.000002  public_key.pem
[root@ftx data]# mkdir /opt/backup     ##创建一个新目录
[root@ftx data]# mv * /opt/backup/     ##将我们之前恢复的数据全部移到新建的目录中存放
[root@ftx data]# ls
[root@ftx data]# cd
[root@ftx ~]# mysqld --initialize --user mysql --datadir /opt/data   ##初始化数据库
2023-09-05T05:39:00.864770Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-09-05T05:39:01.408258Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-09-05T05:39:01.491798Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-09-05T05:39:01.565573Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 849294ee-4bae-11ee-a3c9-000c29d9f7e8.
2023-09-05T05:39:01.566011Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-09-05T05:39:01.736074Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-09-05T05:39:01.736101Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-09-05T05:39:01.736420Z 0 [Warning] CA certificate ca.pem is self signed.
2023-09-05T05:39:01.849120Z 1 [Note] A temporary password is generated for root@localhost: rdiA_7lB=Sp;    ##此处生成了一个临时密码，然而我们之前恢复的文件数据中，有我们设置好了的密码，所以我们只需要将我们之前恢复的数据全部覆盖到/opt/data目录中就可以使用我们之前设置的密码登录了
[root@ftx ~]# cd /opt/data
[root@ftx data]# ls
auto.cnf    client-cert.pem  ibdata1      mysql             performance_schema  server-cert.pem
ca-key.pem  client-key.pem   ib_logfile0  mysql_bin.000001  private_key.pem     server-key.pem
ca.pem      ib_buffer_pool   ib_logfile1  mysql_bin.index   public_key.pem      sys
[root@ftx data]# \cp -r /opt/backup/* .   ##cp前加上\是避免需要我们手动确认覆盖相同的文件
[root@ftx data]# ls
auto.cnf         client-key.pem  ib_buffer_pool  ibtmp1            mysql_bin.index     public_key.pem   yyr
ca-key.pem       ftx             ibdata1         mysql             mysql.pid           server-cert.pem
ca.pem           FTX             ib_logfile0     mysql_bin.000001  performance_schema  server-key.pem
client-cert.pem  ftx.err         ib_logfile1     mysql_bin.000002  private_key.pem     sys

[root@ftx ~]# service mysqld start
Starting MySQL. ERROR! The server quit without updating PID file (/opt/data/mysql.pid).
//此时发现服务无法启动

原因在于/opt/data/目录下的某些文件属于root，而并非mysql
[root@ftx ~]# ll /opt/data
total 123024
-rw-r-----. 1 mysql mysql       56 Sep  5 13:40 auto.cnf
-rw-------. 1 mysql mysql     1676 Sep  5 13:40 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Sep  5 13:40 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Sep  5 13:40 client-cert.pem
-rw-------. 1 mysql mysql     1680 Sep  5 13:40 client-key.pem
drwxr-x---. 2 root  root        94 Sep  5 13:40 ftx
drwxr-x---. 2 root  root        58 Sep  5 13:40 FTX
-rw-r-----. 1 root  root     61409 Sep  5 13:40 ftx.err
-rw-r-----. 1 mysql mysql      887 Sep  5 13:40 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Sep  5 13:40 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Sep  5 13:40 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Sep  5 13:40 ib_logfile1
-rw-r-----. 1 root  root  12582912 Sep  5 13:40 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Sep  5 13:39 mysql
-rw-r-----. 1 mysql mysql      201 Sep  5 13:40 mysql_bin.000001
-rw-r-----. 1 root  root       154 Sep  5 13:40 mysql_bin.000002
-rw-r-----. 1 mysql mysql       38 Sep  5 13:40 mysql_bin.index
-rw-r-----. 1 root  root         5 Sep  5 13:40 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Sep  5 13:39 performance_schema
-rw-------. 1 mysql mysql     1680 Sep  5 13:40 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Sep  5 13:40 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Sep  5 13:40 server-cert.pem
-rw-------. 1 mysql mysql     1680 Sep  5 13:40 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Sep  5 13:39 sys
drwxr-x---. 2 root  root        94 Sep  5 13:40 yyr

解决方法：
将/opt/data/目录下的所有文件的属主、属组都改为mysql
[root@ftx ~]# chown -R mysql.mysql /opt/data/
[root@ftx ~]# service mysqld start
Starting MySQL. SUCCESS! 
[root@ftx ~]# ss -antl
State        Recv-Q        Send-Q                  Local Address:Port                 Peer Address:Port        
LISTEN       0             128                           0.0.0.0:22                        0.0.0.0:*           
LISTEN       0             128                              [::]:22                           [::]:*           
LISTEN       0             80                                  *:3306                            *:* 

mysql> show databases;    //再次查看，数据库全部恢复
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FTX                |
| ftx                |
| mysql              |
| performance_schema |
| sys                |
| yyr                |
+--------------------+
7 rows in set (0.00 sec)