C05S13-MySQL数据库备份与恢复

一、MySQL数据备份

1. 数据备份概述

数据备份的主要目的是灾难恢复，也就是当数据库等出现故障导致数据丢失，能够通过备份恢复数据。
数据备份可以分为物理备份和逻辑备份。物理备份，又称为冷备份，需要关闭数据库进行备份；逻辑备份，又称为热备份，不需要关闭数据库。数据备份的策略包括全量备份和增量备份，物理备份只能实现全量备份，而逻辑备份不仅支持全量备份，还支持增量备份。
2. mysqldump命令 – MySQL数据库备份

mysqldump命令是MySQL数据库中备份工具，用于将MySQL服务器中的数据库以尺度的sql语言的方式导出，并保存到文件中。是针对数据库和数据表的备份。

1. # 基本语法
2. mysqldump [选项]
4. # 常见用法
5. mysqldump -u 用户名 -p [密码] 库名 [表名] > 保存文件

复制代码

• 用户密码可以显示输入，大概交互输入。
  
• 如果是导出多个数据库或多个数据表，需要使用英文逗号分隔。
  

常见的选项：
选项功能-A所有数据库-B指定数据库 3. 二进制日记备份

MySQL的二进制日记记录了对数据库的更改操纵，也就是增、删、改、查等操纵，查询操纵不会被记录，因此可以通过二进制日记来实现数据的备份。
要实现基于二进制日记的数据备份，需要在MySQL的设置文件my.cnf中开启二进制日记功能。MySQL 8.0版本开始，默认开启二进制文件功能，二进制日记文件的默认前缀是binlog。下面是在my.cnf文件中的基本设置内容：

1. log-bin=mysql-bin
2. binlog-format=MIXED

复制代码

• log-bin：设置二进制日记文件的路径和前缀。默认路径是在MySQL的data目录下。
  
• binlog-format：设置二进制日记文件的格式。
  

MySQL二进制日记文件的记录格式：

• ROW
  基于数据行。记录每一行的数据，正确，但是恢复的效率低。
  
• STATEMENT
  基于SQL语句，按照实行次序记录SQL语句。高并发的环境下，SQL语句的记录次序可能出错，导致恢复的数据有所偏差，固然恢复的效率较高。
  
• MIXED
  混合模式。正常环境下使用STATEMENT方式记录，高并发的时间会使用ROW的记录方式。
  

开启后，只要实行的更改操纵都会被记录在日记文件中。使用MySQL自带的mysqlbinlog命令，可以解析查看二进制日记文件，联合管道符和重定向符就能够实现数据的恢复。根据二进制日记恢复数据，现实上就是重新实行日记文件中所记录的操纵语句。
可以通过mysqlbinlog命令查看日记文件的全文，也可以基于位置大概基于时间查看日记文件的部分内容。因此恢复数据也包括全部恢复、基于位置的断点恢复和基于时间的断点恢复。
MySQL二进制日记文件的主要内容格式如下：

1. BEGIN
2. /*!*/;
3. # at 593
4. #241220 15:53:46 server id 1  end_log_pos 717 CRC32 0x8673bc7c  Query   thread_id=8     exec_time=0     error_code=0
5. SET TIMESTAMP=1734681226/*!*/;
6. insert into student values(1,'小明',18,'男')
7. /*!*/;
8. # at 717
9. #241220 15:53:46 server id 1  end_log_pos 748 CRC32 0x551b5137  Xid = 25
10. COMMIT/*!*/;

复制代码

• at 593：593表示事务在二进制日记文件中的位置。
  
• 241220 15:53:46：事务的时间。使用mysqlbinlog基于时间查看，需要写成完整的时间格式，也就是2024-12-20 15:53:46的格式。
  

4. mysqlbinlog命令 – 查看二进制日记文件

使用mysqlbinlog命令可以查看二进制日记文件。

1. mysqlbinlog [选项] 二进制日志文件

复制代码

常见的选项：
选项功能–no-defaults不读取默认的设置文件，以免查看结果受到干扰–start-position起始位置–stop-position终止位置–start-datetime起始时间–stop-datetime终止时间 5. 二进制日记文件

二、数据备份与恢复实操

1. 全量备份与恢复

• 将MySQL数据库的数据目录备份。
  
  1. # 先关闭MySQL服务
  2. systemctl stop mysqld
  4. # 备份数据
  5. cd /usr/local/mysql/data
  6. tar -Jcvf /opt/mysql_all_$(date +%F).tar.xz *
  8. # 数据恢复
  9. tar -Jxvf /opt/mysql_all_2024-12-20.tar.xz -C /usr/local/mysql/data

  复制代码
