MySQL mysqldump is used for export. The basic usage is shell> mysqldump [Options] database [Tables]. If you do not specify any table, the entire database will be exported. Run mysqldump -- help to obtain the option table supported by your mysqldump version. Note: If you run mysqldump without the -- quick or -- opt option, mysqldump loads the entire result set to the memory before the export result. If you are exporting a large database, this may be a problem. Mysqldump supports the following options: -- add-locks
Add lock tables and unlock table before each table is exported. (To make it faster to insert data to MySQL ).
-- Add-drop-table
Add a drop table before each create statement.
-- Allow-keywords
Names of columns allowed to be created as keywords. This is done by the table name prefix on each column name.
-C, -- complete-insert
Use the complete insert Statement (with the column name ).
-C, -- Compress
If both the client and server support compression, all information is compressed between the two.
-- Delayed
Use the insert delayed command to insert rows.
-E, -- extended-insert
Use the new multiline insert syntax. (A more compact and faster insert statement is provided)
-#, -- Debug [= option_string]
Tracking Program usage (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 options are used with-T options and have the same meaning as the load data infile clause.
Load data infile syntax.
-F, -- flush-logs
Wash Out the log files on the MySQL server before starting the export.
-F, -- force,
Even if we get an SQL error while exporting 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 start export.
-T, -- no-create-Info
Create table statement)
-D, -- no-Data
No row information is written to the table. If you only want to export the structure of a table, this is very useful!
-- OPT
Same as -- quick -- add-drop-table -- add-Locks -- extended-insert -- lock-tables.
You should be given the fastest export possible for reading a MySQL server.
-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
The TCP/IP Port used to connect to a host. (This is used to connect to a host other than localhost because it uses UNIX sockets .)
-Q, -- quick
Directly export the data to stdout without buffering the query; Use mysql_use_result () to do it.
-S/path/to/socket, -- socket =/path/to/socket
The socket file used when connecting to localhost (which is the default host.
-T, -- tab = path-to-some-directory
Pair
Create a table_name. SQL file for each given table, which contains the SQL CREATE command and a table_name.txt file, which contains the number
Data. Note: This only works when mysqldump runs on the same machine where the mysqld daemon is running .. The TXT file is formatted according to -- fields-xxx and
-- Lines -- XXX option.
-U user_name, -- user = user_name
The username used by MySQL to connect to the server. The default value is your UNIX login name.
-O Var = option, -- Set-variable VAR = option to set the value of a variable. Possible variables are listed below.
-V, -- verbose
Lengthy mode. Print out more information about the program.
-V, -- version
Print the version information and exit.
-W, -- where = 'where-condition'
Only selected records are exported. Note that the quotation marks are mandatory!
"-- Where = user = 'jimf '" "-wuserid> 1" "-wuserid <1" the most common mysqldump may create a backup of the entire database: mysqldump -- opt database> backup-file. SQL but it is also useful for enriching the information from a database and another MySQL database: mysqldump -- opt database | MySQL -- Host = remote-host-C database because mysqldump exports a complete SQL statement, it is easy to use a mysql client program to import data: shell> mysqladmin create target_db_name
Shell> MySQL target_db_name <backup-file. SQL
Yes
Shell> MySQL database name <file name ==================================
Several common use cases: 1. Export the entire database
Mysqldump-u username-P Database Name> exported file name
Mysqldump-u wcnc-P smgp_rj_wcnc> wcnc. SQL
2. Export a table
Mysqldump-u user name-P database name Table Name> exported file name
Mysqldump-u wcnc-P smgp_rj_wcnc users> wcnc_users. SQL
3. Export a database structure
Mysqldump-u wcnc-p-d -- add-drop-Table smgp_apps_wcnc> D: \ wcnc_db. SQL-D has no data -- add-drop-table adds a drop Table 4 before each create statement. import Database
Common source commands
Go to the MySQL Database Console,
For example, MySQL-u root-P ///////////////////////////////// //////////////////////////////////////// //////////////////////////////////////// //////////////////////////////////////// ////////////// // mysqldump-umysql-ppassword-P 3308-H 127.0.0.1 -- default -character-set = utf8-c -- triggers-r -- Hex-blob-X -- opt vtms3000 qdevices> 3000. SQL
-- Simplified
Mysqldump-umysql-ppassword-P 3308-H 127.0.0.1 -- default-character-set = utf8-c -- Hex-blob-X -- opt test> test. SQL
-- Only export data
Mysqldump-umysql-ppassword-P 3308-H 127.0.0.1 -- default-character-set = utf8-c-I-t -- opt vtms3000 qdevices> 3000. SQL
-- Mysqldump-H 10.30.4.89-umysql-ppassword -- default-character-set = utf8 -- Opt -- extended-insert = false -- triggers-r-d -- Hex-blob-x vtms3000_new> vtms3000. SQL
-- Triggers: Export triggers at the same time
-- Routines,-R: Export stored procedures and user-defined functions
-- No-data,-D: only the database table structure is exported without exporting any data.
-- Lock-all-tables,-x submits a request before starting export to lock all tables in all databases to ensure data consistency.
-- Hex-blob exports binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include binary, varbinary, and blob.
-- No-create-Info,-t only exports data, without adding the create table statement.
-P, -- Port = # port (must be an upper-case port)
-- Default-character-set = charset specifies the character set used for data export.
-- Complete-insert: The data exported by-C adopts the complete insert method containing the field name, that is, all values are written in one row.
This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure.
Therefore, you need to use this parameter with caution. At least I do not recommend this parameter.
No questions have been asked. Test qregkey100w, qtablelog321.9w, max_allowed_packet = 25165824, net_buffer_length, backup, restoring (backup file 221 MB ). None of the above problems
-- Single-transaction: This option submits a begin SQL statement before exporting data. Begin does not block any applications and ensures Database Consistency during export. It is only applicable to transaction tables, such as InnoDB and bdb.
This option and the -- lock-tables option are mutually exclusive, Because lock tables will implicitly commit any pending transactions.
To export a large table, use the -- quick option.
-OPT: This is just a shortcut, equivalent to adding both
-- Add-drop-tables
-- Add-locking
-- Create-Option
-- Disable-keys: tells mysqldump to add/* at the beginning and end of the insert statement /*! 40000 alter table table disable keys */; And /*! 40000 alter table table enable keys */; Statement, which greatly improves the speed of the insert statement because it re-creates the index after all data is inserted. This option is only applicable to MyISAM tables.
-- Extended-insert: by default, mysqldump enables the -- complete-insert mode. If you do not want to use it, use this option and set its value to false.
-- Lock-tables is used to lock the currently exported data table, rather than locking all tables in the database at once. This option is only applicable to MyISAM tables. For InnoDB tables, you can use the -- Single-transaction option.
-- Quick
-- Set-charset option.
This option allows mysqldump to export data quickly and export data back quickly. This option is enabled by default, but can be disabled with -- skip-OPT.
-I, -- Comments write additional information.
-- Ignore-table = Name do not dump the specified table. to specify more than one
Table to ignore, use the directive multiple times, once
For each table. Each table must be specified with both
Database and table names, e.g .,
-- Ignore-table = database. Table.
Note: If the -- quick or -- opt option is not specified when running mysqldump, the entire result set is stored in the memory. If you export a large database, problems may occur.
-- Log-error = Name append warnings and errors to given file.
-T, -- tab = Name create tab-separated textfile for each table to given
Path. (create. SQL and. txt files.) Note: This only works
If mysqldump is run on the same machine as the mysqld
Server.
//////////////////////////////////////// //////////////////////////////////////// //////////////////////////////////////// //////////////////////////////////////// ///////////////////
Mysql> Use Database and then use the source command. The following parameter is a script file (for example,. SQL used here)
Mysql> source D: \ wcnc_db. SQL
