Detailed explanation of mysqldump

Http://blog.chinaunix.net/u/29134/showart_287899.html

Note: If you use mysqldump, you must have at least select and lock tables permissions. Database or table.

If you want to use the -- tab switch, you must have the file permission.

Preparations:

Databases and tables:

Table CREATE TABLE

-----------------------------------------------

Pwss create table 'pwss '(

'Id' int (11) not null auto_increment,

'Username' varchar (64) default null,

'Description' mediumtext,

Primary Key ('id ')

) Engine = InnoDB default charset = utf8

Mysql> grant usage on *. * To 'dump _ user' @ 'localhost' identified by 'dump _ user ';

Query OK, 0 rows affected (0.00 Sec)

(This statement can be omitted because it has the usage permission by default .)

Mysql> grant lock tables on *. * To 'dump _ user' @ 'localhost ';

Query OK, 0 rows affected (0.00 Sec)

Mysql> flush privileges;

Query OK, 0 rows affected (0.01 Sec)

Mysql> show grants for 'dump _ user' @ 'localhost ';

+ -------------------------------------------------------------------------------

----------------------------------------- +

| Grants for dump_user @ localhost

|

+ -------------------------------------------------------------------------------

----------------------------------------- +

| Grant lock tables on *. * To 'dump _ user' @ 'localhost' identified by password' * 9

E3a8684dfaf643fd3d08d24c0432c0101417d8b' |

+ -------------------------------------------------------------------------------

----------------------------------------- +

1 row in SET (0.00 Sec)

To export the woshittest contents of the entire database to a file, run the following command:

C: \> mysqldump-udump_user-pdump_user woshittest> C: \ woshittestdumpfile.txt (The default path is the current path. For other options, use the full path.)

You can also add the following switch to this command line:

-- Databases (-- all-databases)

To export the table pwss separately:

C: \> mysqldump-udump_user-pdump_user woshittest pwss> C: \ woshittestdumpfilepwss.txt

If you want to export a record, turn on and off -- where = "Your SQL statement"

-- Where = "id = 1" or-W = "id = 1"

Mysqldump has the following list of other switches:

-- Add-drop-table

This option will add the drop table if exists statement before each table, which ensures that there will be no errors when importing your MySQL database, because every time you import back, check whether the table exists and delete the table if it exists.

-- Add-locks (I personally do not recommend this switch)

This option binds a lock table and an unlock TABLE statement in the insert statement. This prevents operations performed on tables by other users when these records are re-imported to the database.

Instance:

Lock tables 'pws' write;

/*! 40000 alter table 'pws' disable keys */;

Insert into 'pws' values (1, 'weiwei', 'I love this girl ');

/*! 40000 alter table 'pws' enable keys */;

Unlock tables;



-C or-complete_insert

This option causes the mysqldump command to add the column (field) name to each insert statement. This option is useful when exporting data to another database.

Instance:

Insert into 'pws' ('id', 'username', 'description') values (1, 'weiwei', 'I love this girl ');

-- Delayed-insert

Add the delay option to the INSERT command.

-F or-flush-logs

The log of the MySQL server will be refreshed before the export is executed.

-F or-Force

Export continues even if an error occurs.

-L or -- lock-tables

When exporting a table, the server locks the table. (Same as above: -- add-locks)

-T or -- no-create-Info

This option does not allow the mysqldump command to create a create table statement. This option is convenient when you only need data and do not need DDL statements.



-D or -- no-Data

This option does not allow the mysqldump command to create an insert statement.

You can use this option when you only need DDL statements.

-- OPT

This option will enable all options that will increase the File Export speed and create a file that can be imported more quickly. (Personal Recommendation.

If -- OPT is not used, mysqldump loads the entire result set to the memory and exports it. If the data size is very large, the export will fail. This switch is enabled by default. If you do not want to enable it: -- skip-opt to disable it.)

-Q or-quick

This option prevents MySQL from reading the entire exported content into the memory and then exporting it. Instead, it writes the exported content to the exported file when reading the exported content. This is the same as the above switch.

-- Tab = path

This option will create two files, one containing the DDL statement or table creation statement, and the other containing the data. The ddlfile name is tablename. SQL, and the data file name is tablename.txt. The path name is the directory where the two files are stored. The directory must already exist, and the user of the command has privileges on the file.

(The result of tablename.txt is equivalent to the data generated using select * From tablename into OUTFILE.)



-- Allow-keywords

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

-C, -- Compress

If both the client and server support compression and compression

All the information between the two.

-E, -- extended-insert

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

-#, -- Debug [= option_string]

TrackingProgramFor debugging ).

-- 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-T options and have the same meaning as the load data infile clause.

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

-P port_num, -- Port = port_num

TCP/IP port used to connect to a host. (Common for any system)

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

The socket file used when connecting to localhost (which is the default host.(For Linux systems)

-U user_name, -- user = Username

The username used by MySQL to connect to the server.(Results of the default who am I in Linux)

-O Var = option, -- Set-variable VAR = Option

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

-V, -- verbose

Lengthy mode. Print out more information about the program. (That is, detailed information)

-The "B" switch can generate the "CREATE DATABASE" statement:

C: \> mysqldump-udump_user-pdump_user-B woshittest -- add-drop-table-C> C: \ 1.txt

-- Tables

This is a very useful switch.

Skip the -- databases or-B switch. All names after this switch are considered as table names.

Instance:

C: \> mysqldump-udump_user-pdump_user-B woshittest -- tables pwss -- add-drop-table-c-r

> C: \ 1.txt

-R

To export the stored procedure, add the-r switch.

Instance:

C: \> mysqldump-udump_user-pdump_user woshittest pwss -- add-drop-table-c -- tab = "C :\\"-

V

-- Connecting to localhost...

-- Retrieving table structure for table pwss...

-- Sending SELECT query...

-- Disconnecting from localhost...

-V, -- version

Print the version information and exit.

Here we only talk about backup, but recovery is the most important:

Mysql-udump_user-pdump_user woshittest <1.txt

Import databases and data tables one by one SQLStatements.

-- No-create-Info,-T

Only export data without adding Create TableStatement.

Example:[Root @ 10 shell] # mysqldump -- no-create-Info wang_db>/home/Shell/wang_db. SQL

-- No-Data,-D

Only the database table structure is exported without exporting any data.

[Root @ 10 shell] # mysqldump -- no-data wang_db>/home/Shell/Wang. DB. SQL .1

Export complete data(Including the table structure and data)

[Root @ 10 shell] # mysqldump wang_db>/home/Shell/Wang. DB. SQL .2

Import a table separately

[Root @ 10 shell] # mysqldump -- add-drop-Table wang_db wang_tb>/home/Shell/Wang. DB. SQL .3

Note: Be careful when backing up a running database !!

If you must back up data during service running, add-Skip-OPTOption, similar to execution:

/Usr/local/MySQL/bin/mysqldump -- skip-opt-uroot-p123456 mysqlfxv> MySQL. SQL

To avoid table locking.