一、备份的核心意义

数据库备份是保障数据安全的关键手段，主要作用在于防范数据丢失风险，应对硬件故障、人为误操作、SQL注入、数据误删、服务器宕机等突发情况，同时满足数据迁移、版本回滚、故障排查、合规审计等需求，是运维工作中必不可少的环节。

二、MySQL备份类型划分

1. 按备份方式划分

• 物理备份：直接复制数据库的物理文件（数据文件、日志文件、配置文件等），备份速度快、恢复快，适合大数据量场景，缺点是跨平台兼容性差。常见工具：ibbackup、XtraBackup、MySQL Enterprise Backup。

• 逻辑备份：导出数据库的SQL语句（建库、建表、插入数据等），备份文件体积小、可读性强、跨平台兼容，缺点是备份和恢复速度较慢，大数据量下效率低。常见工具：mysqldump、mysqlpump、mydumper。

2. 按备份范围划分

• 全量备份：备份整个数据库的所有数据和结构，备份完整，恢复简单，但耗时久、占用空间大，适合作为基础备份。

• 增量备份：仅备份上一次全量备份或增量备份后变化的数据，备份速度快、占用空间小，恢复时需要依赖全量备份和历次增量文件，步骤繁琐。

• 差异备份：备份上一次全量备份后变化的数据，恢复比增量简单，只需全量+最新差异文件，空间占用介于全量和增量之间。

3. 按备份状态划分

• 热备份：数据库正常运行，读写不受影响，业务无中断，适合生产环境。

• 温备份：数据库运行中，仅加读锁，禁止写入，会短暂影响业务写入。

• 冷备份：数据库停止服务后备份，数据一致性最高，但会导致业务停机。

创建需要所需的库和表

create database auth;
use auth;
create table users (user_name char(16),user_passwd char(65) default '');
insert into users values('zhangsan',SHA2('123456',256));
insert into users values('lisi',SHA2('123456',256));

备份数据库

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /backup

[root@localhost ~]# cd /usr/local/
[root@localhost local]# tar zcvf /backup/mysql_$(date +%F).tar.gz mysql/
[root@localhost local]# cd /backup/
[root@localhost backup]# ls
mysql_2023-06-08.tar.gz

模拟故障

[root@localhost backup]# systemctl start mysqld
[root@localhost backup]# mysql -u root -p -e 'drop database auth;'
[root@localhost backup]# mysql -u root -p -e 'select * from auth.users;'

恢复数据库

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# cd /backup/
[root@localhost backup]# tar zxvf mysql_2023-06-08.tar.gz

[root@localhost backup]# cd /usr/local/
[root@localhost local]# mv mysql/ mysql.bak

[root@localhost local]# cd /backup/
[root@localhost backup]# mv mysql /usr/local/
[root@localhost backup]# systemctl start mysqld
[root@localhost backup]# mysql -u root -p -e 'select * from auth.users;'

2.mysqldump备份与恢复     

在服务启动状态下使用

①导出数据

[root@localhost ~]# mysqldump -u root -p auth users > /opt/auth-users.sql       \\导出数据表
[root@localhost ~]# mysqldump -u root -p auth > /opt/auth.sql         \\导出数据库
[root@localhost ~]# mysqldump -u root -p --opt --all-databases >/opt/all-data.sql     ##备份所有库

②导入表

[root@localhost ~]# mysql -u root -p -e 'create database test;'  
[root@localhost ~]# mysql -u root -p test < /opt/auth-users.sql   ##导入到其他库
[root@localhost ~]# mysql -u root -p auth < /opt/auth-users.sql   ##导入到原始所在的库
[root@localhost ~]# mysql -u root -p -e 'show tables from test;' 

③导入库

[root@localhost ~]# mysql -u root -p -e 'drop database auth;'  
[root@localhost ~]# mysql -u root -p -e 'show databases;' 
[root@localhost ~]# mysql -u root -p -e 'create database auth;' 
[root@localhost ~]# mysql -u root -p auth</opt/auth.sql

[root@localhost ~]# mysql -u root -p -e 'show databases;' 
[root@localhost ~]# mysql -uroot -p -e 'select * from auth.users;'

④全库还原

保证至少已经创建了这些库

[root@localhost ~]# mysql -u root -p </opt/all-data.sql

3.增量备份与恢复

1.启用二进制日志功能

（1）修改主配置文件

[root@localhost ~]# vim /etc/my.cnf

在[mysqld]模块中添加

log-bin=mysql-bin

server-id=1

binlog_format = MIXED

[root@localhost ~]# systemctl restart mysqld

（2）生成新的日志文件

[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# mysqladmin -u root -p flush-logs

（3）查看日志内容

mysqlbinlog --no-defaults mysql-bin.000002

如果sql语句被加密就是用以下命令

mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002

2.向表添加数据（多添加一些）

mysql> use auth;

为了验证效果，逐个添加新的数据，并生成一个日志文件

use auth;

insert into users values('aaa','123456');

insert into users values('bbb','123456');

insert into users values('ccc','123456');

insert into users values('ddd','123456');

insert into users values('eee','123456');

insert into users values('fff','123456');

3.模拟误操作将auth.users表删掉

[root@localhost data]# mysql -u root -p -e 'drop table auth.users;'
[root@localhost data]# mysql -u root -p -e 'select * from auth.users;'  ##查看表，已经不存在

4.常规恢复操作

①先恢复完全备份

[root@localhost data]# mysql -u root -p auth </opt/auth.sql 
[root@localhost data]# mysql -u root -p -e 'select * from auth.users;'   ##查看表，已经存在，但数据不全

②再恢复增量备份

[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002
注意：要先查看日志内容，确定是需要恢复的数据

[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'    ##查看表的内容

5.基于位置的恢复

①模拟误操作

[root@localhost mysql]# mysql -uroot -p -e 'drop table auth.users;'
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'   ##没有了数据

②恢复完全备份

[root@localhost mysql]# mysql -u root -p auth < /opt/auth.sql

③查看日志内容

[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002
[root@localhost mysql]# mysqlbinlog --no-defaults --stop-position='521' mysql-bin.000002 | mysql -u root -p

④恢复到指定位置

[root@localhost mysql]# mysqlbinlog --no-defaults --stop-position='521' mysql-bin.000002 | mysql -u root -p      ##恢复到521之前的一个数据，不包含521

⑤从指定的位置向后开始恢复

[root@localhost mysql]# mysqlbinlog --no-defaults --start-position='521' mysql-bin.000002 | mysql -u root -p     ##从521之后开始恢复，包含521

⑥恢复指定范围内的数据

[root@localhost mysql]# mysqlbinlog --no-defaults --start-position='521' --stop-position='729' mysql-bin.000002  | mysql -u root -p

6.基于时间点恢复

①恢复到指定的时间

[root@localhost mysql]# mysqlbinlog --no-defaults --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002  | mysql -u root -p

②从指定的时间开始恢复

[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' mysql-bin.000002  | mysql -u root -p

③恢复指定时间范围内的数据

[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002  | mysql -u root -p

五.总结

MySQL备份与恢复的核心是“防患于未然”：合理选择备份类型（全量+增量/差异结合）、使用合适的备份工具（中小数据量mysqldump，大数据量xtrabackup）、设计科学的备份策略，同时定期校验备份文件、执行恢复测试，才能在故障发生时快速恢复数据，最大限度减少业务损失。

实际应用中，需结合业务数据量、可用性要求（如7×24小时运行）、存储资源等因素，灵活调整备份策略，确保数据安全与业务连续性。