使用 mysqlpump 进行 Mysql 逻辑备份

待我称王封你为后i 2023-10-01 08:01 18阅读 0赞

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

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

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

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

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

**1)** **Mysqldump****[ ][Link 1]**  
 **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  < 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:

[Link 1]: https://dbsguru.com/mysql-logical-backup-using-mysqldump-export/