死磕数据库系列（十）：MySQL 数据库的备份与恢复

在这之前，我们学习了：死磕数据库系列（八）：MySQL 主从同步详解、死磕数据库系列（九）：MySQL 读写分离详解等内容。其实，在数据库运行开始之时，有一个非常重要点就是：数据库里的数据备份与恢复，数据对于任何一家企业来说，都是重中之重，那么今天，民工哥就给大家详解介绍一下MySQL数据库的数据备份与恢复。

为什么需要数据库备份？

很多人，一看这标题，肯定张口就会答，这不是废话么。不备份故障了怎么办？跑路吗？数据被沙雕开发（不许喷）误删了怎么办？背锅吗？

当然，大家都知道备份的重要性与必要性。

1、保证数据安全与完整

企业的数据安全应该来说是企业的命脉，一旦丢失或造成损坏，轻则损失客户与金钱，重则倒闭（已经有前例在）。

备份的目的：为了保证数据在被人为失误、操作不当、蓄意等情况下删除或损坏后，能及时、有效的进行恢复并不会很大程度上影响到业务运行。

2、为业务提供不间断服务

实际生产环境对数据库的要求，首先就是具备7×24×365不间断服务的能力，这也是一定要备份数据库的其中原因之一。

要备份什么?

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

• 数据
• 二进制日志, InnoDB事务日志
• 代码(存储过程、存储函数、触发器、事件调度器)
• 服务器配置文件

备份的类型

按照备份时数据库的运行状态，可以分为三种。

冷备：停库、停服务来备份

即当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

温备：不停库、不停服务来备份，会(锁表)阻止用户的写入

即当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作

热备（建议）：不停库、不停服务来备份，也不会(锁表)阻止用户的写入

即当数据库进行备份时, 数据库的读写操作均不是受影响

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

MyISAM热备 ×温备 √冷备 √

数据库的备份方式

常用的备份方式包括以下：

• 逻辑备份
• 物理备份

逻辑备份

逻辑备份其实就是利用MySQL数据库自带的mysqldump命令，或者使用第三方的工具，然后把数据库里的数据以SQL语句的方式导出成文件的形式。在需要恢复数据时，通过使用相关的命令（如：source ）将备份文件里的SQL语句提取出来重新在数据库中执行一遍，从而达到恢复数据的目的。

实例如下：

mysqldump -A -B --single-transaction >/server/backup/mysql_$(date +%F).sql

一般备份时都会进行压缩处理，以节省磁盘空间，如下

mysqldump -A -B --single-transaction ｜gzip>/server/backup/mysql_$(date +%F).sql.gz

恢复操作

cd /server/backup/

上面的操作，都是针对整个数据库的备份与恢复。但是，在实际生产环境中，这种的备份与恢复方式是有缺点时。因此，一般我们会针对某个库、某个表，也就是我们所说的单库、单表进行备份，在需要恢复数据时，我们可以选择性的进行恢复，可以提高恢复效率，减少宕机时间。

单库备份

[root@centos7 ~]# mysqldump -uroot -p test >/download/testbak_$(date +%F).sqlEnter password: [root@centos7 ~]# ll /download/total 2-rw-r--r--.  1 root root 1888 Dec 12 20:34 testbak_2022-12-12.sql

下面我们看看这个备份文件到底是什么内容

[root@centos7 ~]# egrep -v "^--|\*|^$" /download/testbak_2022-12-12.sqlDROP TABLE IF EXISTS `test`;CREATE TABLE `test` (  `id` int(4) NOT NULL,  `name` char(20) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;LOCK TABLES `test` WRITE;INSERT INTO `test` VALUES (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');UNLOCK TABLES;

由上的文件内容，可以看出，这个备份实际的过程就是将创建数据库、建表、插入数据的sql语句备份出来，也可以说是将sql语句导出。

[root@centos7 ~]# mysqldump -uroot -p -B test >/download/testbak_$(date +%F)_b.sqlEnter password: [root@centos7 ~]# egrep -v "^--|^$" /download/testbak_2022-12-12_b.sql   /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;USE `test`;DROP TABLE IF EXISTS `test`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `test` (  `id` int(4) NOT NULL,  `name` char(20) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;LOCK TABLES `test` WRITE;/*!40000 ALTER TABLE `test` DISABLE KEYS */;INSERT INTO `test` VALUES (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');/*!40000 ALTER TABLE `test` ENABLE KEYS */;UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-B参数的作用一目了然，就是当我们的数据库丢失时，可以直接用此备份文件进行恢复，无需再重新建库、建表，然后再进行数据恢复的操作。

单表备份

分库备份是为了恢复数据库时方便操作，但是同样面临问题，如果是某个库中的某一个表有损坏，但又不有全库进行恢复，所以实际生产中常用的是分库、分表进行备份，这样数据也备份了，恢复时也好操作

