MySQL备份与恢复
MySQL备份与恢复
1、数据备份概述
备份是数据安全的最后一道防线，对于任何数据丢失的场景，备份虽然不一定能恢复百分之百的数据(取决于备份周期)，但至少能将损失降到最低。
数据丢失的场景举例：
- 人为操作失误造成某些数据被误操作
- 软件 BUG 造成部分数据或全部数据丢失
- 硬件故障造成数据库部分数据或全部数据丢失
- 安全漏洞被入侵数据恶意破坏
1.1 衡量备份恢复重要指标
衡量备份恢复有两个重要的指标：
- 恢复点目标(RPO)
- 恢复点目标是指数据能恢复到什么程度
- 恢复时间目标(RTO)
- 恢复时间目标是指数据恢复需要多长时间
1.2 数据库备份分类
数据库备份方式分很多种，从物理与逻辑的角度来看，备份可分为：
- 物理备份：指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份又可以分为脱机备份(冷备份)和联机备份(热备份)。
- 冷备份:在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性。
- 热备份:在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件。
- 逻辑备份：指对数据库逻辑组件(如"表"等数据库对象)的备份。
数据库的备份从策略角度来看，备份可分为以下几类：
- 完全备份：每次对数据进行完整的备份。可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和空间，所以，做一次完全备份的周期要长些。
- 差异备份：备份那些自从上次完全备份之后被修改过的文件，只备份数据库部分的内容。它比最初的完全备份小，因为只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快。
- 增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。
2、MySQL备份工具
2.1 mysqldump备份工具
MySQL官方提供了Mysqldump逻辑备份工具，它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert语句，当我们需要还原这些数据时，只要执行这些insert语句，即可将对应的数据还原。
# mysqldump命令语法：
mysqldump [选项] 数据库名 [表名] > 备份文件名
# 数据恢复
mysql [选项] 数据库名 [表名] > 备份文件名
source 备份文件名
选项列表：
| 选项名 | 含义 |
|---|---|
| --host | 服务器IP地址 |
| --port | 服务器端口号 |
| --user | MySQL 用户名 |
| --password | MySQL 密码 |
| --databases | 指定要备份的数据库 |
| --all-databases | 备份mysql服务器上的所有数据库 |
| --compact | 压缩模式，产生更少的输出 |
| --comments | 添加注释信息 |
| --complete-insert | 输出完成的插入语句 |
| --lock-tables | 备份前，锁定所有数据库表 |
| –no-create-db / --no-create-info | 禁止生成创建数据库语句 |
| --force | 当出现错误时仍然继续备份操作 |
| --default-character-set | 指定默认字符集 |
| --add-locks | 备份数据库表时锁定数据库表 |
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工具备份常用选项：
| 选项类别 | 命令全名 | 含义 |
|---|---|---|
| 通用选项 | --user=name | 数据库账号名 |
| 通用选项 | --password | 数据库密码 |
| 通用选项 | --host=name | 数据库主机IP地址 |
| 通用选项 | --port=name | 数据库主机端口号 |
| 通用选项 | --defaults-file | 定义包含默认配置的文件的路径。 |
| 通用选项 | --socket=name | 数据库socket文件地址 |
| 备份选项 | --backup | 创建备份并且放入--target-dir目录中 |
| 备份选项 | --target-dir | 备份文件的存放目录路径，如果目录不存在，xtrabakcup会创建。如果目录存在且为空则成功。不会覆盖已存在的文件。 |
| 备份选项 | --databases=name | 指定要备份的数据库 |
| 增量选项 | --incremental-basedir | 使用增量备份 |
| 压缩/解压选项 | --compress | compress压缩 |
| 压缩/解压选项 | --compress-threads=n | 启用n个线程进行压缩 |
| 压缩/解压选项 | --decompress | 准备数据之前先解压 |
| 准备选项 | --prepare | 实现同步回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态 |
| 准备选项 | --apply-log-only | 阻止回滚未提完成的事务（最后一次增量备份的准备不需要此选项） |
| 准备选项 | --incremental-dir | 指定增量备份，与全备合并 |
| 还原选项 | --copy-back | 做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir。 |
| 还原选项 | --move-back | 这个选项与–copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项会移除backup文件，用时候必须小心。 |
# 下载安装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 changed
mysql> 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').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-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').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-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').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-database-2022-07-29-10-45-04.sql table-2022-07-29-10-45-27.sql
anaconda-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: 0
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 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/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=1 #设置服务器标识符
log-bin=mysql_bin #开启二进制日志功能
[root@localhost ~]# service mysqld restart
Shutting 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').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll
total 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.err
drwxr-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_info
drwxr-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.pem
drwxr-x---. 2 mysql mysql 8192 Jul 29 11:10 sys
[root@localhost ~]# mysqladmin -uroot -pPasswd123! flush-logs
mysqladmin: [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.err
drwxr-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_info
drwxr-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.pem
drwxr-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.err
drwxr-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_info
drwxr-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.pem
drwxr-x---. 2 mysql mysql 8192 Jul 29 11:10 sys
drwxr-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 //基于正确的时间恢复 从指定的时间点到结尾都会执行操作
