MySQL数据库备份策略与实践详解

引言

一、MySQL数据库备份的重要性

（一）数据丢失的原因

（二）数据丢失的后果

引言

无论你是IT专业人士还是初次接触数据库管理的初学者，理解并掌握数据库备份技术都是非常关键的。备份不仅能够防止因硬件故障、软件错误、人为误操作等因素造成的损失，也是灾难恢复计划中的基石。

一、MySQL数据库备份的重要性

（一）数据丢失的原因

在生产环境中造成数据丢失的原因可能有很多种：

人为操作：在输入数据是命令错误导致数据丢失。

磁盘故障：比如由于物理磨损，导致磁盘出现坏道，造成数据丢失

服务BUG：由于数据量过大，导致MySQL服务出现逻辑错误，造成数据丢失

物理损坏：硬盘被盗、服务器物理损毁等

（二）数据丢失的后果

数据是企业的核心资产，一次意外的数据丢失可能导致业务停滞、客户信任度下降甚至法律纠纷。MySQL数据库备份就像是给企业数据买了一份保险，能够在危机时刻快速恢复业务运行。

二、MySQL备份类型

数据备份的类型可根据不同的标准进行分类

（一）根据数据库状态

根据备份时数据库的状态分为：

热备份：在数据库仍在运行的情况下进行备份，不影响正常的业务操作，适用于支持在线备份的数据库系统。

冷备份：在数据库完全关闭状态下进行的备份，这时的数据是静态的，不会发生变化，但会导致业务中断。

温备份：介于热备份与冷备份之间，数据库可能只允许读操作而不允许写操作，或者部分数据库实例处于待机模式。

（二）根据数据的完整性

根据备份数据时，数据的完整性，即备份策略可分为：

完整备份：顾名思义，就是备份数据库的全部数据。这是最直观也最全面的备份方式，适合数据量不大或者初期备份时使用。

增量备份：只备份自上次备份以来发生改变的数据。这种方式节省空间，但恢复时需要依次恢复所有增量备份。

差异备份：备份自上次完整备份以来所有变化的数据。相比增量备份，恢复时只需要最后一次完整备份和最后一次差异备份即可。

（三）根据存储介质

磁盘备份：传统的备份方式，用于大规模数据备份，现在依然在大型企业中有应用。

可移动存储备份：如U盘、光盘等。

本机多硬盘备份：利用计算机内部的多个硬盘进行备份

网络备份：通过网络将数据备份至远程服务器或其他网络存储设备。

（四）常见的备份方式

1.冷备份

数据库处于关闭状态，数据不会发生改变，从而保证数据的一致性，备份速度快，且不会存在任何中间状态，方法比较简单，虽然简单，但它只能用于数据库完全关闭时的状态恢复，无法做到实时备份

2.专用备份工具

mysqldump是MySQL自带的一个命令行工具，它可以生成数据库的SQL脚本，其中包括了创建表结构、插入数据等内容。

3.二进制日志备份

二进制日志记录了数据库的所有更改，可用于进行基于时间点的恢复或主从复制。启用二进制日志并在进行完整备份后，定期备份二进制日志文件，可以实现增量备份的效果。

4.第三方工具备份

对于InnoDB存储引擎的大规模数据库，可以使用Percona Xtrabackup工具进行热备份，它能实现在线备份，不影响数据库的正常使用。

MEB是Oracle官方提供的付费备份工具，支持在线热备份，适合大规模、高负载的生产环境。

三、数据的备份与恢复

（一）物理冷备份

首先关闭MySQL服务：systemctl stop mysqld.service

使用tar命令将文件压缩，而后进行解压恢复

使用tar命令将数据库文件进行压缩保存

将该数据库删除，模拟数据文件丢失

使用命令解压置MySQL的data目录下进行恢复

开启服务进行查看数据库文件

对于整个数据库系统同理，将mysql目录下的data目录进行压缩打包备份，而后进行恢复

删除数据库文件，模拟系统损坏

数据恢复后启动服务登录

（二）mysqldump工具备份与恢复

mysqldump工具备份与恢复严格意义上来说属于温备份，它可以在数据库服务运行的状态下进行备份，通过将备份文件中的SQL语句导入MySQL服务中来重新执行SQL语句，达到备份效果

首先使用mysql -u username -p [password] -e 'SQL语句'来查看数据库的信息

-e：指令作用是在shell环境下不进入mysql服务，直接执行SQL语句，一般用于脚本文件

查看所有数据库名称

查看work库下的所有表

1.库备份与恢复

1.1 备份数据库

#mysqldump -u username -p [password] --databases database_name >backup_name.sql

