MySQL全量、增量备份与恢复

物理备份是对数据库文件（如数据文件、日志文件等）进行直接拷贝。它备份的是数据库的物理存储结构，备份文件可以直接用于恢复数据库。物理备份的优点是恢复速度快，因为它直接拷贝物理文件，不需要重新执行 SQL 语句来重建数据结构和数据。缺点是备份文件与数据库版本紧密相关，不同版本的数据库可能无法使用同一备份文件进行恢复，而且备份文件占用空间较大。

逻辑备份

逻辑备份是通过执行 SQL 语句来生成包含数据库对象（如表、视图、存储过程等）定义和数据的文件。常见的逻辑备份工具如MySQL dump。逻辑备份的优点是备份文件通用性强，不受数据库版本影响，可以在不同版本的 MySQL 数据库上恢复数据，而且备份文件可以方便地进行编辑和查看。缺点是恢复时需要重新执行 SQL 语句，恢复速度相对较慢，尤其是在数据量较大的情况下。

2.2从数据库的备份策略角度分类

完全备份

完全备份是对整个数据库进行完整的备份，包括所有的数据文件、日志文件和数据库对象。它是最基本的备份方式，优点是恢复简单，只需将备份文件恢复到指定位置即可。缺点是备份时间长，占用存储空间大，每次备份都需要拷贝所有数据，即使数据没有发生变化。

差异备份

差异备份是基于上一次完全备份，只备份自上一次完全备份以来发生变化的数据。与完全备份相比，差异备份的备份时间较短，占用空间较小。缺点是随着时间推移，差异备份文件会越来越大，因为它包含了从上次完全备份到当前的所有变化数据。

增量备份

增量备份是基于上一次备份（可以是完全备份或增量备份），只备份自上一次备份以来发生变化的数据。它是最节省空间和时间的备份方式，但恢复过程相对复杂，需要依次恢复上一次完全备份和所有的增量备份。

3.常见的备份方法

3.1物理冷备份

物理冷备份是在数据库关闭状态下进行的物理备份。它的优点是备份速度快，因为数据库处于关闭状态，不会有数据写入和修改操作，避免了数据不一致的问题。

3.2专用备份工具 MySQL dump或MySQL hotcopy

MySQL dump是 MySQL 官方提供的逻辑备份工具，它通过执行 SQL 语句将数据库对象和数据导出到文件中。MySQL hotcopy是一个用于快速备份 MySQL 数据库的脚本，它通过直接拷贝数据库文件来实现备份，但要求数据库必须使用 MyISAM 存储引擎。

3.3通过启用二进制日志进行增量备份

MySQL 的二进制日志记录了所有对数据库进行修改的 SQL 语句，通过启用二进制日志，可以实现基于时间点或基于位置的增量备份和恢复。这种方式可以在不停止数据库服务的情况下进行备份，并且能够精确恢复到指定的时间点或操作位置。

3.4通过第三方工具备份

除了 MySQL 官方提供的备份工具外，还有许多第三方备份工具可供选择，如 XtraBackup、Percona XtraBackup 等。这些工具通常具有更强大的功能，如热备份（在数据库运行状态下进行备份）、增量备份、并行备份等，可以满足不同用户的需求。

二：数据库完全备份操作

1.物理冷备份与恢复

1.1备份数据库

创建一个/backup目录作为备份数据存储路径,使用 tar创建备份文件。
整个数据库文件夹备份属于完全备份。

[root@localhost ~]# systemctl stop mysqld
[root@localhost~]# mkdir /backup
[root@localhost~]# tar zcf /backup/mysql_all-$(date +%F).tar.gg2/usr/local/mysql/data/
[root@localhost ~]# ls -l /backup/

1.2恢复数据库

执行下面操作将数据库文件/usr/local/mysql/data/转移至bak目录下,模拟故障。

