使用 mysqlpump 进行 Mysql 逻辑备份

 在本文中，我们将使用mysqlpump来演示 Mysql 逻辑备份。 

有几种方法可以备份 MySQL 数据。

它们是： –
1) 物理备份
2) 逻辑备份
物理备份：
它复制具有数据库内容的目录及其文件。物理备份也称为 原始备份。
逻辑备份：

它将所有可用的行转换为单个或多个插入，还包含用于创建数据库、表、触发器等的 create 语句。逻辑备份也称为 文本备份。

可用于逻辑备份的实用程序列表： – 

 1) Mysqldump 
 2) Mysqlpump
3) Mydumper   
mysql泵：

所述Mysqlpump客户实用程序执行逻辑备份，产生一组将被执行滋生主数据库对象定义和表数据的SQL语句。
mysqlpump 优于 mysqldump 的优势：

• 本机支持备份压缩。
• 默认情况下对并行性的原生支持是 2。
• 默认情况下，所有对象都被备份，包括例程、触发器、事件，不需要像mysqldump那样指定 –routines –events –triggers 。
• 显示备份进度。
• 支持–include-database , –exclude-database , –include-table , –exclude-table与模式匹配（%）。
• 默认情况下不会在转储文件中添加drop table 命令。您需要通过--add-drop-table 显式指定参数。

完整数据库备份：使用以下命令进行完整的 mysql 实例备份，除了 information_schema 和 performance_schema。

# mysqlpump -hlocalhost -uroot -p  --all-databases > alldbdump.sql
Enter password:
Dump progress: 0/3 tables, 250/331176 rows
Dump progress: 4/31 tables, 1399627/3916165 rows
Dump progress: 30/31 tables, 2755761/3916165 rows
Dump completed in 2928

压缩备份： 使用以下命令创建压缩备份。您可以使用 LZ4或ZLIB压缩算法。
请注意， LZ4使用的时间较少，但提供的压缩级别较低，相反，ZLIB 需要更多的时间，但提供的压缩级别较高。

# mysqlpump -hlocalhost -uroot -p  --all-databases --compress-output=lz4 > lz4compress_alldbdump.sql
Enter password:
Dump progress: 1/3 tables, 0/331176 rows
Dump progress: 4/31 tables, 1641377/3916165 rows
Dump progress: 30/31 tables, 3221511/3916165 rows
Dump completed in 2532
# mysqlpump -hlocalhost -uroot -p  --all-databases --compress-output=zlib > zlibcompress_alldbdump.sql
Enter password:
Dump progress: 1/2 tables, 0/331152 rows
Dump progress: 2/31 tables, 307000/3916165 rows
Dump progress: 3/31 tables, 644774/3916165 rows
Dump progress: 4/31 tables, 1074627/3916165 rows
Dump progress: 4/31 tables, 1509877/3916165 rows
Dump progress: 30/31 tables, 1878511/3916165 rows
Dump progress: 30/31 tables, 2279261/3916165 rows
Dump progress: 30/31 tables, 2679761/3916165 rows
Dump progress: 30/31 tables, 3081511/3916165 rows
Dump progress: 30/31 tables, 3482261/3916165 rows
Dump progress: 30/31 tables, 3885761/3916165 rows
Dump completed in 10862

并行备份：使用命令设置并行度–default-parallelism，您甚至可以使用命令–parallel-schemas设置单个数据库/模式的并行度。

# mysqlpump -hlocalhost -uroot -p --all-databases --default-parallelism=4 > parallel1_alldbdump.sql
Enter password:
Dump progress: 1/1 tables, 0/9 rows
Dump progress: 30/31 tables, 1798761/3916165 rows
Dump progress: 30/31 tables, 3460261/3916165 rows
Dump completed in 2357
# mysqlpump -hlocalhost -uroot -p -uroot -p --all-databases --default-parallelism=2 --parallel-schemas=4:employees  > parallel2_alldbdump.sql
Enter password:
Enter password:
Dump progress: 1/2 tables, 0/331152 rows
Dump progress: 30/31 tables, 1756261/3916165 rows
Dump progress: 30/31 tables, 3407511/3916165 rows
Dump completed in 2416

多个数据库：使用 以下命令进行多个数据库备份，但不是全部。