'-u [username]：指定MySQL用户名。'
'-p：提示输入密码。也可以直接在 -p 后面跟上密码（不推荐，因为命令行历史中可能会保存密码）'
'[database_name]：要备份的数据库名称。可以一个或者多个'
'>：重定向符号，将mysqldump命令的输出写入到文件中。'
'backup_name.sql：备份文件的名称，扩展名通常为.sql。'

#mysqldump -u username -p [password] --databases 苦1 库2 > backup_filename.sql
'备份多个数据库'

#mysqldump -u username -p [password] --all-databases > backup_filename.sql
'备份所有数据库'

首先进行数据的备份，比如想要备份work库，指定目录下生成备份文件

而后进行模拟丢失，将work库删除

1.2 恢复数据库

可以通过grep过滤出备份文件中的SQL语句

恢复的方式有两种

1.登录MySQL服务，使用source命令进行恢复

在MySQL服务中输入：source 备份文件

2.使用mysql命令进行恢复

使用：mysql -u username -p [password] < 备份文件

将备份文件重定向输入到mysql当中

2.表的备份与恢复

#mysqldump -u username -p [password] [-d] database_name table_name >backup_name.sql

'-u [username]：指定MySQL用户名。'
'-p：提示输入密码。也可以直接在 -p 后面跟上密码（不推荐，因为命令行历史中可能会保存密码）'
'[-d]：使用-d选项表示只备份表的结构，不使用-d表示表结构加数据一起备份'
'[database_name]：要备份的表的所在数据库名称。'
'[table_name]：要备份的表名称。'
'>：重定向符号，将mysqldump命令的输出写入到文件中。'
'backup_name.sql：备份文件的名称，扩展名通常为.sql。'

#mysqldump -u username -p [password] [-d] database_name 表1 > backup_name.sql
'备份多张表'

#mysqldump -u username -p [password] [-d] > backup_name.sql
'备份所有表'

2.1 备份表

分别备份两张表

可以使用grep命令过滤出备份文件的SQL语句

可以看到，备份的SQL语句中，使用-d与不使用备份的数据信息不一样，使用后，并没有将输入数据信息的SQL语句备份下来

表恢复的方式与库一样，可以使用source或者mysql命令恢复

首先删除两张表模拟数据丢失

2.2 恢复表

使用source恢复emp表

在登录mysql服务器输入：source /bak/emp_bak.sql

查看表的信息后发现，表的数据并没有恢复，而表结构保存了下来

使用mysql命令恢复emp_user表的数据

输入：mysql -u username -p [password] database_name < 备份文件

需要注意的是，在备份的过程中，加不加--databases选项有些许区别

例如：

mysqldump -uroot -p123123 --databases work > /bak/work.sql
#这是备份整个库文件，包括库本身建库语句

mysqldump -uroot -p123123 work > /bak/work.sql
#这是备份work库下所有的表，并不包括work库本身的建库语句

可以查看一下备份文件中的SQL语句

所以在进行数据恢复的时候会有区别

（三）二进制日志备份

MySQL的二进制日志（Binary Log）记录了数据库的所有更改，包括数据修改、DDL语句和其它重要事件。利用二进制日志进行备份，不仅可以实现数据恢复，还可以用于主从复制和数据审计等目的

MySQL的二进制日志备份通常并不意味着单独备份某个特定的.log文件，而是定期备份整个日志链中的一系列文件。

1.开启二进制日志

首先需要开启日志功能，在/etc/my.cnf文件中添加以下信息

log-error=/usr/local/mysql/data/mysql_error.log 
#这一行设置了MySQL错误日志文件的位置。

general_log=ON 
#开启了MySQL的一般查询日志，这意味着所有对MySQL服务器执行的每一条SQL语句都将被记录下来，
#包括成功的查询、失败的查询以及连接和断开数据库的行为等。

general_log_file=/usr/local/mysql/data/mysql_general.log 
#指定了MySQL一般查询日志的存储路径和文件名。

log-bin=mysql-bin 开启了MySQL的二进制日志（Binary Log）
#这是一种用于复制和恢复的重要日志，记录了数据库的所有更改，包括数据修改、DDL语句等。

slow_query_log=ON 
#开启MySQL慢查询日志，该日志记录执行时间超过一定阈值（long_query_time设置）的所有SQL查询

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log 
#设置了慢查询日志的存储路径和文件名。

long_query_time=5 
#设置了慢查询的阈值，单位为秒。此配置下，任何执行时间超过5秒的SQL查询都会被记录到慢查询日志

添加之后重启MySQL服务：systemctl restart mysqld

可以使用：variables like 查看日志开启状况

variables 表示变量 like 表示模糊查询
#xxx（字段）
xxx% 以xxx为开头的字段
%xxx 以xxx为结尾的字段
%xxx% 只要出现xxx字段的都会显示出来
xxx   精准查询

重启后会在data/目录下生成四个日志文件

查看二进制日志文件

