一文带你掌握 MySQL 数据库备份解决方案

作者: you的日常 | 来源:发表于2021-02-26 12:07 被阅读0次

简介

点进来本文的读者，说明你对 MySQL 数据库有一定的基础，起码肯定会 CRUD，本文主要有以下 6 个主要内容：

使用 MySQL 自带的 mysqldump 工具，做单表、多表、单库、多库、全库的逻辑备份，以及数据恢复。
MySQL 在线热备工具 percona-xtrabackup 的安装介绍。
使用 percona-xtrabackup 对 MySQL 数据库做在线热备，以及全库恢复。
介绍 mysqldump、innobackupex 工具备份原理。
binlog2sql 闪回工具帮你无损恢复业务记录。
当你误删除业务表的 .ibd 文件时，如何恢复被删除的 .ibd 文件。

mysqldump 工具逻辑备份

在 MySQL 数据库日常运维过程中，肯定有需要导出单表数据、多表数据、单库、多库，甚至券库导出的需求，如果你对 mysqldump 工具不是很了解，很容易就造成生产故障。

举个例子，为什么有人使用 mysqldump 工具在线导出表记录，不锁表，不影响业务，而你使用 mysqldump 工具导出表记录却锁表、影响到业务呢。

下面，我就一步一步带你解决上面的疑问。

mysqldump 工具重要参数介绍

要想把 mysqldump 工具玩的溜，必须掌握 2 个重要的参数。

--single-transaction 参数作用

当你使用 mysqldump 工具导数时，一定要带上，如果不带这个参数，会锁表，并禁止任何数据的写入，从而影响业务。

如果设置此参数，当使用 mysqldump 的时候，会自动设置会话的隔离级别为 RR，然后再开启一个事务，这样到导出数据整个过程，能保证数据的一致性，当然前提是你的表使用的存储引擎是 InnoDB。

但是 mysqldump 开启的事务，会被一些 DDL 语句破坏掉，例如 alter table，所以呢，在执行 mysqldump 过程中，不要使用 DDL 语句。尤其是当你想用 mysqldump 导出的数据，搭建主从复制的情况，不然会导致你的主从数据不一致的。

--skip-tz-utc 参数作用

这个参数也非常重要，大家应该知道时区，mysqldump 默认会启用 tz-utc 选项的，即会设置导出数据时区为格林威治时间，即 0 时区，如果你的数据库在中国，当你把导出的数据，导入到目标库之后，就会很奇怪，为什么 timestamp 字段显示的时间，会比源库差 8 个小时，原因就在此。

所以当你导出数据的时候，一定要加上 --skip-tz-utc 参数，告诉 MySQL 数据库，我不使用格林威治时间，使用当前 MySQL 数据库的时区进行导出。

mysqldump 单表、多表、单库、多库、全库逻辑导出

本次模拟创建了 2 个数据库，分别为 testdb 和 testdb2，在 testdb 中有 2 张表（t_test_1，t_test_2），在 testdb2 中有一张表（t_test_3）。

[root@localhost] 14:45:10 [testdb2]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| testdb2            |
+--------------------+
6 rows in set (0.00 sec)

[root@localhost] 14:45:14 [testdb2]>use testdb;
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
[root@localhost] 14:45:22 [testdb]>show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| t_test_1         |
| t_test_2         |
+------------------+
2 rows in set (0.00 sec)

[root@localhost] 14:45:24 [testdb]>use testdb2;
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
[root@localhost] 14:45:31 [testdb2]>show tables;
+-------------------+
| Tables_in_testdb2 |
+-------------------+
| t_test_3          |
+-------------------+
1 row in set (0.00 sec)

1. mysqldump 单表导出 testdb 库的 t_test_1 表，不导出建表语句，带上 -t 参数即可。

mysqldump --databases testdb --skip-tz-utc -t --single-transaction -u root -proot --socket=/data/mysql/run/3307/mysql.sock --tables t_test_1 > db_script.sql

查看一下导出结果：

[mysql@mysql backup]$ more db_script.sql 
-- MySQL dump 10.13  Distrib 5.7.26, for el7 (x86_64)
--
-- Host: localhost    Database: testdb
-- ------------------------------------------------------
-- Server version       5.7.26-log

/*!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 */;
/*!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='892f6da4-f0d2-11ea-9e95-000c29cc2388:1-10';

--
-- Dumping data for table `t_test_1`
--

LOCK TABLES `t_test_1` WRITE;
/*!40000 ALTER TABLE `t_test_1` DISABLE KEYS */;
INSERT INTO `t_test_1` VALUES (1,'trest'),(2,'e99e'),(3,'test'),(4,'fresd'),(5,'fsfa');
/*!40000 ALTER TABLE `t_test_1` ENABLE KEYS */;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

/*!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 2020-12-10 14:56:01

2. mysqldump 单表导出 testdb 库的 t_test_1 表，带建表语句：

mysqldump --databases testdb --skip-tz-utc --single-transaction -u root -proot --socket=/data/mysql/run/3307/mysql.sock --tables t_test_1 > db_script.sql

查看一下导出结果：

[mysql@mysql backup]$ more db_script.sql 
-- MySQL dump 10.13  Distrib 5.7.26, for el7 (x86_64)
--
-- Host: localhost    Database: testdb
-- ------------------------------------------------------
-- Server version       5.7.26-log

/*!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 */;
/*!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='892f6da4-f0d2-11ea-9e95-000c29cc2388:1-10';

--
-- Table structure for table `t_test_1`
--

DROP TABLE IF EXISTS `t_test_1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_test_1` (
  `id` int(11) NOT NULL,
  `name` char(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_test_1`
--

LOCK TABLES `t_test_1` WRITE;
/*!40000 ALTER TABLE `t_test_1` DISABLE KEYS */;
INSERT INTO `t_test_1` VALUES (1,'trest'),(2,'e99e'),(3,'test'),(4,'fresd'),(5,'fsfa');
/*!40000 ALTER TABLE `t_test_1` ENABLE KEYS */;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

由于导出的内容比较多，后续的案例导出内容，就不一一展示了。

3. mysqldump 多表导出 testdb 库的 t_test_1、t_test_2：

mysqldump --databases testdb --skip-tz-utc -t --single-transaction -u root -proot --socket=/data/mysql/run/3307/mysql.sock --tables t_test_1 t_test_2> db_script.sql

4. mysqldump 导出单库 testdb：

mysqldump --databases testdb --skip-tz-utc -t --single-transaction -u root -proot --socket=/data/mysql/run/3307/mysql.sock> db_script.sql

5. mysqldump 导出多库 testdb、testdb2：

mysqldump --databases testdb testdb2 --skip-tz-utc -t --single-transaction -u root -proot --socket=/data/mysql/run/3307/mysql.sock> db_script.sql

6. mysqldump 导出全库：

mysqldump --all-databases --skip-tz-utc --single-transaction -u root -proot --socket=/data/mysql/run/3307/mysql.sock> db_script.sql

注意事项：如果导出语句中不带建表语句，需要加参数 -t，否则不带参数。默认情况下，mysqldump 工具是不导出存储过程和函数，事件的，如果要导出，需要加 -ER 参数。

percona-xtrabackup 在线热备工具安装

percona-xtrabackup 工具是 Percona 公司免费的一款针对 MySQL 数据库在线热备的工具，安装和使用都非常简单，登录官网，找到对应的软件版本，下载即可，如下图所示：

网友评论

数据库

本文标题：一文带你掌握 MySQL 数据库备份解决方案

本文链接：https://www.haomeiwen.com/subject/yopsnktx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！