Mysqldump How to use

several common methods of 1.mysqldump:

(1) Export the entire database (including data in the database)

Mysqldump-u username-p dbname > Dbname.sql    

(2) Export database structure (without data)

Mysqldump-u username-p-D dbname > Dbname.sql    

(3) Export a data table (containing data) in the database

Mysqldump-u username-p dbname tablename > Tablename.sql    

(4) The table structure of a data table in the export database (without data)

Mysqldump-u Username-p-D dbname tablename > Tablename.sql   

2.mysqldump Common parameter Description:

--all-databases,-A export all databases

Mysqldump-uroot-p--all-databases

--all-tablespaces,-y exports all table spaces.

Mysqldump-uroot-p--all-databases--all-tablespaces--no-tablespaces,--uroot-p--all-databases-- No-tablespaces

--add-drop-database Add a DROP DATABASE statement before each database is created.

Mysqldump-uroot-p--all-databases--add-drop-database

--add-drop-table Add a Drop data table statement before each data table is created. (The default is on, using the--skip-add-drop-table cancel option)

Mysqldump-uroot-p--all-databases (Add drop statement by default)
Mysqldump-uroot-p--all-databases--skip-add-drop-table (Cancel drop statement)

--add-locks adds lock tables before each table is exported and then unlock table. (The default is on, using the--skip-add-locks cancel option)

Mysqldump-uroot-p--all--uroot-p--all-databases--skip-add-locks (Cancel lock statement)

--comments additional comment information. The default is open, you can cancel with--skip-comments

Mysqldump-uroot-p--all--uroot-p--all-databases--skip-comments (uncomment)

--compact exports less output information (for debugging). Remove the structure of annotations and Kinsoku. Available options:--skip-add-drop-table--skip-add-locks--skip-comments--skip-disable-keysmysqldump-uroot-p--all-databases --compact

--complete-insert,-C uses the full INSERT statement (containing the column name). This can improve insertion efficiency, but may be affected by the Max_allowed_packet parameter, causing the insert to fail.

Mysqldump-uroot-p--all-databases--complete-insert

--compress,-C enables compression to pass all information between the client and the server

Mysqldump-uroot-p--all-databases--compress

--databases,-B exports several databases. All name parameters after the parameter are treated as database names.

Mysqldump-uroot-p--databases Test MySQL

--debug output debug information for debugging. The default value is: D:t:o,/tmp/mysqldump.tracemysqldump-uroot-p--all-databases--debugmysqldump-uroot-p--all-databases--debug= "D:t:o,/tmp/debug.trace"

--debug-info output debug Information and exit

Mysqldump-uroot-p--all-databases--debug-Info

--default-character-set Set default character set, default value is UTF8

Mysqldump-uroot-p--all-databases--default-character-set=latin1

--delayed-insert exporting data using the time-lapse insertion method (insert delayed)

Mysqldump-uroot-p--all-databases--delayed-insert

--events,-e export event.

Mysqldump-uroot-p--all-databases--events

--flush-logs to refresh the log before starting the export. Note: If you export more than one database at a time (using option--databases or--all-databases), the log will be refreshed on a per-database basis. In addition to using--lock-all-tables or--master-data. In this case, the log will be refreshed once, so the table is locked at the same time. Therefore, if you intend to export and refresh the logs at the same time, you should use--lock-all-tables or--master-data and--flush-logs.

Mysqldump-uroot-p--all-databases--flush-logs

--flush-privileges after exporting the MySQL database, issue a flush privileges statement. For proper recovery, this option should be used at any time to export the MySQL database and rely on MySQL database data.

Mysqldump-uroot-p--all-databases--flush-privileges

--force ignores the SQL errors that occur during the export process.

Mysqldump-uroot-p--all-databases--force

--host,-H needs to export the host information

Mysqldump-uroot-p--host=localhost--all-databases

--ignore-table does not export the specified table. Specifies that when multiple tables are ignored, you need to repeat them multiple times, one table at a time. Each table must specify both the database and the table name. For example:--ignore-table=database.table1--ignore-table=database.table2 ...

Mysqldump-uroot-p--host=localhost--all-databases--ignore-table=mysql.user

--lock-all-tables,-X commits the request to lock all tables in all databases to ensure data consistency. This is a global read lock, and the--single-transaction and--lock-tables options are turned off automatically.

Mysqldump-uroot-p--host=localhost--all-databases--lock-all-tables

--lock-tables,-l locks all tables before starting export. Lock the table with read local to allow the MyISAM table to be inserted in parallel. Tables that support transactions, such as InnoDB and bdb,--single-transaction, are a better choice because they do not need to lock the table at all. Note that when you export multiple databases,--lock-tables locks the table for each database separately. Therefore, this option does not guarantee logical consistency between the tables in the exported file and the database. The export state of different database tables can be completely different.

Mysqldump-uroot-p--host=localhost--all-databases--lock-tables

--no-create-db,-n exports only data without adding the Create DATABASE statement.

Mysqldump-uroot-p--host=localhost--all-databases--no-create-db

--no-create-info,-t exports only data without adding the Create TABLE statement.

Mysqldump-uroot-p--host=localhost--all-databases--no-create-Info

--no-data,-D does not export any data, only the database table structure is exported.

Mysqldump-uroot-p--host=localhost--all-databases--no-data

--password,-P connection Database password

--port,-P connection database port number

--user,-u Specifies the user name of the connection.

3.mysqldump Common examples:

Mysqldump often used for database backup and restore, in the process of backup we can add any of the above parameters according to their own situation, assuming that there is a database test_db, execute the following command, you can complete the backup of the entire database:

Mysqldump-u root-p test_db > Test_db.sql    

To restore the data, execute the following command:

Mysql-u Username-p test_db < Test_db.sql    

Restoring a database operation can also use the following methods:

Mysql> Source Test_db.sql    

Mysqldump How to use