#--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
#-v: 显示详细内容
#--no-defaults : 默认字符集（不加会报UTF-8的错误）

在二进制日志文件中需要关注以下几个部分

#at sum ：开始的位置点 
#end_log_pos sum：结束的位置
#时间戳： 240325 18:06:33  2024年3月25日 18时06分33秒
#SQL语句

2.完全备份

二进制日志文件，是对整个数据库文件进行增量备份，当数据有修改，删除，新建等操作时会记录到二进制日志当中，一般的查询语句不会记录

在开始使用二进制日志文件之前需要先进行完全备份，因为增量备份时基于完全备份实现的，它只记录修改后的信息，所以需要先进行完全备份

在完全备份后刷新日志信息，生成新的二进制文件，用于记载增量备份的数据信息

3.修改数据

而后进行数据的修改

可以看新的二进制日志文件中看到操作信息

也可以执行以下命令，将打印内容重定生成为txt文件，便于以后查看

mysqlbinlog --no-defaults --base64-output=decode-rows -v /bak/mysql-bin.000002 > /bak/mysql-bin_$(date +%F).000002.txt

此时再刷新，生成新的二进制日志文件（因为下一步需要模拟数据丢失，删除work库文件，此操作会计入二进制日志文件中，在使用二进制日志文件恢复数据时，依然会删除库）

4.模拟数据丢失

模拟数据丢失，删除库文件

5.数据恢复

5.1 完全恢复

直接使用二进制日志文件恢复会报错，因为它是基于完全备份后的增量备份，完全备份的数据是它的基础，在上面的操作中，对emp_user表进行添加三条数据，所以二进制日志文件，只记录了对表的一些操作语句，如果直接使用增量恢复，会因为找不到库文件与表而报错

所以首先需要进行完全备份的恢复

如果想将插入的三条数据全部恢复，需要将二进制日志文件mysql-bin.000002全部恢复即可

5.2 断点恢复

使用--stop-position与--start-position指令进行断点恢复

--stop-position='at值' 
#从开始值向下匹配，不设定--start-position，表示匹配到目标二进制日志文件的结尾

--start-position='end_log_pos值'
#结束位置，不设定--stop-position，表示从目标二进制日志文件的开头匹配，到设定值结束

过滤出转换为txt格式后的二进制文件的开始值与结束值

同样删除原本的数据信息，将信息恢复到完全备份之前的状态

比如第五条信息出现错误，不想恢复第五条信息，想从第六条数据开始恢复，只需要将--start-position的值设为第六条数据的开始值即可

通过--stop-position选项可以恢复到想要的数值为止

比如只想恢复第五条数据可以添加--stop-position='结束值'进行恢复

首先找到需要恢复的信息的结束值

使用mysqlbinlog命令进行恢复

在没有设置主键或者唯一键的情况下可以多次恢复同一条数据

比如想再次恢复第六条数据，找到该数据的开始值与结束值

使用：mysqlbinlog --no-defaults --strat-position='at值' --stop-position='end_log_pos值' 进行恢复

5.3 时间戳恢复

基于时间戳恢复原理与断点恢复原理一致，将--stop-position与--start-position指令更改为

--stop-datetime与--start-datetime即可

--stop-datetime='at值' 
#从开始值向下匹配，不设定--start-datetime，表示匹配到目标二进制日志文件的结尾

--start-datetime='end_log_pos值'
#结束位置，不设定--stopt-datetime，表示从目标二进制日志文件的开头匹配，到设定值结束

找到需要恢复的数据的开始时间戳与结束时间戳

恢复该条数据，使用--stop-datetime与--start-datetime选项

总结

本章知识点主要在于数据如何备份与恢复，在生产环境中，根据不同的信息或者服务状态，选择不同的备份恢复方式，能够达到特定的效果。

（一）备份原理

备份方式的基本原理，主要将库或者表的SQL语句进行备份，在数据恢复时，将SQL语句重新导入到MySQL服务当中，再次执行一遍SQL语句，达到数据恢复的效果

（二）常用的备份方式

1.冷备份

2.专用备份工具

3.二进制日志备份

4.第三方工具备份

（三）制定备份策略

在生产环境中，可以根据清空制定备份策略

定期备份：可根据数据变化频率设定每日、每周或每月的备份计划。

使用crontab -e，制定计划任务，来定期进行数据备份

比如每周六对wor库下emp_user表进行备份

0 1 * * 6 /usr/local/mysql/bin/mysqldump -uroot -pabc123 work emp_user > /bak/work_emp_user_$(date +%F).sql;/usr/local/mysql/bin/mysqladmin -u root -p flush-logs

也可以使用shell脚本进行备份

备份保留策略：根据法规要求和存储资源，确定备份文件的保留周期。

异地备份：将备份文件存放在不同的地理位置，以防单一地点的灾难事件