mysqldump命令详解
2020/08/02
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 ```
6