MySQL的备份及恢复

妍妍的宝贝

已于 2024-05-19 01:47:45 修改

阅读量901

点赞数 9

分类专栏： MySQL 文章标签： mysql 数据库运维

于 2024-05-16 17:31:49 首次发布

本文链接：https://blog.csdn.net/weixin_68398469/article/details/138811208

版权

MySQL 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

使用mysqlbinlog工具进行二进制日志备份:

5、MySQL的备份及恢复

5.1 MySQL日志管理

在数据库保存数据时，有时候不可避免会出现数据丢失或者被破坏，这样情况下，我们必须保证数据的安全性和完整性，就需要使用日志来查看或者恢复数据了

数据库中数据丢失或被破坏可能原因：

1、误删除数据库

2、数据库工作时、意外程序终止、程序崩溃

3、病毒的入侵

4、文件系统损坏后，系统进行自检操作时，误删除了核心数据

5、升级数据库时、命令不严格

6、设备故障，硬件损坏

7、自然灾害，被盗

5.1.1 MySQL日志类型

日志类型	记录文件中的信息类型
错误日志	记录启动、运行或停止时出现问题
查询日志	记录建立的客户端连接和执行的语句
二进制日志	记录所有更改数据的语句。主要用于复制或即时点恢复
慢日志	记录所有执行时间超过最大查询时间 long_query_time 的查询操作或不使用索引的查询
事务日志	记录InnoDB等支持事务管理的存储引擎执行事务时所产生的日志

检查MySQL配置文件

MySQL的配置文件（如my.cnf或my.ini）中通常包含了日志文件的设置。可以使用文本编辑器打开这个文件，并搜索与日志相关的设置。在配置文件中，你可能会找到类似于以下的行：

[mysqld]  
log_error = /var/log/mysqld.log

默认情况下，所有日志都创建于mysqld数据目录内，通过刷新日志，你可以强制 mysqld来关闭和重新打开||切换日志文件。

刷新日志
FLUSH LOGS ;
或
mysqladmin flush-logs

5.1.2 错误日志

错误日志中主要记录的几种日志：

1、服务器启动和关闭过程中的信息

2、服务器运行过程中的错误信息

3、事件调度器运行一个时间是产生的信息

4、在从服务上启动进程时所产生的信息

错误日志的定义：

可以用--log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。
如果没有给定file_name值，mysqld使用错误日志名host_name.err 并在数据目录中写入日志文件。
如果执行刷新操作，错误日志用-old重新命名后缀并且mysqld创建一个新的空日志文件。

[mysqld]  
log_error = /var/log/mysqld.log

#错误日志
mysql> SHOW GLOBAL VARIABLES LIKE '%log_error%';
#警告日志
mysql> SHOW GLOBAL VARIABLES LIKE '%log_warning%';

5.1.3 通用查询日志

刚开始查看通用查询日志时图使OFF(关闭)状态，如果想要观察其日志，第一步先启动日志。（下面的"||"不是命令符，代表”或者“的意思）

启动开关：general_log = {ON || OFF}

日志文件变量：general_log_file[=/PATH/TO/file]

全局日志开关：log={ON|OFF} 该开关打开后，所有日志都会被启用

记录类型：log_output={TABLE||FILE||NONE}

因此，要启用通用查询日志，需要至少配置general_log=ON，log_output={TABLE||FILE}。而general_log_file如果没有指定，默认名是host_name.log。

看看上述几个值的默认配置：

临时设置（当前会话有效）:

SET GLOBAL general_log = 'ON';

永久设置（编辑配置文件）:

在[mysqld]部分添加或修改以下行：

general_log = 1

或者

general_log = ON

查看通用查询日志：
mysql> show global variables like '%general%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | ON                           |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+

查看通用查询日志的记录类型是什么方式：
mysql> SHOW GLOBAL VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

打开此文件查看，能发现什么时间使用什么语句这里都是有记录的

通用查询日志查询的就是这种

设置log_output

你可以选择将日志输出到文件、表或两者都输出。如果你只想输出到文件，设置log_output为FILE；如果只想输出到表，设置为TABLE；如果两者都要，设置为TABLE,FILE。

临时设置（当前会话有效）:

SET GLOBAL log_output = 'FILE'; -- 或者 'TABLE' 或 'FILE,TABLE'

永久设置（编辑配置文件）:

在[mysqld]部分添加或修改以下行：

log_output = FILE

或者

log_output = TABLE,FILE

然后重启MySQL服务器以使更改生效。

设置general_log_file（可选）

如果你选择将日志输出到文件，并且想指定一个不同的文件名或路径，你可以设置general_log_file。如果未指定，MySQL将使用默认的日志文件名，通常是hostname.log，其中hostname是MySQL服务器的主机名。

