目录
一、背景
二、简介
三、备份
1. 打开“命令提示符”
2. 执行命令（在命令行下）
2.1 如果有需要，请先切换到对应的目录下
2.2 输入命令，进行备份
2.3 其他的参数
四、恢复
一、背景
因为数据库时常需要进行备份，而每次备份都需要对每个数据库进行相应的操作，耗费很多的时间和精力，后来偶然发现可以使用MySQL数据库提供的逻辑备份工具mysqlpump（mysqldump也可以，不过mysqlpump会显示进度，个人感觉更好用一些）进行数据库的备份，这可以省去很多的时间。
二、简介
以下内容源自mysql手册。
mysqlpump程序执行逻辑备份。可以生成一组 SQL 语句，通过执行这些语句可以恢复原始数据库对象定义和表数据。
其功能包括：
- 并行处理数据库和数据库中的对象，以加快转储过程（相较于mysqldump）
- 更好地控制要转储的数据库和数据库对象（表、存储的程序、用户帐户）
- 将用户帐户转储为帐户管理语句（CREAT USER、GRANT），而不是插入到系统数据库mysql中
- 创建压缩输出的能力
- 进度指示器（值为估计值）
- 对于转储文件重新加载，通过在插入行后添加索引来更快地为表创建二级索引
InnoDB
更多内容请参考：mysqlpump — A Database Backup Program
三、备份
1. 打开“命令提示符”
可以使用“Windows键 + R，输入cmd，点击回车”打开
也可以以管理员权限打开命令提示符
2. 执行命令（在命令行下）
2.1 如果有需要，请先切换到对应的目录下
cd 对应的目录2.2 输入命令，进行备份
如果对本地的数据库进行备份（可以省略IP），使用以下命令：
mysqlpump -u用户名 -p密码 --all-databases > 备份文件名.sql
如果对远程数据库进行备份，则需要提供IP信息：
mysqlpump -h你的IP地址 -u用户名 -p密码 --all-databases > 备份文件名.sql
可以看到：
注：如果提示找不到该命令，则需要切换到MySQL程序安装目录的bin文件夹下执行上述命令。
2.3 其他的参数
Option Name | Description | Introduced | Deprecated |
--add-drop-database | Add DROP DATABASE statement before each CREATE DATABASE statement | ||
--add-drop-table | Add DROP TABLE statement before each CREATE TABLE statement | ||
--add-drop-user | Add DROP USER statement before each CREATE USER statement | ||
--add-locks | Surround each table dump with LOCK TABLES and UNLOCK TABLES statements | ||
--all-databases | Dump all databases | ||
--bind-address | Use specified network interface to connect to MySQL Server | ||
--character-sets-dir | Directory where character sets are installed | ||
--column-statistics | Write ANALYZE TABLE statements to generate statistics histograms | ||
--complete-insert | Use complete INSERT statements that include column names | ||
--compress | Compress all information sent between client and server | 8.0.18 | |
--compress-output | Output compression algorithm | ||
--compression-algorithms | Permitted compression algorithms for connections to server | 8.0.18 | |
--databases | Interpret all name arguments as database names | ||
--debug | Write debugging log | ||
--debug-check | Print debugging information when program exits | ||
--debug-info | Print debugging information, memory, and CPU statistics when program exits | ||
--default-auth | Authentication plugin to use | ||
--default-character-set | Specify default character set | ||
--default-parallelism | Default number of threads for parallel processing | ||
--defaults-extra-file | Read named option file in addition to usual option files | ||
--defaults-file | Read only named option file | ||
--defaults-group-suffix | Option group suffix value | ||
--defer-table-indexes | For reloading, defer index creation until after loading table rows | ||
--events | Dump events from dumped databases | ||
--exclude-databases | Databases to exclude from dump | ||
--exclude-events | Events to exclude from dump | ||
--exclude-routines | Routines to exclude from dump | ||
--exclude-tables | Tables to exclude from dump | ||
--exclude-triggers | Triggers to exclude from dump | ||
--exclude-users | Users to exclude from dump | ||
--extended-insert | Use multiple-row INSERT syntax | ||
--get-server-public-key | Request RSA public key from server | ||
--help | Display help message and exit | ||
--hex-blob | Dump binary columns using hexadecimal notation | ||
--host | Host on which MySQL server is located | ||
--include-databases | Databases to include in dump | ||
--include-events | Events to include in dump | ||
--include-routines | Routines to include in dump | ||
--include-tables | Tables to include in dump | ||
--include-triggers | Triggers to include in dump | ||
--include-users | Users to include in dump | ||
--insert-ignore | Write INSERT IGNORE rather than INSERT statements | ||
--log-error-file | Append warnings and errors to named file | ||
--login-path | Read login path options from .mylogin.cnf | ||
--max-allowed-packet | Maximum packet length to send to or receive from server | ||
--net-buffer-length | Buffer size for TCP/IP and socket communication | ||
--no-create-db | Do not write CREATE DATABASE statements | ||
--no-create-info | Do not write CREATE TABLE statements that re-create each dumped table | ||
--no-defaults | Read no option files | ||
--parallel-schemas | Specify schema-processing parallelism | ||
--password | Password to use when connecting to server | ||
--password1 | First multifactor authentication password to use when connecting to server | 8.0.27 | |
--password2 | Second multifactor authentication password to use when connecting to server | 8.0.27 | |
--password3 | Third multifactor authentication password to use when connecting to server | 8.0.27 | |
--plugin-dir | Directory where plugins are installed | ||
--port | TCP/IP port number for connection | ||
--print-defaults | Print default options | ||
--protocol | Transport protocol to use | ||
--replace | Write REPLACE statements rather than INSERT statements | ||
--result-file | Direct output to a given file | ||
--routines | Dump stored routines (procedures and functions) from dumped databases | ||
--server-public-key-path | Path name to file containing RSA public key | ||
--set-charset | Add SET NAMES default_character_set to output | ||
--set-gtid-purged | Whether to add SET @@GLOBAL.GTID_PURGED to output | ||
--single-transaction | Dump tables within single transaction | ||
--skip-definer | Omit DEFINER and SQL SECURITY clauses from view and stored program CREATE statements | ||
--skip-dump-rows | Do not dump table rows | ||
--skip-generated-invisible-primary-key | Do not dump information about generated invisible primary keys | 8.0.30 | |
--socket | Unix socket file or Windows named pipe to use | ||
--ssl-ca | File that contains list of trusted SSL Certificate Authorities | ||
--ssl-capath | Directory that contains trusted SSL Certificate Authority certificate files | ||
--ssl-cert | File that contains X.509 certificate | ||
--ssl-cipher | Permissible ciphers for connection encryption | ||
--ssl-crl | File that contains certificate revocation lists | ||
--ssl-crlpath | Directory that contains certificate revocation-list files | ||
--ssl-fips-mode | Whether to enable FIPS mode on client side | ||
--ssl-key | File that contains X.509 key | ||
--ssl-mode | Desired security state of connection to server | ||
--ssl-session-data | File that contains SSL session data | 8.0.29 | |
--ssl-session-data-continue-on-failed-reuse | Whether to establish connections if session reuse fails | 8.0.29 | |
--tls-ciphersuites | Permissible TLSv1.3 ciphersuites for encrypted connections | 8.0.16 | |
--tls-version | Permissible TLS protocols for encrypted connections | ||
--triggers | Dump triggers for each dumped table | ||
--tz-utc | Add SET TIME_ZONE='+00:00' to dump file | ||
--user | MySQL user name to use when connecting to server | ||
--users | Dump user accounts | ||
--version | Display version information and exit | ||
--watch-progress | Display progress indicator | ||
--zstd-compression-level | Compression level for connections to server that use zstd compression | 8.0.18 |
比如我需要备份“以a开头”和“以BC开头”的数据库，文件存储在bakup.sql中。同时，我希望先清空所有“以a开头”和“以BC开头”的数据库之后再把备份的内容写入到数据库，可以这么写：
mysqlpump -u用户名 -p密码 --add-drop-database --add-drop-table --include-databases=a%,BC% > bakup.sql
注：
--add-drop-database 在每个创建数据库语句之前添加 DROP DATABASE 语句
--add-drop-table 在每个 CREATE TABLE 语句之前添加 DROP TABLE 语句
--include-databases=a%,BC% 只对“以a开头”和“以BC开头”的数据库进行备份更多内容请参考：mysqlpump — A Database Backup Program
四、恢复
删除上面的所有用于测试的数据库，观察是否能够恢复：
mysql -u用户 -p -f < 备份文件名称.sql
注：-f意为遇到错误仍然继续
最后可以发现，把数据库都删除之后，数据库被成功复原。
如有不当或错误之处，恳请您的指正，谢谢！！！
