MySQL数据库备份问题及处理实用指南

作者：袖梨 2026-08-27

平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“MySQL数据库备份问题及处理”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

一、MySQL常用日志
- 1.1 概述
- 1.2 错误日志
- 1.3 二进制文件
- 1.4 中继日志
- 1.5 慢查询日志
- 1.6 数据库中的查询日志状态
  - 1.6.1 查看二进制日志状态开启
  - 1.6.2 查看慢查询日志功能是否开启
  - 1.6.3 查询慢时间设置
  - 1.6.4 在数据库中设置开启慢查询的办法（临时）
二、备份
- 2.1 概述
- 2.2 备份的重要性
- 2.3 备份类型
- 2.4 备份的办法
  - 2.4.1 冷备份
  - 2.4.2 借助启用二进制日志进行增量备份
  - 2.4.3 借助第三方工具备份
- 2.5 备份命令
- 2.6 增量备份与恢复
  - 2.6.1 增量备份需开启二进制日志功能
  - 2.6.2 可每天进行增量备份操作，生成新的二进制文件
  - 2.6.3 查看新生成的日志内容
- 2.7 恢复的方法
  - 2.7.1 按位置恢复
  - 2.7.2 按时间恢复
总结

一、MySQL常用日志

1.1 概述

日志文件在数据库进行备份和恢复时起到了很重要的作用

常用的日志文件默认保存在 /usr/local/mysql/data 目录下

可在 /etc/my.cnf 设置文件中的 [mysqld] 中进行日志的路径修改、开启、关闭等操作

1.2 错误日志

用来记录 mysql 启动、停止或运行时产生的错误信息

可借助一下字段进行更新

log-error=/usr/local/mysql/data/mysql_error.log （指定日志的保存位置和文件名）

1.3 二进制文件

结合项目来看，二进制日志，用来记录所有更新的数据或者已经潜在更新了数据的语句，记录了数据的更改，可用来数据恢复

开启方式：

log-bin=mysql-bin 或者 log_bin=mysql-bin

1.4 中继日志

从实现思路看，一般情况下，它在 mysql 主从同步（复制）、读写分离集群的从节点上才开启。

主节点一般不需这个日志。

1.5 慢查询日志

实际处理时，慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，能够找到哪些查询语句执行时间长，以便于优化

开启方式：

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log （指定文件路径和名称）
long_query_time=5 （设置执行超过5秒的语句会被记录，缺省时默认为10秒）

1.6 数据库中的查询日志状态

1.6.1 查看二进制日志状态开启

show variables like '%log_bin%';

1.6.2 查看慢查询日志功能是否开启

show variables like '%slow%';

1.6.3 查询慢时间设置

show variables like 'long_query_time';

1.6.4 在数据库中设置开启慢查询的办法（临时）

set global slow_query_log=ON;

查看
show variables like ‘long_query_time';

日志超时时间

二、备份

2.1 概述

备份的主要目的是灾难恢复
还能够用来测试应用、回滚数据修改、查询历史数据、审计等
在生产环境中，数据的安全性至关重要
任何数据的丢失都可能产生严重的后果

2.2 备份的重要性

在企业里，数据的价值至关重要，数据保障了企业业务的正常运行。

落到代码里，所以，数据的安全性及数据的可靠性是运维的重中之重，任何数据的吊事都可能对企业产生严重的后果。

== 通常情况下，造成数据丢失的原因有一下几种：==

程序错误
人为操作错误
运算错误
磁盘故障
灾难（火灾、地震、盗窃等）

2.3 备份类型

从物理与逻辑的角度分类可分为：逻辑备份、物理备份

从数据库的备份策略角度分类可分为：完全备份、差异备份、增量备份

==完全备份：==每次对数据进行完整的备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。完全备份的备份与恢复操作都很轻松便于，但是数据存在大量的重复，同时且会占用大量的磁盘空间，备份的时间也很长。
==差异备份：==备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。
==增量备份：==只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需从上一次的完整备份开始到最后一次增量备份之的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