# mysqlpump  -hlocalhost -uroot -p  --databases employees repl_test  >employees_repldb_dump.sql
Enter password:
Dump progress: 1/1 tables, 0/9 rows
Dump progress: 2/11 tables, 496000/3912633 rows
Dump progress: 4/11 tables, 944127/3912633 rows
Dump progress: 4/11 tables, 1292127/3912633 rows
Dump progress: 10/11 tables, 1706185/3912633 rows
Dump progress: 10/11 tables, 2323935/3912633 rows
Dump progress: 10/11 tables, 2756935/3912633 rows
Dump progress: 10/11 tables, 3327185/3912633 rows
Dump progress: 10/11 tables, 3458685/3912633 rows
Dump progress: 10/11 tables, 3555685/3912633 rows
Dump progress: 10/11 tables, 3768685/3912633 rows
Dump progress: 10/11 tables, 3888685/3912633 rows
Dump completed in 13137

单个数据库：使用以下命令进行单个数据库备份。

# mysqlpump -hlocalhost -uroot -p employees >employeesdb_dump.sql
Enter password:
Dump progress: 1/1 tables, 0/9 rows
Dump progress: 2/6 tables, 85000/3912591 rows
Dump progress: 2/6 tables, 154250/3912591 rows
Dump progress: 2/6 tables, 309500/3912591 rows
Dump progress: 2/6 tables, 490750/3912591 rows
Dump progress: 4/6 tables, 688377/3912591 rows
Dump progress: 4/6 tables, 960627/3912591 rows
Dump progress: 4/6 tables, 1201877/3912591 rows
Dump progress: 5/6 tables, 1836935/3912591 rows
Dump progress: 5/6 tables, 2328685/3912591 rows
Dump progress: 5/6 tables, 2922185/3912591 rows
Dump progress: 5/6 tables, 3405935/3912591 rows
Dump completed in 12451

多表：使用以下命令进行多表备份。

# mysqlpump  -hlocalhost -uroot -p employees current_dept_emp departments > employeesspecifictable_dump.sql
Enter password:
Dump progress: 1/1 tables, 0/9 rows
Dump completed in 355

单表：使用以下命令进行单表备份。

# mysqldump  -hlocalhost -uroot -p employees current_dept_emp > employeesspecifictable_dump.sql
Enter password:

使用正则表达式排除/包含数据库对象： Mysqlpump 支持排除和包含命令，您还可以将这些命令与正则表达式结合起来以包含或排除对象模式。

# mysqlpump -hlocalhost -uroot -p --include-databases=repl% --result-file=db_include.sql
Enter password:
Dump progress: 1/4 tables, 0/42 rows
Dump completed in 76 
# mysqlpump -hlocalhost -uroot -p --include-databases=emp% --exclude-tables=departments,employees,salaries --result-file=exclude_table.sql
Enter password:
Dump progress: 0/2 tables, 250/331167 rows
Dump completed in 496

只有数据没有创建表和删除表命令。

# mysqlpump -hlocalhost -uroot -p  --skip-add-drop-table --no-create-info --databases employees departments > skip_dropcreate_table_dump.sql        
Enter password:
Dump progress: 1/4 tables, 0/630599 rows
Dump progress: 5/6 tables, 1800935/3912591 rows
Dump progress: 5/6 tables, 3254935/3912591 rows
Dump progress: 5/6 tables, 3822185/3912591 rows
Dump completed in 3373

只有架构没有数据。

# mysqlpump  -hlocalhost -uroot -p  --all-databases  --skip-dump-rows > onlyschema_nodata_alldbdump.sql
Enter password:
Dump completed in 169

只有数据没有架构。

# mysqlpump  -hlocalhost -uroot -p  --databases employees --no-create-db --no-create-info --complete-insert > onlydata_noschema_alldbdump.sql
Enter password:
Dump progress: 1/1 tables, 0/9 rows
Dump progress: 5/6 tables, 1793435/3912591 rows
Dump progress: 5/6 tables, 3459435/3912591 rows
Dump completed in 2479

导入：
与mysqldump的区别之一是mysqlpump 默认将CREATE DATABASE语句添加到转储中，除非使用–no-create-db选项指定。mysqlpump在添加CREATE TABLE语句时还包括数据库名称。

以下是我们使用mysqlpump 备份实用程序创建的备份转储文件 。

