Mysqldump backup restore and import export in MySQL

Mysqldump tools, basic usage is:

Shell> mysqldump [OPTIONS] database [tables]

If you do not give any tables, the entire database will be exported.
By executing mysqldump--help, you can get the options table supported by your mysqldump version.
Note that if you run mysqldump without--quick or--opt option, mysqldump will load the entire result set into memory before exporting the results, if you are exporting a large database, this could be a problem.
Mysqldump supports the following options:
--add-locks
Add lock tables before each table is exported and then unlock table. (in order to allow faster insertion into MySQL).
--add-drop-table
Add a drop table before each create statement.
--allow-keywords
Allow creation of column names that are keywords. This is done by the table name prefix in 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
Inserts a row with the insert delayed command.
-E,--extended-insert
Use the new multiple-line insert syntax. (Gives a tighter and faster insert statement)
-#,--debug[=option_string]
Trace the use of the program (for 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 selections are used with the-T selection and have the same meaning as the corresponding load DATA infile clause.
The LOAD DATA infile syntax.
-F,--flush-logs
Before you start exporting, wash 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 the MySQL server on the named host. The default host is localhost.
-L,--lock-tables.
Lock all tables for starting export.
-T,--no-create-info
Do not write table creation information (CREATE TABLE statement)
-D,--no-data
Any row information that does not write to the table. This is useful if you want to export only the structure of a table!
--opt
--extended-insert--lock-tables with--quick--add-drop-table--add-locks.
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, the mysqldump requires a password from the terminal.
-P Port_num,--port=port_num
The TCP/IP port number to use when connecting to a 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
The socket file used by the 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 works only when Mysqldump is running on the same machine that the mysqld daemon is running. The format of the. txt file is based on 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 about what the program is doing.
-V,--version
Print version information and exit.
-W,--where= ' where-condition '

mysqldump Backup Restore and mysqldump Import Export statements Daquan detailed

Mysqldump Backup:

Mysqldump-u Username-p password-H host database a-w "SQL condition"--lock-all-tables > Path

Case:

The code is as follows	Copy Code
Mysqldump-uroot-p1234-hlocalhost DB1 a-w "id in" (SELECT ID from b) "--lock-all-tables > C:aa.txt

Mysqldump Restore:

Mysqldump-u Username-p password-H host database < path

Case:

The code is as follows	Copy Code
mysql-uroot-p1234 DB1 < C:aa.txt

Mysqldump Export by Condition:

Mysqldump-u Username-p password-H host database a--where "conditional statement"--no-Build table > Path

The code is as follows	Copy Code
mysqldump-uroot-p1234 dbname a--where "tag= '"--no-create-info> c:a.sql

Mysqldump by import:

Mysqldump-u Username-p password-H host database < path

Case:

The code is as follows	Copy Code
mysql-uroot-p1234 DB1 < C:a.txt

Mysqldump Export Table:

Mysqldump-u Username-p password-H host database table

Case:

code is as follows	copy code
Mysqldump- Uroot-p Sqlhk9 a--no-data