Mysqldump 参数详解-阿里云开发者社区

开发者社区> 科技小能手> 正文

Mysqldump 参数详解

简介:
+关注继续查看

copy 自http://hi.baidu.com/javava/blog/item/533b6a892de479b20e2444c9.html
使用的时候MySQL当要导入或者导出数据量大的库的时候,用PHPMYADMIN甚至MySQL Administrator这些工具都会力不从心,这时只能使用MySQL所提供的命令行工具mysqldump进行备份恢复。
 

MySQL数据的导出和导入工具:mysqldump,导出要用到MySQL的mysqldump工具,基本用法是:
shell> mysqldump [OPTIONS] database [tables]
如果你不给定任何表,整个数据库将被导出。
通过执行mysqldump --help,你能得到你mysqldump的版本支持的选项表。
注意,如果你运行mysqldump没有--quick--opt选项,mysqldump将在导出结果前装载整个结果集到内存中,如果你正在导出一个大的数据库,这将可能是一个问题。
 
mysqldump支持下列选项:

--add-locks
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。

--add-drop-table
在每个create语句之前增加一个drop table。

--allow-keywords
允许创建是关键词的列名字。这由表名前缀于每个列名做到。

-c, --complete-insert
使用完整的insert语句(用列名字)。

-C, --compress
如果客户和服务器均支持压缩,压缩两者间所有的信息。

--delayed
用INSERT DELAYED命令插入行。

-e, --extended-insert
使用全新多行INSERT语法。(给出更紧缩并且更快的插入语句)

-#, --debug[=option_string]
跟踪程序的使用(为了调试)。

--help
显示一条帮助消息并且退出。

--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--fields-terminated-by=...
 
这些选择与-T选择一起使用,并且有相应的LOAD DATA INFILE子句相同的含义。

LOAD DATA INFILE语法。

-F, --flush-logs
在开始导出前,洗掉在MySQL服务器中的日志文件。

-f, --force,
即使我们在一个表导出期间得到一个SQL错误,继续。

-h, --host=..
从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。

-l, --lock-tables.
为开始导出锁定所有表。

以下是执行 mysqldump --help 命令后打印的帮助信息:
-----------------------------------------------------------------------------------------------------------------------------
mysqldump Ver 10.11 Distrib 5.0.37, for pc-linux-gnu (i686)
By Igor Romanenko, Monty, Jani & Sinisa
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf /usr/local/mysql/etc/my.cnf 
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults    Print the program argument list and exit
--no-defaults        Don't read default options from any options file
--defaults-file=#    Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read
-a, --all           Deprecated. Use --create-options instead.
-A, --all-databases Dump all the databases. This will be same as --databases
                      with all databases selected.
--add-drop-database Add a 'DROP DATABASE' before each create.
--add-drop-table    Add a 'drop table' before each create.
--add-locks         Add locks around insert statements.
--allow-keywords    Allow creation of column names that are keywords.
--character-sets-dir=name 
                      Directory where character sets are.
-i, --comments      Write additional information.
--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.
--compact           Give less verbose output (useful for debugging). Disables
                      structure comments and header/footer constructs. Enables
                      options --skip-add-drop-table --no-set-names
                      --skip-disable-keys --skip-add-locks
-c, --complete-insert 
                      Use complete insert statements.
-C, --compress      Use compression in server/client protocol.
--create-options    Include all MySQL specific create options.
-B, --databases     To dump several databases. Note the difference in usage;
                      In this case no tables are given. All name arguments are
                      regarded as databasenames. 'USE db_name;' will be
                      included in the output.
-#, --debug[=#]     This is a non-debug version. Catch this and exit
--debug-info        Print some debug info at exit.
--default-character-set=name 
                      Set the default character set.
--delayed-insert    Insert rows with INSERT DELAYED; 
--delete-master-logs 
                      Delete logs on master after backup. This automatically
                      enables --master-data.
-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
                      '/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
                      in the output.
-e, --extended-insert 
                      Allows utilization of the new, much faster INSERT syntax.
--fields-terminated-by=name 
                      Fields in the textfile are terminated by ...
--fields-enclosed-by=name 
                      Fields in the importfile are enclosed by ...
--fields-optionally-enclosed-by=name 
                      Fields in the i.file are opt. enclosed by ...
--fields-escaped-by=name 
                      Fields in the i.file are escaped by ...
-x, --first-slave   Deprecated, renamed to --lock-all-tables.
-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. The exception is when
                      using --lock-all-tables or --master-data: in this case
                      the logs will be flushed only once, corresponding to the
                      moment all tables are locked. So if you want your dump
                      and the log flush to happen at the same exact moment you
                      should use --lock-all-tables or --master-data with
                      --flush-logs
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
                      database. This option should be used any time the dump
                      contains the mysql database and any other database that
                      depends on the data in the mysql database for proper
                      restore. 
-f, --force         Continue even if we get an sql-error.
-?, --help          Display this help message and exit.
--hex-blob          Dump binary strings (BINARY, VARBINARY, BLOB) in
                      hexadecimal format.