2.4 备份的办法

结合项目来看，数据库的备份能够采用很多种方式，如直接打包数据库文件（物理冷备份）、专用备份工具（mysqldump）、二进制日志增量备份、第三方工具备份等

2.4.1 冷备份

冷备份时需在数据库处于关闭状态下，能够较好地保证数据库的完整性
冷备份的特点就是速度快，恢复时也是最为轻松的。
借助直接打包数据库文件夹(/usr/loc.al/mysql/data)来实现备份

2.4.2 借助启用二进制日志进行增量备份

兼容增量备份，进行增量备份时必须启用二进制日志。
从实现思路看，二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。
理解这一步时，如果进行增量备份(包含自上次完全备份或增量备份以来发生的数据修改)，需刷新二进制日志

2.4.3 借助第三方工具备份

理解这一步时，第三方工具Percona xtraBackup是一个免费的MysQL热备份软件，兼容在线热备份Innodb和xtraDB，也能够兼容MySQL表备份，不过MyISAM表的备份要在表锁的情况下进行。

2.5 备份命令

完全备份

理解这一步时，InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。

物理冷备份与恢复

systemctl stop mysqld
yum -y install xz
 
#压缩备份
cd /usr/local/mysql/data
tar jcvf mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
systemctl start mysqld
 
#模拟故障，删除数据库
drop database HUISUO;
 
#解压恢复
tar jxvf /opt/mysql_all_2022-06-21.tar.xz -C /usr/local/mysql/data
cd /usr/local/mysql/data
mv usr/local/mysql/data/* ./

完全备份一个或多个完整的库（包括其中所有的表）

mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql   
#导出的就是数据库脚本文件
 
例:
 
mysqldump -u root -p --databases liu > /opt/kgc.sql       #备份一个kgc库
mysqldump -u root -p --databases mysql li > /opt/mysql-kgc.sql    #备份mysql与 kgc两个库

备份所有的库

mysqldump -uroot -p[密码] --all-databases > /备份路径/备份文件名.sql

完全备份指定库中的部分表

mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
 
如：
mysqldump -uroot -p[密码] [-d] HUISUO member1 > /opt/member1.sql
#使用“-d”选项，说明只保存数据库的表结构
#不使用“-d”选项，说明表数据也进行备份

查看备份文件

grep -v "^--" /opt/member1.sql | grep -v "^/" | grep -v "^$"

2.6 增量备份与恢复

2.6.1 增量备份需开启二进制日志功能

vim /etc/my.cnf
#错误日志
log-error=/usr/local/mysql/data/mysql_error.log	 
#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二进制日志
log-bin=mysql-bin	
#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
#配置文件添加完后需要重启MySQL
systemctl restart mysql

2.6.2 可每天进行增量备份操作，生成新的二进制文件

先完成完全备份（在新建好表和库的基础上）

systemctl restart mysqld.service
mysqldump -uroot -p meeting working > /mnt/meeting_working_$(date +%F).sql
mysqldump -uroot -p meeting > /mnt/meeting_$(date +%F).sql
生成新的二进制文件(可每天进行增量备份操作)
mysqladmin -uroot -p flush-logs

2.6.3 查看新生成的日志内容

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002

2.7 恢复的方法

2.7.1 按位置恢复

先删除表
drop table working;
 
清空表内容
truncate table meeting.working;
 
恢复结束点为删除命令前和插入命令后
mysqlbinlog --no-defaults --stop-position='902' usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p

2.7.2 按时间恢复

先清空表CLASS1,方便实验
mysql -uroot -p -e "truncate table meeting.working;"
mysql -uroot -p -e "select * from meeting.woring;"
 
mysqlbinlog --no-defaults --stop-datetime='2021-04-15 15:39:23' /opt/mysql-bin.000003 |mysql -uroot -p
mysql -uroot -p -e "select * from meeting.woring;"