MySQL热备份（实时备份）及恢复

备份是数据安全的最后一道防线，对于任何数据丢失的场景，备份虽然不一定能恢复百分之百的数据(取决于备份周期)，但至少能将损失降到最低。

数据丢失的场景举例：

• 人为操作失误造成某些数据被误操作

• 软件 BUG 造成部分数据或全部数据丢失

• 硬件故障造成数据库部分数据或全部数据丢失

• 安全漏洞被入侵数据恶意破坏

1.1 衡量备份恢复重要指标

衡量备份恢复有两个重要的指标：

• 恢复点目标(RPO)

• 恢复点目标是指数据能恢复到什么程度

• 恢复时间目标(RTO)

• 恢复时间目标是指数据恢复需要多长时间

1.2 数据库备份分类

数据库备份方式分很多种，从物理与逻辑的角度来看，备份可分为：

• 物理备份：指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份又可以分为脱机备份(冷备份)和联机备份(热备份)。

• 冷备份:在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性。

• 热备份:在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件。

• 逻辑备份：指对数据库逻辑组件(如"表"等数据库对象)的备份。

数据库的备份从策略角度来看，备份可分为以下几类：

• 完全备份：每次对数据进行完整的备份。可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和空间，所以，做一次完全备份的周期要长些。

• 差异备份：备份那些自从上次完全备份之后被修改过的文件，只备份数据库部分的内容。它比最初的完全备份小，因为只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快。

• 增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。

2、MySQL备份工具

2.1 mysqldump备份工具

MySQL官方提供了Mysqldump逻辑备份工具，它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert语句，当我们需要还原这些数据时，只要执行这些insert语句，即可将对应的数据还原。

# mysqldump命令语法：mysqldump [选项] 数据库名 [表名] > 备份文件名# 数据恢复mysql [选项] 数据库名 [表名] > 备份文件名source 备份文件名

选项列表：

2.2 Xtrabackup备份工具

XtraBackup(PXB)工具是Percona公司用perl语言开发的一个用于 MySQL数据库物理热备的备份工具，能够非常快速地备份与恢复mysql数据库，且支持在线热备份（备份时不影响数据读写）。

Xtrabackup中包含两个工具：

• xtrabackup ：用于热备份innodb,xtradb引擎表的工具，不能备份其他表。

• innobackupex ：提供了用于myisam(会锁表)和innodb引擎，及混合使用引擎备份的能力。

Xtrabackup的优点：

• 备份速度快，物理备份可靠

• 备份过程不会打断正在执行的事务（无需锁表）

• 能够基于压缩等功能节约磁盘空间和流量

• 自动备份校验

• 还原速度快

• 可以流传，将备份传输到另外一台机器上

• 在不增加服务器负载的情况备份数据

• 支持增量备份

# 语法：xtrabackup | innobackupex  [--defaults-file=#] [--backup | --prepare | --copy-back | --move-back] [OPTIONS]

Xtrabackup工具备份常用选项：

# 下载安装Xtrabackup备份工具[root@localhost ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.22-15/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.22-15.1.el8.x86_64.rpm[root@localhost ~]#dnf -y localinstall percona-xtrabackup-80-8.0.22-15.1.el8.x86_64.rpm # 完全备份[root@localhost ~]#trabackup --backup --databases=zsl --target-dir=/backup/xtrabackup/ -uroot -pPasswd123!# 恢复阶段：准备备份，恢复之前需要准备备份[root@localhost ~]#xtrabackup --prepare --target-dir=/backup/xtrabackup/# 恢复数据（保证要还原的数据库服务器的data目录为空）[root@localhost ~]#rm -rf /var/lib/mysql/*		#模拟数据丢失[root@localhost ~]#xtrabackup --copy-back --target-dir=/backup/xtrabackup/[root@localhost ~]#chown -R mysql:mysql /var/lib/mysql    [root@localhost ~]#systemctl restart mysqld

增量备份：