临时设置（当前会话有效，但通常不推荐这样做，因为重启后会失效）:

SET GLOBAL general_log_file = '/var/lib/mysql/localhost.log';

永久设置（编辑配置文件）:

在[mysqld]部分添加或修改以下行：

general_log_file = /var/lib/mysql/localhost.log

然后重启MySQL服务器以使更改生效。

[root@localhost ~] systemctl restart mysqld.service

重启MySQL服务器（如果进行了永久设置）

如果你更改了配置文件中的设置，你需要重启MySQL服务器以使这些更改生效。

验证设置

登录到MySQL服务器并运行以下命令来验证设置是否已正确应用：

SHOW VARIABLES LIKE '%general_log%';  
SHOW VARIABLES LIKE '%log_output%';

5.1.4 慢查询日志

Mysql 如果启用了 slow_query_log = ON 选项。就会记录执行时间超过long_query_time的查询。

日志记录文件为slow_query_log_file[=file_name] 若没有给定file_name，默认为主机名，后缀为-slow.log。

与慢查询相关的变量设置：

# 系统默认关闭慢查询日志开关 查看状态
MYSQL> SHOW GLOBAL VARIABLES LIKE '%slow_query_log%'; 

#开启方式   --临时生效
MYSQL> SET GLOBAL slow_query_log=ON;

# 如果查询时长超过long_query_time的定义值（默认10秒），即为慢查询：（查看默认查询时效）
mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';

5.1.5 二进制日志

开启日志

二进制日志启动开关：log-bin [= file_name]。

注意：在5.6及以上版本一定要手动指定。

在5.6以下版本默认file_name为$datadir/mysqld-binlog。

开启二进制日志

在MySQL的配置文件（通常是my.cnf或my.ini）中，可以通过设置log_bin选项来开启二进制日志。在MySQL 5.6及更高版本中，你需要明确指定日志文件的名称或路径，而在早期版本中，如果不指定文件名，它通常会使用默认的路径和名称（如$datadir/hostname-bin.XXXXXX）。

下面是一个示例配置，展示了如何在配置文件中开启二进制日志：

[mysqld]  
log_bin = /var/log/mysql/mysql-bin.log

在上面的配置中，log_bin被设置为/var/log/mysql/mysql-bin.log，这意味着二进制日志文件将被保存在/var/log/mysql/目录下，并且文件名为mysql-bin.log。

其他相关配置选项

binlog_format：指定二进制日志的格式。常见的格式有STATEMENT（基于SQL语句的）、ROW（基于行的）和MIXED（混合模式）。默认是STATEMENT，但在复制环境中，ROW或MIXED通常更为推荐。

binlog_format = ROW

expire_logs_days：设置二进制日志文件的过期天数。超过这个天数的二进制日志文件将被自动删除。这有助于防止磁盘空间被无限期占用的情况。

expire_logs_days = 7

max_binlog_size：设置单个二进制日志文件的最大大小（以字节为单位）。当文件大小达到这个限制时，MySQL将自动开始一个新的日志文件。

max_binlog_size = 100M

binlog_cache_size：设置每个会话用于二进制日志缓存的内存大小。如果缓存不够大，二进制日志事件可能会被写入磁盘，这可能会降低性能。

binlog_cache_size = 32M

查看二进制日志的工具为：mysqlbinlog

二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有 语句。语句以“事件”的形式保存，它描述数据更改。

二进制日志还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。

二进制日志的主要目的（作用）：是在数据库存在故障时，恢复时能够最大可能地更新数据库（即时点恢复），因为二进制日志包含备份后进行的所有更新。二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。

二进制日志是记录执行的语句还是 执行后的数据 ？？？分为三种情况：

1、假如一个表有10万行数据，而现在要执行一个如下语句将amount字段的值全部在原来的基础上增加1000

UPDATE sales.january SET amount=amount+1000 ;

如果影响的行数过于庞大，日志内记录的是执行的语句

2、如果像某一个指定的行内字段插入数据

UPDATE student SET Email = "XXX" WHERE StudentNo = ???;

此时就不能记录语句了，因为不同时间执行的结果是不一样的。这是应该记录这一行的值，这种就是基于行（row）的二进制日志

3、如果出现触发器存储函数这种方式进行数据的更新的话，可能会结合两种方式来进行记录,这种叫做 混合方式的二进制日志

二进制日志管理==>又叫日志滚动