[root@localhost~]# mkdir bak
[root@localhost~]# mv/usr/local/mysql/data//root/bak/
[root@localhost~]# mkdir restore
[root@localhost~]# tar zxf /backup/mysql_all-2025-03-22.tar.gz -C 
restore/
[root@localhost~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/
[root@localhost ^]# systemctl start mysqld

2.MySQL dump备份与恢复

2.1备份数据库

格式1：备份指定库中的部份表

假设要备份test数据库中的table1和table2表，可以使用以下命令：

mysqldump -u [username] -p test table1 table2 > /backup/test_table1_table2.sql

执行命令后，会提示输入密码，输入正确密码后，备份文件将保存到/backup/test_table1_table2.sql。

格式2：备份一个或多个完整的库

备份test数据库，可以使用以下命令：

mysqldump -u [username] -p test > /backup/test_full.sql

备份test和test2两个数据库：

mysqldump -u [username] -p test test2 > /backup/test_test2_full.sql

格式3：备份MySQL服务器中所有的库

mysqldump -u [username] -p --all-databases > /backup/all_databases.sql

2.2查看备份文件

备份文件是 SQL 格式的文本文件，可以使用文本编辑器（如vim、nano等）打开查看。例如，使用vim打开备份文件：

vim /backup/test_full.sql

在文件中可以看到创建数据库对象和插入数据的 SQL 语句。

2.3恢复数据库

使用mysqldump命令导出的SQL备份脚本,在需要恢复时可以通过mysql命令对其进行导入操作,命令格式如下所示。
mysql[选项][库名][表名]</备份文件名
当备份文件中只包含表的备份,而不包含创建的库的语句时,执行导入操作时必须指定库名,且目标库必须存在。
例如,执行以下操作可以从备份文件mysql-user.sql中将表导入test库。其中"-e"选项是用于指定连接 MySQL后执行的命令,命令执行完后自动退出。

[root@localhost ~]# mysql -u root -p test < mysql-user.s(q)
Enter password:
[root@localhost~]# mysql -u root -p -e 'SHOW TABLES FROM test;'//验证
导入结果
Enter password:
Tables_in_test|
user
若备份文件中已经包括完整的库信息,则执行导入操作时无多顺指定库名

例如,执行以下操作可以从备份文件test.sgl恢复test库3。

[root@localhost ^]# mysql -u root -p -e 'DROP DATABASE test国
//删除 test数据库,模拟故障
Enter password:
[root@localhost ^]# mysql -u root -p -e 'SHOW DATABASES;
//查看test数据库是否存在
Enter password:
[root@localhost data]# mysql -u root -p <^/test.sql
//执行导入恢复操作
Enter password:
[root@localhost ^]# mysql -u root -p -e 'SHOW DATABASES;'
//确认恢复后结果
Enter password:

除了使用mysql命令结合"<"恢复数据外,还可以使用ssource命令恢复数
据,具体用法如下

[root@localhost `]# mysql -u root -p
Enter password:
mysql> source /root/test.sql
Query OK, O rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

3.MySQL增量备份与恢复

3.1MySQL增量备份概述

（1）增量备份的特点

增量备份只备份自上一次备份以来发生变化的数据，因此备份时间短、占用空间小。但恢复时需要依次应用上一次完全备份和所有的增量备份，恢复过程相对复杂。

（2）MySQL二进制日志对备份的意义

MySQL 的二进制日志记录了所有对数据库进行修改的 SQL 语句，通过分析二进制日志，可以确定自上一次备份以来发生的变化，从而实现增量备份和基于时间点或基于位置的恢复。

3.2MySQL增量恢复

一般恢复

一般恢复是依次应用上一次完全备份和所有的增量备份。基于位置的恢复

基于位置的恢复

数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作,通过基于位置进行恢复可以更加精准,命令格式如下所示。

格式1：恢复数据到指定位置

假设要恢复到二进制日志mysql-bin.000001的第1000个字节位置，可以使用以下命令：

mysqlbinlog --stop-position=1000 /var/log/mysql/mysql-bin.000001 | mysql -u [username] -p

格式2：从指定的位置开始恢复数据

从指定的位置开始恢复数据：从二进制日志mysql-bin.000001的第1000个字节位置开始恢复数据：

mysqlbinlog --start-position=1000 /var/log/mysql/mysql-bin.000001 | mysql -u [username] -p

基于时间点的恢复

跳过某个发生错误的时间点实现数据恢复，而基于时间点的恢复可以分成三种情况

格式1：从日志开头截止到某个时间点的恢复

假设要恢复到2025-01-01 12:00:00这个时间点，可以使用以下命令：

mysqlbinlog --stop-datetime="2025-01-01 12:00:00" /var/log/mysql/mysql-bin.* | mysql -u [username] -p

格式2：从某个时间点到日志结尾的恢复

从2025-01-01 12:00:00这个时间点到日志结尾恢复数据：

mysqlbinlog --start-datetime="2025-01-01 12:00:00" /var/log/mysql/mysql-bin.* | mysql -u [username] -p

格式3：从某个时间点到某个时间点恢复

从2024-01-01 12:00:00到2025-01-01 13:00:00恢复数据：

mysqlbinlog --start-datetime="2025-01-01 12:00:00" --stop-datetime="2025-01-01 13:00:00" /var/log/mysql/mysql-bin.* | mysql -u [username] -p

3.3MySQL启用备份案例

1.一般恢复

（1）添加数据库，表，录入信息

到 MySQL 命令行，创建数据库和表，并插入数据：

CREATE DATABASE test;
USE test;
CREATE TABLE user_info (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT);
INSERT INTO user_info (name, age) VALUES ('John', 25), ('Alice', 30);

（2）先进行一次完全备份

为方便验证二进制日志的增量恢复功能,在插入三条用户数据后先对client数据库的user_info表进行一次完全备份。然后在1Linux 系统命令行下执行"mysqladmin-uroot-pflush-logs"命令或在"myssql>"命令提示符下执行"flushlogs;"生成新的二进制日志。

[root@localhost ~]# mkdir /mysql_bak
[root@localhost~]#    mysqldump    -uroot    clientuser_info    >/mysql_bak/client_userinfo-$(date+%F). sql
Enter password:
[root@localhost~]# ls /mysql_bak/
client_userinfo-2025-03-24. sql
[root@localhost ~]# mysqladmin -uroot -p flush-logs
Enter password:
[root@localhost ~]# ls -1 /usr/local/mysql/data/mysql-bin. *

（3）继续录入新的数据并进行增量备份

继续录入两个用户的数据,并执行"mysqladmin-uroot-pflush-logs命令刷新二进制日志,进行增量备份。如此,二进制日志文件mysql-bin. 0000002中仅保留插入两个用户数据的操作。

mysql>USE client;
Database changed
mysql>INSERT INTO user_info VALUES('00009','019', '37')
Query OK, I row affected (0.02 sec)
mysql>INSERT INTO user_info VALUES('000010',,'孙七','男',020',36')
Query OK, I row affected (0.02 sec)
mysql>SELECT * FROM user_info;
[root@localhost ~]# mysqladmin -uroot -p flush-logs
Enter password:
[root@localhost~]# ls -l /usr/local/mysql/data/mysql-bin.*
[root@localhost~]#
/bin/cp /usr/local/mysql/data/mysql-bin.000002
mysql_baky

（4）模拟误操作删除user_info表

[root@localhost~]# mysql -uroot -p -e 'DROP TABLE client.user_info;'
Enter password:
[root@localhost ~]# mysql -uroot -p -e ' SELECT * FROM client.user_info;
Enter password:
ERROR 1146 (42S02) at line 1: Table 'client.user_info'doesn't exist

（5）恢复操作

再执行恢复操作时,需要先恢复完全备份,然后恢复增量备份。

[root@localhost~]#mysql -uroot -p  client  </mysql_bak/client_userinfo-2025-03-24. sql
//恢复完全备份
Enter password:
[root@localhost ~]# mysql -uroot -p -e ' SELECT * FROMclient.user_info;
Enter password:
[root@localhost~]# mysqlbinlog  --no-defaults/mysql_bak/mysql-bin.00000002| mysql -u root -p
//恢复增量备份
Enter password:
[root@localhost `]# mysql -uroot -p -e 'SELECT * FROM client. user_info;
Enter password:

2.基于位置恢复

由于前面已经做过备份操作,接下来直接进行模拟故障与数敢据恢复的操作。

[root@localhost~]# mysql -uroot -p -e 'DROP TABLE client.user_info;
//模拟误操作删除user_info表
Enter password:
[root@localhost ^]# mysql -uroot -p -e 'SELECT * FROM client. user_info;
//查看user_info表是否存在
Enter password:
ERROR 1146 (42S02) at line 1: Table 'client.user info'doesn't exist
[root@localhost~]#mysql  -uroot   -p   client  </mysql_bak/client_userinfo-2025-03-24. sql
//恢复完全备份
Enter password:

想要实现基于位置或时间点恢复数据,必须先通过查看二进制日志文件确定恢复的位置或时间点。使用"mysqlbinlog--no-defaults二进制日志文件"可以查看二进制日志文件的具体内容。

[root@localhost~] # mysqlbinlog --no-defaults /mysql_bak/mysql-bin.0000002

3.基于时间点恢复

基于时间点的数据恢复所使用的选项是"--stop-datetime",指定的时间同样也是查询二进制日志所得。执行以下操作可以实现仅恢复到16:41:24之前的数据,即不恢复"孙七"的信息。

[root@localhost ^]# mysql -uroot -p -e 'DROP TABLE client. user_info;
Enter password:
[root@localhost~]# mysql -uroot -p  client </mysql_bak/client_userinfo-2025-03-24. sql
Enter password:
[root@localhost~]#mysqlbinlog --no-defaults --stop-datetime=' 2025-03-24-19:29:28'/mysql_bak/mysql-bin.0000002
--no-defaults mysql -uroot -p
Enter password:
[root@localhost ^]# mysql -uroot -p -e ' SELECT * FROM cl;ient.user_info;
Enter password:

执行以下操作可以实现仅恢复孙七的信息，跳过赵六的信息恢复。

[root@localhost ^]# mysql -uroot -p -e ' DROP TABLE client.user info;
Enter password:
[root@localhost~]# mysql -uroot -p client </mysql_bak/client_userinfo-2025-03-24. sql
Enter password:
[root@localhost~]#mysqlbinlog --no-defaults --start-datetime=' 2025-03-24 19:29:28'/mysql_bak/mysql-bin.0000002|mysql -uroot -p
Enter password:
[root@localhost ^]# mysql -uroot -p -e ' SELECT * FROM c]lient.user_info;
Enter password:

三：制定企业备份策略的思路

在企业中备份策略并不是千篇一律的,而是根据每个企业上的实际生产环境与业务需求制定合适的备份策略。无论是选择完全备份,还是选择增量备份,都需考虑它们的优缺点,是否适合当前的生产环境。

在进行热备份时,备份操作和应用服务在同时运行,这样就十分消耗系统资源了,导致数据库服务性能下降,这就需要选择一个合适的时间(如在应用负担很小的时候)再来进行备份操作。

需要注意的是,不是备份完就万事大吉,最好确认备份是是否可用,所以备份之后的恢复测试是非常有必要的。同时备份时间也要灵活舌调整,如

>数据更新频繁,则应该频繁地备份。
>数据的重要性,在有适当更新时进行备份。
>在数据库压力小的时间段进行备份,如一周一次完全备份,每天进行增量备份。
>中小公司,完全备份一般一天一次即可。
>大公司可每周进行一次完全备份,每天进行一次增量备份。
>尽量为企业实现主从复制架构,以增加数据的可用性。

四：扩展：MySQL和GTID和XtraBackup

1.MySQL的GTID

GTID（Global Transaction Identifier）是 MySQL 5.6 版本引入的一个新特性，它为每个事务分配一个唯一的标识符，用于标识事务的来源和执行顺序。通过 GTID，可以更方便地进行主从复制、数据恢复和故障转移。

（1）配置my.cnf开启gtid

[root@localhost ^]# vim /etc/my.cnf
[mysqld]
gtid_mode = ON
enforce_gtid_consistency=ON # 确保事务安全性
[root@localhost ^]# systemctl restart mysqld
验证是否开启
[root@localhost ^]# mysql -uroot -p
mysql> SHOW GLOBAL VARIABLES LIKE
gtid_mode'

（2）创建基本测试库，表，数据

mysql>reset master;#初始化master,会清除所有binlog和gtid信意
mysql>show master status;
创建测试库test,测试表user,并导入3条数据
mysql> create database test;
Query OK, I row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table user(id int);
Query OK, O rows affected (0.07 sec)
mysql> insert into user values(l);
Query OK, I row affected (0.14 sec)
mysql> insert into user values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(3);
Query OK, I row affected (0.00 sec)
mysql> show master status;

（3）全量备份

[root@localhost ^]# mysqldump -u root--databases test >test. sql
关于数据一致性的提醒的警告信息,可忽略
[root@localhost ^]# grep -i gtid test.sql
GTID state at the beginning of the backup
SET
@GLOBAL.GTID_PURGED=/*!80000+' */
d780a5a6-055f-11f0-b6e6-000c29078b04:1-5';

（4）插入新数据

[root@localhost ^]# mysql -uroot -p
mysql> use test;
Database changed
mysql> insert into user values(4);
Query OK, I row affected (0.00 sec)
mysql> insert into user values(5);
Query OK, I row affected (0.00 sec)
mysql> select * from test.user;
mysql> show master status;

（5）模拟数据误删除

mysql> drop database test;
Query OK, I row affected (0.00 sec)
mysql> show master status;

（6）导出增量数据

[root@localhost~]#mysqlbinlog
--include-gtids='d780a5a6-055f-11f0-b6e6-000c29078b04:3-7
/usr/local/mysql/data/mysql-bin.000000001 >/mysqlbak. sql
这里把3-7的事务导出,也就是全量备份后,新插入的两条数据,第8个事务不
能导除,因为它是dropdatabase的误删除语句

（7）恢复全量

[root@localhost ^]#mysql -u root -p -e "RESET MASTER;"
#清空GTID历
史,不然恢复全量备份时会产生冲突,生产环境谨慎操作须提前备份
[root@localhost~]# mysql -uroot -p <test. sql
[root@localhost~]# mysql -uroot -p -e "select * from test.user;
Enter password:

（8）恢复增量

[root@localhost ^]# mysql -uroot -p <mysqlbak.sql
[root@localhost ^]# mysql -uroot -p -e "select * from test. user;"
Enter password:

2.XtraBackup

MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的,如果数据大于50G或100G,存储空间足够的情况下,可以每天进行完整备份,如果每天产生的数据量较大,需要定制数据备份策略。例如每周实用完整备份,周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具,xtrabakackup有2个工具,分别是xtrabakup、innobakupe。

Percona-xtrabackup是Percona公司开发的一个用于MySQL数据库物理热备的备份工具,支持MySQL、Percona server和MariaDB,开源免费,是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表,而不能备份MyISAM数据表。

（1）安装XtraBackup

[root@localhost~]#wgethttps://downloads.percona.com/downToads/Percona
-XtraBackup-8.0/Percona-XtraBackup-8.0.35-30/binary/tarba11/percona-x
trabackup-8.0.35-30-Linux-x86_64.glibc2.17. tar. gz
[root@localhost~]#tar xzf percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.177. tar. gz
[root@localhost~]#mv percona-xtrabackup-8. 0.35-30-Linux-x86_64.glibc2.17 /usr/local/xtrabackup
[root@localhost~]#echo 'export PATH=$PATH:/usr/local/xtrabackup/bin' >> /etc/profile source
[root@localhost ^]#/etc/profile

（2）安装apress

qpress是解压缩需要用到的工具
[root@localhost~]#wget -d --user-agent="Mozilla/5.0 (Windows NT x.y
rv:10.0)   Gecko/20100101
Firefox/10.0'
https://docs-tencentdb-1256569818. cos. ap-guangzhou.myqclfoud. com/gpres
s-11-linux-x64.tar
[root@localhost~]#tar -xf_qpress-11-linux-x64. tar -C /usr/local/bin
source /etc/profile

