MySQL–数据库备份

大家好，欢迎来到IT知识分享网。

前言：本博客仅作记录学习使用，部分图片出自网络，如有侵犯您的权益，请联系删除

一、为什么要备份

• 备份：能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。
• 冗余： 数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库 集群。

二、MySQL数据备份需要重视的内容

• 备份内容：databases、 Binlog、 my.cnf
• 所有备份数据都应放在非数据库本地，而且建议有多份副本。
• 测试环境中做日常恢复演练，恢复较备份更为重要。

备份过程必须考虑因素：

 1. 数据的一致性  2. 服务的可用性  Mysql A (读写) 压力比较大  |  Mysql B (读) 压力比较小 从节点做备份

三、MySQL备份类型

1、物理备份

直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

（1）热备（hot backup）

在线备份，数据库处于运行状态，这种备份方法依赖于数据库的日志文件；对应用基本无影响（但是性能还是会有下降,所以尽量不要在主库上做备份,在从库上做）

（2）冷备（cold backup）

备份数据文件，需要停机，是在关闭数据库的时候进行的；

• 备份 datadir 目录下的所有文件

（3）温备（warm backup）

• 针对myisam的备份(myisam不支持热备),备份时候实例只读不可写，数据库锁定表格（不可写入但可 读）的状态下进行的
• 对应用影响很大
• 通常加一个读锁

2、逻辑备份

备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。

3、物理和逻辑备份的区别

四、MySQL备份工具

 1、ibbackup  官方备份工具；收费；物理备份  ​  2、xtrabackup  开源社区备份工具；开源免费；物理备份  ​  3、mysqldump  官方自带备份工具；开源免费；逻辑备份（速度慢）  ​  4、mysqlbackup  mysql 官方备份工具；innodb 引擎的表mysqlbackup 可以进行热备；非innodb 表mysqlbackup 就只能温备；物理备份，备份还原速度快；适合大规模数据使用；

五、物理备份的方式

1、完全备份——完整备份

每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。

优点：备份与恢复操作简单方便，恢复时一次恢复到位，恢复速度快

缺点：占用空间大，备份速度慢

2、增量备份

每次备份上一次备份到现在产生的新数据；只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化；

特点：因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起按备份时间顺序，逐个备份版本进行恢复，恢复时间长，如中间某次的备份数据损坏，将导致数据的丢失。

3、差异备份

只备份跟完整备份不一样的；备份那些自从第一次次完全备份之后被修改过的所有文件，备份的时间起点是从第一次的完整备份起，且以 后每次备份都是和第一次完整备份进行比较（注意是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件；备份数据量会越来越大；

特点：占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间。

六、percona工具

Xtrabackup 是开源免费的支持MySQL 数据库热备份的软件，在 Xtrabackup 包中主要有Xtrabackup 和innobackupex 两个工具。其中 Xtrabackup 只能备份 InnoDB 和 XtraDB 两种引擎; innobackupex则是封装了Xtrabackup，同时增加了备份MyISAM引擎的功能。它不暂停服务创建Innodb热备份；

Software Downloads – Percona

1、安装xtrackbackup

百度自行搜索；

（1）上传peronaRPM包

 # yum -y install percona-xtrabackup-24-2.4.5-1.el7.x86_64.rpm 

2、完全备份流程

 1、systemctl stop mysqld # 关闭数据库  2、mkdir /xtrabackup/ # 创建备份目录  3、innobackupex --user=root --password=123 /xtrabackup/ # 备份  4、innobackupex --apply-log /xtrabackup/<要恢复的目录> # 准备恢复数据  5、innobackupex --copy-back /xtrabackup/<要恢复的目录> # 恢复数据  6、chown mysql.mysql /data -R # 添加权限