# 先创建完全备份[root@localhost ~]#xtrabackup --backup --databases=test --target-dir=/backup/xtrabackup/ -uroot -p123456# 创建第一次增量备份[root@localhost ~]#xtrabackup --backup --databases=test --target-dir=/backup/inc1/ --incremental-basedir=/backup/xtrabackup/  -uroot -p123456# 创建第二次增量备份[root@localhost ~]#xtrabackup --backup --databases=test --target-dir=/backup/inc2/ --incremental-basedir=/backup/inc1/ -uroot -p123456# 恢复阶段：准备全量备份[root@localhost ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/xtrabackup/# 准备第一次增量备份，将第一次增量备份与全备合并[root@localhost ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/xtrabackup/ --incremental-dir=/backup/inc1# 准备第二次增量备份，将第二次增量备份与全备合并[root@localhost ~]#xtrabackup --prepare --target-dir=/backup/xtrabackup/ --incremental-dir=/backup/inc2/# 恢复数据（保证要还原的数据库服务器的data目录为空）[root@localhost ~]#rm -rf /var/lib/mysql/*		# 模拟数据丢失[root@localhost ~]#xtrabackup --copy-back --target-dir=/backup/xtrabackup/[root@localhost ~]#chown -R mysql:mysql /var/lib/mysql    [root@localhost ~]#systemctl restart mysqld

2.3 Mariabackup备份工具

Mariabackup是MariaDB提供的一个开源工具，用于对InnoDB，Aria和MyISAM表进行物理在线备份。这个工具基于Percona的XtraBackup解决方案。

# 语法：mariabackup [--defaults-file=#] [--backup | --prepare | --copy-back | --move-back] [OPTIONS]# 常用选项- -backup									#备份数据库- -copy-back							 #将备份还原到数据目录- -defaults-file					 #定义包含默认配置的文件的路径-H, --host								 #定义要备份的MariaDB服务器的主机- -incremental-basedir			 #定义是否要增加备份- -incremental-dir					#定义是否要增加准备好的备份- -move-back								#将备份还原到数据目录-p, --password							#定义用于连接MariaDB Server的密码-P, --port									#定义要连接的服务器端口- -prepare									#准备现有备份以还原到MariaDB服务器-S, --socket								#定义用于连接本地数据库的套接字- -user											#定义用于连接MariaDB服务器的用户名- -version-check						 #启用版本检查- -version									#打印版本信息

完全备份与恢复：

# 全量备份mariabackup --backup --target-dir /root/mariadb-backup-`date +%F` --user root --password "1"# 恢复阶段：准备全备数据，恢复之前必须准备mariabackup --prepare --target-dir /root/mariadb-backup-2022-07-28/ --user root --password "1"# 恢复数据（保证要还原的数据库服务器的data目录为空）rm -rf /var/lib/mysql/*		#模拟数据丢失mariabackup --copy-back --target-dir /root/mariadb-backup-2022-07-28/ --user root --password "1"chown -R mysql.mysql /var/lib/mysql/systemctl restart mariadb.service

增量备份与恢复：

# 全量备份，增量备份前需要先进行一次全量备份mariabackup --backup --target-dir /root/mariadb-backup-`date +%F` --user root --password "1"# 基于全量备份，进行第一次增量备份mariabackup --backup --target-dir /root/mariadb-backup-`date +%F`-inc1 --incremental-basedir /root/mariadb-backup-2022-07-28/ --user root --password "1"# 恢复阶段：现在有2个备份，一个是全备一个是增量备份# 准备全备数据mariabackup --prepare --apply-log-only --target-dir /root/mariadb-backup-2022-07-28/ --user root --password "1"# 将增量备份与全备合并mariabackup --prepare --target-dir /root/mariadb-backup-2022-07-28/ --incremental-dir /root/mariadb-backup-2022-07-28-inc1/ --user root --password "1"# 如果有多次增量备份，按照增量备份顺序依次将增量备份与全备合并，记得加--apply-log-only选项，最后一次增量备份不需要添加该选项# 恢复数据（保证要还原的数据库服务器的data目录为空）rm -rf /var/lib/mysql/*		#模拟数据丢失mariabackup --copy-back --target-dir /root/mariadb-backup-2022-07-28/ --user root --password "1"chown -R mysql.mysql /var/lib/mysql/systemctl restart mariadb.service