• 使用mysqldump全量备份。
  
  1. # 导出数据库stu中的所有数据表数据
  2. mysqldump -u root -p stu > stu.sql
  3. # 导出数据库stu中指定的数据表stu_info数据
  4. mysqldump -u root -p stu stu_info > stu_info.sql
  6. # 恢复数据
  7. # 恢复数据库stu
  8. mysql -u root -p < stu.sql
  9. # 恢复数据表stu_info到数据库stu中
  10. mysql -u root -p < stu.sql
  11. mysql -u root -p stu < stu_info.sql

  复制代码

2. 增量备份与恢复

• 恢复全部数据，也就是将日记文件中记录的SQL语句全部重新实行一遍。
  
  1. mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p

  复制代码
• 基于位置恢复数据。
  
  1. # 恢复从某个位置开始的全部数据
  2. # 也就是执行日志文件中某个位置开始的全部SQL语句
  3. mysqlbinlog --no-defaults --start-position='593' mysql-bin.000001 | mysql -u root -p
  5. # 恢复从某个位置之前的全部数据
  6. # 也就是执行日志文件中某个位置之前的全部SQL语句
  7. mysqlbinlog --no-defaults --stop-position='593' mysql-bin.000001 | mysql -u root -p
  9. # 恢复某个位置范围内的数据
  10. # 也就是执行日志文件中某个位置范围内的全部SQL语句
  11. mysqlbinlog --no-defaults --start-position='593' --stop-position='717' mysql-bin.000001 | mysql -u root -p

  复制代码
• 基于时间恢复数据。
  
  1. # 恢复从某个时间开始的全部数据
  2. # 也就是执行日志文件中某个时间开始的全部SQL语句
  3. mysqlbinlog --no-defaults --start-datetime='2024-12-20 15:53:46' mysql-bin.000001 | mysql -u root -p
  5. # 恢复从某个时间之前的全部数据
  6. # 也就是执行日志文件中某个时间之前的全部SQL语句
  7. mysqlbinlog --no-defaults --stop-datetime='2024-12-20 15:53:46' mysql-bin.000001 | mysql -u root -p
  9. # 恢复某个时间范围内的数据
  10. # 也就是执行日志文件中某个时间范围内的全部SQL语句
  11. 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日记，然后再通过原有二进制日记文件恢复数据，步伐实行的操纵就会被记录在新的日记文件当中。
  该操纵会关闭当前的二进制日记文件，并打开一个新的日记文件。常用于日记归档和维护，好比天天定时进行日记文件的归档保存。
  
  1. mysqladmin -u root -p flush-logs

  复制代码

三、MySQL常见的日记文件

1. 二进制日记

二进制日记主要记录除SELECT等查询语句外所实行的操纵语句，从MySQL 8.0版本开始，二进制日记文件是默认开启的，可以不用手动设置。默认的二进制日记文件位于MySQL的数据目录下，前缀为binlog。
下面是在my.cnf中显式开启二进制日记文件的参数基本格式。

1. # 如果不指定路径只指定前缀，就依然保存在MySQL的数据目录下
2. log_bin=文件路径/前缀
3. binlog-format=文件的记录格式

复制代码

2. 错误日记

错误日记主要包括MySQL服务的启动、关闭和报错。与二进制日记文件雷同，错误日记也是默认开启的，默认的错误日记文件位于MySQL的数据目录下，名称是主机名.err。
下面是显示开启错误日记文件的参数基本格式。

1. log_error=文件路径

复制代码

3. 普通日记

普通日记记录MySQL运行时所有的实行的SQL语句，可以根据文件进行MySQL的排错和调试。普通日记默认不开启。
下面是显示开启普通日记文件的参数基本格式。

1. general_log=1 # 1表示开启，0表示不开启
2. general_log_file=文件路径

复制代码

4. 慢日记

慢日记用于记录MySQL中实行较慢的语句，默认不开启。
下面是显示开启慢日记文件的参数基本格式。

1. slow_query_log=1 # 1表示开启，0表示不开启
2. slow_query_log_file=文件路径    #慢日志路径
3. 数基本格式。```shellgeneral_log=1 # 1表示开启，0表示不开启
4. general_log_file=文件路径

复制代码

4. 慢日记

慢日记用于记录MySQL中实行较慢的语句，默认不开启。
下面是显示开启慢日记文件的参数基本格式。

1. slow_query_log=1 # 1表示开启，0表示不开启
2. slow_query_log_file=文件路径    #慢日志路径

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。