[root@centos7 ~]# mysqldump -uroot -p -B test test >/download/test_testbak_$(date +%F).sql      Enter password: [root@centos7 ~]# egrep -v "#|^$|\*" /download/test_testbak_2016-12-12.sql-- MySQL dump 10.13  Distrib 5.5.52, for linux2.6 (x86_64)---- Host: localhost    Database: test-- -------------------------------------------------------- Server version       5.5.53-log---- Current Database: `test`--USE `test`;---- Table structure for table `test`--DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (  `id` int(4) NOT NULL,  `name` char(20) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;---- Dumping data for table `test`--LOCK TABLES `test` WRITE;INSERT INTO `test` VALUES (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');UNLOCK TABLES;---- Current Database: `test`--USE `test`;---- Table structure for table `test`--DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (  `id` int(4) NOT NULL,  `name` char(20) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;---- Dumping data for table `test`--LOCK TABLES `test` WRITE;INSERT INTO `test` VALUES (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');UNLOCK TABLES;-- Dump completed on 20226-12-12 21:13:16

因此分表备份同分库备份一样，只需要进行多次单表备份的操作，但是有的小伙伴肯定会提出问题了，如果一个库里几千张表，几万张表，这种备份要备到猴年马月吧？？？？，数据量比较大的备份可以使用专业的备份工具，数据量不大或者表不是很多的情况，可以将备份操作写成脚本 纳入定时任务，定时执行（MySQL 数据库定时备份的几种方式（非常全面）），只需要检查备份是否成功即可。

逻辑备份的优点与使用场景

• 优点：简单，易操作，自带工具方便、可靠。
• 使用场景：数据库数据量不大的情况可以使用，数据量比较大（超过20G左右）时备份速度比较慢，一定程度上还会影响数据库本身的性能。

物理备份

物理备份就是利用命令（如cp、tar、scp等）直接将数据库的存储数据文件复制一份或多份，分别存放在其它目录，以达到备份的效果。

这种备份方式，由于在备份时数据库还会存在数据写入的情况，一定程度上会造成数据丢失的可能性。在进行数据恢复时，需要注意新安装的数据的目录路径、版本、配置等与原数据要保持高度一致，否则同样也会有问题。

所以，这种物理备份方式，常常需要在停机状态下进行，一般对实际生产中的数据库不太可取。因此，此方式比较适用于数据库物理迁移，这种场景下这种方式比较高效率。

物理备份的优点及使用场景

• 优点：速度快，效率高。
• 场景：可用于停机维护及数据库物理迁移场景中。

实际生产环境中，具体使用哪种方式，就需要看需求与应用场景所定。

全量与增量备份概述

在介绍完备份方式之后，再来介绍一下，增量与全量备份这两个概念。

什么是全量备份？

全量备份：就是将数据库中的所有数据，或者是某一个特定的库里的所有数据，一次全部备份下来。备份数据库中所有数据

mysqldump -A -B --single-transaction ｜gzip>/server/backup/All_data_$(date +%F).sql.gz

备份某个库的数据

mysqldump -A -B --single-transaction testDB1｜gzip>/server/backup/testDB1_$(date +%F).sql.gz

什么是增量备份？

增量备份：指的是上一次全量备份之后到下一次全量备份这前这段时间内数据库所更新或者是增加的数据，将其备份下来。注：全量备份是一个文件，而增量备份则是MySQL的binlog日志文件。所以常说的增量备份就是备份binlog日志文件。

两者的区别在哪？

全量备份：需要的备份时间长一点，恢复时间会短一点，因为文件数少，维护方便。但是，全量备份的文件大，占用一定的磁盘空间，全理备份时会一定程序上影响数据库的性能（这也就是为什么在0:00点备份的原因），也因文件大的原因，不便于服务器本地保存过多文件，重要业务的全量备份文件可能需要手工下载或迁移到服务器之外的存储空间中。

增量备份：备份简单，恢复时复杂一点，因为文件数量多，需将所有binlog文件解析成SQL语句，如下：

mysqlbinlog testDB1-bin.000001 testDB1-bin.000002 >./bin.sql

然后，再通过恢复的方式进行恢复

mysql -uroot -pMyadmin -h mysqldb.mingongge.com> source /server/backup/bin.sql

或者如下操作

cd /server/backupmysql testDB1  create database test;Query OK, 1 row affected (0.00 sec)mysql> use test;Database changedmysql> create table T1 (name varchar(10) not null,sex varchar(10) not null);Query OK,  rows affected (0.15 sec)mysql> insert into T1 values('zhang','man');Query OK, 1 row affected (0.01 sec)mysql> insert into T1 values('zhan','man');Query OK, 1 row affected (0.01 sec)mysql> insert into T1 values('sun','woman');Query OK, 1 row affected (0.00 sec)

Xtrabackup全量备份与恢复

