mysqlpump 和 mysqldump 简单对比

原创心在梦在 2023-05-24

1995

mysqlpump 和 mysqldump 简单对比

一、mysqlpump 介绍

MySQL5.7之后引入一个新的备份工具：mysqlpump。它是mysqldump的一个衍生，mysqlpump和mysqldump一样，属于逻辑备份，备份以SQL形式的文本保存。恢复方式也和mysqldump一样。

1. Mysqlpump优缺点

优点：
- 基于表并行备份数据库和数据库中对象，加快备份过程。（–default-parallelism）
- 更好地控制数据库和数据库对象（表，存储过程，用户帐户）的备份。
- 备份用户账号作为帐户管理语句（CREATE USER，GRANT），而不是直接插入到MySQL的系统数据库。
- 支持直接压缩导出（参数–compress-output，而且支持ZLIB和LZ4）。
- 导出的时候带有进度条（–watch-progress，默认开启）,显示进度（估计值）。
- 导出时可以排除或指定数据库。
- 导入备份文件时，先建表后插入数据最后建立索引，减少了索引维护开销，加快了还原速度。
缺点：
- 5.7.11之前，–defaut-parallelism>0时与–single-transaction、–parallel-schemas互斥，无法使用并行。直到5.7.11才解决了–single-transaction和–default-parallelism、–parallel-schemas互斥的问题。
- 只能并行到表级别，如果有一个表数据量特别大那么会存在非常严重的短板效应，甚至不如mysqldump。
- 导出的数据保存在一个文件中，导入仍旧是单线程，效率较低。
- 无法获取当前备份对应的binlog位置。
- 多个表、多个db可以并行导出（多线程并行导出），单个表不支持并行。

图片.png

2. mysqlpump限制

默认情况下，mysqlpump不会转储information_schema、performance_schema、ndbinfo或sys等系统库。
mysqlpump不会转储innodb创建表空间语句。
mysqlpump使用create user和grant语句以逻辑形式转储数据库账户。因此，mysql系统数据库的转储在默认情况下不包括用户定义的授权表。

更多参数，参考官方文档：
https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html
https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html

1. 常用备份语句

语法基本上和mysqldump 一样，我们这里简单列几个：

--备份sbtest库下的sbtest1表
mysqlpump -uroot -plhr -h192.168.56.2 -P3306 sbtest sbtest1 --single-transaction --set-gtid-purged=OFF --default-parallelism=4 > sbtest1.sql

--备份sbtest库
mysqlpump -uroot -plhr -h192.168.56.2 -P3306 sbtest --single-transaction --set-gtid-purged=OFF --default-parallelism=4 > sbtest_full.sql

--备份mysql库和sbtest库
mysqlpump -uroot -plhr -h192.168.56.2 -P3306 --databases mysql sbtest --single-transaction --set-gtid-purged=OFF --default-parallelism=4 > mysql_sbtest_full.sql

--备份所有数据库
mysqlpump -uroot -plhr -h192.168.56.2 -P3306 --all-databases --single-transaction --set-gtid-purged=OFF --default-parallelism=4 > all_full.sql

复制

2.恢复数据库

mysql> drop table sbtest.sbtest1;
Query OK, 0 rows affected (0.00 sec)

[root@mysql57 ~]# mysql -uroot -plhr -h192.168.56.2 -P3306 < sbtest1.sql
复制

二、mysqlpump 和 mysqldump对比

1. 备份文件对比

–1) 分别备份sxcdb 数据库

--1) 使用mysqldump备份
[root@mysql57 ~]# mysqldump -uroot -plhr sxcdb --single-transaction --set-gtid-purged=OFF > sxcdb_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

--2) 使用mysqlpump备份
[root@mysql57 ~]# mysqlpump -uroot -plhr sxcdb --single-transaction --set-gtid-purged=OFF --default-parallelism=4 > sxcdb_pump.sql
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
Dump progress: 1/2 tables, 0/2 rows
Dump completed in 421 milliseconds
复制

–2) 对比两个备份文件

mysqldump备份

