第九章 数据库的备份与恢复
1.备份原理
备份是数据安全的最后一道防线,对于任何数据丢失的场景,备份虽然不一定能恢复百分之百的数据(取决于备份周期),但至少能将损失降到最低。衡量备份恢复有两个重要的指标:恢复点目标(RPO)和恢复时间目标(RTO),前者重点关注能恢复到什么程度,而后者则重点关注恢复需要多长时间。这篇文章主要讨论MySQL的备份方案,重点介绍几种备份方式的原理,包括文件系统快照(LVM),逻辑备份工具Mysqldump,Mydumper,以及物理备份工具Xtrabackup,同时会详细讲解几种方案的优缺点,以及可能遇到的问题。

2.冷备份
最简单的备份方式就是,关闭MySQL服务器,然后将data目录下面的所有文件进行拷贝保存,需要恢复时,则将目录拷贝到需要恢复的机器即可。这种方式确实方便,但是在生产环境中基本没什么作用。因为所有的机器都是要提供服务的,即使是Slave有时候也需要提供只读服务,所以关闭MySQL停服备份是不现实的。与冷备份相对应的一个概念是热备份,所谓热备份是在不影响MySQL对外服务的情况下,进行备份,热备份是这篇文章讨论的重点。

3.快照备份
首先要介绍的热备份是快照备份,快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的原理是将所有的数据库文件放在同一分区中,然后对该分区执行快照工作,对于Linux而言,需要通过LVM(Logical Volumn Manager)来实现。LVM使用写时复制(copy-on-write)技术来创建快照,例如,对整个卷的某个瞬间的逻辑副本,类似于数据库中的innodb存储引擎的MVCC,只不过LVM的快照在文件系统层面,而MVCC在数据库层面,而且仅支持innodb存储引擎。LVM有一个快照预留区域,如果原始卷数据有变化时,LVM保证在任何变更写入之前,会复制受影响块到快照预留区域。简单来说,快照区域内保留了快照点开始时的一致的所有old数据。对于更新很少的数据库,快照也会非常小。对于MySQL而言,为了使用快照备份,需要将数据文件,日志文件都放在一个逻辑卷中,然后对该卷快照备份即可。由于快照备份,只能本地,因此,如果本地的磁盘损坏,则快照也就损坏了。快照备份更偏向于对误操作防范,可以将数据库迅速恢复到快照产生的时间点,然后结合二进制日志可以恢复到指定的时间点。基本原理如下图:
图片 3.png

4.逻辑备份
冷备份和快照备份由于其弊端在生产环境中很少使用,使用更多是MySQL自带的逻辑备份和物理备份工具,这节主要讲逻辑备份,MySQL官方提供了Mysqldump逻辑备份工具,虽然已经足够好,但存在单线程备份慢的问题。在社区提供了更优秀的逻辑备份工具mydumper,它的优势主要体现在多线程备份,备份速度更快。
Mysqldump Mysqldump用于备份,不得不提两个关键的参数: --single-transaction:在开始备份前,执行start transaction命令,以此来获取一致性备份,该参数仅对innodb存储引擎有效。 --master-data=2:主要用于记录一致性备份的位点。 理解Mysqldump工作原理,一定要将事务表(innodb)和非事务表(比如myisam)区别对待,因为备份的流程与此息息相关。而且,到目前为止,我们也无法规避myisam表,即使我们的所有业务表都是innodb,因为mysql库中系统表仍然采用的myisam表。

备份的基本流程如下:
1.调用FTWRL(flush tables with read lock),全局禁止读写;
2.开启快照读,获取此时的快照(仅对innodb表起作用) ;
3.备份非innodb表数据(.frm,.myi,*.myd等);
4.非innodb表备份完毕后,释放FTWRL锁 ;
5.逐一备份innodb表数据 ;
6.备份完成。
图片 4.png
整个过程,可以参考左图,但这张图只考虑innodb表的备份情况,实际上在unlock tables执行完毕之前,非innodb表已经备份完毕,后面的t1,t2和t3实质都是innodb表,而且5.6的mysqldump利用保存点机制,每备份完一个表就将一个表上的MDL锁释放,避免对一张表锁更长的时间。

