MySQL mysqldump

mysqldump是mysql自带的一个基本的逻辑备份工具,用于转存储数据库结构和数据。它主要产生一个SQL脚本,其中包含创建数据库所必需的命令CREATE TABLE INSERT等命令。下面总结一下mysqldump的参数,方便日后查询使用。
mysqldump –help
mysqldump Ver 10.13 Distrib 5.6.19-67.0, for Linux (x86_64)
……
# 通过usage可以看出,可以逻辑导出某个数据库、数据库中的表或者全部数据库。一般后面加重定向将逻辑语句写入文件。
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…] #或-B
OR mysqldump [OPTIONS] –all-databases [OPTIONS] #或-A
# 模式和错误输出

-q, –quick Don’t buffer query, dump directly to stdout. (Defaults to on; use –skip-quick to disable.) 如果你运行mysqldump没有–quick或–opt选项,mysqldump将在导出结果前装载整个结果集到内存中,如果你正在导出一个大的数据库,这将可能是一个问题。
-f, –force Continue even if we get an SQL error.
–ignore-create-error
Don’t exit on show create table errors.
–log-error=name Append warnings and errors to given file.
# 格式和详细程度
–compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options –skip-add-drop-table –skip-add-locks
–skip-comments –skip-disable-keys –skip-set-charset.
-v, –verbose Print info about the various stages.
-X, –xml Dump a database as well formed XML.
-T, –tab=name Create tab-separated textfile for each table to given
path. (Create .sql and .txt files.) NOTE: This only works
if mysqldump is run on the same machine as the mysqld
server.
-r, –result-file=name
Direct output to a given file. This option should be used
in systems (e.g., DOS, Windows) that use carriage-return
linefeed pairs (\r\n) to separate text lines. This option
ensures that only a single newline is used.
# 连接,除了常用的-u&-p, -h&-P, -S之外,还可以使用SSL(略)或者压缩传输
-C, –compress Use compression in server/client protocol.
### 一些常用的功能如下:
# – 是否先删除数据库/表/触发器(重建)
–add-drop-database Add a DROP DATABASE before each create.
–add-drop-table Add a DROP TABLE before each create.
(Defaults to on; use –skip-add-drop-table to disable.)
–add-drop-trigger Add a DROP TRIGGER before each create.
# – 逻辑导出时的锁定
-x, –lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns –single-transaction and
–lock-tables off.
# 在开始导出之前,提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭 –single-transaction 和 –lock-tables 选项。
-l, –lock-tables Lock all tables for read.
(Defaults to on; use –skip-lock-tables to disable.)
# 和 –lock-all-tables 类似,不过是锁定当前导出的数据表,而不是一下子锁定全部库下的表。
–lock-for-backup Use lightweight metadata locks to block updates to
non-transactional tables and DDL to all tables. This
works only with –single-transaction, otherwise this
option is automatically converted to –lock-all-tables. disabled by default
# 详见Backup Locks
When used together with the –single-transaction option, the option makes mysqldump issue LOCK TABLES FOR BACKUP before starting the dump operation to prevent unsafe statements that would normally result in an inconsistent backup.
When used without the single-transaction option, lock-for-backup is automatically converted to lock-all-tables.
Option lock-for-backup is mutually exclusive with lock-all-tables, i.e. specifying both on the command line will lead to an error.
–single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
–single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off –lock-tables.
# 该选项在导出数据之前提交一个 BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。
# 只适用于事务存储引擎(InnoDB)。本选项和 –lock-tables 选项是互斥的(启动该选项会自动关闭-l选项),因为 LOCK TABLES 会使所有挂起的事务隐含提交。
–add-locks Add locks around INSERT statements.
(Defaults to on; use –skip-add-locks to disable.)
# 在生成的逻辑备份语句中,insert语句之前增加LOCK TABLES并在之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。
–delayed-insert Insert rows with INSERT DELAYED.
–insert-ignore Insert rows with INSERT IGNORE.
# 当一个客户端使用INSERT DELAYED 时,会立刻从服务器处得到一个确定。并且行被排入队列,当表没有被其它线程使用时,此行被插入。
# 使用INSERT DELAYED 的另一个重要的好处是,来自许多客户端的插入被集中在一起,并被编写入一个块。这比执行许多独立的插入要快很多。
# 好处是提高插入的速度,客户端不需要等待太长时间。坏处则是不能返回自动递增的ID,以及系统崩溃时,MySQL还没有来得及插入数据的话,这些数据将会丢失。
# insert ignore会忽略已经存在的数据。如果数据不存在就插入数据。
### 导出的内容
–default-character-set=name
Set the default character set.
–hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.
-R, –routines Dump stored routines (functions and procedures).
-d, –no-data No row information.
-E, –events Dump events.
-c, –complete-insert
Use complete insert statements.
# with -c :INSERT INTO test (x) VALUES (1); –显示完整列名。
# without -c :INSERT INTO test VALUES (1);
-e, –extended-insert
Use multiple-row INSERT syntax that include several
VALUES lists.
(Defaults to on; use –skip-extended-insert to disable.)
# 允许一条insert语句中包含多条values(即多行数据),例如 insert into tab values (1, ‘hello’), (2, ‘world’);等。
–replace Use REPLACE INTO instead of INSERT INTO.
# REPLACE INTO = if not exists then insert into … else update…
–set-charset Add ‘SET NAMES default_character_set’ to the output.
(Defaults to on; use –skip-set-charset to disable.)
# 输出字符集的信息
-a, –create-options
Include all MySQL specific create options.
(Defaults to on; use –skip-create-options to disable.)
# 显示完整的建表语句
–ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.,
–ignore-table=database.table.
-K, –disable-keys ‘/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
‘/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
in the output.
(Defaults to on; use –skip-disable-keys to disable.)
# 禁用索引插入后再重建会提高数据批量导入的速度,详见叶大的这篇:[MySQL优化案例]系列 — DISABLE/ENABLE KEYS的作用
-w, –where=name Dump only selected records. Quotes are mandatory.
# 只导出被选择了的记录;注意引号是强制的!
# –where “tag=’88′”
–allow-keywords Allow creation of column names that are keywords.
-n, –no-create-db Suppress the CREATE DATABASE … IF EXISTS statement that
normally is output for each dumped database if
–all-databases or –databases is given.
-t, –no-create-info
Don’t write table creation info.
–tables Overrides option –databases (-B).
–triggers Dump triggers for each dumped table.
(Defaults to on; use –skip-triggers to disable.)
–opt Same as –add-drop-table, –add-locks, –create-options,
–quick, –extended-insert, –lock-tables, –set-charset,
and –disable-keys. Enabled by default, disable with
–skip-opt.
# opt是为了更快的导出
# SQL dump的兼容性
–compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.
# 导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、
# postgresql、Oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等,
# 要使用几个值,用逗号将它们隔开。它并不保证能完全兼容,而是尽量兼容。

