Export the Mysqldump tool that you want to use with MySQL, basic usage:
Copy Code code as follows:
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:
Copy Code code as follows:
Add lock tables before each table is exported and then unlock table. (in order to allow faster insertion into MySQL).
Copy Code code as follows:
Add a drop table before each create statement.
Copy Code code as follows:
Allow creation of column names that are keywords. This is done by the table name prefix in each column name.
Copy Code code as follows:
Use the full INSERT statement (with the column name).
Copy Code code as follows:
If both the client and the server support compression, compress all the information between the two.
Copy Code code as follows:
Inserts a row with the insert delayed command.
Copy Code code as follows:
Use the new multiple-line insert syntax. (Gives a tighter and faster insert statement)
Copy Code code as follows:
-#,--debug[=option_string]
Trace the use of the program (for debugging).
Copy Code code as follows:
Displays a help message and exits.
Copy Code code as follows:
--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.
Copy Code code as follows:
Before you start exporting, wash the log files in the MySQL server.
Copy Code code as follows:
Even if we get a SQL error during the export of a table, continue.
Copy Code code as follows:
Export data from the MySQL server on the named host. The default host is localhost.
Copy Code code as follows:
Lock all tables for starting export.
Copy Code code as follows:
Do not write table creation information (CREATE TABLE statement)
Copy Code code as follows:
Any row information that does not write to the table. This is useful if you want to export only the structure of a table!
Copy Code code as follows:
With
Copy Code code as follows:
--quick--add-drop-table--add-locks--extended-insert--lock-tables
。
Should give you the fastest possible export for reading into a MySQL server.
Copy Code code as follows:
-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.
Copy Code code as follows:
-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.) ）
Copy Code code as follows:
Do not buffer queries, export directly to stdout; use Mysql_use_result () to do it.
Copy Code code as follows:
-s/path/to/socket,--socket=/path/to/socket
The socket file used by the localhost when it is connected (it is the default host).
Copy Code code as follows:
-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.
Copy Code code as follows:
-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.
Copy Code code as follows:
-O var=option,--set-variable var=option
Sets the value of a variable. The possible variables are listed below.
Copy Code code as follows:
Verbose mode. Print out more information about what the program is doing.
Copy Code code as follows:
Print version information and exit.
Copy Code code as follows:
-W,--where= ' where-condition '
Only the selected records are exported; Note that the quotation marks are mandatory!
Copy Code code as follows:
"--where=user= ' JIMF '" "-wuserid>1" "-wuserid<1"
The most common mysqldump uses a backup that might make the entire database:
Copy Code code as follows:
Mysqldump--opt Database > Backup-file.sql
But it's also useful for enriching another MySQL database with information from one database:
Copy Code code as follows:
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:
Copy Code code as follows:
Shell> mysqladmin Create Target_db_name
shell> MySQL Target_db_name < backup-file.sql
It is
Copy Code code as follows:
shell> MySQL Library name < filename
Several common use cases:
1. Export the entire database
Copy Code code as follows:
Mysqldump-u user name-p database name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql
2. Export a table
Copy Code code as follows:
Mysqldump-u user name-P database name Table name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC users> wcnc_users.sql
3. Export a database structure
Copy Code code as follows:
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
Copy Code code as follows:
Mysql>use Database
Then use the source command, followed by the script file (such as the. SQL used here)
Copy Code code as follows:
Mysql>source D:wcnc_db.sql
MySQL uses the source command to import a database encoding problem
Mysql>use database name (same as your site database name)
Copy Code code as follows:
(First confirm encoding note is not UTF-8)
Copy Code code as follows:
(The name of the database to import)