具体示例演示：

 # 数据库备份  [root@centos /]# innobackupex --user=root --password=123 /xtrabackup/  # 备份完成，在/xtrabackup/目录下能看到备份好的目录  [root@centos /]# cd /xtrabackup/  [root@centos xtrabackup]# ls  2024-07-26_15-41-02  --------------------------------------------------------  # 实验：删除数据库中的数据  [root@centos /]# rm -rf /data/mysql/data/ # 删除完数据，可进入数据库查看库进行验证，开始恢复数据  [root@centos /]# innobackupex --apply-log /xtrabackup/2024-07-26_15-41-02  [root@centos /]# innobackupex --copy-back /xtrabackup/2024-07-26_15-41-02  [root@centos /]# chown mysql.mysql /data -R # 恢复完成  [root@centos /]# systemctl restart mysqld  [root@centos /]# mysql  mysql> show databases;

3、增量备份流程

基本语法：

 1、systemctl stop mysqld  2、innobackupex --user=root --password=123 /xtrabackup/ # 完全备份  3、innobackupex --user=root --password=123 --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/完全备份的目录  4、innobackupex --user=root --password=123 --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/上一次增量的备份目录  ​  5、innobackupex --apply-log --redo-only /xtrabackup/全备目录  6、innobackupex --apply-log --redo-only /xtrabackup/全备 --incremental-dir=/xtrabackup/要恢复的上一次增量目录  7、innobackupex --copy-back /xtrabackup/全备

具体示例演示：

 [root@centos /]# innobackupex --user=root --password=123 /xtrabackup/ #全备  [root@centos /]# cd /xtrabackup # 检查第一次全备目录  [root@centos xtrabackup]# ls  2024-07-26_16-54-18  ​  ​  # 开始实验，创建周一sunday库  mysql> create database sunday;  # 第一次备份选用上个备份目录，这里即全备目录  [root@centos /]# innobackupex --user=root --password=123 --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2024-07-26_16-54-18  # 查看目录，发现多了带有sunday库的目录  [root@localhost xtrabackup]# ls  2024-07-26_16-54-18  2024-07-26_17-01-01  ​  ​  # 再创建一个周二tuesday库  mysql> create database tuesday;  # 第二次增量备份选用上次目录，即从sunday库备份的目录  [root@centos /]# innobackupex --user=root --password=123 --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2024-07-26_17-01-01  [root@localhost xtrabackup]# ls # 查看，多出的目录即带有tuesday的备份目录  2024-07-26_16-54-18  2024-07-26_17-01-01  2024-07-26_17-03-34  ​  # 增加权限并重启mysqld服务  [root@centos /]# rm -rf /data/mysql/data  [root@centos /]# chown -R mysql.mysql /data  [root@centos /]# systemctl restart mysqld  mysql> show databases; # 检查数据库，发现无数据库;开始恢复  [root@centos /]# innobackupex --apply-log --redo-only /xtrabackup/2024-07-26_16-54-18  [root@centos /]# innobackupex --apply-log --redo-only /xtrabackup/全备 --incremental-dir=/xtrabackup/2024-07-26_17-03-34 # 这里选择恢复tuesday备份目录  mysql> show databases; # 检查数据库，发现数据库中连带monday库一起恢复  +--------------------+  | Database           |  +--------------------+  | information_schema |  | monday             |  | mysql             |  | performance_schema |  | sys               |  | tuesday           |  +--------------------+

4、差异备份流程

七、mysqldump逻辑备份

推荐优先使用

1、常用备份选项

 -A, --all-databases #备份所有库  -B, --databases #备份多个数据库  -F, --flush-logs #备份之前刷新binlog日志  --default-character-set #指定导出数据时采用何种字符集，如果数据表不是采用默认的latin1  字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。  --no-data，-d #不导出任何数据，只导出数据库表结构。  --lock-tables #备份前，锁定所有数据库表  --single-transaction #保证数据的一致性和服务的可用性  -f, --force #即使在一个表导出期间得到一个SQL错误，继续。

使用 mysqldump 备份数据库时避免锁表:

对一个正在运行的数据库进行备份请慎重！！ 如果一定要在服务运行期间备份，可以选择添加 –single-transaction选项， 类似执行： mysqldump –single-transaction -u root -p dbname > mysql.sql

