MySQL数据备份与恢复机制详解

1,825 阅读7分钟

前言

安全管理数据库的关键是定期备份,根据数据量、MySQL服务器的数量和数据库工作负载等情况,选择最适合的备份方式。备份分为以下类型:

  • 冷备:停库,停服,备份
  • 温备:不停库,备份时锁表
  • 热备:不停库,备份时不锁表

备份

按备份类型分

冷备

如果可以关闭 MySQL 服务器,则可以进行物理备份。好处是可以保证数据库的完整性,备份过程简单且恢复速度相对较快。但前提是停掉MySQL服务器意味着系统需要停摆。

冷备过程

  • 停掉 MySQL 服务
  • 复制 InnoDB 数据文件(.ibdata 文件和 .ibd 文件)
  • 复制 InnoDB 日志文件(ib_logfile 文件)
  • 复制所有的 my.cnf 配置文件

温备

温备是指在数据库运行过程中进行的,但是会对当前数据库的操作有所影响。如加一个全局读锁以保证备份数据的一致性。

热备

与冷备相反,热备就是数据库处于运行状态下的备份,不影响现有业务的进行。热备又细分为逻辑备份和裸文件备份。

逻辑备份

逻辑备份是指备份出的文件的内容是可读的,一般是文本文件。内容由一条条SQL语句,或者是表内实际数据组成,这种方法的好处是可以观察导出文件的内容,一般适用于数据库的升级、迁移等工作。但其缺点是恢复所需要的时间往往较长。

mysqldump工具

mysqldump 是 MySQL 自带的命令工具,它的备份过程是先从 buffer 中找到需要备份的数据进行备份。如果 buffer 中没有,就去磁盘中查找并调回到 buffer 里再备份,最后形成一个可编辑的备份文件。

备份

# 备份数据库
mysqldump [arguments] > file_name

# 其中 [arguments] 如下:
--host(-h)				# 服务器IP地址
--port(-p)				# 服务器端口号
--user(-u)				# MySQL 用户名
--pasword(-p)			# MySQL 密码
--databases			# 指定要备份的数据库
--all-databases		# 备份所有数据库
--databases d1 d2	# 备份指定数据库
--compact			# 压缩模式,产生更少的输出
--comments			# 添加注释信息
--single-transaction test 	# 对 test 数据库备份前,执行 start transaction 保证备份一致性
--lock-tables		# 备份前,锁定所有数据库表
--add-locks			# 备份数据库表时锁定数据库表
--force				# 当出现错误时仍然继续备份操作
--default-character-set		# 指定默认字符集

执行备份命令:

mysqldump --single-transaction -uroot -p123456 dbname > file_name

备份成sql文件后,如果使用 docker 部署的 MySQL,将 sql 文件从容器内转移到宿主机,在宿主机上执行如下命令:

docker cp 容器id:容器中sql文件目录 将sql文件转移的宿主机目录

可查看该 SQL 文件内容:

-- MySQL dump 10.13  Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost    Database: fei2_backup
-- ------------------------------------------------------
-- Server version       8.0.26