# 输出文本的符号标记
–fields-terminated-by=name
Fields in the output file are terminated by the given
string.
–fields-enclosed-by=name
Fields in the output file are enclosed by the given
character.
–fields-optionally-enclosed-by=name
Fields in the output file are optionally enclosed by the
given character.
–fields-escaped-by=name
Fields in the output file are escaped by the given
character.
–lines-terminated-by=name
Lines in the output file are terminated by the given
string.
-Q, –quote-names Quote table and column names with backticks ().
(Defaults to on; use --skip-quote-names to disable.)
# 以反引号标识表和列名
# 主从
--dump-slave[=#] This causes the binary log position and filename of the
master to be appended to the dumped data output. Setting
the value to 1, will print it as a CHANGE MASTER command
in the dumped data output; if equal to 2, that command
will be prefixed with a comment symbol. This option will
turn --lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump - don't
forget to read about --single-transaction below). In all
cases any action on logs will happen at the exact moment
of the dump.Option automatically turns --lock-tables off.
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (on servers that don't provide
Binlog_snapshot_file and Binlog_snapshot_position status
variables this will still take a global read lock for a
short time at the beginning of the dump; don't forget to
read about --single-transaction below). In all cases, any
action on logs will happen at the exact moment of the
dump. Option automatically turns --lock-tables off.
# master-data和dump-slave都用于dump数据,创建slave,添加必要的change master命令。
# 前者是在master端,后者是在slave端(创建同级slave)。而二者都有1和2选项,2表示以注释的形式添加:
# CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin2.000001', MASTER_LOG_POS=259; #为1时
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin2.000001', MASTER_LOG_POS=259; #为2时
--apply-slave-statements
Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START
SLAVE' to bottom of dump.
# 在'CHANGE MASTER'前添加'STOP SLAVE',并且在导出的最后添加'START SLAVE'。
--delete-master-logs
Delete logs on master after backup. This automatically
enables --master-data.
--include-master-host-port
Adds 'MASTER_HOST=, MASTER_PORT= ' to 'CHANGE
MASTER TO..' in dump produced with --dump-slave.
# 其他行为
--no-autocommit Wrap tables with autocommit/commit statements.
--innodb-optimize-keys
Use InnoDB fast index creation by creating secondary
indexes after dumping the data.
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped.
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database.
--order-by-primary Sorts each table's rows by primary key, or first unique
key, if such a key exists. Useful when dumping a MyISAM
table to be loaded into an InnoDB table, but will make
the dump itself take considerably longer.
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
(Defaults to on; use --skip-tz-utc to disable.)