3、mysql全量备份与恢复实例演示

3.1 mysql全量备份

# 备份整个数据库(全备)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || zsl                |+--------------------+5 rows in set (0.00 sec)mysql> use zsl;Database changedmysql> show tables;+---------------+| Tables_in_zsl |+---------------+| course        || students      || teacher       |+---------------+3 rows in set (0.00 sec)[root@localhost ~]# mysqldump -uroot -pPasswd123! --all-databases > all-database-$(date '+%F-%H-%M-%S').sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@localhost ~]# lsall-database-2022-07-29-10-45-04.sql  anaconda-ks.cfg# 备份zsl库的course表、students表和teacher表[root@localhost ~]# mysqldump -uroot -pPasswd123! zsl course students teacher > table-$(date '+%F-%H-%M-%S').sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@localhost ~]# lsall-database-2022-07-29-10-45-04.sql  anaconda-ks.cfg  table-2022-07-29-10-45-27.sql# 备份zsl库[root@localhost ~]# mysqldump -uroot -pPasswd123! --databases zsl  > zsl-database-$(date '+%F-%H-%M-%S').sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@localhost ~]# lsall-database-2022-07-29-10-45-04.sql  table-2022-07-29-10-45-27.sqlanaconda-ks.cfg                       zsl-database-2022-07-29-10-47-47.sql

3.2 mysql数据恢复

# 模拟误删zsl数据库mysql> drop database zsl;Query OK, 3 rows affected (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)# 恢复zsl数据库##方法一：系统行命令数据恢复[root@localhost ~]# mysql -uroot -pPasswd123! < zsl-database-2022-07-29-10-47-47.sql mysql: [Warning] Using a password on the command line interface can be insecure.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || zsl                |+--------------------+5 rows in set (0.00 sec)方法二：source数据恢复mysql> source zsl-database-2022-07-29-10-47-47.sql;......Query OK, 5 rows affected (0.01 sec)Records: 5  Duplicates: 0  Warnings: 0Query 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 databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || zsl                |+--------------------+5 rows in set (0.00 sec)# 模拟删除zsl库的course表、students表mysql> drop table course;Query OK, 0 rows affected (0.00 sec)mysql> drop table students;Query OK, 0 rows affected (0.01 sec)mysql> show tables;+---------------+| Tables_in_zsl |+---------------+| teacher       |+---------------+1 row in set (0.00 sec)# 恢复zsl库的course表、students表mysql> source table-2022-07-29-10-45-27.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)Query OK, 0 rows affected (0.00 sec)......Query OK, 0 rows affected (0.00 sec)mysql> show tables;+---------------+| Tables_in_zsl |+---------------+| course        || students      || teacher       |+---------------+3 rows in set (0.00 sec)# 模拟删除整个数据库mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || zsl                |+--------------------+5 rows in set (0.00 sec)mysql> drop database zsl;Query OK, 3 rows affected (0.00 sec)mysql> drop database sys;Query OK, 101 rows affected (0.02 sec)mysql> drop database mysql;Query OK, 31 rows affected, 2 warnings (0.03 sec)mysql> drop database performance_schema;Query OK, 87 rows affected, 2 warnings (0.01 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema |+--------------------+1 row in set (0.01 sec)# 恢复整个数据库[root@localhost ~]# mysql -uroot -pPasswd123! < all-database-2022-07-29-10-45-04.sql mysql: [Warning] Using a password on the command line interface can be insecure.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || zsl                |+--------------------+3 rows in set (0.00 sec)

4、mysql差异备份与恢复实例演示

4.1 mysql差异备份

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

[root@localhost ~]# vim /etc/my.cnf [root@localhost ~]# cat /etc/my.cnf [mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.piduser = mysqlskip-name-resolvesql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONserver-id=1					      #设置服务器标识符log-bin=mysql_bin 				  #开启二进制日志功能[root@localhost ~]# service mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!