-h, --host=name     Connect to host.
--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
--insert-ignore     Insert rows with INSERT IGNORE.
--lines-terminated-by=name 
                      Lines in the i.file are terminated by ...
-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.
-l, --lock-tables   Lock all tables for read.
--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 (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.
--max_allowed_packet=# 
--net_buffer_length=# 
--no-autocommit     Wrap tables with autocommit/commit statements.
-n, --no-create-db 'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;' will
                      not be put in the output. The above line will be added
                      otherwise, if --databases or --all-databases option was
                      given.}.
-t, --no-create-info 
                      Don't write table creation info.
-d, --no-data       No row information.
-N, --no-set-names Deprecated. Use --skip-set-charset instead.
--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.
--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.
-p, --password[=name] 
                      Password to use when connecting to server. If password is
                      not given it's solicited on the tty.
-P, --port=#        Port number to use for connection.
--protocol=name     The protocol of connection (tcp,socket,pipe,memory).
-q, --quick         Don't buffer query, dump directly to stdout.
-Q, --quote-names   Quote table and column names with backticks (`).
-r, --result-file=name 
                      Direct output to a given file. This option should be used
                      in MSDOS, because it prevents new line '\n' from being
                      converted to '\r\n' (carriage return + line feed).
-R, --routines      Dump stored routines (functions and procedures).
--set-charset       Add 'SET NAMES default_character_set' to the output.
                      Enabled by default; suppress with --skip-set-charset.
-O, --set-variable=name 
                      Change the value of a variable. Please note that this
                      option is deprecated; you can set variables directly with
                      --variable-name=value.
--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. Option
                      automatically turns off --lock-tables.
--skip-opt          Disable --opt. Disables --add-drop-table, --add-locks,
                      --create-options, --quick, --extended-insert,
                      --lock-tables, --set-charset, and --disable-keys.
-S, --socket=name   Socket file to use for connection.
--ssl               Enable SSL for connection (automatically enabled with
                      other flags). Disable with --skip-ssl.
--ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies
                      --ssl).
--ssl-capath=name   CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name     X509 cert in PEM format (implies --ssl).
--ssl-cipher=name   SSL cipher to use (implies --ssl).
--ssl-key=name      X509 key in PEM format (implies --ssl).
--ssl-verify-server-cert 
                      Verify server's "Common Name" in its cert against
                      hostname used when connecting. This option is disabled by
                      default.
-T, --tab=name      Creates tab separated textfile for each table to given
                      path. (creates .sql and .txt files). NOTE: This only
                      works if mysqldump is run on the same machine as the
                      mysqld daemon.
--tables            Overrides option --databases (-B).
--triggers          Dump triggers for each dumped table
--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.
-u, --user=name     User for login if not current user.
-v, --verbose       Print info about the various stages.
-V, --version       Output version information and exit.
-w, --where=name    Dump only selected records; QUOTES mandatory!
-X, --xml           Dump a database as well formed XML.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
all                               TRUE
all-databases                     FALSE
add-drop-database                 FALSE
add-drop-table                    TRUE
add-locks                         TRUE
allow-keywords                    FALSE
character-sets-dir                (No default value)
comments                          TRUE
compatible                        (No default value)
compact                           FALSE
complete-insert                   FALSE
compress                          FALSE
create-options                    TRUE
databases                         FALSE
debug-info                        FALSE
default-character-set             gbk
delayed-insert                    FALSE
delete-master-logs                FALSE
disable-keys                      TRUE
extended-insert                   TRUE
fields-terminated-by              (No default value)
fields-enclosed-by                (No default value)
fields-optionally-enclosed-by     (No default value)
fields-escaped-by                 (No default value)
first-slave                       FALSE
flush-logs                        FALSE
flush-privileges                  FALSE
force                             FALSE
hex-blob                          FALSE
host                              (No default value)
insert-ignore                     FALSE
lines-terminated-by               (No default value)
lock-all-tables                   FALSE
lock-tables                       TRUE
master-data                       0
max_allowed_packet                16776192
net_buffer_length                 1047551
no-autocommit                     FALSE
no-create-db                      FALSE
no-create-info                    FALSE
no-data                           FALSE
order-by-primary                  FALSE
port                              3306
quick                             TRUE
quote-names                       TRUE
routines                          FALSE
set-charset                       TRUE
single-transaction                FALSE
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-key                           (No default value)
ssl-verify-server-cert            FALSE
tab                               (No default value)
triggers                          TRUE
tz-utc                            TRUE
user                              (No default value)
verbose                           FALSE
where                             (No default value)



本文转自holy2009 51CTO博客,原文链接:http://blog.51cto.com/holy2010/530665

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
10096 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,大概有三种登录方式:
2963 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
11640 0
使用SSH远程登录阿里云ECS服务器
远程连接服务器以及配置环境
2512 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13894 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
7504 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
7365 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
4510 0
23706
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载