C05S13-MySQL数据库备份与恢复
一、MySQL数据备份
1. 数据备份概述
数据备份的主要目的是灾难恢复，也就是当数据库等出现故障导致数据丢失，能够通过备份恢复数据。
数据备份可以分为物理备份和逻辑备份。物理备份，又称为冷备份，需要关闭数据库进行备份；逻辑备份，又称为热备份，不需要关闭数据库。数据备份的策略包括全量备份和增量备份，物理备份只能实现全量备份，而逻辑备份不仅支持全量备份，还支持增量备份。
2. mysqldump命令 – MySQL数据库备份
mysqldump命令是MySQL数据库中备份工具，用于将MySQL服务器中的数据库以标准的sql语言的方式导出，并保存到文件中。是针对数据库和数据表的备份。
# 基本语法
mysqldump [选项]
# 常见用法
mysqldump -u 用户名 -p [密码] 库名 [表名] > 保存文件
- 用户密码可以显示输入，或者交互输入。
- 如果是导出多个数据库或多个数据表，需要使用英文逗号分隔。
常见的选项：
| 选项 | 功能 |
|---|---|
| -A | 所有数据库 |
| -B | 指定数据库 |
3. 二进制日志备份
MySQL的二进制日志记录了对数据库的更改操作，也就是增、删、改、查等操作，查询操作不会被记录，因此可以通过二进制日志来实现数据的备份。
要实现基于二进制日志的数据备份，需要在MySQL的配置文件my.cnf中开启二进制日志功能。MySQL 8.0版本开始，默认开启二进制文件功能，二进制日志文件的默认前缀是binlog。下面是在my.cnf文件中的基本配置内容：
log-bin=mysql-bin
binlog-format=MIXED
- log-bin：设置二进制日志文件的路径和前缀。默认路径是在MySQL的data目录下。
- binlog-format：设置二进制日志文件的格式。
MySQL二进制日志文件的记录格式：
-
ROW
基于数据行。记录每一行的数据，准确，但是恢复的效率低。
-
STATEMENT
基于SQL语句，按照执行顺序记录SQL语句。高并发的情况下，SQL语句的记录顺序可能出错，导致恢复的数据有所偏差，虽然恢复的效率较高。
-
MIXED
混合模式。正常情况下使用STATEMENT方式记录，高并发的时候会使用ROW的记录方式。
开启后，只要执行的更改操作都会被记录在日志文件中。使用MySQL自带的mysqlbinlog命令，可以解析查看二进制日志文件，结合管道符和重定向符就能够实现数据的恢复。根据二进制日志恢复数据，实际上就是重新执行日志文件中所记录的操作语句。
可以通过mysqlbinlog命令查看日志文件的全文，也可以基于位置或者基于时间查看日志文件的部分内容。因此恢复数据也包括全部恢复、基于位置的断点恢复和基于时间的断点恢复。
MySQL二进制日志文件的主要内容格式如下：
BEGIN
/*!*/;
# at 593
#241220 15:53:46 server id 1 end_log_pos 717 CRC32 0x8673bc7c Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1734681226/*!*/;
insert into student values(1,'小明',18,'男')
/*!*/;
# at 717
#241220 15:53:46 server id 1 end_log_pos 748 CRC32 0x551b5137 Xid = 25
COMMIT/*!*/;
- at 593：593表示事务在二进制日志文件中的位置。
- 241220 15:53:46：事务的时间。使用mysqlbinlog基于时间查看，需要写成完整的时间格式，也就是2024-12-20 15:53:46的格式。
4. mysqlbinlog命令 – 查看二进制日志文件
使用mysqlbinlog命令可以查看二进制日志文件。
mysqlbinlog [选项] 二进制日志文件
常见的选项：
| 选项 | 功能 |
|---|---|
| –no-defaults | 不读取默认的配置文件，以免查看结果受到干扰 |
| –start-position | 起始位置 |
| –stop-position | 终止位置 |
| –start-datetime | 起始时间 |
| –stop-datetime | 终止时间 |
5. 二进制日志文件
二、数据备份与恢复实操
1. 全量备份与恢复
-
将MySQL数据库的数据目录备份。
# 先关闭MySQL服务 systemctl stop mysqld # 备份数据 cd /usr/local/mysql/data tar -Jcvf /opt/mysql_all_$(date +%F).tar.xz * # 数据恢复 tar -Jxvf /opt/mysql_all_2024-12-20.tar.xz -C /usr/local/mysql/data -
使用mysqldump全量备份。
# 导出数据库stu中的所有数据表数据 mysqldump -u root -p stu > stu.sql # 导出数据库stu中指定的数据表stu_info数据 mysqldump -u root -p stu stu_info > stu_info.sql # 恢复数据 # 恢复数据库stu mysql -u root -p < stu.sql # 恢复数据表stu_info到数据库stu中 mysql -u root -p < stu.sql mysql -u root -p stu < stu_info.sql
2. 增量备份与恢复
-
恢复全部数据，也就是将日志文件中记录的SQL语句全部重新执行一遍。
mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p -
基于位置恢复数据。
# 恢复从某个位置开始的全部数据 # 也就是执行日志文件中某个位置开始的全部SQL语句 mysqlbinlog --no-defaults --start-position='593' mysql-bin.000001 | mysql -u root -p # 恢复从某个位置之前的全部数据 # 也就是执行日志文件中某个位置之前的全部SQL语句 mysqlbinlog --no-defaults --stop-position='593' mysql-bin.000001 | mysql -u root -p # 恢复某个位置范围内的数据 # 也就是执行日志文件中某个位置范围内的全部SQL语句 mysqlbinlog --no-defaults --start-position='593' --stop-position='717' mysql-bin.000001 | mysql -u root -p -
基于时间恢复数据。
# 恢复从某个时间开始的全部数据 # 也就是执行日志文件中某个时间开始的全部SQL语句 mysqlbinlog --no-defaults --start-datetime='2024-12-20 15:53:46' mysql-bin.000001 | mysql -u root -p # 恢复从某个时间之前的全部数据 # 也就是执行日志文件中某个时间之前的全部SQL语句 mysqlbinlog --no-defaults --stop-datetime='2024-12-20 15:53:46' mysql-bin.000001 | mysql -u root -p # 恢复某个时间范围内的数据 # 也就是执行日志文件中某个时间范围内的全部SQL语句 mysqlbinlog --no-defaults --start-datetime='2024-12-20 15:53:46' --stop-datetime='2024-12-20 15:58:12' mysql-bin.000001 | mysql -u root -p -
以上三种恢复方法本质就是重新执行日志文件中的SQL语句，再次执行的命令也会被记录在日志文件当中。因此为了日后恢复数据不受到干扰，可以先通过flush刷新生成新的MySQL日志，然后再通过原有二进制日志文件恢复数据，程序执行的操作就会被记录在新的日志文件当中。
该操作会关闭当前的二进制日志文件，并打开一个新的日志文件。常用于日志归档和维护，比如每天定时进行日志文件的归档保存。
mysqladmin -u root -p flush-logs
三、MySQL常见的日志文件
1. 二进制日志
二进制日志主要记载除SELECT等查询语句外所执行的操作语句，从MySQL 8.0版本开始，二进制日志文件是默认开启的，可以不用手动配置。默认的二进制日志文件位于MySQL的数据目录下，前缀为binlog。
下面是在my.cnf中显式开启二进制日志文件的参数基本格式。
# 如果不指定路径只指定前缀，就依然保存在MySQL的数据目录下
log_bin=文件路径/前缀
binlog-format=文件的记录格式
2. 错误日志
错误日志主要包括MySQL服务的启动、关闭和报错。与二进制日志文件相同，错误日志也是默认开启的，默认的错误日志文件位于MySQL的数据目录下，名称是主机名.err。
下面是显示开启错误日志文件的参数基本格式。
log_error=文件路径
3. 普通日志
普通日志记录MySQL运行时所有的执行的SQL语句，可以根据文件进行MySQL的排错和调试。普通日志默认不开启。
下面是显示开启普通日志文件的参数基本格式。
general_log=1 # 1表示开启，0表示不开启
general_log_file=文件路径
4. 慢日志
慢日志用于记录MySQL中执行较慢的语句，默认不开启。
下面是显示开启慢日志文件的参数基本格式。
slow_query_log=1 # 1表示开启，0表示不开启
slow_query_log_file=文件路径 #慢日志路径
数基本格式。
```shell
general_log=1 # 1表示开启，0表示不开启
general_log_file=文件路径
4. 慢日志
慢日志用于记录MySQL中执行较慢的语句，默认不开启。
下面是显示开启慢日志文件的参数基本格式。
slow_query_log=1 # 1表示开启，0表示不开启
slow_query_log_file=文件路径 #慢日志路径