mysqldump, Dumping Table Structure and Data
Utility to dump a database or a collection of database for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump will contain SQL statements to create the table and/or populate the table.
用 mysqldump 可以把一個 database 中的資料備份出來或者轉到其它的 database(不一定是 mysql database)中, 用dump指令導出的資料中包含 SQL Command, create/drop table 的指令
If you are doing a backup on the server, you should consider using the mysqlhotcopy instead. See section
如果你要在 Server 上做一個備份的話﹐你可以考慮用 mysqlhotcopy 指令來代替。請看章節
mysqlhotcopy, Copying MySQL Databases and Tables.
mysqlhotcopy 拷貝 MYSQL database 和TABLES.
語法如下﹕
shell> mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
If you don't give any tables or use the --databases or --all-databases, the whole database(s) will be dumped.
如果你沒有給出 table 名稱或用 --databaeses、--all-databases, 所有的資料庫將會被導出來。
You can get a list of the options your version of mysqldump supports by executing mysqldump --help.
你可以用 mysqldump --help 來列出你所使用的 mysqldump 版本的選項清單。
Note that if you run mysqldump without --quick or --opt, mysqldump will load the whole result set into memory before dumping the result. This will probably be a problem if you are dumping a big database.
注意﹕如果你用 mysqldump 指令未加 --quick 或 --opt選項的話﹐mysqldump 會在導出資料之前把所有的資料先放到記憶體中。如果你導出一很大的 databaes時可能有問題。
Note that if you are using a new copy of the mysqldump program and you are going to do a dump that will be read into a very old MySQL server, you should not use the --opt or -e options.
如果你用新的mysqldump程式并且把導出的資料放到一個很舊版本的 MYSQL Server 中的話﹐你不可以用 --opt 或 -e選項。
mysqldump supports the following options:
mysqldump 支持以下選項
--add-locks
Add LOCK TABLES before and UNLOCK TABLE after each table dump. (To get faster inserts into MySQL.)
--add-drop-table 選項會在導入資料之前 LOCK TABLES,資料全部導入之后 UNLOCK TABLE(為了資料能快速新增到 mysql 資料庫中)
--add-drop-table
Add a drop table before each create statement.
--add-drop-table 后會在每一個 create 指令前加 drop table 的指令
-A, --all-databases
Dump all the databases. This will be same as --databases with all databases selected.
-A 或 –all-databases導出所有的資料庫。這和 –databases選項一樣會選擇所有的資料庫中的資料。
-a, --all
Include all MySQL-specific create options.
-a, --all 將包含所有 MySQL 所特有的 create 選項。
--allow-keywords
Allow creation of column names that are keywords. This works by prefixing each column name with the table name.
--allow-keywords 允許欄位名稱使用關鍵字。
-c, --complete-insert
Use complete insert statements (with column names).
-c, --complete-insert 用完整的新增指令(包括欄位名稱)
-C, --compress
Compress all information between the client and the server if both support compression.
-C, --compress 如果客戶端和服務器端都支持壓縮的話﹐導出的所有資訊將會被壓縮。
-B, --databases
To dump several databases. Note the difference in usage. In this case no tables are given. All name arguments are regarded as database names. USE db_name; will be included in the output before each new database.
-B, --databases 為了 dump 多個資料庫。注意這個用法的差異。在這個選項中未給出任何 table 的名稱。所有的參數都視為資料庫的名稱。用資料庫名﹔將會包含在輸出的每一新的資料庫之前。
--delayed
Insert rows with the INSERT DELAYED command.
新增記錄時帶有 INSERT DELAYED 指令
-e, --extended-insert
Use the new multiline INSERT syntax. (Gives more compact and faster inserts statements.)
-e, --extended-insert 用這個新的多行新增的語法。（產生更緊湊快速的新增指令）
-#, --debug[=option_string]
Trace usage of the program (for debugging).
-#, --debug追蹤 mysqldump 的用法(為了debugging)
--help
Display a help message and exit.
--help 顯示幫助信息并且退出。
--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...
These options are used with the -T option and have the same meaning as the corresponding clauses for LOAD DATA INFILE. See section 6.4.9 LOAD DATA INFILE Syntax.
-F, --flush-logs
Flush log file in the MySQL server before starting the dump.
-f, --force,
Continue even if we get a SQL error during a table dump.
-h, --host=..
Dump data from the MySQL server on the named host. The default host is localhost.
-l, --lock-tables.
Lock all tables before starting the dump. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. Please note that when dumping multiple databases, --lock-tables will lock tables for each database separately. So using this option will not guarantee your tables will be logically consistent between databases. Tables in different databases may be dumped in completely different states.