[root@mysql57 ~]# cat sxcdb_dump.sql
-- MySQL dump 10.13  Distrib 5.7.27, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: sxcdb
-- ------------------------------------------------------
-- Server version       5.7.27-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 */;
/*!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 `t`
--

DROP TABLE IF EXISTS `t`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t`
--

LOCK TABLES `t` WRITE;
/*!40000 ALTER TABLE `t` DISABLE KEYS */;
INSERT INTO `t` VALUES (1);
/*!40000 ALTER TABLE `t` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t2`
--

DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t2`
--

LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
INSERT INTO `t2` VALUES (1,1,1),(2,2,2);
/*!40000 ALTER TABLE `t2` 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 2023-05-24 13:23:02

复制

mysqlpump备份

[root@mysql57 ~]# cat sxcdb_pump.sql
-- Dump created by MySQL pump utility, version: 5.7.27, linux-glibc2.12 (x86_64)
-- Dump start time: Wed May 24 13:23:08 2023
-- Server version: 5.7.27

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sxcdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
CREATE TABLE `sxcdb`.`t` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
;
CREATE TABLE `sxcdb`.`t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
;
INSERT INTO `sxcdb`.`t` VALUES (1);
INSERT INTO `sxcdb`.`t2` VALUES (1,1,1),(2,2,2);
USE `sxcdb`;
ALTER TABLE `sxcdb`.`t2` ADD UNIQUE KEY `c` (`c`);
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET SQL_MODE=@OLD_SQL_MODE;
-- Dump end time: Wed May 24 13:23:08 2023

复制

图片.png

通过对比可以看到主要区别，如下：

Mysqldump 备份	Mysqlpump 备份
加上参数–set-gtid-purged=OFF ，dmp文件中不记录SETSQL_LOG_BIN= 0;	加上参数–set-gtid-purged=OFF ，dmp文件中记录SETSQL_LOG_BIN= 0;这在导入时候，不记录binlog。
插入数据的时候，会执行lock table	不执行lock table操作
默认没有创建database语句，需要加上-B参数，这样可能导致导入时候导入到其他库中，需要小心。	默认有创建database语句
默认先drop table，再create table	不执行drop table 命令，并且创建表语句中包含db名称
创建表时就指定key	创建表时候除了主键PRIMARY KEY会创建，其他key在插入数据后再添加二级索引，这样能够减少数据插入时间
建一个表，插入一个表的数据	并行N个线程，会先建N个表，插入N个表的数据
可以通过–master-data 来获取binlog位置	无法获取binlog 位置，只能获取gtid，且set-gtid-purged必填。这对我们做一些不完全恢复是有影响。
–no-data 只导出定义	没有–no-data参数，不能只导出定义

--1)加上参数--master-data报错
[root@mysql57 ~]# mysqlpump -uroot -plhr sxcdb --single-transaction --set-gtid-purged=OFF --default-parallelism=4 --master-data=2 > sxcdb_pump.sql2
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
mysqlpump: [ERROR] unknown variable 'master-data=2'
mysqlpump: [ERROR] (7) Error during handling options
Dump process encountered error and will not continue.

--2)不加set-gtid-purged参数报错
[root@mysql57 ~]# mysqlpump -uroot -plhr sxcdb --single-transaction --default-parallelism=4 > sxcdb_pump.sql2
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
mysqlpump: [ERROR] (1) A partial dump from a server that is using GTID-based replication requires the --set-gtid-purged=[ON|OFF] option to be specified. Use ON if the intention is to deploy a new replication slave using only some of the data from the dumped server. Use OFF if the intention is to repair a table by copying it within a topology, and use OFF if the intention is to copy a table between replication topologies that are disjoint and will remain so.
Dump process encountered error and will not continue.

--3）加上参数--no-data报错
[root@mysql57 ~]# mysqlpump  -uroot -plhr --databases sbtest --single-transaction --set-gtid-purged=OFF --no-data > sbtest_ddl.sql
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
mysqlpump: [ERROR] unknown option '--no-data'
mysqlpump: [ERROR] (2) Error during handling options
Dump process encountered error and will not continue.

复制

2. 存在锁表的情况下

–1) 测试mysqldump备份

-- session 1 模拟一个锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


-- session2 做mysqldump备份：
[root@mysql57 ~]# mysqldump -uroot -plhr sxcdb t --set-gtid-purged=OFF  > t_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
blocked  --》 锁住

-- session3 检查：
mysql> show processlist;
+----+------+-----------+-------+---------+------+---------------------------------+----------------------------------------+
| Id | User | Host      | db    | Command | Time | State                           | Info                                   |
+----+------+-----------+-------+---------+------+---------------------------------+----------------------------------------+
|  2 | root | localhost | sxcdb | Sleep   |  120 |                                 | NULL                                   |
| 20 | root | localhost | NULL  | Query   |    0 | starting                        | show processlist                       |
| 21 | root | localhost | sxcdb | Query   |   12 | Waiting for table metadata lock | LOCK TABLES `t` READ /*!32311 LOCAL */ |
+----+------+-----------+-------+---------+------+---------------------------------+----------------------------------------+
3 rows in set (0.00 sec)
复制