在my.cnf中设定max_binlog_size = 200M，表示限制二进制日志最大尺寸为200M，超过200M后进行滚动。
MySQL的滚动方式与其他日志不太一样，滚动时会创建一个新的编号大1的日志用于记录最新的日志，而原日志名字不会被改变。每次重启MySQL服务，日志都会自动滚动一次。
另外如果需要手动滚动，则使用命令：mysql> FLUSH LOGS ;

二进制日志还原数据

使用mysqlbinlog读取需要的日志内容，使用标准输入重定向到一个sql文件，然后在mysql服务器上导入即可，如下：

mysqlbinlog binlog.000005 >/root/temp_date.sql

如果报编码错误：mysqlbinlog: [ERROR] unknown variable 'default-character-set

原因：mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8mb4这个指令。

有两种方式解决：

添加 --no-defaults 参数

mysqlbinlog --no-defaults binlog.000069 >c:/a.sql # 注意需要指定binlog的位置，如果是当前路径，则可以直接使用名称即可。

修改配置文件my.cnf

default-character-set=utf8mb4 修改为 character-set-server = utf8mb4，但是需要重启MySQL服务。

删除二进制日志文件：

二进制日志文件不能直接删除的，如果使用rm等命令直接删除日志文件，可能导致数据库的崩溃。必须使用命令 PURGE 删除日志，语法如下：

PURGE { BINARY | MASTER } LOGS { TO 'log_name' |BEFORE datetime_expr }

注意：如果数据库使用的编码是utf8mb4编码，mysqlbinlog命令可能不能解析这种编码，提供两种方案：

在MySQL的配置/etc/my.cnf中将default-character-set=utf8mb4 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。

用mysqlbinlog --no-defaults mysql-bin.000004 命令打开。

5.1.6实例：

使用mysqlbinlog工具进行二进制日志备份:

查看已备份的二进制数据文件，这里的000006是备份学生表的数据文件（文件大）

使用mysqlbinlog 查看此数据文件发现异常得多

将文件查看数据重定向仅一个sql文件中去

[root@localhost ~] mysqlbinlog /var/lib/mysql/binlog.000006 > /root/myschool_db2

使用vim查看器查看里面的数据文件发现有日志文件也有sql执行语句

注意此处使用mysqlbinlog查看二进制文件中是附带use MySchool_db 这条执行语句的，证明会自动跳转到此数据库，假如要备份的新的数据库中可以将这里的值进行修改

vim myschool_db2

再次登录回数据库

将原先的表删除

mysql> source /root/myschool_db2.sql

查看表是否正常恢复

二进制备份总结

日志查看：

查看有哪些二进制日志文件：mysql> SHOW BINARY LOGS;
查看当前正在使用的是哪一个二进制日志文件：mysql> SHOW MASTER STATUS;
查看二进制日志内容：mysql> SHOW BINLOG EVENTS IN 'binlog.000005';

数据以及日志备份和导入：

数据,日志备份：
[root@localhost ~] mysqlbinlog /var/lib/mysql/binlog.000006 > /root/myschool_db2
数据导入：
mysql> source /root/myschool_db2.sql

5.2 MySQL备份

5.2.1 备份类型

根据服务器的状态，备份分为三种类型：热备份、温备份、冷备份。

热备份：读、写不受影响；
温备份：仅可以执行读操作；
冷备份：读写均终止，离线备份；

1）热备：在数据库运行时，直接进行备份。对运行的数据库没有影响。
2）温备：同样是数据库运行的时候进行备份的，但对当前数据库的操作会产生影响。
3）冷备：在数据库停止运行的时候进行备份，这种备份方式最为简单，只需要拷贝数据库物理文件即可。

热备份的优点：

1.可在表空间或数据文件级备份，备份时间短。
2.备份时数据库依然可以使用。
3.可达到秒级恢复，能够恢复到某一个时间点上。
4.恢复的速度很快，在大多数情况下在数据库工作时就可恢复。
5.几乎所有的数据库实体都可以进行恢复。

热备份的缺点:

1.尽量不要出错，否则后果会很严重。

ps:
在热备份过程中，系统仍然在运行并处理数据。如果备份过程中出现任何错误或中断，那么备份的数据可能会不完整或损坏。由于这些备份是在系统运行时进行的，所以它们通常包含的是系统当前状态的快照。如果快照不完整或损坏，那么当需要使用备份来恢复数据时，可能会遇到数据丢失或不一致的情况。

示例：假设你正在对一家银行的交易系统进行热备份。在备份过程中，由于某种原因（如电源故障、网络中断等），备份过程中断了。由于备份不完整，当银行系统出现严重问题时，你尝试使用备份来恢复数据，但发现备份中的数据不完整，导致一些交易记录丢失或不一致。

