MySQL Database backup--mysqldump usage

To export the Mysqldump tool to use with MySQL, the basic usage is:

Shell>[OPTIONS]database[tables]  

If you do not 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 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.

- --

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)

- -- 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.

- -- flush-logs  

Before starting the export, wash down the log files in the MySQL server.

- -- Force ,  

Even if we get a SQL error during the export of a table, continue.

- -- host=   .

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

- --

Locks all tables for starting export.

- -- No-create-info  

Do not write table creation information (CREATE TABLE statement)

- -- 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.

- -- 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.

- -- 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.) ）

- -- Quick  

Do not buffer queries, export directly to stdout; use Mysql_use_result () to do it.

- /Path/to/--socket=/path/to/socket  

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.

- user_name -- 

The user name that MySQL uses when connecting to the server. The default value is your UNIX login name.

- var = option -- set-variable var=option

Sets the value of a variable. The possible variables are listed below.

- -- verbose

Verbose mode. Print out more information that the program has done.

- -- version

Print the version information and exit.

- -- where= ' where-condition '  

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

"--

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

-- opt database > Backup-file.sql  

But it is also useful to enrich another MySQL database with information from one database:

--

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

Shell>Create  target_db_name   shell><Backup  -file

It is

Shell>< file name

Several common use cases:

1. Export the entire database

- - > the exported file     name - - > wcnc.sql

2. Export a table

- -P database Name table name >--p SMGP_APPS_WCNC users> wcnc_users.sql

3. Export a database structure

- - - -- add-drop-table SMGP_APPS_WCNC >d:wcnc_db.sql - -- add-drop-table add a drop table before each create statement

4. Import the database

Common Source Commands Enter the MySQL database console, as

- -P

MySQL> Use database

Then use the source command, followed by the script file (for example, the. SQL used here)

MySQL>source D:wcnc_db.sql

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)

Instance:

1, Export tablename table in accordance with field>xxx data for the condition

- -u username  ---w "field>>export.sql

2. Import data

SOURCE Export.sql

MySQL Database backup--mysqldump usage