How to use mysqldump tools in MySQL

Export the Mysqldump tool that you want to use with MySQL, basic usage:

The code is as follows	Copy Code
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 '
Only the selected records are exported; Note that the quotation marks are mandatory!
"--where=user= ' JIMF '" "-wuserid>1" "-wuserid<1"
The most common mysqldump uses a backup that might make the entire database:
Mysqldump--opt Database > Backup-file.sql
But it's also useful for enriching another MySQL database with information from one database:
Mysqldump--opt Database | MySQL--host=remote-host-c database
Since mysqldump is exporting complete SQL statements, it is easy to import data into the MySQL client program:
Shell> mysqladmin Create Target_db_name
shell> MySQL Target_db_name < backup-file.sql
It is
shell> MySQL Library name < filename

Several common use cases:

1. Export the entire database
Mysqldump-u user name-p database name > exported file name

The code is as follows	Copy Code
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql

2. Export a table
Mysqldump-u user name-P database name Table name > exported file name

The code is as follows	Copy Code
Mysqldump-u wcnc-p SMGP_APPS_WCNC users> wcnc_users.sql

3. Export a database structure

The code is as follows	Copy Code
Mysqldump-u Wcnc-p-D--add-drop-table SMGP_APPS_WCNC >d:wcnc_db.sql

-D No data--add-drop-table add a drop table before each CREATE statement
4. Import Database
Common source Commands
Enter the MySQL database console,
such as Mysql-u root-p

Mysql>use Database
Then use the source command, followed by the script file (such as the. SQL used here)

code is as follows	copy code
MySQL >source d:wcnc_db.sql