[root@centos ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password="123456" --backup /root

从备份过程截图可以看出会创建一个时间的目录

[root@centos ~]# ll /root/2017-12-04_13-57-29/total 12352-rw-r----- 1 root root   425 Dec  4 13:57 backup-my.cnf-rw-r----- 1 root root  322 Dec  4 13:57 ib_buffer_pool-rw-r----- 1 root root 12582912 Dec  4 13:57 ibdata1drwxr-x--- 2 root root  4096 Dec  4 13:57 mysqldrwxr-x--- 2 root root   4096 Dec  4 13:57 performance_schemadrwxr-x--- 2 root root   12288 Dec  4 13:57 sysdrwxr-x--- 2 root root   4096 Dec  4 13:57 test-rw-r----- 1 root root    22 Dec  4 13:57 xtrabackup_binlog_info-rw-r----- 1 root root    113 Dec  4 13:57 xtrabackup_checkpoints-rw-r----- 1 root root    537 Dec  4 13:57 xtrabackup_info-rw-r----- 1 root root   2560 Dec  4 13:57 xtrabackup_logfile#这里面就是相关的备份文件，同样也可以看到我们创建的库的名称

[root@centos ~]#innobackupex --apply-log /root/2017-12-04_13-57-29/#使用此参数使用相关数据性文件保持一致性状态

mysql> drop table T1;Query OK,  rows affected (0.01 sec)mysql> select * from T1;ERROR 1146 (42S02): Table 'test.T1' doesn't exist

接下来准备恢复误删除数据

#恢复数据之前需要保证数据目录是空的状态[root@centos ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /root/2017-12-04_13-57-29/

[root@centos ~]# /etc/init.d/mysqld startStarting MySQL. SUCCESS! [root@centos ~]# lsof -i :3306COMMAND PID USER  FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 5935 mysql 21u  IPv6 21850  0t0  TCP *:mysql (LISTEN)mysql> use test;Database changedmysql> select * from T1;+-------+-------+| name  | sex |+-------+-------+| zhang | man  || zhan  | man || sun  | woman |+-------+-------+3 rows in set (0.00 sec)#恢复成功

Xtrabackup增量备份与恢复

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

mysql> select * from T1;+-------+-------+| name | sex  |+-------+-------+| zhang | man  || zhan  | man  || sun  | woman || susun | woman || sige | man  || mgg  | man |+-------+-------+6 rows in set (0.00 sec)

创建用于增量备份的数据，用来模拟删除掉了全备后的数据，能否通过增量备份文件来恢复

[root@Vcentos ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental /backup/ --incremental-basedir=/root/2017-12-04_13-57-29

[root@Vcentos ~]# ll /backup/2017-12-05_09-27-06/total 312-rw-r----- 1 root root    425 Dec  5 09:27 backup-my.cnf-rw-r----- 1 root root    412 Dec  5 09:27 ib_buffer_pool-rw-r----- 1 root root 262144 Dec  5 09:27 ibdata1.delta-rw-r----- 1 root root     44 Dec  5 09:27 ibdata1.metadrwxr-x--- 2 root root   4096 Dec  5 09:27 mysqldrwxr-x--- 2 root root   4096 Dec  5 09:27 performance_schemadrwxr-x--- 2 root root  12288 Dec  5 09:27 sysdrwxr-x--- 2 root root   4096 Dec  5 09:27 test-rw-r----- 1 root root     21 Dec  5 09:27 xtrabackup_binlog_info-rw-r----- 1 root root    117 Dec  5 09:27 xtrabackup_checkpoints-rw-r----- 1 root root    560 Dec  5 09:27 xtrabackup_info-rw-r----- 1 root root   2560 Dec  5 09:27 xtrabackup_logfile[root@centos ~]# cd /backup/2017-12-05_09-27-06/[root@centos 2017-12-05_09-27-06]# cat  xtrabackup_binlog_infomysql-bin.000001 945[root@centos 2017-12-05_09-27-06]# cat xtrabackup_checkpointsbackup_type = incrementalfrom_lsn = 2542843to_lsn = 2547308last_lsn = 2547317compact = 0recover_binlog_info = 0

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

mysql> delete  from T1 where name='susun';Query OK, 1 row affected (0.06 sec)

增量恢复操作过程如下

[root@centos ~]# innobackupex --apply-log --redo-only /root/2017-12-04_13-57-29/[root@centos ~]# innobackupex --apply-log --redo-only /root/2017-12-04_13-57-29/ --incremental-dir=/backup/2017-12-05_09-27-06/

恢复全部数据

[root@centos ~]#innobackupex --defaults-file=/etc/my.cnf --copy-back /root/2017-12-04_13-57-29/[root@centos ~]# /etc/init.d/mysqld startStarting MySQL.. SUCCESS! [root@centos ~]# lsof -i :3306COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAMEmysqld  23217 mysql  21u  IPv6 283226  0t0  TCP *:mysql (LISTEN)

查看恢复的数据完整性以上就是针对 MySQL 数据库的数据备份与恢复的详细介绍，希望对大家有所帮助，点个在看与转发支持一下吧。