Some usage parameters of mysqldump

Source: Internet
Author: User
Tags mysql client sql error

Backing Up the database
#mysqldump database name > Database backup Name

#mysqldump-A-u user name-p password database name > database backup Name

#mysqldump-D-A--add-drop-table-uroot-p >xxx.sql

1. Export structure does not export data

mysqldump-d database name-uroot-p > Xxx.sql

2. Export data does not export structure

MYSQLDUMP-T database name-uroot-p > Xxx.sql

3. Export Data and table structure

Mysqldump database name-uroot-p > Xxx.sql

4. Export the structure of a specific table

Mysqldump-uroot-p-B database name--table table name > Xxx.sql

#mysqldump [OPTIONS] database [tables]

Mysqldump supports the following options:

Add lock tables before each table is exported and then unlock table. (To make it faster to insert into MySQL).
Add a drop table before each create statement.
Allows you to create column names that are keywords. This is done by prefixing the table name with each column name.
Use the full INSERT statement (with the column name).
If both the client and the server support compression, compress all the information between the two.
Insert a row with the insert delayed command.
Use the new multi-line insert syntax. (Give a more condensed and faster INSERT statement)
The use of the tracker (for debugging purposes).
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.
Before starting the export, wash down the log files in the MySQL server.
Even if we get a SQL error during the export of a table, continue.
Export data from a MySQL server on a named host. The default host is localhost.
Locks all tables for starting export.
Do not write table creation information (CREATE TABLE statement)
No row information is written to the table. This is useful if you only want to export the structure of a table!
Should give you the fastest possible export for reading into a MySQL server.
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.) )
Do not buffer queries, export directly to stdout; use Mysql_use_result () to do it.
A socket file that is used with localhost when it is connected (it is the default host).
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.
Verbose mode. Print out more information that the program has done.
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"

Import data:

Since mysqldump exports a complete SQL statement, it is easy to import the data using the MySQL client program:

#mysql Database name < file name


#show databases;

Then select the database to be imported:

#use * * *;


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.