（3）完整备份与恢复

备份

[root@localhost~]#bakdir="/backup/fullbackups/$(date'+%F')"
[root@localhost`]#mkdir -p $bakdir
#--backup备份
#--compress压缩 
[root@localhost`]#xtrabackup --defaults-file=/etc/my.cnf --user=bkpuser --password=s3cret --backup --compress --target-dir=$bakdir

恢复（恢复数据的时候要先关闭MySQL，清理数据存储目录）

解压备份数据
[root@localhost~]#xtrabackup --defaults-file=/etc/my.cnf
-user=bkpuser --password=s3cret --decompress --target-dir=$bakdir
#准备备份文件以供恢复
[root@localhost~]#xtrabackup --prepare --target-dir=$baakdir
#恢复数据
[root@localhost~]#xtrabackup --defaults-file=/etc/my.cnf
--user=bkpuser --password=s3cret --copy-back --target-dir=$bakdir
#修改权限,替换成自己的数据存储目录
[root@localhost~]#chown -R mysql:mysql /var/lib/mysql
#然后启动MySQL即可
[root@localhost`]#systemctl start mysqld

（4）增量备份与恢复

备份

[root@localhost~]#fulldir="/backup/fullbackups/$(date
[root@localhost~]#incdir="/backup/incrementalbackups/$(datee, +%F,)
[root@localhost~]#mkdir -p $fulldir
[root@localhost~]#mkdir -p $incdir
准备基础数据后进行全量备份
[root@localhost~]#xtrabackup --defaults-file=/etc/my.cnf --user=bkpuser             --password=s3cret --backup --compress --target-dir=$fulldir
插入新数据后进行增量备份
[root@localhost~]#xtrabackup --defaults-file=/etc/my.cnf --user=bkpuser             --password=s3cret --backup --compress --target-dir=$incdir --incremental-basedir=$fullddir
在备份的目录里面会有xtrabackup_checkpoints文件

增量恢复

恢复数据的时候要先关闭MySQL,清理数据存储目录
#解压备份数据
[root@localhost~]#xtrabackup --defaults-file=/etc/my.cnf --user=root
--password= --decompress --target-dir=$fulldir
[root@localhost~]#xtrabackup --defaults-file=/etc/mycnf --user=root
--password= --decompress --target-dir=$incdir
#准备数据
[root@localhost~]#xtrabackup --prepare --apply-log-only --target-dir=$fulldir
#把增量备份的数据合并到完整备份里面
[root@localhost~]#xtrabackup --prepare --apply-log-only --target-dir=$fulldir       --incremental-dir=$incdir
#完整重放日志
[root@localhost~]#xtrabackup --prepare --target-dir=$ifulldir
#恢复数据
[root@localhost~]#xtrabackup --defaults-file=/etc/my.cnf --user=root
--password= --copy-back --target-dir=$fulldir
#修改权限,替换成自己的数据存储目录
[root@localhost~]#chown -R_mysql:mysql /usr/local/myse1/data
#然后启动MySQL即可
[root@localhost`]#systemctl start mysqld