2.如果热备份不成功，所得结果不可用于时间点的数据恢复。
ps:
热备份通常捕获的是备份开始时的系统状态的快照。如果备份过程由于某种原因而失败或中断，那么你将无法获得一个完整的时间点快照。这意味着你将无法使用这些备份来恢复到某个特定的时间点。

示例：继续上面的银行交易系统示例。假设你计划在上午10点进行热备份，但由于某种原因，备份在上午10:15时失败了。由于备份不完整，你将无法使用这些备份来恢复到上午10点的系统状态。你只能恢复到之前的一个完整备份点，这可能会导致一些在上午10点到10:15之间发生的交易数据丢失。

3.维护的工作比较困难。

冷备份的优点：
1.是非常快速的备份方法，因为只需要拷贝文件即可
2.容易归档，容易恢复到某个时间点上(只需将文件再拷贝回去即可)
3.能与归档方法相结合，作数据库(最新状态)的恢复。
4.容易维护，且比较安全。

冷备份的缺点：

1.单独使用时，只能提供到"某一时间点的上"的恢复。

解释：冷备份是在数据库关闭的状态下进行的，因此它只能备份到数据库关闭那一刻的数据状态。如果在备份之后，数据库又进行了更新或修改，那么这些变化将不会被包含在备份中。当需要恢复数据时，只能恢复到备份时的那个时间点的状态，而无法恢复到备份之后的某个特定时间点。
示例：假设在晚上10点进行了冷备份，但之后数据库又进行了新的交易或数据更新。如果在第二天下午2点发生了数据丢失或损坏，使用晚上10点的冷备份进行恢复时，将无法恢复到晚上10点之后到下午2点之间的任何数据变化。

2.再实施备份的全过程中，数据库必须是关闭状态。

解释：冷备份需要在数据库关闭的情况下进行，这意味着在备份期间，数据库无法提供正常的服务。这可能会对业务造成中断，特别是在需要24小时在线服务的场景中。
示例：一个电商平台在每天的高峰时段都需要处理大量的订单和用户请求。如果在这个时段进行冷备份，那么平台就需要暂时关闭或暂停服务，这会导致用户无法下单或查询信息，对业务造成损失。

3.不能按表或按用户恢复。

解释：由于冷备份是对整个数据库进行备份，而不是针对某个表或用户进行备份，因此在恢复时也无法只恢复某个表或用户的数据。如果需要恢复某个表或用户的数据，可能需要从整个备份中提取相关数据，这可能会比较繁琐和耗时。
示例：假设某个数据库中有多个表，其中一个表的数据出现了错误或损坏。如果使用冷备份进行恢复，那么整个数据库都会被恢复到备份时的状态，而不仅仅是那个出错的表。这可能会导致其他未出错的表也被不必要地恢复，增加了恢复的复杂性和时间成本。

从对象的角度分析，分为物理备份以及逻辑备份

物理备份：复制数据文件
逻辑备份：将数据导出至目标 XXX.sql文件中

物理备份：直接复制数据文件进行的备份
优点：不需要其他的工具，直接复制就好，恢复直接复制备份文件即可
缺点：与存储引擎有关，跨平台能力较弱

逻辑备份：从数据库中导出数据另存而进行的备份
优点：能使用编辑器处理，恢复简单，能基于网络恢复，有助于避免数据损坏
缺点：备份文件较大，备份较慢，无法保证浮点数的精度，使用逻辑备份数据恢复后，还需要手动重建索引，十分消耗cpu资源。

从数据收集上来分，分为完全备份、增量备份、差异备份

完全备份：备份全部的数据
差异备份：仅备份上次完全备份后变化的数据
增量备份：仅备份上次完全备份或增量备份后所变化的数据

(1) 完全备份：即对整个数据库的数据和数据结构进行备份。
好处：
    就是很直观容易被人理解。而且当发生数据丢失的灾难时，只要用灾难之前的备份文件，就可以恢复丢失的数据。
不足之处：
    首先由于每天都对系统进行完全备份,因此在备份数据中有大量是重复的。这些重复的数据占用了大量的空间，这对用户来说就意味着增加成本；其次，由于需要备份的数据量相当大，因此备份所需时间较长。对于那些业务繁忙，备份窗口时间有限的单位来说，选择这种备份策略无疑是不明智的。