说明: 大家可能有一个疑问,为啥备份innodb表之前,就已经将锁释放掉了,这实际上是利用了innodb引擎的MVCC机制,开启快照读后(set transaction isolation level repeatable read),就能获取那个时间的一致的数据,无论需要备份多长时间,直到整个事务结束(commit)为止。当然,这个RR级别的快照,对表的元数据也有要求,因为逻辑备份表是一个个进行的,如果在备份某个表之前,这个表做了DDL操作(此时备份事务暂时还没有加MDL锁),后续再备份这个表时,就会因为表定义不一致而报错(Table definition has changed, please retry transaction)。所以总地来说,通过保存点机制,可以有效减少DDL操作的限制,但是也不能完全消除由于DDL操作导致的备份失败问题。

5.Mydumper
Mydumper原理与Mysqldump原理类似,最大的区别是引入了多线程备份,每个备份线程备份一部分表,当然并发粒度可以到行级,达到多线程备份的目的。这里要解决最大一个问题是,如何保证备份的一致性,其实关键还是在于FTWRL。对于非innodb表,在释放锁之前,需要将表备份完成。对于innodb表,需要确保多个线程都能拿到一致性位点,这个动作同样要在持有全局锁期间完成,因为此时数据库没有读写,可以保证位点一致。所以基本流程如下:
图片 5.png

6.物理备份(Xtrabackup)
相对于逻辑备份利用查询提取数据中的所有记录,物理备份更直接,拷贝数据库文件和日志来完成备份,因此速度会更快。当然,无论是开源的Mydumper还是官方最新的备份工具(5.7.11的mysqlpump)都支持了多线程备份,所以速度差异可能会进一步缩小,至少从目前生产环境来看,物理备份使用还是比较多的。由于Xtrabackup支持备份innodb表,实际生产环境中我们使用的工具是innobackupex,它是对xtrabackup的一层封装。innobackupex脚本用来备份非InnoDB表,同时会调用xtrabackup命令来备份InnoDB表,innobackupex的基本流程如下:
1.开启redo日志拷贝线程,从最新的检查点开始顺序拷贝redo日志;
2.开启idb文件拷贝线程,拷贝innodb表的数据 ;
3.idb文件拷贝结束,通知调用FTWRL,获取一致性位点 ;
4.备份非innodb表(系统表)和frm文件 ;
5.由于此时没有新事务提交,等待redo日志拷贝完成;
6.最新的redo日志拷贝完成后,相当于此时的innodb表和非innodb表数据都是最新的 ;
7.获取binlog位点,此时数据库的状态是一致的;
8.释放锁,备份结束。
说明:我们知道MySQL里面有个double-write为了防止写的时候页断裂,那么备份读的时候,有没有可能也只一半的page呢?这个其实是有可能的,因此,我们在拷贝page的时候,需要对page算checksum,如果checksum不符合预期,我们认为拷贝的页面不完整(这种情况可能发生在你在读页面,而后台线程正在刷脏),需要重试。所以,最终也能保证数据的一致性。
图片 6.png