参考:
Manual
http://blog.chinaunix.net/uid-27038861-id-3591736.html MYSQLdump参数详解

mysqldump是mysql自带的一个基本的逻辑备份工具,用于转存储数据库结构和数据。它主要产生一个SQL脚本,其中包含创建数据库所必需的命令CREATE TABLE INSERT等命令。下面总结一下mysqldump的参数,方便日后查询使用。
mysqldump --help
mysqldump Ver 10.13 Distrib 5.6.19-67.0, for Linux (x86_64)
......

# Usage
# 通过usage可以看出,可以逻辑导出某个数据库、数据库中的表或者全部数据库。一般后面加重定向将逻辑语句写入文件。
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] #或-B
OR mysqldump [OPTIONS] --all-databases [OPTIONS] #或-A

# 模式和错误输出
-q, --quick Don't buffer query, dump directly to stdout. (Defaults to on; use --skip-quick to disable.) 如果你运行mysqldump没有--quick或--opt选项,mysqldump将在导出结果前装载整个结果集到内存中,如果你正在导出一个大的数据库,这将可能是一个问题。
-f, --force Continue even if we get an SQL error.
--ignore-create-error Don't exit on show create table errors.
--log-error=name Append warnings and errors to given file.

# 格式和详细程度
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --skip-add-locks
--skip-comments --skip-disable-keys --skip-set-charset.
-v, --verbose Print info about the various stages.
-X, --xml Dump a database as well formed XML.
-T, --tab=name Create tab-separated textfile for each table to given
path. (Create .sql and .txt files.) NOTE: This only works
if mysqldump is run on the same machine as the mysqld
server.
-r, --result-file=name
Direct output to a given file. This option should be used
in systems (e.g., DOS, Windows) that use carriage-return
linefeed pairs (\r\n) to separate text lines. This option
ensures that only a single newline is used.
# 连接,除了常用的-u&-p, -h&-P, -S之外,还可以使用SSL(略)或者压缩传输
-C, --compress Use compression in server/client protocol.

# - 是否先删除数据库/表/触发器(重建)
--add-drop-database Add a DROP DATABASE before each create.
--add-drop-table Add a DROP TABLE before each create.
(Defaults to on; use --skip-add-drop-table to disable.)
--add-drop-trigger Add a DROP TRIGGER before each create.