(2) 增量备份：每次备份的数据只是相当于上一次备份后增加的和修改过的数据。
优点：没有重复的备份数据，即节省空间，又缩短了备份时间。
缺点：在于当发生灾难时，恢复数据比较麻烦。
1. 举例来说，如果系统在星期四的早晨发生故障，丢失大批数据，
2. 那么现在就需要将系统恢复到星期三晚上的状态。
3. 这时管理员需要首先找出星期一的那份完全备份数据进行系统恢复，
4. 然后，再找出星期二的数据来恢复星期二的数据，然后在找出星期三的数据来恢复星期三的数据。
5. 很明显这比第一种策略要麻烦得多。
6. 另外这种备份可靠性也差。
7. 在这种备份下，各备份数据间的关系就象链子一样，一环套一环
8. 其中任何一个备份数据出了问题都会导致整条链子脱节。

(3) 差异备份
    每次备份的数据是相对于上一次全备份之后新增加的和修改过的数据。管理员先在星期一进行一次系统完全备份；然后在接下来的几天里，管理员再将当天所有与星期一不同的数据(新的或经改动的)备份到磁带上。

1. 举例来说，在星期一，网络管理员按惯例进行系统完全备份；
2. 在星期二，假设系统内只多了一个资产清单，于是管理员只需将这份资产清单一并备份下来即可；
3. 在星期三，系统内又多了一份产品目录，于是管理员不仅要将这份目录
4. 还要连同星期二的那份资产清单一并备份下来。
5. 如果在星期四系统内有多了一张工资表
6. 那么星期四需要备份的内容就是：工资表+产品目录+资产清单。

逻辑备份的优缺点

优点：

在备份速度上两种备份要取决于当前的存储引擎

物理备份的还原速度非常快，但是物理备份的最小单元只能做到表

逻辑备份保存的结构通常都是ASCII形式，所以我们只能通过.sql方式进行保存

逻辑备份有非常强的兼容性，而物理备份对版本要求非常高

逻辑备份对保持数据的安全性有保障

缺点：

逻辑备份会对DBMS产生额外的压力,物理备份则无明显压力

逻辑备份的结果可能比信息本身还要大。可以通过压缩来缓解这一现象。

针对于高精度内容，逻辑备份可能会倒置浮点精度丢失。

5.2.2 备份内容

数据文件
数据结构
日志(二进制日志，事务日志)
存储过程，存储函数，触发器
个人配置信息
脚本信息

5.2.3 常见的备份工具

MySQL自带的工具

基于DBMS=====> mysqldump（用于创建 MySQL 数据库的逻辑备份的工具）

mysqldump 是mysql数据库管理系统，自带的逻辑备份工具。支持所有的主流引擎，MyISAM引擎是温备，InnoDB引擎是热备。备份速度中等，还原速度较慢。因此在实现还原时，我们会采用其他的方式进行操作。

备份所有数据库:
[root@localhost ~]mysqldump -uroot -p --all-databases > /usr/local/databases.sql
备份指定的数据库(数据表)
[root@localhost ~]mysqldump -uroot -p MySchool_db student grade > /usr/local/MySchool_db.sql;
-------------------------------------------------------------------------------------------
还原方式1
mysql> use MyScl_db;
mysql> source/usr/local/MySchool_db.sql;
还原方式2
mysql> mysql -uroot -p MyScl_db < /usr/local/MySchool_db.sql
--------------------------------------------------------------------------------------------

文件系统备份工具

cp命令，冷备份，支持所有引擎，复制命令，只能实现冷备，物理备份。使用归档工具，cp命令，对其进行备份的，备份速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差。

lvm（逻辑卷管理）几乎是热备份，支持所有引擎，基于快照(LVM，ZFS)的物理备份，速度非常快，几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用，所以备份速度比较快，恢复速度比较快，没有什么弹性空间，而且LVM的限制：不能对多个逻辑卷同一时间进行备份，所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。

其它工具

其他工具

ibbackup 商业工具 MyISAM是温备份，InnoDB是热备份，备份和还原速度都很快，这个软件它的每服务器授权版本是5000美元。

xtrabackup 开源工具 MyISAM是温备份，InnoDB是热备份，是ibbackup商业工具的替代工具。

mysqlbackup ORACLE公司也提供了针对企业的备份软件MySQL Enterprise Backup简称：mysqlbackup。

MySQL企业备份工具执行在线“热备“，无阻塞的MySQL数据库备份。全备份可以在所有InnoDB数据库上执行，而无需中断MySQL查询或更新。此外，支持增量备份，只备份自上次备份后有变化的数据。另外部分备份，支持特定的表或表空间按需要进行备份。

#备份

选择需要备份的数据库，右键-》备份/导出-》以SQL转储文件备份数据库
选择需要恢复的数据库，右键-》导入-》从sql转储文件导入数据库

PS 不要混用不同软件导出的sql文件，可能会出现冲突，注意甄别核心命令

妍妍的宝贝

关注

9
点赞
踩
23

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录