7.Xtrabackup的改进
从前面介绍的逻辑备份和物理备份来看,无论是哪种备份工具,为了获取一致性位点,都强依赖于FTWRL。这个锁杀伤力非常大,因为持有锁的这段时间,整个数据库实质上不能对外提供写服务的。此外,由于FTWRL需要关闭表,如有大查询,会导致FTWRL等待,进而导致DML堵塞的时间变长。即使是备库,也有SQL线程在复制来源于主库的更新,上全局锁时,会导致主备库延迟。从前面的分析来看,FTWRL这把锁持有的时间主要与非innodb表的数据量有关,如果非innodb表数据量很大,备份很慢,那么持有锁的时间就会很长。即使全部是innodb表,也会因为有mysql库系统表存在,导致会锁一定的时间。为了解决这个问题,Percona公司对Mysql的Server层做了改进,引入了BACKUP LOCK,具体而言,通过"LOCK TABLES FOR BACKUP"命令来备份非innodb表数据;通过"LOCK BINLOG FOR BACKUP"来获取一致性位点,尽量减少因为数据库备份带来的服务受损。我们看看采用这两个锁与FTWRL的区别:
LOCK TABLES FOR BACKUP 作用:
备份数据
1.禁止非innodb表更新
2.禁止所有表的ddl
优化点:
1.不会被大查询堵塞(关闭表) ;
2.不会堵塞innodb表的读取和更新,这点非常重要,对于业务表全部是innodb的情况,则备份过程中DML完全不受损 。

LOCK BINLOG FOR BACKUP 作用:
获取一致性位点
1.禁止对位点更新的操作 优
化点: 1.允许DDl和更新,直到写binlog为止。

