当前位置:网站首页>MySQL备份与恢复 (转载非原创)
MySQL备份与恢复 (转载非原创)
2022-08-09 12:21:00 【wxilejun】
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备份工具
[[email protected] ~]# 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
[[email protected] ~]#dnf -y localinstall percona-xtrabackup-80-8.0.22-15.1.el8.x86_64.rpm
# 完全备份
[[email protected] ~]#trabackup --backup --databases=zsl --target-dir=/backup/xtrabackup/ -uroot -pPasswd123!
# 恢复阶段:准备备份,恢复之前需要准备备份
[[email protected] ~]#xtrabackup --prepare --target-dir=/backup/xtrabackup/
# 恢复数据(保证要还原的数据库服务器的data目录为空)
[[email protected] ~]#rm -rf /var/lib/mysql/* #模拟数据丢失
[[email protected] ~]#xtrabackup --copy-back --target-dir=/backup/xtrabackup/
[[email protected] ~]#chown -R mysql:mysql /var/lib/mysql
[[email protected] ~]#systemctl restart mysqld
增量备份:
# 先创建完全备份
[[email protected] ~]#xtrabackup --backup --databases=test --target-dir=/backup/xtrabackup/ -uroot -p123456
# 创建第一次增量备份
[[email protected] ~]#xtrabackup --backup --databases=test --target-dir=/backup/inc1/ --incremental-basedir=/backup/xtrabackup/ -uroot -p123456
# 创建第二次增量备份
[[email protected] ~]#xtrabackup --backup --databases=test --target-dir=/backup/inc2/ --incremental-basedir=/backup/inc1/ -uroot -p123456
# 恢复阶段:准备全量备份
[[email protected] ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/xtrabackup/
# 准备第一次增量备份,将第一次增量备份与全备合并
[[email protected] ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/xtrabackup/ --incremental-dir=/backup/inc1
# 准备第二次增量备份,将第二次增量备份与全备合并
[[email protected] ~]#xtrabackup --prepare --target-dir=/backup/xtrabackup/ --incremental-dir=/backup/inc2/
# 恢复数据(保证要还原的数据库服务器的data目录为空)
[[email protected] ~]#rm -rf /var/lib/mysql/* # 模拟数据丢失
[[email protected] ~]#xtrabackup --copy-back --target-dir=/backup/xtrabackup/
[[email protected] ~]#chown -R mysql:mysql /var/lib/mysql
[[email protected] ~]#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)
[[email protected] ~]# 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.
[[email protected] ~]# ls
all-database-2022-07-29-10-45-04.sql anaconda-ks.cfg
# 备份zsl库的course表、students表和teacher表
[[email protected] ~]# 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.
[[email protected] ~]# ls
all-database-2022-07-29-10-45-04.sql anaconda-ks.cfg table-2022-07-29-10-45-27.sql
# 备份zsl库
[[email protected] ~]# 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.
[[email protected] ~]# 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数据库
##方法一:系统行命令数据恢复
[[email protected] ~]# 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)
# 恢复整个数据库
[[email protected] ~]# 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服务器的二进制日志功能
[[email protected] ~]# vim /etc/my.cnf
[[email protected] ~]# 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 #开启二进制日志功能
[[email protected] ~]# 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)
# 开始完全备份
[[email protected] ~]# 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.
[[email protected] ~]# 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差异备份数据恢复
# 模拟误删数据
[[email protected] ~]# mysql -uroot -pPasswd123! -e 'drop database zsl;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[[email protected] ~]# 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这个数据库已被删除
# 刷新创建新的二进制日志
[[email protected] ~]# 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
[[email protected] ~]# mysqladmin -uroot -pPasswd123! flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[[email protected] ~]# 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
# 恢复完全备份
[[email protected] ~]# 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.
[[email protected] ~]# 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 |
+--------------------+
[[email protected] ~]# 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 |
+---------------+
[[email protected] ~]# 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++ |
+----+-------------+
[[email protected] ~]# 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 |
+----+-------+------+------+--------+-----------+
[[email protected] ~]# 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 |
+----+------+------+------+--------+-----------+
# 恢复差异备份
[[email protected] ~]# 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恢复差异备份
[[email protected] ~]# 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.
[[email protected] ~]# 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 //基于正确的时间恢复 从指定的时间点到结尾都会执行操作
转载来源: https://www.cnblogs.com/Alone-8712/p/16563155.html
边栏推荐
- 我们真的需要DApp吗?App真的不能满足我们的幻想吗?
- 注:检测到当前使用的ADB不是HBuilder内置或自定义ADB:PID为:9544进程名称为:adb.exe 路径为:c:\users\administrator\appdata\local\and
- 李开复花上千万投的缝纫机器人,团队出自大疆
- 荣耀携手Blue Yonder,加快企业战略增长
- 无重复字符的最长子串
- The batch size does not have to be a power of 2!The latest conclusions of senior ML scholars
- 智驾科技完成C1轮融资,此前2轮已融4.5亿元
- 【无标题】
- 8、IDEA提交代码出现: Fetch failed fatal: Could not read from remote repository
- API调用,API传参,面向对接开发,你真的会写接口文档吗?
猜你喜欢
腾讯欲成育碧最大股东/ 米哈游招NLP内容生成研究员/ AI发现四千余物种濒临灭绝...今日更多新鲜事在此...
Rust从入门到精通04-数据类型
苹果Meta都在冲的Pancake技术,中国VR团队YVR竟抢先交出产品答卷
Scala Advanced (7): Collection Content Summary (Part 1)
Do you know the difference between comments, keywords, and identifiers?
西湖大学教授怎么看AI制药革命?|量子位智库圆桌实录
注释、关键字、标识符的区别你知道吗?
脱光衣服待着就能减肥,当真有这好事?
JD.com architects tidy up: what are the core technical knowledge points of jvm and performance tuning
Go 事,如何成为一个Gopher ,并在7天找到 Go 语言相关工作,第1篇
随机推荐
Go-based web access parameters
Intra-group reverse order adjustment of K nodes
Rust从入门到精通04-数据类型
The grep command Shell regular expressions, the three musketeers
FFmpeg在win10上编译安装(配置libx264)
Simple encapsulation of glide tool class
无需精子卵子子宫体外培育胚胎,Cell论文作者这番话让网友们炸了
glide工具类的简单封装
保存Simulink仿真模型为图片或者PDF的方法
Win10 compiles the x264 library (there are also generated lib files)
Say goodbye to the AI era of hand looms
【微服务~远程调用】整合RestTemplate、WebClient、Feign
世界第4疯狂的科学家,在103岁生日那天去世了
The latest interview summary in 20022 brought by Ali senior engineer is too fragrant
Apexsqlrecover cannot connect to database
十分钟教会你如何使用VitePress搭建及部署个人博客站点
2022 Niu Ke Duo School (6) M. Z-Game on grid
李开复花上千万投的缝纫机器人,团队出自大疆
西湖大学教授怎么看AI制药革命?|量子位智库圆桌实录
ABAP interview questions: how to use the System CALL interface of the ABAP programming language, direct execution ABAP server operating System's shell command?