Mysqldump data Backup and restore detailed introduction

Let's take a look at the use of this mysqldump first.

The Mysqldump tool has a number of options, some of which are listed below:

Option/option action/action performed

--add-drop-table

This option will precede each table with the drop table if exists statement, which guarantees that the MySQL database will be returned without error, because each time it is returned, the table is first checked for existence and deleted.

--add-locks

This option bundles the previous lock table and unlock table statement in the INSERT statement. This prevents other users from working on the table when these records are imported again into the database
　　
-C Or-complete_insert

This option causes the mysqldump command to add a column (field) name to each generated INSERT statement. This option is useful when exporting data to another database.

--delayed-insert Add delay option to insert command

-F or-flush-logs Use this option to flush the MySQL server log before performing the export.

-F Or-force Use this option to continue exporting even if errors occur

--full This option adds additional information to the statement in the CREATE table

-L or-lock-tables using this option, the server will lock the table when the table is exported.

-T Or-no-create-info

This option causes the mysqldump command to not create a creating table statement, which is handy when you need only data without the need for DDL (database definition statements).
　　
-D or-no-data This option causes the mysqldump command to not create INSERT statements.

You can use this option when you need only DDL statements.

--opt This option opens all options that will increase the speed of file exports and create a file that can be imported faster.

-Q or-quick This option allows MySQL to not read the entire exported content into memory and then perform the export, but write to the guide file when read.

-T Path or-tab = Path This option will create two files, one file contains DDL statements or table creation statements, and another file contains data. The DDL file is named Table_name.sql, and the data file is named Table_name.txt. The path name is the directory where the two files are stored. The directory must already exist and the user of the command has privileges to the file.
　　
-W "where Clause" Or-where = "where Clause"

As mentioned earlier, you can use this option to filter the data that will be placed in the exported file.
　　
Suppose you need to create a file for the account that you want to use in a form, the manager wants to see all orders for this year (2004), they are not interested in DDL, and require a comma-delimited file because it's easy to import into Excel. To complete this character, you can use the following sentence:

Bin/mysqldump–p–where "order_date >= ' 2000-01-01 '"
–tab =/home/mark–no-create-info–fields-terminated-by=, Meet_a_geek Orders

This will get the results you want.
　　
Schema: Schema

The set of statements, expressed in data definition language, that completely describe the structure of a data base.

A set of statements expressed in a data definition language that fully describes the structure of the database.
　　
SELECT into outfile:

If you think the mysqldump tool is not cool enough to use SELECT into OutFile, MySQL also provides a command that has the opposite effect to the load DATA infile command, which is the SELECT INTO OutFile command. There are many similarities between the two commands. First, they have almost the same options. Now you need to complete the previous function with mysqldump

Okay, here's a detailed introduction to the mysqldump data backup and restore bar
Commands for backing up the MySQL database

The code is as follows	Copy Code
Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql

Backing up the MySQL database for a deleted table format
Backing up the MySQL database is a format with a deleted table that allows the backup to overwrite the existing database without having to manually delete the original database.

The code is as follows	Copy Code
Mysqldump-–add-drop-table-uusername-ppassword databasename > Backupfile.sql

Directly compress MySQL database to backup

The code is as follows	Copy Code
Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz

Backing up a MySQL database (some) tables

The code is as follows	Copy Code
Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql

Backup multiple MySQL databases at the same time

The code is as follows	Copy Code
Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql

Just back up the database structure

The code is as follows	Copy Code
Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql

Back up all databases on the server

The code is as follows	Copy Code
Mysqldump–all-databases > Allbackupfile.sql

command to restore MySQL database

The code is as follows	Copy Code
Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql

Restoring a compressed MySQL database

The code is as follows	Copy Code
Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName

Transferring a database to a new server

The code is as follows	Copy Code
Mysqldump-uusername-ppassword DatabaseName | Mysql–host=*.*.*.*-C DatabaseName

The files you might feel

Http://www.111cn.net/database/mysql/40556.htm