MySQL的备份方式有哪几种？分别如何实现？

目录

一、数据的备份类型

1、数据的备份类型根据其自身的特性主要分为以下几组：

二、MySQL备份数据的方式

三、常见的备份工具

1、一般情况下, 我们需要备份的数据分为以下几种

2、备份工具

3、备份策略

四、如何实现

1、使用cp进行备份

2、xtrabackup实现完全备份

2.1 安装xtrabackup

2.2 Xtrabackup全量备份与恢复

2.3 Xtrabackup增量备份与恢复

3、使用mysqldump备份数据库

---

一、数据的备份类型

1、数据的备份类型根据其自身的特性主要分为以下几组：

• 完全备份：指的是备份整个数据集( 即整个数据库 )

• 部分备份：指的是备份部分数据集(例如: 只备份一个表)

  • 部分备份又分为以下两种：

  • 增量备份：指的是备份自上一次备份以来(增量或完全)以来变化的数据; 特点: 节约空间、还原麻烦；

  • 差异备份：指的是备份自上一次完全备份以来变化的数据 特点: 浪费空间、还原比增量备份简单；

---

二、MySQL备份数据的方式

• 热备份指的是当数据库进行备份时, 数据库的读写操作均不是受影响；
• 温备份指的是当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作；
• 冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线。

MySQL中进行不同方式的备份还要考虑存储引擎是否支持：

	热备	温备	冷备
MyISAM	×	√	√
InnoDB	√	√	√

我们在考虑完数据在备份时, 数据库的运行状态之后还需要考虑对于MySQL数据库中数据的备份方式：

• 物理备份：通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果 

• 逻辑备份：通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)

---

三、常见的备份工具

1、一般情况下, 我们需要备份的数据分为以下几种

• 数据

• 二进制日志, InnoDB事务日志

• 代码(存储过程、存储函数、触发器、事件调度器)

• 服务器配置文件

2、备份工具

常用的几种备份工具

•  mysqldump： 是一个客户端的逻辑备份工具, 可以生成一个重现创建原始数据库和表的SQL语句, 可以支持所有的存储引擎, 对于InnoDB支持热备；
                            是逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备 ；
• cp, tar 等归档复制工具：物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份
• lvm2 snapshot：几乎热备, 借助文件系统管理工具进行备份 
• mysqlhotcopy：名不副实的的一个工具, 几乎冷备, 仅支持MyISAM存储引擎 
• xtrabackup：一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份, 由percona提供

3、备份策略

• 直接cp,tar复制数据库文件：适用于数据量较小 ==> 直接复制数据库文件；

• mysqldump+复制BIN LOGS：适用于数据量还行 ==> 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果；

• lvm2快照+复制BIN LOGS：适用于数据量一般, 而又不过分影响业务运行 ==> 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果；

• xtrabackup：适用于数据量很大, 而又不过分影响业务运行 ==> 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份；

小结：

备份方法	备份速度	恢复速度	便捷性	功能	一般用于
cp	快	快	一般、灵活性低	很弱	少量数据备份
mysqldump	慢	慢	一般、可无视存储引擎的差异	一般	中小型数据量的备份
lvm2快照	快	快	一般、支持几乎热备、速度快	一般	中小型数据量的备份
xtrabackup	较快	较快	实现innodb热备、对存储引擎有要求	强大	较大规模的备份

---

四、如何实现

1、使用cp进行备份

1）查看数据库的信息

show databases;
select * from testdb.t_user;

2) 向数据库施加读锁

FLUSH TABLES WITH READ LOCK; #向所有表施加读锁

3) 备份数据文件

mkdir /backup #创建文件夹存放备份数据库文件
cp -a /var/lib/mysql/* /backup #保留权限的拷贝源数据文件
ls /backup #查看目录下的文件

4) 模拟数据丢失并恢复

#删除数据库的所有文件
rm -rf /var/lib/mysql/* 

#重启MySQL, 如果是编译安装的应该不能启动, 如果rpm安装则会重新初始化数据库
service mysqld restart

#连接到MySQL进行查看, 发现数据丢失了！
SHOW DATABASES;

#恢复数据
cp -a /backup/* /var/lib/mysql/ #将备份的数据文件拷贝回去

service mysqld restart #重启MySQL

5) 数据成功恢复

2、xtrabackup实现完全备份

Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：

• 备份过程快速、可靠；
• 备份过程不会打断正在执行的事务；
• 能够基于压缩等功能节约磁盘空间和流量；
• 自动实现备份检验；
• 还原速度快；

我们这里使用xtrabackup的前端配置工具innobackupex来实现对数据库的完全备份

2.1 安装xtrabackup

​
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar

tar xf Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar

yum install percona-xtrabackup-24-2.4.9-1.el6.x86\_64.rpm -y

innobackupex -v

2.2 Xtrabackup全量备份与恢复

a. 全量备份

innobackupex --defaults-file=/etc/my.cnf --user=root --password="123456" --backup /extrabackup

b. 恢复数据

(1) 删除数据库 testdb;

(2) 全备恢复

--apply-log：//应用 BACKUP-DIR 中的 xtrabackup_logfile 事务日志文件。一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处于不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件使得数据文件处于一致性状态。

innobackupex --apply-log /extrabackup/2023-05-25_10-13-31/

#恢复数据之前需要保证数据目录是空的状态

systemctl stop mysqld
rm -rf /var/lib/mysql/*
chown -R mysql:mysql /var/lib/mysql
1027 chmod -R 777 /var/lib/mysql

#恢复
innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /extrabackup/2023-05-25_10-13-31/

数据成功恢复：

2.3 Xtrabackup增量备份与恢复

a. 增量备份

需要注意的是，增量备份仅能应用于InooDB或XtraDB表，对于MyISAM表，增量与全备相同。

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental /backup/ --incremental-basedir=/extrabackup/2023-05-25_10-13-31

参数说明：
#--incremental /backup/ 指定增量备份文件备份的目录

#--incremental-basedir 指定上一次全备或增量备份的目录

#删除一条数据来测试增量恢复：

  

b. 恢复数据

#将增量备份1、增量备份2…合并到完整备份
innobackupex --apply-log --redo-only /extrabackup/2023-05-25_10-13-31/
innobackupex --apply-log --redo-only /extrabackup/2023-05-25_10-13-31/ --incremental-dir=/backup/2023-05-25_11-12-19/

#恢复数据之前需要保证数据目录是空的状态

systemctl stop mysqld
rm -rf /var/lib/mysql/*

# 恢复数据
innobackupex --defaults-file=/etc/my.cnf --copy-back /extrabackup/2023-05-25_10-13-31/

# 开启MySQL服务
chown -R mysql:mysql /var/lib/mysql
chmod -R 777 /var/lib/mysql
systemctl start mysqld

数据成功恢复：

3、使用mysqldump备份数据库

1）备份命令

a. 格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql

b. 导出的数据有可能比较大，不好备份到远程，这时候就需要进行压缩：

格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 | gzip > 文件名.sql.gz

c. 备份实例上所有的数据库

格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --all-databases > 文件名.sql

d. 备份同个库多个表

格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 表1 表2 .... > 文件名.sql

e. 同时备份多个库

格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --databases 数据库名1 数据库名2 数据库名3 > 文件名.sql

2)  恢复数据

mysql -u username -P [dbname] < filename.sql