当前位置：首页 > news >正文

五、MySQL的备份及恢复

news 2025/11/6 16:09:39

5.1 MySQL日志管理

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

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

误删除数据库
数据库工作时、意外程序终止、程序崩溃
病毒的入侵
文件系统损坏后，系统进行自检操作时，误删除了核心数据
升级数据库时、命令不严格
设备故障，硬件损坏
自然灾害，被盗

5.1.1 MySQL日志类型

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

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

mysql > FLUSH LOGS
或
mysqladmin flush-logs

5.1.2 错误日志

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

服务器启动和关闭过程中的信息
务器运行过程中的错误信息
事件调度器运行一个时间是产生的信息
在从服务上启动进程时所产生的信息

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

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

5.1.3 通用查询日志

启动开关 general_log = {ON||OFF}
日志文件变量 general_log_file [=/PATH/TO/file]
全局日志开关 log = {ON||OFF}
记录类型 log_output = {TABLE||FILE||NONE}

如果需要启用通用查询日志至少要配置 general_log = NO ，log_output = {TABLE||FILE}
如果general_log_file 没有额外指定默认名为 host_name.log

mysql> SHOW GLOBAL VARIABLES LIKE '%general_log%';mysql> SHOW GLOBAL 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%'; #开启方式
SET GLOBAL slow_query_log=ON;#查看默认的查询时效
MYSQL> SHOW GLOBAL VARIABLES LIKE 'long_query_time';

5.1.5 二进制日志

开启日志

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

5.6版本

前：一定要手动指定

后：默认file_name为$datadir/mysqld-binlog

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

二进制日志包含了所有更新了数据或者已经潜在更新了数据。语言语句以事件形式保存，描述了数据的更改。

二进制日志还包含关于每个更新数据库的语句的执行时间信息。

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

二进制日志是记录 执行的语句 还是 执行后的数据 ？？？

情况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> SHOW BINARY LOGS;
查看当前正在使用的是哪一个二进制日志文件：mysql> SHOW MASTER STATUS;

5.2 MySQL备份

5.2.1备份类型

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

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

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

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

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

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

逻辑备份的优缺点

优点：

在备份速度上两种备份要取决于当前的存储引擎
物理备份的还原速度非常快，但是物理备份的最小单元只能做到表
逻辑备份保存的结构通常都是ASCII形式，所以我们只能通过.sql方式进行保存
逻辑备份有非常强的兼容性，而物理备份对版本要求非常高
逻辑备份对保持数据的安全性有保障

缺点：

逻辑备份会对DBMS产生额外的压力,物理备份则无明显压力
逻辑备份的结果可能比信息本身还要大。可以通过压缩来缓解这一现象。
针对于高精度内容，逻辑备份可能会倒置浮点精度丢失。

5.2.2 备份内容

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

5.2.3 常见的备份工具

MySQL自带的工具

基于DBMS=====> mysqldump

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

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

其他工具

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

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

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

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

#备份
选择需要备份的数据库，右键-》备份/导出-》以SQL转储文件备份数据库
选择需要恢复的数据库，右键-》导入-》从sql转储文件导入数据库PS 不要混用，可能会出现冲突，注意甄别核心命令