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. 全量备份与恢复

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
   

2. 使用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. 增量备份与恢复

1. 恢复全部数据，也就是将日志文件中记录的SQL语句全部重新执行一遍。

   mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p
   

2. 基于位置恢复数据。

   # 恢复从某个位置开始的全部数据
   # 也就是执行日志文件中某个位置开始的全部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
   

3. 基于时间恢复数据。

   # 恢复从某个时间开始的全部数据
   # 也就是执行日志文件中某个时间开始的全部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
   

4. 以上三种恢复方法本质就是重新执行日志文件中的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=文件路径    #慢日志路径