# - 逻辑导出时的锁定
-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.
# 在开始导出之前,提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭 --single-transaction 和 --lock-tables 选项。
-l, --lock-tables Lock all tables for read.
(Defaults to on; use --skip-lock-tables to disable.)
# 和 --lock-all-tables 类似,不过是锁定当前导出的数据表,而不是一下子锁定全部库下的表。
--lock-for-backup Use lightweight metadata locks to block updates to
non-transactional tables and DDL to all tables. This
works only with --single-transaction, otherwise this
option is automatically converted to --lock-all-tables. disabled by default
# 详见Backup Locks
When used together with the --single-transaction option, the option makes mysqldump issue LOCK TABLES FOR BACKUP before starting the dump operation to prevent unsafe statements that would normally result in an inconsistent backup.
When used without the single-transaction option, lock-for-backup is automatically converted to lock-all-tables.
Option lock-for-backup is mutually exclusive with lock-all-tables, i.e. specifying both on the command line will lead to an error.
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
# 该选项在导出数据之前提交一个 BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。
# 只适用于事务存储引擎(InnoDB)。本选项和 --lock-tables 选项是互斥的(启动该选项会自动关闭-l选项),
# 因为 LOCK TABLES 会使所有挂起的事务隐含提交。
--add-locks Add locks around INSERT statements.
(Defaults to on; use --skip-add-locks to disable.)
# 在生成的逻辑备份语句中,insert语句之前增加LOCK TABLES并在之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。
--delayed-insert Insert rows with INSERT DELAYED.
--insert-ignore Insert rows with INSERT IGNORE.
# 当一个客户端使用INSERT DELAYED 时,会立刻从服务器处得到一个确定。并且行被排入队列,当表没有被其它线程使用时,此行被插入。
# 使用INSERT DELAYED 的另一个重要的好处是,来自许多客户端的插入被集中在一起,并被编写入一个块。这比执行许多独立的插入要快很多。
# 好处是提高插入的速度,客户端不需要等待太长时间。坏处则是不能返回自动递增的ID,以及系统崩溃时,MySQL还没有来得及插入数据的话,这些数据将会丢失。
# insert ignore会忽略已经存在的数据。如果数据不存在就插入数据。

### 导出的内容
--default-character-set=name Set the default character set.
--hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.
-R, --routines Dump stored routines (functions and procedures).
-d, --no-data No row information.
-E, --events Dump events.
-c, --complete-insert Use complete insert statements.
# with -c :INSERT INTO
test (x) VALUES (1); --显示完整列名。
# without -c :INSERT INTO
test VALUES (1);
-e, --extended-insert Use multiple-row INSERT syntax that include several VALUES lists.
(Defaults to on; use --skip-extended-insert to disable.)
# 允许一条insert语句中包含多条values(即多行数据),例如 insert into tab values (1, 'hello'), (2, 'world');等。
--replace Use REPLACE INTO instead of INSERT INTO.
# REPLACE INTO = if not exists then insert into ... else update...
--set-charset Add 'SET NAMES default_character_set' to the output.
(Defaults to on; use --skip-set-charset to disable.)
# 输出字符集的信息
-a, --create-options
Include all MySQL specific create options.
(Defaults to on; use --skip-create-options to disable.)
# 显示完整的建表语句
--ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.,
--ignore-table=database.table.
-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
in the output.
(Defaults to on; use --skip-disable-keys to disable.)
# 禁用索引插入后再重建会提高数据批量导入的速度,详见叶大的[MySQL优化案例]系列 -- DISABLE/ENABLE KEYS的作用
-w, --where=name Dump only selected records. Quotes are mandatory.
# 只导出被选择了的记录;注意引号是强制的!例如--where "tag='88'"
--allow-keywords Allow creation of column names that are keywords.
-n, --no-create-db Suppress the CREATE DATABASE ... IF EXISTS statement that
normally is output for each dumped database if
--all-databases or --databases is given.
-t, --no-create-info
Don't write table creation info.
--tables Overrides option --databases (-B).
--triggers Dump triggers for each dumped table.
(Defaults to on; use --skip-triggers to disable.)
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
# opt是为了更快的导出