对数据库进行完全备份

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || zsl                |+--------------------+5 rows in set (0.00 sec)mysql> show tables from zsl;+---------------+| Tables_in_zsl |+---------------+| course        || students      || teacher       |+---------------+3 rows in set (0.00 sec)mysql> select * from zsl.course;+----+-------------+| id | course_name |+----+-------------+|  1 | HTML        ||  2 | JAVA        ||  3 | MySQL       ||  4 | Python      ||  5 | C++         |+----+-------------+5 rows in set (0.01 sec)mysql> select * from zsl.students;+----+-------+------+------+--------+-----------+| id | name  | age  | sex  | height | course_id |+----+-------+------+------+--------+-----------+|  1 | meng  |   25 | 女   |    160 |         1 ||  2 | ke    |   22 | 男   |    180 |         3 ||  3 | yang  |   18 | 男   |    175 |         2 ||  4 | ding  |   19 | 女   |    165 |         4 ||  5 | zhong |   20 | 男   |    165 |         5 |+----+-------+------+------+--------+-----------+5 rows in set (0.00 sec)mysql> select * from zsl.teacher;+----+------+------+------+--------+-----------+| id | name | age  | sex  | height | course_id |+----+------+------+------+--------+-----------+|  1 | aa   |   25 | 女   |    160 |         1 ||  2 | bb   |   22 | 男   |    180 |         3 ||  3 | cc   |   18 | 男   |    175 |         2 ||  4 | dd   |   19 | 女   |    165 |         4 ||  5 | ee   |   20 | 男   |    165 |         5 |+----+------+------+------+--------+-----------+5 rows in set (0.00 sec)# 开始完全备份[root@localhost ~]# mysqldump -uroot -pPasswd123! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-database-$(date '+%F-%H-%M-%S').sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@localhost ~]# lltotal 1732-rw-r--r--. 1 root root 878539 Jul 29 10:45 all-database-2022-07-29-10-45-04.sql-rw-r--r--. 1 root root 878710 Jul 29 11:21 all-database-2022-07-29-11-21-10.sql-rw-------. 1 root root   1097 Jul 21 18:41 anaconda-ks.cfg-rw-r--r--. 1 root root   3726 Jul 29 10:45 table-2022-07-29-10-45-27.sql-rw-r--r--. 1 root root   3862 Jul 29 10:47 zsl-database-2022-07-29-10-47-47.sql# 添加或删减内容mysql> select * from course;+----+-------------+| id | course_name |+----+-------------+|  1 | HTML        ||  2 | JAVA        ||  3 | MySQL       ||  5 | C++         ||  6 | wuli        ||  7 | huaxue      ||  8 | shuxue      ||  9 | yuwen       |+----+-------------+8 rows in set (0.00 sec)mysql> select * from students;+----+-------+------+------+--------+-----------+| id | name  | age  | sex  | height | course_id |+----+-------+------+------+--------+-----------+|  1 | meng  |   25 | 女   |    160 |         1 ||  2 | ke    |   22 | 男   |    180 |         3 ||  3 | yang  |   18 | 男   |    175 |         2 ||  4 | ding  |   19 | 女   |    165 |         4 ||  5 | zhong |   20 | 男   |    165 |         5 |+----+-------+------+------+--------+-----------+5 rows in set (0.00 sec)mysql> select * from teacher;+----+------+------+------+--------+-----------+| id | name | age  | sex  | height | course_id |+----+------+------+------+--------+-----------+|  1 | aa   |   25 | 女   |    160 |         1 ||  2 | bb   |   22 | 男   |    180 |         3 ||  3 | cc   |   18 | 男   |    175 |         2 ||  4 | dd   |   19 | 女   |    165 |         4 ||  5 | ee   |   20 | 男   |    165 |         5 ||  6 | qq   |   20 | 男   |    156 |         2 ||  7 | gg   |   60 | 男   |    186 |         3 ||  8 | ff   |   20 | 女   |    185 |         2 ||  9 | rr   |   15 | 男   |    189 |         9 |+----+------+------+------+--------+-----------+9 rows in set (0.00 sec)

4.2 mysql差异备份数据恢复