2、备份表

 语法: # mysqldump -u root -p1 db1 t1 > /db1.t1.bak  [root@mysql-server ~]# mkdir /home/back # 创建备份目录  [root@mysql-server ~]# mysqldump -uroot -p'qf123' company employee5 >  /home/back/company.employee5.bak  # 备份多个表：  语法: mysqldump -u root -p1 db1 t1 t2 > /db1.t1_t2.bak  [root@mysql-server ~]# mysqldump -uroot -p'qf123' company new_t1 new_t2 >  /home/back/company.new_t1_t2.bak

3、备份库

 备份一个库：相当于将这个库里面的所有表全部备份。  语法: # mysqldump -u root -p1 db1 > /db1.bak  [root@mysql-server ~]# mysqldump -uroot -p'qf123' company >  /home/back/company.bak  备份多个库：  语法：mysqldump -u root -p1 -B db1 db2 db3 > /db123.bak  [root@mysql-server ~]# mysqldump -uroot -p'qf123' -B company testdb >  /home/back/company_testdb.bak  备份所有的库：  语法：# mysqldump -u root -p1 -A > /alldb.bak  [root@mysql-server ~]# mysqldump -uroot -p'qf123' -A > /home/back/allbase.bak

4、恢复数据库和表

为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志 因为binlog使用binlog日志恢复数据时也会产生binlog日志。

为实验效果先将刚才备份的数据库和表删除了。登陆数据库：

 [root@mysql-server ~]# mysql -uroot -pqf123  mysql> show databases;  mysql> drop database company;  mysql> \q

恢复库

 登陆mysql创建一个库  mysql> create database company;  恢复：  [root@mysql-server ~]# mysql -uroot -p'qf123' company < /home/back/company.bak

恢复表

 登陆到刚才恢复的库中将其中的一个表删除掉  mysql> show databases;  mysql> use company  mysql> show tables;  +-------------------+  | Tables_in_company |  +-------------------+  | employee5 |  | new_t1 |  | new_t2 |  +-------------------+  mysql> drop table employee5;  开始恢复:  mysql> set sql_log_bin=0; #停止binlog日志  Query OK, 0 rows affected (0.00 sec)  mysql> use company;  mysql> source /home/back/company.employee5.bak; -------加路径和备份的文件  恢复方式二：  # mysql -u root -p1 db1 < db1.t1.bak  库名 备份的文件路径  [root@mysql-server ~]# mysql -uroot -p'qf123' company <  /home/back/company.employee5.bak

5、备份及恢复表结构

备份 linux中执行

 mysqldump -uroot -p123 库 > 备份路径  mysqldump -uroot -p123 库 表1 表2 > 备份路径 # 备份一个库里的多张表  mysqldump -uroot -p123 -B 库1 库2 > 备份路径 # 备份多个库  mysqldump -uroot -p123 -A > 备份路径 # 备份所有数据

还原

 mysql -uroot -p 库 < 备份数据 # 恢复单个库需要手动创建库  mysql -uroot -p < 备份数据 # 如果还原多个库或者所有数据，无需手动创建库

seuce还原

在数据库中，执行 source 备份文件

备份恢复表结构

 1.备份表结构：  语法：mysqldump -uroot -p -d database table > dump.sql  [root@mysql-server ~]# mysqldump -uroot -p'qf123' -d company employee5 >  /home/back/emp.bak  恢复表结构：  登陆数据库创建一个库  mysql> create database t1;  语法：# mysql -u root -p1 -D db1 < db1.t1.bak  [root@mysql-server ~]# mysql -uroot -p'qf123' -D t1 < /home/back/emp.bak

通过binlog日志恢复数据

• 先开启binl 重启服务
• 查看正在使用的binlog，找到binlog日志 /data/mysql/data /var/lib/mysql /usr/local/mysql
• 找到想要恢复数据的偏移量
• linux下，mysqlbinlog –start-position –stop-position 137.000003 |mysql -uroot
• 在数据库中查看数据是否恢复

致谢

在此，我要对所有为知识共享做出贡献的个人和机构表示最深切的感谢。同时也感谢每一位花时间阅读这篇文章的读者，如果文章中有任何错误，欢迎留言指正。 

学习永无止境，让我们共同进步！！