mysqldump是mysql软件内自带的逻辑备份工具,把数据从MySQL库中以逻辑的SQL语句的形式直接输出或生成备份的文件的过程。该工具只支持单线程工作。支持远程导出。
Eg:mysqldump –h127.0.0.1 -uroot –pmysql –R –single-transaction –master-data=2 --q –e –all-databases
Format |
Introduced |
默认配置 |
--add-drop-database |
文件中在创建数据库语句前添加删库语句 |
FALSE |
--add-drop-table |
文件中在创建表语句前添加删表语句 |
TRUE |
--add-drop-trigger |
文件中在创建触发器语句前添加删触发器语句 |
FALSE |
--add-locks |
数据前后添加锁表解锁表 |
TRUE |
--all-databases |
导出所有数据库 |
FALSE |
--allow-keywords |
允许创建是关键词的列名字 |
FALSE |
--apply-slave-statements |
在'CHANGE MASTER'前添加'STOP SLAVE',并且在导出的最后添加'START SLAVE'。 |
FALSE |
--bind-address |
使用指定的网络接口连接到MySQL服务器 |
(No default value) |
--character-sets-dir |
安装字符集的目录 |
(No default value) |
--comments |
添加注释 |
FALSE |
--compact |
导出更少的输出信息(用于调试)。去掉注释和头尾等结构 |
FALSE |
--compatible |
导出的数据将和其它数据库或旧版本的MySQL 相兼容 |
(No default value) |
--complete-insert |
使用完整的insert语句(包含列名称)。这么做能提高插入效率 |
FALSE |
--compress |
在客户端和服务器之间启用压缩传递所有信息 |
FALSE |
--create-options |
在CREATE TABLE语句中包括所有MySQL特性选项 |
FALSE |
--databases |
导出几个数据库 |
FALSE |
--debug |
输出debug信息,用于调试 |
|
--debug-check |
输出调试信息并退出 |
FALSE |
--debug-info |
检查内存和打开文件使用说明并退出 |
FALSE |
--default-auth |
客户端插件默认使用权限。 |
|
--default-character-set |
默认字符集 |
utf8 |
--defaults-extra-file |
读取额外参数文件 |
|
--defaults-file |
读取参数文件 |
|
--defaults-group-suffix |
|
|
--delayed-insert |
延时插入方式(INSERT DELAYED)导出数据 |
FALSE |
--delete-master-logs |
master备份后删除日志 |
FALSE |
--disable-keys |
插入所有行之后创建索引 |
TRUE |
--dump-date |
添加导出时间 |
TRUE |
--dump-slave |
添加主库的binlog位置 |
0 |
--enable-cleartext-plugin |
|
|
--events |
导出时间 |
FALSE |
--extended-insert |
使用使用具有多个VALUES列的INSERT语法 |
TRUE |
--fields-enclosed-by |
在每一个字段的前后加上char这个字符 |
(No default value) |
--fields-escaped-by |
使用char去转义特殊字符 |
(No default value) |
--fields-optionally-enclosed-by |
在每一个非数字的字段前后加上char这个字符 |
(No default value) |
--fields-terminated-by |
列与列之间分隔 |
(No default value) |
--flush-logs |
开始导出之前刷新日志。 |
FALSE |
--flush-privileges |
刷新权限 |
FALSE |
--force |
在导出过程中忽略出现的SQL错误 |
FALSE |
--help |
帮助信息 |
|
--hex-blob |
使用十六进制格式导出二进制字符串字段 |
FALSE |
--host |
主机地址 |
(No default value) |
--ignore-table |
不导出指定表 |
|
--include-master-host-port |
--dump-slave同时使用添加主机信息 |
FALSE |
--insert-ignore |
在插入行时使用INSERT IGNORE语句. |
FALSE |
--lines-terminated-by |
换行 |
(No default value) |
--lock-all-tables |
锁住所有表,自动关闭--single-transaction 和--lock-tables 选项 |
FALSE |
--lock-tables |
锁住表 |
TRUE |
--log-error |
附加警告和错误信息到给定文件 |
(No default value) |
--login-path |
|
|
--master-data |
主机端binlog点 |
0 |
--max_allowed_packet |
服务器发送和接受的最大包长度。 |
25165824 |
--net_buffer_length |
TCP/IP和socket连接的缓存大小。 |
1046528 |
--no-autocommit |
使用autocommit/commit 语句包裹表 |
FALSE |
--no-create-db |
不添加create database语句 |
FALSE |
--no-create-info |
只导出数据 |
FALSE |
--no-data |
只导出格式 |
FALSE |
--no-defaults |
不阅读参数文件 |
|
--no-set-names |
|
|
--no-tablespaces |
不写相关语句 |
|
--opt |
等于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys |
|
--order-by-primary |
根据主键排序 |
FALSE |
--password |
密码 |
|
--pipe |
使用命名管道连接mysql |
|
--plugin-dir |
客户端插件的目录,用于兼容不同的插件版本 |
(No default value) |
--port |
端口 |
0 |
--print-defaults |
输出默认参数 |
|
--protocol |
|
|
--quick |
不缓冲查询,直接导出到标准输出 |
TRUE |
--quote-names |
使用单引号包围表和列名 |
TRUE |
--replace |
使用REPLACE INTO 取代INSERT INTO. |
FALSE |
--result-file |
直接输出到指定文件 |
FALSE |
--routines |
导出存储过程及自定义函数 |
FALSE |
--secure-auth |
|
TRUE |
--set-charset |
添加SET NAMES default_character_set' |
TRUE |
--set-gtid-purged |
|
|
--shared-memory-base-name |
|
|
--single-transaction |
事务 |
FALSE |
--skip-add-drop-table |
不添加drop表操作 |
|
--skip-add-locks |
不加锁 |
|
--skip-comments |
不添加注释 |
|
--skip-compact |
不减少注释 |
|
--skip-disable-keys |
不允许关键字作为列名 |
|
--skip-extended-insert |
关闭extended-insert |
|
--skip-opt |
禁用-opt |
|
--skip-quick |
禁用-quick |
|
--skip-quote-names |
|
|
--skip-set-charset |
|
|
--skip-triggers |
不导出触发器 |
|
--skip-tz-utc |
|
|
--socket |
套接字 |
/tmp/mysql.sock |
--ssl |
|
FALSE |
--ssl-ca |
|
(No default value) |
--ssl-capath |
|
(No default value) |
--ssl-cert |
|
(No default value) |
--ssl-cipher |
|
(No default value) |
--ssl-crl |
|
(No default value) |
--ssl-crlpath |
|
(No default value) |
--ssl-key |
|
(No default value) |
--ssl-mode |
|
(No default value) |
--ssl-verify-server-cert |
|
FALSE |
--tab |
创建tab分割文件 |
(No default value) |
--tables |
表 |
|
--triggers |
导出触发器 |
TRUE |
--tz-utc |
导出顶部设置时区TIME_ZONE='+00:00' |
TRUE |
--user |
用户 |
root |
--verbose |
输出多种平台信息 |
FALSE |
--version |
版本 |
|
--where |
筛选条件 |
(No default value) |
--xml |
导出xml格式 |
|
参考文档:https: // blog.csdn.net/stephenxu111/article/details/9310715
导出格式 |
文件内容 |
SQL Format |
导出文件是一组SQL语句集,文件内包含创建转储对象(数据库、表、存储例程等)的CREATE语句,以及将数据加载到表中的INSERT语句。 |
Delimited-Text Format |
导出文件可以以特定分隔符分隔的一个或者多个文本文件(TXT,CSV等),可以直接对数据进行查找、替换和修改等操作。 |
涉及参数 secure-file-priv
Command-Line Format |
--secure-file-priv=dir_name |
|
System Variable |
Name |
secure_file_priv |
|
Variable Scope |
Global |
|
Dynamic Variable |
No |
Permitted Values (<= 5.6.33) |
Type |
string |
|
Default |
empty |
|
Valid Values |
empty |
|
|
dirname |
Permitted Values (>= 5.6.34) |
Type |
string |
|
Default |
platform specific |
|
Valid Values |
empty |
|
|
dirname |
|
|
NULL |
常用参数 |
含义 |
-A |
全库导出的只有表、视图、触发器默认不导出存储函数、存储过程、事件 |
-E |
事件 |
-R |
存储过程及函数等定义好的MySQL程序 |
-d |
只导出结构 |
-t |
只导出数据 |
-f |
忽略错误 |
-F |
--flush-logs 导出之前刷新日志文件 |
--dump-slave |
备库端使用,获取备份数据的Binlog位置和Binlog文件名 |
--master-data=(2|1) |
获取备份数据的Binlog位置和Binlog文件名 |
--single-transaction |
锁定但是可以进行写入操作,无法ddl操作,保证innodb引擎表一致性 |
mysqldump -uroot -pmysql --single-transaction --master-data=2 -E -R --all-databases > full.sql
mysqldump -uroot -pmysql --single-transaction --master-data=2 -E -R --databases test [employees ceshi] > test.sql
mysqldump -uroot -pmysql --single-transaction --master-data=2 -E -R test a [b c] > double.sql
注:不指定databases参数,不包含建库语句
mysqldump -uroot -pmysql --single-transaction --master-data=2 test test --where="id<6 and name='a'" > test.sql
mysqldump -uroot -pmysql --no-data --routines --events --databases test [ceshi]> dump-defs.sql
注:可用于测试升级
mysqldump -uroot -pmysql --no-create-info --routines --events --databases test [ceshi]> dump-data.sql
mysqldump --host=h1 -uroot -proot remote_db1 tablename | mysql --host=h2 -uroot -proot db2
注:db2数据库必须存在
|
常用参数 |
含义 |
0 |
--tab |
导出位置 |
1 |
--fields-terminated-by=str |
列与列之间分隔 |
2 |
--fields-enclosed-by=char |
在每一个字段的前后加上char这个字符 |
3 |
--fields-optionally-enclosed-by=char |
在每一个非数字的字段前后加上char这个字符 |
4 |
--fields-escaped-by=char |
使用char去转义特殊字符 |
5 |
--lines-terminated-by=str |
行与行之间分隔 #linux \n windows \r\n |
|
|
注:2与3不能同时使用 |
注:必须指定secure-file-priv 安全目录参数!!!否则报错无法导出
mysqldump --tab=/tmp/12 --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --lines-terminated-by='\n' DBNAME
mysqldump --tab=/tmp/12 --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --lines-terminated-by='\n' DBNAME TABNAME1 [TABNAME2]
注:每张表格生成两个文件
*.sql 属于执行命令用户(root) 包含建表语句
*.txt 属于数据库用户(mysql) 包含数据
mysqldump -uroot -pmysql --single-transaction --master-data=2 --all-databases > full_1.sql
mysqldump -uroot -pmysql --single-transaction --master-data=2 -E -R --all-databases > full.sql
注:--all-database 参数是不包含存储过程,事件等,需要格外指定参数导出。
测试环境:
测试语句:
mysqldump -uroot -pmysql --single-transaction --master-data=2 -E -R --all-databases > full.sql
测试目的:针对INNODB表,开启事务性备份是否会堵塞DDL操作
测试方法:根据general 通用日志,判断表是否已经完成备份。对已备份完毕,正在完成备份,未开始备份表分别做DDL操作,判断是否会堵塞DDL操作
1.开启全库备份
mysqldump -uroot -pmysql --single-transaction --master-data=2 -E -R --all-databases > full.sql
2.观察通用日志,发现已经employees.departments表已经完成备份
3.进行DDL操作,正常进行
4.备份正常完成
1.开启全库备份
mysqldump -uroot -pmysql --single-transaction --master-data=2 -E -R --all-databases > full.sql
2.观察通用日志,发现正在对xh.test表进行备份
3.进行DDL操作,并堵塞
4.查看进程
5.备份正常完成
1.开启全库备份
mysqldump -uroot -pmysql --single-transaction --master-data=2 -E -R --all-databases > full.sql
2.观察通用日志,现在仍在备份employees数据库,还未备份到xh库
3.进行DDL操作,正常进行
4.导出报错
结论:使用 --single-transaction 参数导出数据,DDL操作只会影响正在备份的表,已经完成备份或还未备份的表不会被堵塞。但是对未完成备份的表做完DDL操作,当备份到这张被DDL之后的表,mysqldump进程被中断并报错。
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/30310891/viewspace-2784988/,如需转载,请注明出处,否则将追究法律责任。