# 模拟误删数据[root@localhost ~]# mysql -uroot -pPasswd123! -e 'drop database zsl;'mysql: [Warning] Using a password on the command line interface can be insecure.[root@localhost ~]# mysql -uroot -pPasswd123! -e 'show databases;'mysql: [Warning] Using a password on the command line interface can be insecure.+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+# 由上可以看到zsl这个数据库已被删除# 刷新创建新的二进制日志[root@localhost ~]# ll /opt/data/total 122984-rw-r-----. 1 mysql mysql       56 Jul 29 02:43 auto.cnf-rw-------. 1 mysql mysql     1680 Jul 29 02:43 ca-key.pem-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 ca.pem-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 client-cert.pem-rw-------. 1 mysql mysql     1680 Jul 29 02:43 client-key.pem-rw-r-----. 1 mysql mysql      669 Jul 29 11:12 ib_buffer_pool-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:39 ibdata1-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:39 ib_logfile0-rw-r-----. 1 mysql mysql 50331648 Jul 29 02:43 ib_logfile1-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:21 ibtmp1-rw-r-----. 1 mysql mysql    20346 Jul 29 11:12 localhost.localdomain.errdrwxr-x---. 2 mysql mysql     4096 Jul 29 11:10 mysql-rw-r-----. 1 mysql mysql     3913 Jul 29 11:39 mysql_bin.000003-rw-r-----. 1 mysql mysql       19 Jul 29 11:21 mysql_bin.index-rw-r-----. 1 mysql mysql        5 Jul 29 11:12 mysql.pid-rw-r--r--. 1 root  root         6 Jul 29 11:10 mysql_upgrade_infodrwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 performance_schema-rw-------. 1 mysql mysql     1676 Jul 29 02:43 private_key.pem-rw-r--r--. 1 mysql mysql      452 Jul 29 02:43 public_key.pem-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 server-cert.pem-rw-------. 1 mysql mysql     1680 Jul 29 02:43 server-key.pemdrwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 sys[root@localhost ~]#  mysqladmin -uroot -pPasswd123! flush-logsmysqladmin: [Warning] Using a password on the command line interface can be insecure.[root@localhost ~]# ll /opt/data/total 122988-rw-r-----. 1 mysql mysql       56 Jul 29 02:43 auto.cnf-rw-------. 1 mysql mysql     1680 Jul 29 02:43 ca-key.pem-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 ca.pem-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 client-cert.pem-rw-------. 1 mysql mysql     1680 Jul 29 02:43 client-key.pem-rw-r-----. 1 mysql mysql      669 Jul 29 11:12 ib_buffer_pool-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:41 ibdata1-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:41 ib_logfile0-rw-r-----. 1 mysql mysql 50331648 Jul 29 02:43 ib_logfile1-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:21 ibtmp1-rw-r-----. 1 mysql mysql    20346 Jul 29 11:12 localhost.localdomain.errdrwxr-x---. 2 mysql mysql     4096 Jul 29 11:10 mysql-rw-r-----. 1 mysql mysql     3960 Jul 29 11:41 mysql_bin.000003-rw-r-----. 1 mysql mysql      154 Jul 29 11:41 mysql_bin.000004-rw-r-----. 1 mysql mysql       38 Jul 29 11:41 mysql_bin.index-rw-r-----. 1 mysql mysql        5 Jul 29 11:12 mysql.pid-rw-r--r--. 1 root  root         6 Jul 29 11:10 mysql_upgrade_infodrwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 performance_schema-rw-------. 1 mysql mysql     1676 Jul 29 02:43 private_key.pem-rw-r--r--. 1 mysql mysql      452 Jul 29 02:43 public_key.pem-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 server-cert.pem-rw-------. 1 mysql mysql     1680 Jul 29 02:43 server-key.pemdrwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 sys# 恢复完全备份[root@localhost ~]# mysql -uroot -pPasswd123! < all-database-2022-07-29-11-21-10.sql mysql: [Warning] Using a password on the command line interface can be insecure.[root@localhost ~]# mysql -uroot -pPasswd123! -e 'show databases;'mysql: [Warning] Using a password on the command line interface can be insecure.+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || zsl                |+--------------------+[root@localhost ~]# mysql -uroot -pPasswd123! -e 'show tables from zsl;'mysql: [Warning] Using a password on the command line interface can be insecure.+---------------+| Tables_in_zsl |+---------------+| course        || students      || teacher       |+---------------+[root@localhost ~]# mysql -uroot -pPasswd123! -e 'select * from zsl.course;'mysql: [Warning] Using a password on the command line interface can be insecure.+----+-------------+| id | course_name |+----+-------------+|  1 | HTML        ||  2 | JAVA        ||  3 | MySQL       ||  4 | Python      ||  5 | C++         |+----+-------------+[root@localhost ~]# mysql -uroot -pPasswd123! -e 'select * from zsl.students;'mysql: [Warning] Using a password on the command line interface can be insecure.+----+-------+------+------+--------+-----------+| id | name  | age  | sex  | height | course_id |+----+-------+------+------+--------+-----------+|  1 | meng  |   25 | 女   |    160 |         1 ||  2 | ke    |   22 | 男   |    180 |         3 ||  3 | yang  |   18 | 男   |    175 |         2 ||  4 | ding  |   19 | 女   |    165 |         4 ||  5 | zhong |   20 | 男   |    165 |         5 |+----+-------+------+------+--------+-----------+[root@localhost ~]# mysql -uroot -pPasswd123! -e 'select * from zsl.teacher;'mysql: [Warning] Using a password on the command line interface can be insecure.+----+------+------+------+--------+-----------+| id | name | age  | sex  | height | course_id |+----+------+------+------+--------+-----------+|  1 | aa   |   25 | 女   |    160 |         1 ||  2 | bb   |   22 | 男   |    180 |         3 ||  3 | cc   |   18 | 男   |    175 |         2 ||  4 | dd   |   19 | 女   |    165 |         4 ||  5 | ee   |   20 | 男   |    165 |         5 |+----+------+------+------+--------+-----------+# 恢复差异备份[root@localhost ~]# ll /opt/data/total 189544-rw-r-----. 1 mysql mysql       56 Jul 29 02:43 auto.cnf-rw-------. 1 mysql mysql     1680 Jul 29 02:43 ca-key.pem-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 ca.pem-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 client-cert.pem-rw-------. 1 mysql mysql     1680 Jul 29 02:43 client-key.pem-rw-r-----. 1 mysql mysql      669 Jul 29 11:12 ib_buffer_pool-rw-r-----. 1 mysql mysql 79691776 Jul 29 11:47 ibdata1-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:47 ib_logfile0-rw-r-----. 1 mysql mysql 50331648 Jul 29 02:43 ib_logfile1-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:21 ibtmp1-rw-r-----. 1 mysql mysql    20346 Jul 29 11:12 localhost.localdomain.errdrwxr-x---. 2 mysql mysql     4096 Jul 29 11:45 mysql-rw-r-----. 1 mysql mysql     3960 Jul 29 11:41 mysql_bin.000003-rw-r-----. 1 mysql mysql   859354 Jul 29 11:45 mysql_bin.000004-rw-r-----. 1 mysql mysql       38 Jul 29 11:41 mysql_bin.index-rw-r-----. 1 mysql mysql        5 Jul 29 11:12 mysql.pid-rw-r--r--. 1 root  root         6 Jul 29 11:10 mysql_upgrade_infodrwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 performance_schema-rw-------. 1 mysql mysql     1676 Jul 29 02:43 private_key.pem-rw-r--r--. 1 mysql mysql      452 Jul 29 02:43 public_key.pem-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 server-cert.pem-rw-------. 1 mysql mysql     1680 Jul 29 02:43 server-key.pemdrwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 sysdrwxr-x---. 2 mysql mysql      134 Jul 29 11:45 zsl# 检查误删数据库的位置在什么地方mysql> show binlog events in 'mysql_bin.000003';+------------------+------+----------------+-----------+-------------+---------------------------------------+| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |+------------------+------+----------------+-----------+-------------+---------------------------------------+| mysql_bin.000003 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.37-log, Binlog ver: 4 || mysql_bin.000003 |  123 | Previous_gtids |         1 |         154 |                                       || mysql_bin.000003 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  || mysql_bin.000003 |  219 | Query          |         1 |         290 | BEGIN                                 || mysql_bin.000003 |  290 | Table_map      |         1 |         349 | table_id: 180 (zsl.teacher)           || mysql_bin.000003 |  349 | Write_rows     |         1 |         510 | table_id: 180 flags: STMT_END_F       || mysql_bin.000003 |  510 | Xid            |         1 |         541 | COMMIT /* xid=988 */                  || mysql_bin.000003 |  541 | Anonymous_Gtid |         1 |         606 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  || mysql_bin.000003 |  606 | Query          |         1 |         677 | BEGIN                                 || mysql_bin.000003 |  677 | Table_map      |         1 |         728 | table_id: 178 (zsl.course)            || mysql_bin.000003 |  728 | Write_rows     |         1 |         808 | table_id: 178 flags: STMT_END_F       || mysql_bin.000003 |  808 | Xid            |         1 |         839 | COMMIT /* xid=989 */                  || mysql_bin.000003 |  839 | Anonymous_Gtid |         1 |         904 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  || mysql_bin.000003 |  904 | Query          |         1 |         975 | BEGIN                                 || mysql_bin.000003 |  975 | Table_map      |         1 |        1034 | table_id: 180 (zsl.teacher)           || mysql_bin.000003 | 1034 | Write_rows     |         1 |        1201 | table_id: 180 flags: STMT_END_F       || mysql_bin.000003 | 1201 | Xid            |         1 |        1232 | COMMIT /* xid=990 */                  || mysql_bin.000003 | 1232 | Anonymous_Gtid |         1 |        1297 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  || mysql_bin.000003 | 1297 | Query          |         1 |        1368 | BEGIN                                 || mysql_bin.000003 | 1368 | Table_map      |         1 |        1427 | table_id: 180 (zsl.teacher)           || mysql_bin.000003 | 1427 | Delete_rows    |         1 |        1483 | table_id: 180 flags: STMT_END_F       || mysql_bin.000003 | 1483 | Xid            |         1 |        1514 | COMMIT /* xid=994 */                  || mysql_bin.000003 | 1514 | Anonymous_Gtid |         1 |        1579 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  || mysql_bin.000003 | 1579 | Query          |         1 |        1650 | BEGIN                                 || mysql_bin.000003 | 1650 | Table_map      |         1 |        1709 | table_id: 180 (zsl.teacher)           || mysql_bin.000003 | 1709 | Delete_rows    |         1 |        1765 | table_id: 180 flags: STMT_END_F       || mysql_bin.000003 | 1765 | Xid            |         1 |        1796 | COMMIT /* xid=995 */                  || mysql_bin.000003 | 1796 | Anonymous_Gtid |         1 |        1861 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  || mysql_bin.000003 | 1861 | Query          |         1 |        1932 | BEGIN                                 || mysql_bin.000003 | 1932 | Table_map      |         1 |        1991 | table_id: 180 (zsl.teacher)           || mysql_bin.000003 | 1991 | Delete_rows    |         1 |        2048 | table_id: 180 flags: STMT_END_F       || mysql_bin.000003 | 2048 | Xid            |         1 |        2079 | COMMIT /* xid=996 */                  || mysql_bin.000003 | 2079 | Anonymous_Gtid |         1 |        2144 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  || mysql_bin.000003 | 2144 | Query          |         1 |        2215 | BEGIN                                 || mysql_bin.000003 | 2215 | Table_map      |         1 |        2274 | table_id: 180 (zsl.teacher)           || mysql_bin.000003 | 2274 | Delete_rows    |         1 |        2331 | table_id: 180 flags: STMT_END_F       || mysql_bin.000003 | 2331 | Xid            |         1 |        2362 | COMMIT /* xid=997 */                  || mysql_bin.000003 | 2362 | Anonymous_Gtid |         1 |        2427 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  || mysql_bin.000003 | 2427 | Query          |         1 |        2498 | BEGIN                                 || mysql_bin.000003 | 2498 | Table_map      |         1 |        2557 | table_id: 180 (zsl.teacher)           || mysql_bin.000003 | 2557 | Delete_rows    |         1 |        2614 | table_id: 180 flags: STMT_END_F       || mysql_bin.000003 | 2614 | Xid            |         1 |        2645 | COMMIT /* xid=998 */                  || mysql_bin.000003 | 2645 | Anonymous_Gtid |         1 |        2710 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  || mysql_bin.000003 | 2710 | Query          |         1 |        2781 | BEGIN                                 || mysql_bin.000003 | 2781 | Table_map      |         1 |        2840 | table_id: 180 (zsl.teacher)           || mysql_bin.000003 | 2840 | Delete_rows    |         1 |        2897 | table_id: 180 flags: STMT_END_F       || mysql_bin.000003 | 2897 | Xid            |         1 |        2928 | COMMIT /* xid=999 */                  || mysql_bin.000003 | 2928 | Anonymous_Gtid |         1 |        2993 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  || mysql_bin.000003 | 2993 | Query          |         1 |        3064 | BEGIN                                 || mysql_bin.000003 | 3064 | Table_map      |         1 |        3123 | table_id: 180 (zsl.teacher)           || mysql_bin.000003 | 3123 | Delete_rows    |         1 |        3180 | table_id: 180 flags: STMT_END_F       || mysql_bin.000003 | 3180 | Xid            |         1 |        3211 | COMMIT /* xid=1000 */                 || mysql_bin.000003 | 3211 | Anonymous_Gtid |         1 |        3276 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  || mysql_bin.000003 | 3276 | Query          |         1 |        3347 | BEGIN                                 || mysql_bin.000003 | 3347 | Table_map      |         1 |        3406 | table_id: 180 (zsl.teacher)           || mysql_bin.000003 | 3406 | Delete_rows    |         1 |        3463 | table_id: 180 flags: STMT_END_F       || mysql_bin.000003 | 3463 | Xid            |         1 |        3494 | COMMIT /* xid=1001 */                 || mysql_bin.000003 | 3494 | Anonymous_Gtid |         1 |        3559 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  || mysql_bin.000003 | 3559 | Query          |         1 |        3630 | BEGIN                                 || mysql_bin.000003 | 3630 | Table_map      |         1 |        3681 | table_id: 178 (zsl.course)            || mysql_bin.000003 | 3681 | Delete_rows    |         1 |        3728 | table_id: 178 flags: STMT_END_F       || mysql_bin.000003 | 3728 | Xid            |         1 |        3759 | COMMIT /* xid=1002 */                 || mysql_bin.000003 | 3759 | Anonymous_Gtid |         1 |        3824 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  || mysql_bin.000003 | 3824 | Query          |         1 |        3913 | drop database zsl                     || mysql_bin.000003 | 3913 | Rotate         |         1 |        3960 | mysql_bin.000004;pos=4                |+------------------+------+----------------+-----------+-------------+---------------------------------------+65 rows in set (0.00 sec)# 使用mysqlbinlog恢复差异备份[root@localhost ~]# mysqlbinlog --stop-position=3824 /opt/data/mysql_bin.000003 |mysql -uroot -pPasswd123!mysql: [Warning] Using a password on the command line interface can be insecure.[root@localhost ~]# mysql -uroot -pPasswd123! -e 'select * from zsl.course;'mysql: [Warning] Using a password on the command line interface can be insecure.+----+-------------+| id | course_name |+----+-------------+|  1 | HTML        ||  2 | JAVA        ||  3 | MySQL       ||  5 | C++         ||  6 | wuli        ||  7 | huaxue      ||  8 | shuxue      ||  9 | yuwen       |+----+-------------+

扩展

• 二进制日志转换文本文件

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000003 > /opt/mysql_bin003.txt

• 根据时间点恢复数据

mysqlbinlog --no-defaults --stop-datetime='2022-07-29 12:06:12' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p //基于时间恢复 从开头到指定的时间停止 之前的都会执行操作
mysqlbinlog --no-defaults --start-datetime='2022-07-29 12:06:16' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p //基于正确的时间恢复 从指定的时间点到结尾都会执行操作