mysqldump命令详解

创建日期：2020/08/02 修改日期：2022/11/15 MySQL

MySQL版本：5.7.30

> 官方教程：https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

### 1. 命令格式

```bash
# 备份指定的数据库，若需要备份指定的表，则跟在数据库后面，可跟多个
mysqldump [options] db_name [tbl_name ...]
# 备份单个或多个数据库
mysqldump [options] -B db_name ...
# 备份所有的数据库
mysqldump [options] -A
```

完整参数请查看`mysqldump --help`或者官方教程

### 2. 常用选项

<div style="width: 150pt">参数 | 描述 | 默认值
---|---|---
-u, --user=name | 用户名 | ODBC
-p, --password[=name] | 数据库密码；如果密码没有些，则需要在命令回车后输入 |
-h, --host=name | 数据库地址 | 127.0.0.1
-P, --port=# | 数据库端口 | 3306
-r, --result-file=name | 备份到指定文件 |
--ignore-table=database.table | 跳过指定表（一次仅指定一张表，如需多张表，需要多次使用） |
-B, --databases | 备份多个数据库 |
-A, --all-databases | 备份所有数据库 |
--single-transaction | 使用单个事务转储所有表来创建一致的快照（仅支持InnoDB） |
-F, --flush-logs | 备份前刷新日志文件 |
-e, --extended-insert | 使用多行插入的INSERT语法，即一个`INSERT`语句插入多条记录 | 默认
--skip-extended-insert | 使用单行插入的INSERT语法，即一个`INSERT`语句插入一条记录 |
-R, --routines | 备份存储函数和过程 |
-t, --no-create-info | 仅备份表数据 |
-d, --no-data | 仅备份表结构 |
--no-autocommit | 设置后，SQL不包含`set autocommit=0` `commit`，即开启自动提交。不推荐使用 |
--dump-slave[=#] | 略。待整理 |
--master-data[=#] | 略。待整理 |
-C, --compress | 在服务器/客户端协议中使用压缩（待测试） |
--compact | 简化输出，禁用结构注释和页眉/页脚构造。 |
--add-drop-database | 添加删除库语句 |

### 3. 参数示例

> 示例中的`-xxx`参数仅举例简写的

#### -u -p 用户名和密码

1. `-p`一定要写，若不写`-p`，则使用空密码
2. 若在`-p`后面填写密码，则需要紧跟，若密码有复杂符合，需要使用`''`引号包起来

```bash
mysqldump -u root -p demo > dump.sql
mysqldump -u root -ppassword demo > dump.sql
mysqldump -u root -p'sGn#CL%c!TpGG%D1' demo > dump.sql
```

#### -h -P 数据库地址和端口号

```bash
mysqldump -h 127.0.0.1 -P 3306 -u root -p demo > dump.sql
```

#### -r 备份到指定文件

> 如果是Windows系统，使用`>`导出时，文件的换行符为`\r\n`，所以在Windows系统中推荐直接使用`-r`指定输出文件，个人推荐在所有系统中都是用`-r`指定输出文件

```bash
# 不使用 > ，直接指定备份输出文件 dump.sql 
mysqldump -u root -p -r dump.sql -B demo
```

#### 备份指定库与表，--ignore-table 跳过备份指定表，-B 备份多个数据库，-A 备份所有数据库

1. 备份单个库时，若不写`-B`，则备份的sql语句中没有`CREATE DATABASE`语句
2. `-A`备份所有库时，不会备份`information_schema`,`performance_schema`,`sys`表，即仅备份自己创建的表和`mysql`表

```
# 备份指定库（单个库时 推荐写一下 -B ）
mysqldump -u root -p -r dump.sql demo
mysqldump -u root -p -r dump.sql -B demo

# 跳过指定表（多张表需要多次指定）
mysqldump -u root -p --ignore-table=demo.table1 -r dump.sql -B demo
mysqldump -u root -p --ignore-table=demo.table1 --ignore-table=demo.table2 -r dump.sql -B demo

# 备份指定库的指定表
mysqldump -u root -p -r dump.sql demo table1
# 备份指定库的多个表
mysqldump -u root -p -r dump.sql demo table1 table2

# 备份多个库
mysqldump -u root -p -r dump.sql -B demo demo2

# 备份整个MySQL
mysqldump -u root -p -r dump.sql -A
```

#### --single-transaction 通过事务隔离模式保证备份的一致性

> 只支持InnoDB。
在使用`--single-transaction`转储时，其他连接不能使用以下语句:`ALTER table`,`DROP table`,`RENAME table`,`TRUNCATE table`

```bash
mysqldump -u root -p --single-transaction -r dump.sql -B demo
```

#### -F 刷新日志文件

```bash
# 备份的同时新建二进制文件（需开启bin-log）
mysqldump -u root -p -F -r dump.sql -B demo
```

#### -e 默认INSERT格式， --skip-extended-insert 修改INSERT格式

```bash
# -e 默认不需要写，多记录一起插入
# 导出的sql为：INSERT INTO `table` VALUES (...),(...)...
mysqldump -u root -p -e -r dump.sql -B demo
# --skip-extended-insert 一条INSERT插入一调记录
# 导出的sql为：INSERT INTO `table` VALUES (...);INSERT INTO `table` VALUES (...);
mysqldump -u root -p --skip-extended-insert -r dump.sql -B demo
```

#### -R 备份存储结构和过程

```bash
mysqldump -u root -p -R -r dump.sql -B demo
```

#### -t 只备份表数据，-d 只备份表结构

```bash
# 只备份表数据，即只有 INSERT 语句
mysqldump -u root -p -t -r dump.sql -B demo
# 只备份表结构，即只有 DROP TABLE,CREATE TABLE 语句
mysqldump -u root -p -d -r dump.sql -B demo
```

### 4. 完整示例

> 推荐参数按照如下顺序写：`-h`,`-P`,`-u`,`-p`,`--single-transaction`,`-F`,`[其他选择]`,`-r 文件名`,`数据库名 [表名 ...] 或 -B 数据库名 ... 或 -A`

```bash
# 备份demo数据库：
# 备份时开启事务保存一致性，备份前刷新二进制文件，使用单行INSERT插入，去除页眉页脚，备份存储过程，备份到db.sql文件
mysqldump -h127.0.0.1 -P3306 -uroot -p --single-transaction -F --skip-extended-insert --compact -R -r db.sql -B demo
```

全部评论