/*!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 */;
/*!50503 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 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup
--

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '62cd056a-e9f1-11eb-9218-0242ac110002:1-12';

--
-- Table structure for table `tb_class`
--

DROP TABLE IF EXISTS `tb_class`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tb_class` (
  `id` int NOT NULL,
  `name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tb_class`
--

LOCK TABLES `tb_class` WRITE;
/*!40000 ALTER TABLE `tb_class` DISABLE KEYS */;
INSERT INTO `tb_class` VALUES (1,'zhangsan'),(2,'lisi');
/*!40000 ALTER TABLE `tb_class` ENABLE KEYS */;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
/*!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-07-24  1:34:54

恢复

执行此 SQL 文件,在容器中执行命令:

mysql -uroot -p123456 dbname < file_name

select … into outfile

select … into outfile 也是一种逻辑备份方法,它的恢复速度非常快,比 insert 的插入速度要快的多。它跟有许多备份功能的 mysqldump 不同,它只能备份表中的数据,并不能包含表的结构。如果备份完成后,表被 “drop” 了,是无法实现恢复操作的。它把备份出的数据导出到一个文本文件中,通过 load data 的方式,实现恢复还原的操作。

常用语法:

select col1,col2... from table_name into outfile '/path/备份文件名称'

备份

进入 mysql,执行以下命令:

select * from user into outfile '/var/lib/mysql/tt.sql';

其中,输出路径为mysql 指定的路径,用以下命令查询,其中指定的路径才是mysql允许输出的文件路径。

show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

备份成功后,将测试表 user 中的数据清空,使用 load data 的方式导入已备份的数据文件。

恢复

mysql> LOAD DATA INFILE '/var/lib/mysql-files/tt.sql' INTO TABLE test.user;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

load data 与 insert 的插入速度对比,load data 的插入速度约是 insert 插入的 12 倍。

others

以上介绍了两种逻辑备份的方式,逻辑备份还有其他的方式,例如:mydumper,mysqlimport,这两种逻辑备份方式都是 MySQL 自带的工具。

mysqldump 是支持单线程工作的工具,只能逐个表导出,而 mydumper 是一个高性能多线程的备份工具,备份速度远远高于 mysqldump,其备份方式也是逻辑备份。数据还原时使用 myloader 工具,俗称“小钢炮”。

mysqlimport 是 MySQL 提供的一个命令行程序,从本质上说,是 load data infile 的命令接口,而且大多数选项都和 load data infile 语法相同。和 load data infile 不同的是,mysqlimport 可以通过参数 --user-thread 参数实现并发的导入不同的文件。

裸文件备份

裸文件备份是指复制数据库的物理文件,既可以是在数据库运行中的复制,也可以是在数据库停止运行时直接的数据文件复制。由于是在底层复制数据文件的,所以速度上比逻辑备份一条条的插入SQL语句更快。裸文件备份的代表作就是 XtraBackup

XtraBackup

XtraBackup是 Percona 公司的开源项目,特点是备份与恢复过程是速度很快,安全可靠,而且在备份过程中不会锁表,不影响现有业务。但它目前还是不能对表结构文件和其他非事务类型的表进行备份。XtraBackup 包含了两个工具xtrabackup 和 innobackupex:

  • xtrabackup只能备份innodb和xtradb两种引擎的表,而不能备份myisam引擎的表
  • innobackupex是一个封装了xtrabackup的Perl脚本,支持同时备份innodb和myisam,但在对myisam备份时需要加一个全局的读锁。还有就是myisam不支持增量备份

XtraBackup原理

XtraBackup是基于 InnoDB 的 crash recovery 功能进行备份的,那为什么是 crash recovery 呢?

InnoDB 内部维护了一个 redo log ,包含了 InnoDB 数据的所有更改信息,在 InnoDB 启动时,会先检查 datafile 和 transaction log,前滚所有已提交的事务并且回滚未提交的事务。

XtraBackup备份时并不锁定表,而是一页页的复制 InnoDB 的数据,这样复制出来的数据不一致。要想保证数据一致性,需要在恢复时使用 crash recovery 进行操作,XtraBackup 还有另外一个线程监视着 redo log,由于 redo log 大小是固定的,而且是循环写的方式,写满最后 一个日志后,就会从头开始写。那就可能会覆盖之前的数据信息。所以一旦日志发生变化,就复制变化过的 log pages,在复制全部数据文件完之后,停止复制 redo log

XtraBackup优点

  • 备份速度快,可靠
  • 备份过程不会打断正在执行的事务(无需锁表)
  • 能基于压缩等功能节约磁盘空间和流量
  • 自动备份校验
  • 还原速度快
  • 可以流传将备份传输到另外一台机器上
  • 在不增加服务器负载的情况备份数据

按备份内容分

若按照备份数据库的内容来分,备份又可以分为:

  • 完全备份
  • 增量备份
  • 日志备份 完全备份是指对数据库做全量备份;增量备份是指在上次完全备份的基础上,对于更改的数据进行备份;日志备份主要是指对 MySQL 数据库 binlog 的备份,通过对一个全量备份进行 binlog 的重做(replay) 来完成数据库的 point-in-time 的恢复工作。MySQL 数据库复制的原理就是异步实时的将 binlog 重做传送应用到从数据库。

总结

本文介绍了一些MySQL数据库常用的备份方式,分为:冷备、温备、热备。热备中又包含逻辑备份和裸文件备份,还有根据内容量划分的全备、增备、日志备份。在实际备份中,我们会根据实际情况选择合适的备份工具。而备份策略的指定也可以根据真实的数据量,考虑全备和增备的频率。