Mysql备份与恢复
大约 3 分钟数据库Mysql
概述
备份数据库非常重要，这样在出现问题时，如系统崩溃、硬件故障、错误删除数据等情况都可以恢复数据并重新启动和运行。
备份类型
物理备份由存储数据库内容的目录和文件的原始副本组成。这种类型的备份适用于出现问题时需要快速恢复的大型、重要数据库。逻辑备份保存表示为逻辑数据库结构,如 DDL 和 DML 语句。这种类型的备份适用于少量的数据。
物理备份特性：
- 备份由数据库目录和文件的结构组成。相对于逻辑备份占用的空间更小，恢复速度更快。
- 除了备份数据库以外，备份还可以包括任何相关文件，如日志或配置文件。
- 备份只能移植到具有相同或类似硬件特征的其他计算机上。
- 备份可以在MySQL服务器未运行时执行。如果服务器正在运行，则会执行适当的锁定，以便服务器在备份期间不会更改数据库内容。
逻辑备份特性：
逻辑备份是通过查询MySQL服务器来获取数据库的结构和内容信息。
逻辑备份比物理方法慢，因为服务器必须访问数据库信息并将其转换为逻辑格式。
逻辑备份文件比物理备份文件更大。
逻辑备份不能备份日志和配置文件。
逻辑格式存储的备份是独立于机器的，并且具有高度的可移植性。
mysqldump
SQL格式备份恢复
备份
mysqldump用于进行逻辑备份，命令格式如下：
mysqldump [arguments] > file_name
## 1 备份所有数据库：
mysqldump --all-databases > dump.sql
## 2 备份指定数据库
mysqldump --databases db1 db2 db3 > dump.sql
## 3 备份指定数据库
mysqldump test > dump.sql
## 备份某个库下的指定表
mysqldump test t1 t3 t7 > dump.sql
：：：note
加 --databases 会添加创库语句和use语句，不加则不会生成。
：：：
执行命令 mysqldump -uroot -p123456 --databases test>/usr/mysql/data/tmp.sql,生成的Sql如下图所示：
执行命令 mysqldump -uroot -p123456 test>/usr/mysql/data/tmp.sql,生成的Sql如下图所示：
回滚
命令如下：
mysql -uroot -p123456 </usr/mysql/data/tmp.sql
如果回滚Sql文件没有包含建库和use语句，需要先创建数据库，恢复时指定数据库，命令如下：
## 创建数据库 test
mysqladmin -uroot -p123456 create test
## 恢复数据
mysql -uroot -p123456 test </usr/mysql/data/tmp.sql
binlog恢复
- 查看binlog列表
SHOW BINARY LOGS;
- 获取当前二进制日志文件的名称
SHOW MASTER STATUS;
- 根据时间段查询指定的SQL语句，示例如下：
mysqlbinlog --start-datetime="2022-10-11 20:05:00" \
--stop-datetime="2023-11-11 20:08:00" --verbose \
/var/lib/mysql/binlog.000002 | grep -C 15 "DROP TABLE"
在3165处删除了user表，接下来进行回滚操作：
mysqlbinlog --start-position=3088 --stop-position=3165 /var/lib/mysql/binlog.000002 \
| mysql -u root -p
使用建议
尽量不要在
mysqldump命令行上使用——databases，因为这会导致dump文件中包含use db1语句，会导致下面mysql db2 < dump.sql语句无效。mysqldump db1 > dump.sql mysqladmin create db2 mysql db2 < dump.sql