The basic usage of MySQL's mysqldump tool is to export the mysqldump tool for MySQL, the basic usage is: shell> mysqldump [OPTIONS] database [tables] If you don't give any tables, The entire database will be exported. By executing mysqldump--help, you can get a list of options supported by your mysqldump version. Note that if you run mysqldump without the--quick or--opt option, mysqldump will load the entire result set into memory before exporting the results, which would probably be a problem if you are exporting a large database. Mysqldump supports the following options:--add-locks
Add lock tables before each table is exported and then unlock table. (To make it faster to insert into MySQL).
--add-drop-table
Add a drop table before each create statement.
--allow-keywords
Allows you to create column names that are keywords. This is done by prefixing the table name with each column name.
-C,--complete-insert
Use the full INSERT statement (with the column name).
-C,--compress
If both the client and the server support compression, compress all the information between the two.
--delayed
Insert a row with the insert delayed command.
-E,--extended-insert
Use the new multi-line insert syntax. (Give a more condensed and faster INSERT statement)
-#,--debug[=option_string]
The use of the tracker (for debugging purposes).
--help
Displays a help message and exits.
--fields-terminated-by= ...
--fields-enclosed-by= ...
--fields-optionally-enclosed-by= ...
--fields-escaped-by= ...
--fields-terminated-by= ...
These options are used with the-T selection and have the same meaning as the corresponding load DATA infile clause.
LOAD DATA infile syntax.
-F,--flush-logs
Before starting the export, wash down the log files in the MySQL server.
-F,--force,
Even if we get a SQL error during the export of a table, continue.
-H,--host=.
Export data from a MySQL server on a named host. The default host is localhost.
-L,--lock-tables.
Locks all tables for starting export.
-T,--no-create-info
Do not write table creation information (CREATE TABLE statement)
-D,--no-data
No row information is written to the table. This is useful if you only want to export the structure of a table!
--opt
With--quick--add-drop-table--add-locks--extended-insert--lock-tables.
Should give you the fastest possible export for reading into a MySQL server.
-pyour_pass,--password[=your_pass]
The password to use when connecting to the server. If you do not specify the "=your_pass" section, Mysqldump requires a password from the terminal.
-P Port_num,--port=port_num
The TCP/IP port number to use when connecting to a single host. (This is used to connect to a host other than localhost because it uses a UNIX socket.) ）
-Q,--quick
Do not buffer queries, export directly to stdout; use Mysql_use_result () to do it.
-s/path/to/socket,--socket=/path/to/socket
A socket file that is used with localhost when it is connected (it is the default host).
-T,--tab=path-to-some-directory
For each given table, create a Table_name.sql file that contains the SQL create command, and a table_name.txt file that contains the data. Note: This only works when the mysqldump is running on the same machine as the mysqld daemon. The format of the. txt file is determined by the--fields-xxx and--lines--xxx options.
-U user_name,--user=user_name
The user name that MySQL uses when connecting to the server. The default value is your UNIX login name.
-O var=option,--set-variable var=option Sets the value of a variable. The possible variables are listed below.
-V,--verbose
Verbose mode. Print out more information that the program has done.
-V,--version
Print the version information and exit.
-W,--where= ' where-condition '
Only the selected records are exported; Note that the quotation marks are mandatory!
"--where=user= ' JIMF '" "-wuserid>1" "-wuserid<1" the most common mysqldump use may make a backup of the entire database: mysqldump--opt databases > Backup-file.sql But it is also useful to enrich another MySQL database with information from one database: Mysqldump--opt | MySQL--host=remote-host-c database because mysqldump exports the full SQL statement, it is easy to use the MySQL client program to import the data: shell> mysqladmin Create Targ Et_db_name
shell> MySQL Target_db_name < backup-file.sql
It is
shell> MySQL Library name < file name ================================
Several common use cases: 1. Exporting the entire database
Mysqldump-u user name-p database name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql
2. Export a table
Mysqldump-u user name-P database name Table name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC users> wcnc_users.sql
3. Export a database structure
Mysqldump-u Wcnc-p-D--add-drop-table smgp_apps_wcnc >d:\wcnc_db.sql-d No data--add-drop-table add a drop before each create statement Table 4. Importing a database
Common source Commands
Go to MySQL Database console,
such as Mysql-u root-p
The Mysql>use database then uses the source command, followed by the script file (for example, the. SQL used here)
Mysql>source D:\wcnc_db.sql
Posted on 2007-08-10 09:21 Alpha Read (140863) Comments (11) Edit Favorites Category: MySQL NoSQL
Comments:# Basic usage of Re:mysql's mysqldump tool [not logged in] 2011-03-07 14:56 | Aa
MySQL uses the source command to import database encoding issues
Mysql>use database name (same as your site database name)
Set names UTF8; (First confirm the coding note is not UTF-8)
SOURCE D:\123.sql (the name of the database to import)
Reply to more comments
# Basic usage of Re:mysql's mysqldump tool [not logged in] 2012-03-21 14:22 | Aaa
Write mysqldump commands based on parameter values, such as:
E:\eis>mysqldump-uroot-p eis_db goodclassification-e--max_allowed_packet=1048576--net_buffer_length=16384 > Good3.sql
SQL that was imported 2 hours ago can now be completed in a few 10 seconds. Reply to more comments
# Basic usage of Re:mysql's mysqldump tool [not logged in] 2012-05-29 10:25 | Lenno
Oh, my God!!! What is the writer, the programmer!!! The idea is too not rigorous!!! Reply to more comments
# Basic usage of Re:mysql's mysqldump tool 2012-07-02 17:16 | Ling
Mysqldump-u user name-p database name > exported file name does not take effect reply more comments
# Basic usage of Re:mysql's mysqldump tool 2012-08-27 20:29 | Tiny
Take a look at useful replies more comments
# Basic usage of Re:mysql's mysqldump tool 2013-01-18 14:01 | Lingcarzy
@ Ling
This command is possible under the Linux host, not under win. Reply to more comments
# Basic usage of Re:mysql's mysqldump tool [not logged in] 2013-01-29 16:39 | Ddd
@ Ling
Mysqldump-uusername-ppassword Dataname>name.sql Reply to more comments