结论：可以看到：mysqldump 备份被锁住，无法执行，通过session3 检查，发现是在执行lock table 期间，需要等Waiting for table metadata lock。

解决办法：

杀掉锁，kill 21; 注意: 这里不能使用crtl+C 取消，原因参考下面的实验3.
mysqldump 备份加上–single-transaction参数

–2) 测试mysqlpump备份

-- session 1 模拟一个锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- session2 做mysqlpump备份：
[root@mysql57 ~]# mysqlpump -uroot -plhr sxcdb t --set-gtid-purged=OFF  > t_pump.sql
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
Dump progress: 1/1 tables, 0/0 rows
Dump completed in 346 milliseconds
复制

结论：可以看到：mysqlpump 可以正常备份。mysqlpump备份默认不会锁定表。

实验3：

-- session 1 模拟一个锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


-- session2 做mysqldump备份
[root@mysql57 ~]# mysqldump -uroot -plhr sxcdb t --set-gtid-purged=OFF  > t_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
^C    --》 通过crtl+C 取消备份操作


-- 重新发起mysqlpump备份
[root@mysql57 ~]# mysqlpump -uroot -plhr sxcdb t --set-gtid-purged=OFF  > t_pump.sql
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
Dump progress: 1/1 tables, 0/0 rows
Dump completed in 381 milliseconds


[root@mysql57 ~]# mysqlpump -uroot -plhr sxcdb t --set-gtid-purged=OFF  --single-transaction > t_pump.sql
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
blocked  --》 发现加上--single-transaction参数后备份也锁住了

-- session3 检查：
mysql> show processlist;
+----+------+-----------+-------+---------+------+---------------------------------+----------------------------------------+
| Id | User | Host      | db    | Command | Time | State                           | Info                                   |
+----+------+-----------+-------+---------+------+---------------------------------+----------------------------------------+
|  2 | root | localhost | sxcdb | Sleep   |  101 |                                 | NULL                                   |
| 20 | root | localhost | NULL  | Query   |    0 | starting                        | show processlist                       |
| 32 | root | localhost | sxcdb | Query   |   11 | Waiting for table metadata lock | LOCK TABLES `t` READ /*!32311 LOCAL */ |
| 33 | root | localhost | NULL  | Query   |    3 | Waiting for table flush         | FLUSH TABLES WITH READ LOCK            |
+----+------+-----------+-------+---------+------+---------------------------------+----------------------------------------+
4 rows in set (0.00 sec)
复制

可以看到：mysqlpump 备份被锁，是因为上面Crtl+C 操作，虽然取消了备份操作，但是相应的MDL锁并未释放，mysqlpump备份加上–single-transaction参数后，执行FLUSH TABLES WITH READ LOCK时候，被锁住。

解决办法：

杀掉锁，kill 32;

这里，我们注意：实际上，执行Ctrl+C的时候，是MySQL客户端另外启动一个连接，然后发送一个kill query命令，而kill query是不释放锁的。

墨力计划 mysqlpump

最后修改时间：2023-05-25 14:50:00

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

mysqlpump 和 mysqldump 简单对比
一、mysqlpump 介绍
- 1. Mysqlpump优缺点
- 2. mysqlpump限制
- 1. 常用备份语句
- 2.恢复数据库
二、mysqlpump 和 mysqldump对比
- 1. 备份文件对比
- 2. 存在锁表的情况下

mysqlpump 和 mysqldump 简单对比

mysqlpump 和 mysqldump 简单对比

一、mysqlpump 介绍

1. Mysqlpump优缺点

2. mysqlpump限制

1. 常用备份语句

2.恢复数据库

二、mysqlpump 和 mysqldump对比

1. 备份文件对比

2. 存在锁表的情况下

评论