mysqldump backup Restore MySQL

This document is implemented in MySQL 5.7 decompression version for example

1. Simply try the next example on window

1. Open the CMD named Line with Administrator privileges and switch to the mysqldump execution program

2. To change the database as an example, in the export path to create an empty file, or the error cannot find the file

3. Execution of commands

Mysqldump-u root-p grc_order > D:\mysql-bak\order.sql

Prompt for password, confirm after input, complete

4. Found SQL file size changed, backup succeeded

Open develop existing complete build table, insert data, lock statement

5. Restore, idle CREATE DATABASE, execute the following statement

<   D:\mysql-bak\order.sql

Wait after entering password, success

6. Check the database, OK

2. Common directives

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 tabl ename > Tablename.sql    (4) Export the table structure of a data table in a database (without data)    

3.mysqldump Common parameter Description:

–all-databases,-A exports all of the database mysqldump-uroot-p–all-databases–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 the drop statement) –add-locks add lock tables before each table is exported and unlock table afterwards. (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 (cancels the 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 export 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 Use 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. The 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 exports the data mysqldump-uroot-p–all-databases–delayed-insert–events with the time-lapse insertion method (insert delayed), the-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 Specify the user name of the connection.

4.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:    <  test_db.sql            You can also use the following methods to restore a database operation:    mysql> sourcetest_db.sql   

mysqldump backup Restore MySQL