#  ls -ltr
total 1300780
-rw-r--r--. 1 root root 170126435 Jan 31 12:18 alldbdump.sql
-rw-r--r--. 1 root root  72578407 Jan 31 12:25 lz4compress_alldbdump.sql
-rw-r--r--. 1 root root  36918513 Jan 31 12:26 zlibcompress_alldbdump.sql
-rw-r--r--. 1 root root 170126435 Jan 31 12:47 parallel1_alldbdump.sql
-rw-r--r--. 1 root root 170126435 Jan 31 12:57 parallel2_alldbdump.sql
-rw-r--r--. 1 root root 169040795 Jan 31 14:07 employees_repldb_dump.sql
-rw-r--r--. 1 root root 169038676 Jan 31 14:08 employeesdb_dump.sql
-rw-r--r--. 1 root root      2775 Jan 31 14:09 employeesspecifictable_dump.sql
-rw-r--r--. 1 root root      3073 Jan 31 14:17 db_include.sql
-rw-r--r--. 1 root root  35227619 Jan 31 14:19 exclude_table.sql
-rw-r--r--. 1 root root 169037182 Jan 31 14:21 skip_dropcreate_table_dump.sql
-rw-r--r--. 1 root root     15654 Jan 31 14:23 onlyschema_nodata_alldbdump.sql
-rw-r--r--. 1 root root 169728790 Jan 31 14:24 onlydata_noschema_alldbdump.sql

为了执行导入，我们在同一服务器中创建了第二个 mysql 实例。

# mysql -u root -p -S mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.22 MySQL Community Server - GPL
 
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>
mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

完整数据库备份导入：请注意默认情况下 mysqlpump 实用程序在转储文件中添加CREATE DATABASE/IF NOT EXISTS语句。因此，如果 DB 不存在，它将被创建。

# mysql -hlocalhost -uroot -p -Smysql.sock < alldbdump.sql
Enter password:
#
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

单个数据库导入：使用以下命令导入单个数据库备份。这两个命令都可以工作，对于第二个命令，您需要在执行导入命令之前创建数据库员工

# mysql -hlocalhost -uroot -p -Smysql.sock  < employeesdb_dump.sql
Enter password:
# mysql -hlocalhost -uroot -p -Smysql.sock employees < employeesdb_dump.sql
Enter password:

压缩备份导入：对于压缩备份，您需要先解压缩它们，然后运行导入命令。

Usage: lz4_decompress input_file output_file
 
# lz4_decompress lz4compress_alldbdump.lz4 lz4compress_alldbdump.sql
 
# mysql -hlocalhost -uroot -p -Smysql.sock < lz4compress_alldbdump.sql
Enter password:
 
Usage: zlib_decompress input_file output_file
 
# zlib_decompress zlibcompress_alldbdump.zlib zlibcompress_alldbdump.sql
 
# mysql -hlocalhost -uroot -p -Smysql.sock < zlibcompress_alldbdump.sql
Enter password:

表备份导入：您需要手动删除表，或者您可以在进行表级备份之前添加 –add-drop-table 选项。

# mysql -hlocalhost -uroot -p -Smysql.sock < departmentstable1_dump.sql
Enter password:
ERROR 1050 (42S01) at line 23: Table 'departments' already exists
 
mysql> drop table departments;
Query OK, 0 rows affected (0.01 sec)
 
# mysql -hlocalhost -uroot -p -Smysql.sock < departmentstable1_dump.sql
Enter password: 
 
# mysql -hlocalhost -uroot -p -Smysql.sock employees < departmentstable1_dump.sql
Enter password:

仅导入数据没有创建表转储： 我首先在表上进行截断，然后使用以下命令导入数据。

mysql> truncate table departments;
Query OK, 0 rows affected (0.01 sec)
 
# mysql -hlocalhost -uroot -p -Smysql.sock employees <  skip_dropcreate_table_dump.sql
Enter password:
 
mysql> select count(*) from departments;
+----------+
| count(*) |
+----------+
|        9 |
+----------+

只有架构没有数据转储文件导入

#  mysql -hlocalhost -uroot -p -Smysql.sock < onlyschema_nodata_alldbdump.sql
Enter password:

只有数据没有架构转储文件导入

# mysql -hlocalhost -uroot -p -Smysql.sock < onlydata_noschema_alldbdump.sql 
Enter password: