MySQL Backup and restore

1.mysqldump1.1. Backup method:

(1) Export the entire database and specify the character set (including data in the database)

(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

1.2.mysqldump Common parameters

–compatible=name it tells Mysqldump that the exported data will be compatible with which database or which old version of the MySQL server. Values can be ANSI, mysql323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, No_tables_options, no_field_options
And so on, to use a few values, separate them with commas. Of course, it is not guaranteed to be fully compatible, but is as compatible as possible.

–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,-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. (The default is on, using the –skip-add-drop-table cancellation 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 add lock tables before each table is exported and then unlock
TABLE. (The default is on, using the –skip-add-locks cancellation option) mysqldump-uroot-p
–all-databases (Add lock statement by default) mysqldump-uroot-p–all-databases
–skip-add-locks (Cancel lock statement)

–comments additional comment information. The default is open, you can cancel the mysqldump-uroot-p with –skip-comments
–all-databases (Default record comment) 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. 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. With Read
Local locks the table to allow MyISAM tables 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

–opt This is just a shortcut option, which is equivalent to adding –add-drop-tables–add-locking–create-option–disable-keys–extended-insert–lock-tables–quick at the same time –set-charset option. This option allows mysqldump to export data quickly, and the exported data can be quickly returned. This option is turned on by default, but can be disabled with –skip-opt. Note that if you run mysqldump without specifying the –quick or –opt option, the entire result set is placed in memory. Problems may occur if you export a large database

1.3. Restore

Reprint or share please indicate address: http://blog.csdn.net/w19981220
Mysqldump-u User name-p password Database < path
mysql-uroot-p1234 Database name < path

You can also use this method
First use of that database

source sql文件路径

Not to be continued

Copyright Notice: Step by step, easy to review, the shot when the shot, there are errors, must correct, thank you, and common progress!

MySQL Backup and restore