Usage of mysqldump in MySQL _ MySQL

This article mainly introduces the usage of MySQL mysqldump and the usage of related Source commands. for details about how to export MySQL mysqldump, refer to the following:

The code is as follows:

Shell> mysqldump [OPTIONS] database [tables]

If you do not specify any tables, the entire database will be exported.
Run mysqldump -- help to obtain the option table supported by your mysqldump version.
Note: If you run mysqldump without the -- quick or -- opt option, mysqldump loads the entire result set to the memory before the export result. if you are exporting a large database, this may be a problem.
Mysqldump supports the following options:

The code is as follows:

-- Add-locks

Add lock tables and unlock table before each TABLE is exported. (To make it faster to insert data to MySQL ).

The code is as follows:

-- Add-drop-table

Add a drop table before each create statement.

The code is as follows:

-- Allow-keywords

Names of columns allowed to be created as keywords. This is done by the table name prefix on each column name.

The code is as follows:

-C, -- complete-insert

Use the complete insert statement (with the column name ).

The code is as follows:

-C, -- compress

If both the client and server support compression, all information is compressed between the two.

The code is as follows:

-- Delayed

Use the insert delayed command to INSERT rows.

The code is as follows:

-E, -- extended-insert

Use the new multiline INSERT syntax. (A more compact and faster insert statement is provided)

The code is as follows:

-#, -- Debug [= option_string]

Tracking program usage (for debugging ).

The code is as follows:

-- Help

Displays a help message and exits.

The code is as follows:

-- Fields-terminated-by =...
　
-- Fields-enclosed-by =...
　
-- Fields-optionally-enclosed-by =...
　
-- Fields-escaped-by =...
　
-- Fields-terminated-by =...

These options are used with-T options and have the same meaning as the load data infile clause.
Load data infile syntax.

The code is as follows:

-F, -- flush-logs

Wash out the log files on the MySQL server before starting the export.

The code is as follows:

-F, -- force,

Even if we get an SQL error while exporting a table, continue.

The code is as follows:

-H, -- host = ..

Export data from the MySQL server on the named host. The default host is localhost.

The code is as follows:

-L, -- lock-tables.

Lock All tables for start export.

The code is as follows:

-T, -- no-create-info

Create table statement)

The code is as follows:

-D, -- no-data

No row information is written to the table. If you only want to export the structure of a table, this is very useful!

The code is as follows:

-- Opt

Same

The code is as follows:

-- Quick -- add-drop-table -- add-locks -- extended-insert -- lock-tables

.
You should be given the fastest export possible for reading a MySQL server.

The code is as follows:

-Pyour_pass, -- password [= your_pass]

The password used to connect to the server. If you do not specify "= your_pass", mysqldump requires a password from the terminal.

The code is as follows:

-P port_num, -- port = port_num

The TCP/IP port used to connect to a host. (This is used to connect to a host other than localhost because it uses Unix sockets .)

The code is as follows:

-Q, -- quick

Directly export the data to stdout without buffering the query; use mysql_use_result () to do it.

The code is as follows:

-S/path/to/socket, -- socket =/path/to/socket

The socket file used when connecting to localhost (which is the default host.

The code is as follows:

-T, -- tab = path-to-some-directory

CREATE a table_name. SQL file for each given table, which contains the SQL CREATE command and a table_name.txt file, which contains data. Note: This only works when mysqldump runs on the same machine where the mysqld daemon is running .. The format of the txt file is determined by the options -- fields-xxx and -- lines -- xxx.

The code is as follows:

-U user_name, -- user = user_name

The username used by MySQL to connect to the server. The default value is your Unix login name.

The code is as follows:

-O var = option, -- set-variable var = option

Set the value of a variable. Possible variables are listed below.

The code is as follows:

-V, -- verbose

Lengthy mode. Print out more information about the program.

The code is as follows:

-V, -- version

Print the version information and exit.

The code is as follows:

-W, -- where = 'Where-condition'

Only selected records are exported. Note that the quotation marks are mandatory!

The code is as follows:

"-- Where = user = 'jimf '"-wuserid> 1 ""-wuserid <1"

The most common use of mysqldump may make a backup of the entire database:

The code is as follows:

Mysqldump -- opt database> backup-file. SQL

However, it is also useful for enriching another MySQL database with information from one database:

The code is as follows:

Mysqldump -- opt database | mysql -- host = remote-host-C database

Mysqldump exports complete SQL statements, so it is easy to import data into mysql client programs:

The code is as follows:

Shell> mysqladmin create target_db_name
Shell> mysql target_db_name <backup-file. SQL

Yes

The code is as follows:

Shell> mysql database name <file name

Several common use cases:
1. export the entire database

The code is as follows:

Mysqldump-u username-p database name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. SQL

2. export a table

The code is as follows:

Mysqldump-u user name-p database name table name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc users> wcnc_users. SQL

3. export a database structure

The code is as follows:

Mysqldump-u wcnc-p-d -- add-drop-table smgp_apps_wcnc> d: wcnc_db. SQL
-D no data -- add-drop-table adds a drop table before each create statement.

4. import the database

Common source commands
Go to the mysql database console,
For example

The code is as follows:

Mysql-u root-p

Mysql> use database
Then run the source Command. the following parameter is the script File (for example,. SQL used here)

The code is as follows:

Mysql> source d: wcnc_db. SQL

Mysql uses the source command to import database encoding problems
Mysql> use database name (same as your website database name)

The code is as follows:

Set names utf8;

(First confirm the encoding note not UTF-8)

The code is as follows:

Source D: 123. SQL

(Name of the database to be imported)