备份很简单直接tar一个命令就OK了,然后就开始还原(只还原到备份那一刻):
mkdir /backup
cd /var/lib/mysql
tar -cf /backup/date +%F.gz *
#开始还原
systemctl stop mysqld
rm -rf /var/lib/mysql/*
tar -xf /backup/2020-04-14.gz -C /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
#登录测试数据库是否还在
mysql -p'Root@321'
show databases;

数据库完全物理备份及恢复
1.先登录数据库并查看现存的数据库有哪些,随后创建一个备份点
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| runtimeedu         |
| test               |
+--------------------+
6 rows in set (0.000 sec)
MariaDB [(none)]> exit
Bye
[root@localhost ~]# mkdir /backup
[root@localhost ~]# ls /
backup  boot  etc   lib    media  opt   root  sbin  sys  usr
bin     dev   home  lib64  mnt    proc  run   srv   tmp  var
[root@localhost ~]#
2.进入数据库存放路径进行tar压缩归档备份
[root@localhost ~]# cd /var/lib/mysql/
[root@localhost mysql]# ls
aria_log.00000001  ibdata1      ibtmp1             mysql.sock          runtime     test
aria_log_control   ib_logfile0  multi-master.info  mysql_upgrade_info  runtimeedu
ib_buffer_pool     ib_logfile1  mysql              performance_schema  tc.log
[root@localhost mysql]# tar -cf /backup/`date +%F`.gz *
tar: mysql.sock: 忽略套接字(socket)
[root@localhost mysql]# ls /backup/
2021-01-18.gz
[root@localhost mysql]#
3.停止数据库,随后删除数据库存放路径下的所有文件
[root@localhost ~]# systemctl  stop mariadb.service 
[root@localhost ~]# rm -rf /var/lib/mysql/*
[root@localhost ~]# ls /var/lib/mysql/
[root@localhost ~]#
4.将备份的文件进行解压到默认存放数据库的路径下并修改其所有者和所属组的权限,并启动数据库
[root@localhost ~]# tar -xf /backup/2021-01-18.gz -C /var/lib/mysql/
[root@localhost ~]# ls /var/lib/mysql/
aria_log.00000001  ibdata1      ibtmp1             mysql_upgrade_info  runtimeedu
aria_log_control   ib_logfile0  multi-master.info  performance_schema  tc.log
ib_buffer_pool     ib_logfile1  mysql              runtime             test
[root@localhost ~]# chown  -R mysql:mysql /var/lib/mysql/
[root@localhost ~]# systemctl start mariadb.service 
[root@localhost ~]#
5.随后登录数据库并查看数据是否完全恢复
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| runtimeedu         |
| test               |
+--------------------+
6 rows in set (0.000 sec)
MariaDB [(none)]>
数据库的备份恢复及删除
1.备份所有数据库到/media目录下
[root@localhost ~]# mysqldump -uroot -p --all-database >/media/redhat.sql
Info: Using unique option prefix 'all-database' is error-prone and can break in the future. Please use the full name 'all-databases' instead.
Enter password: 
[root@localhost ~]# cd /media/
[root@localhost media]# ls
redhat.sql
[root@localhost media]#
2.备份所有数据格式,不备份数据
[root@localhost ~]# mysqldump -uroot -p --all-database --no-data >/media/mysql.txt
Info: Using unique option prefix 'all-database' is error-prone and can break in the future. Please use the full name 'all-databases' instead.
Enter password: 
[root@localhost ~]# cd /media/
[root@localhost media]# ls
mysql.txt  redhat.sql
[root@localhost media]# cat mysql.txt 
-- MySQL dump 10.17  Distrib 10.3.17-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version       10.3.17-MariaDB

/*!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 utf8mb4 */;
/*!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 */;
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。省略
DROP TABLE IF EXISTS `linux`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `linux` (
  `username` varchar(6) NOT NULL,
  `passwd` varchar(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 */;

-- Dump completed on 2021-01-13  2:06:55
[root@localhost media]#
3.备份数据库中的runtime库到/media目录下
[root@localhost ~]# mysqldump -uroot -p runtime >/media/runtime.sql
Enter password: 
[root@localhost ~]# cd /media/
[root@localhost media]# ls
mysql.txt  redhat.sql  runtime.sql
[root@localhost media]# cat runtime.sql 
-- MySQL dump 10.17  Distrib 10.3.17-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: runtime
-- ------------------------------------------------------
-- Server version       10.3.17-MariaDB

/*!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 utf8mb4 */;
/*!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 */;

--
-- Table structure for table `compute5`
--

DROP TABLE IF EXISTS `compute5`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `compute5` (
  `username` varchar(6) NOT NULL,
  `id` varchar(6) NOT NULL,
  `score` varchar(6) NOT NULL,
  `studyid` varchar(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `compute5`
--

LOCK TABLES `compute5` WRITE;
/*!40000 ALTER TABLE `compute5` DISABLE KEYS */;
INSERT INTO `compute5` VALUES ('user1','1','99','1949'),('user2','','90','1950');
/*!40000 ALTER TABLE `compute5` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `linux`
--

DROP TABLE IF EXISTS `linux`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `linux` (
  `username` varchar(6) NOT NULL,
  `passwd` varchar(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `linux`
--

LOCK TABLES `linux` WRITE;
/*!40000 ALTER TABLE `linux` DISABLE KEYS */;
INSERT INTO `linux` VALUES ('user1','123'),('user2','456'),('user3','789');
/*!40000 ALTER TABLE `linux` 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 */;

-- Dump completed on 2021-01-13  2:09:58
[root@localhost media]#
4.删除runtime数据库
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> use runtime
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [runtime]> drop table linux;
Query OK, 0 rows affected (0.001 sec)
MariaDB [runtime]> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| compute5          |
+-------------------+
1 row in set (0.000 sec)
MariaDB [runtime]>exit
Bye
[root@localhost ~]#
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> drop database runtime;
Query OK, 1 row affected (0.002 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]>
5.恢复runtime数据库
MariaDB [(none)]> exit
Bye
[root@localhost ~]# 
[root@localhost ~]# mysql -uroot -p -e "create database runtime;"
Enter password: 
[root@localhost ~]#
[root@localhost ~]# mysql -uroot -p runtime < /media/runtime.sql
Enter password: 
[root@localhost ~]# mysql -uroot -p -e "select * from runtime.linux;"
Enter password: 
+----------+--------+
| username | passwd |
+----------+--------+
| user1    | 123    |
| user2    | 456    |
| user3    | 789    |
+----------+--------+
[root@localhost ~]#
上一篇 下一篇