Mysqldump use Method (Backup and restore of MySQL database)

#mysqldump--help

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 exports all databases. Mysqldump-uroot-p-–all-databases > All_databases_backup.sql

–-all-tablespaces,-y exports all table spaces. Mysqldump-uroot-p–all-databases–all-tablespaces

–-no-tablespaces,-y does not export any tablespace information. Mysqldump-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. (Default is on, use –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, use the –skip-add-locks cancel option) mysqldump-uroot-p–all-databases (add the lock statement by default) mysqldump-uroot-p–all-databases– Skip-add-locks (Cancel lock statement)

–comments additional comment information. Default is on, you can cancel mysqldump-uroot-p–all-databases (default record comment) with –skip-comments mysqldump-uroot-p–all-databases–skip-comments ( Uncomment)

–compact exports less output information (for debugging). Remove the structure of annotations and Kinsoku. You can use the option: –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 Utf8mysqldump-uroot-p–all-databases–default-character-set=latin1

–delayed-insert Export 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:

(1) mysqldump is often used for database backup and restore, in the process of backup we can add any of the above parameters according to their actual 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

(2) If you want to restore the data, you can execute the following command:

Mysql-u Username-p test_db < Test_db.sql

(3) Restoring a database operation can also use the following methods:

Mysql> Sourcetest_db.sql

REF:

Http://www.cnblogs.com/wxb-km/p/3610594.html

Http://blog.chinaunix.net/uid-26884465-id-3332857.html

http://blog.itpub.net/29254281/viewspace-1392757/

Mysqldump use Method (Backup and restore of MySQL database)