# SQL dump的兼容性
--compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.
# 导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、
# postgresql、Oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等,
# 要使用几个值,用逗号将它们隔开。它并不保证能完全兼容,而是尽量兼容。

# 输出文本的符号标记
--fields-terminated-by=name
Fields in the output file are terminated by the given
string.
--fields-enclosed-by=name
Fields in the output file are enclosed by the given
character.
--fields-optionally-enclosed-by=name
Fields in the output file are optionally enclosed by the
given character.
--fields-escaped-by=name
Fields in the output file are escaped by the given
character.
--lines-terminated-by=name
Lines in the output file are terminated by the given
string.
-Q, --quote-names Quote table and column names with backticks (
).
(Defaults to on; use –skip-quote-names to disable.)
# 以反引号标识表和列名

# 主从
--dump-slave[=#]
This causes the binary log position and filename of the master to be appended to the dumped data output. Setting the value to 1, will print it as a CHANGE MASTER command in the dumped data output; if equal to 2, that command will be prefixed with a comment symbol. This option will turn –lock-all-tables on, unless –single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump – don’t forget to read about –single-transaction below). In all cases any action on logs will happen at the exact moment of the dump.Option automatically turns –lock-tables off.
--master-data[=#]
This causes the binary log position and filename to be appended to the output. If equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will be prefixed with a comment symbol. This option will turn –lock-all-tables on, unless –single-transaction is specified too (on servers that don’t provide Binlog_snapshot_file and Binlog_snapshot_position status variables this will still take a global read lock for a short time at the beginning of the dump; don’t forget to read about –single-transaction below). In all cases, any action on logs will happen at the exact moment of the dump. Option automatically turns –lock-tables off.
# master-data和dump-slave都用于dump数据,创建slave,添加必要的change master命令。
# 前者是在master端,后者是在slave端(创建同级slave)。而二者都有1和2选项,2表示以注释的形式添加:
# CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin2.000001′, MASTER_LOG_POS=259; #为1时
# — CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin2.000001′, MASTER_LOG_POS=259; #为2时
--apply-slave-statements
Adds ‘STOP SLAVE’ prior to ‘CHANGE MASTER’ and ‘START
SLAVE’ to bottom of dump.
# 在’CHANGE MASTER’前添加’STOP SLAVE’,并且在导出的最后添加’START SLAVE’。
--delete-master-logs
Delete logs on master after backup. This automatically
enables –master-data.
--include-master-host-port
Adds ‘MASTER_HOST=, MASTER_PORT= ‘ to ‘CHANGE
MASTER TO..’ in dump produced with –dump-slave.

# 其他行为
--no-autocommit Wrap tables with autocommit/commit statements.
--innodb-optimize-keys
Use InnoDB fast index creation by creating secondary
indexes after dumping the data.
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
–databases= or –all-databases), the logs will be
flushed for each database dumped.
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database.
--order-by-primary Sorts each table’s rows by primary key, or first unique
key, if such a key exists. Useful when dumping a MyISAM
table to be loaded into an InnoDB table, but will make
the dump itself take considerably longer.
--tz-utc SET TIME_ZONE=’+00:00′ at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
(Defaults to on; use –skip-tz-utc to disable.)
参考:
Manual
http://blog.chinaunix.net/uid-27038861-id-3591736.html MYSQLdump参数详解

Posted in Database, MySQL.
  1. 还是那句话,如果外媒有你嘴里说得那么靠谱,那这个提问者说中国人的不自由说得言之凿凿,却又提到中国14亿人吃不饱,这是什么媒体灌输给他的?这不就直接打脸了嘛!