Mysql backup and Restoration

Mysql backup and Restoration
1. mysqldump1.1. backup method:

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

Mysqldump-u root-p -- default-character-set = utf8 dbname> storage path

(2) Export the database structure (excluding data)

mysqldump -u username -p -d dbname > dbname.sql    

(3) export a data table in the database (including data)

mysqldump -u username -p dbname tablename > tablename.sql    

(4) export the table structure of a data table in the database (excluding 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 the database or the old MySQL server. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, and no_field_options.
And so on. Use commas to separate them. Of course, it does not guarantee full compatibility, but is as compatible as possible.

-All-databases,-A export all databases mysqldump-uroot-p-all-databases

-All-tablespaces and-Y: export all tablespaces. 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 the drop database statement before each database is created. Mysqldump-uroot-p
-All-databases-add-drop-database

-Add-drop-table: add the drop table statement before each data table is created. (Enabled by default, use the-skip-add-drop-table cancel option) mysqldump
-Uroot-p-all-databases (the drop statement is added by default) mysqldump-uroot-p-all-databases-skip-add-drop-table (cancel the drop Statement)

-Add-locks: add lock tables and UNLOCK before each table is exported.
TABLE. (On by default, use the-skip-add-locks cancel option) mysqldump-uroot-p
-All-databases (the LOCK statement is added by default) mysqldump-uroot-p-all-databases
-Skip-add-locks (cancel LOCK Statement)

-Comments: add comments. Enabled by default. You can use-skip-comments to cancel mysqldump-uroot-p.
-All-databases (default record comment) mysqldump-uroot-p-all-databases
-Skip-comments (uncomment)

-Compact exports less output information (for debugging ). Remove comments, headers, and tails. Option:-skip-add-drop-table
-Skip-add-locks-skip-comments-skip-disable-keysmysqldump-uroot-p
-All-databases-compact

-Complete-insert,
-C use the complete insert Statement (including the column name ). This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure. Mysqldump
-Uroot-p-all-databases-complete-insert

-Compress,-C enables compression between the client and the server to pass all information mysqldump-uroot-p-all-databases
-Compress

-Databases,-B Exports several databases. All name parameters following the parameter are considered as the database name. Mysqldump-uroot-p
-Databases test mysql

-Debug outputs the 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 debugging information and exit mysqldump-uroot-p-all-databases-debug-info.

-Default-character-set: sets the default character set. The default value is utf8mysqldump-uroot-p.
-All-databases-default-character-set = latin1

-Delayed-insert: insert delayed to export data mysqldump-uroot-p
-All-databases-delayed-insert

-Events,-E export event. Mysqldump-uroot-p-all-databases-events

-Refresh the log before flush-logs starts exporting. Note: If you export multiple databases at a time (use the option-databases or-all-databases), the logs will be refreshed one by one. In addition to using-lock-all-tables or-master-data. In this case, the log is refreshed once, and the corresponding table is locked at the same time. Therefore, if you want to export and refresh logs at the same time, use-lock-all-tables
Or-master-data and-flush-logs. Mysqldump-uroot-p-all-databases
-Flush-logs

-Flush-privileges: After exporting the mysql database, a FLUSH PRIVILEGES
Statement. To restore data correctly, this option should be used to export data from the mysql database and dependent mysql database at any time. Mysqldump-uroot-p
-All-databases-flush-privileges

-Force ignores SQL errors during export. Mysqldump-uroot-p-all-databases-force

-Host,-h host information to be exported mysqldump-uroot-p-host = localhost-all-databases

-Ignore-table: the specified table is not exported. When you specify to ignore multiple tables, you need to repeat multiple times for each table. The database and table names must be specified for each table. Example:-ignore-table = database. table1
-Ignore-table = database. table2 ...... Mysqldump-uroot-p-host = localhost
-All-databases-ignore-table = mysql. user

-Lock-all-tables,
-X submits a 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 automatically disabled. Mysqldump-uroot-p-host = localhost-all-databases
-Lock-all-tables

-Lock-tables: All tables are locked before-l is exported. READ
LOCAL locks the table to allow Concurrent Insertion of MyISAM tables. For tables that support transactions such as InnoDB and BDB,-single-transaction is a better choice because it does not need to lock the table at all. Note that when exporting multiple databases,-lock-tables locks the tables for each database. Therefore, this option does not guarantee the logical consistency between the tables in the exported files in the database. The export statuses of different database tables can be completely different. Mysqldump
-Uroot-p-host = localhost-all-databases-lock-tables

-No-create-db,-n only exports data, without adding the create database statement. Mysqldump-uroot-p
-Host = localhost-all-databases-no-create-db

-No-create-info,-t only exports data, without adding the create table statement. Mysqldump-uroot-p
-Host = localhost-all-databases-no-create-info

-No-data,-d: only the database table structure is exported without exporting any data. Mysqldump-uroot-p-host = localhost
-All-databases-no-data

-Opt: This is just a quick option, it is equivalent to adding the-add-drop-tables-add-locking-create-option-disable-keys-extended-insert-lock-tables-quick-set-charset option at the same time. 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. Note: If the-quick or-opt option is not specified when running mysqldump, the entire result set is stored in the memory. Problems may occur if you export a large database.

1. 3. Restore

Reprinted or share please mark the address: http://blog.csdn.net/w19981220
Mysqldump-u username-p password database <path
Mysql-uroot-p1234 database name <path

You can also use this method
Use that database first

Mysql> source SQL file path

To be continued

Copyright statement: one step at a time, so that you can easily review your work. If you have any mistakes, please correct them. Thank you very much for your progress! For Original reprinted shares, please mark the address. Thank you.