Mysql uses the mysqldump command to back up and restore the database

The mysqldump command is a very good database operation command in mysql. It can back up and restore the database, and is used in almost all database command backups, next I will introduce some usage of mysqldump.

I. Common Operations:

Back up the entire database

Format:
Mysqldump-h host name-P port-u user name-p password (-database) database Name> file name. SQL
Mysqldump-h {hostname}-P {port}-u {username}-p {password} {databasename}> {backupfile. SQL}
For example:
 
The Code is as follows:

The Code is as follows:	Copy code
Mysqldump-hlocalhost-P3306-uzhuchao-p123456 db_test> backfile1. SQL

Back up the MySQL database in the format of a table with deletion
Backing up a MySQL database is in the format of a table with deletion, so that the backup can overwrite existing databases without the need to manually delete the original database.
Format: mysqldump -- add-drop-table-u {username}-p {password} {databasename}> {backfile. SQL}
For example:
 
The Code is as follows:

The Code is as follows:	Copy code
Mysqldump -- add-drop-table-uzhuchao-p123456 db_test> backfile2. SQL

Directly compress and back up the MySQL database
Format: mysqldump-h {hostname}-u {username}-p {password} {databasename} | gzip> export backfile. SQL .gz}
For example:
 
The Code is as follows:

The Code is as follows:	Copy code
Mysqldump-hlocalhost-uzhuchao-p123456 db_test1 | gzip> backfile3. SQL .gz

Back up a MySQL database table
Format: mysqldump-h host name-P port-u username-p password (-tables |-quick) database name table name 1 (table name 2 ...) > File name. SQL (which can be defaulted in parentheses ).
Mysqldump-h {hostname} (-P {port})-u {user}-p {password} (-tables |-quick) {databasename} {table1} {table2}> {backfile. SQL}
For example:
 
The Code is as follows:

The Code is as follows:	Copy code
Mysqldump-hlocalhost-uzhuchao-p123456 db_test tbl_test> backfile4-1. SQL Mysqldump-hlocalhost-P3306-uzhuchao-p123456 db_test tbl_test> backfile4-2. SQL Mysqldump-hlocalhost-P3306-uzhuchao-p123456 -- quick db_test tbl_test> backfile4-3. SQL Mysqldump-hlocalhost-P3306-uzhuchao-p123456 -- tables db_test tbl_test1 tbl_test2> backfile4-4. SQL

Back up multiple MySQL databases at the same time
Format: mysqldump-h {hostname} (-P {port})-u {username}-p {password}-databases {databasename1} {databasename2} {databasename3}> multibackfile. SQL
For example:
 
The Code is as follows:

The Code is as follows:	Copy code
Mysqldump-hlocalhost-uzhuchao-p123456-databases db_test1 db_test2 db_test3> multibackfile. SQL

Back up database structures only
Format: mysqldump-no-data-databases {databasename1} {databasename2 }>{ structurebackfile. SQL}
For example:
 
The Code is as follows:

The Code is as follows:	Copy code
Mysqldump-no-data-databases db_test1 db_test2> structurebackfile. SQL

Back up all databases on the server
Format: mysqldump-all-databases> allbackupfile. SQL
 
========================================================== ======================================
 
Command for restoring MySQL database
Format: mysql-h {hostname}-u {username}-p {password} {databasename} <{backfile. SQL}
For example:
 
The Code is as follows:

The Code is as follows:	Copy code
Mysql-hlocalhost-uroot-p123456 db_test4 <back_file1. SQL

Restore a compressed MySQL database
Format: gunzip <your backfile. SQL .gz} | mysql-u {username}-p {password} {databasename}
For example:
 
The Code is as follows:

The Code is as follows:	Copy code
Gunzip <backfile. SQL .gz | mysql-uzhuchao-p123456 db_test5

Transfer database to new server
Mysqldump-u {username}-p {password} {databasename} | mysql-host = *. *-C {databasename}
 
Ii. Others:
1. If the port is 3306 by default,-P {port number} can be omitted.
 
2. The content in the command line format {} is a variable.
Host Name: {hostname}
Port: {port} (3306 by default, which can be default)
Username: {user} {username} (such as root)
Password: {password}
Database Name: {databasename}
Table Name: {table} {table1} {table2}
File Name: {backfile. SQL}

Related Knowledge:

This article discusses the backup and recovery mechanisms of MySQL and how to maintain data tables, including the two most important types of tables: MyISAM and Innodb. the MySQL version designed in this article is 5.0.22.

Currently, MySQL supports the following free BACKUP tools: mysqldump and mysqlhotcopy. You can also use SQL syntax to back up: BACKUP TABLE or SELECT INTO OUTFILE, or back up binary logs (binlog ), you can also directly copy data files and related configuration files. MyISAM tables are saved as files, so they are relatively easy to back up. The methods mentioned above can be used. All Innodb tables are stored in the same data file ibdata1 (multiple files or independent tablespace files), which is relatively difficult to back up, the free solution can be copying data files, backing up binlogs, or using mysqldump.

1. mysqldump

1.1 backup

Mysqldump adopts an SQL-level backup mechanism. It imports data tables into SQL script files and is suitable for upgrading between different MySQL versions. This is also the most common backup method.

Here are some main parameters of mysqldump:

-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. Separate them with commas. Of course, it does not guarantee full compatibility, but is as compatible as possible.

-Complete-insert,-c

The exported data adopts the complete INSERT method containing the field name, that is, all values are written in one row. This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure. Therefore, you need to use this parameter with caution. At least I do not recommend this parameter.

-Default-character-set = charset

Specifies the character set used for data export. If the data table does not use the default latin1 character set, this option must be specified during data export. Otherwise, garbled characters will occur after data is imported again.

-Disable-keys

Tell mysqldump to add/* at the beginning and end of the INSERT statement /*! 40000 alter table table disable keys */; And /*! 40000 alter table table enable keys */; Statement, which greatly improves the speed of the insert statement because it re-creates the index after all data is inserted. This option is only applicable to MyISAM tables.

-Extended-insert = true | false

By default, mysqldump enables the-complete-insert mode, so if you do not want to use it, use this option to set its value to false.

-Hex-blob

Export binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB.

-Lock-all-tables,-x

Before starting the export, submit 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.

-Lock-tables

It is similar to-lock-all-tables, but instead of locking all tables in the database. This option is only applicable to MyISAM tables. For Innodb tables, you can use the-single-transaction option.

-No-create-info,-t

Only export data without adding the create table statement.

-No-data,-d

Only the database table structure is exported without exporting any 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. If you export a large database, problems may occur.

-Quick,-q

This option is useful when exporting large tables. It forces mysqldump to retrieve records from the Server query and output them directly instead of caching all records into the memory.

-Routines,-R

Export stored procedures and user-defined functions.

-Single-transaction

This option submits a begin SQL statement before exporting data. BEGIN does not block any applications and ensures Database Consistency during export. It is only applicable to transaction tables, such as InnoDB and BDB.

This option and the-lock-tables option are mutually exclusive, Because lock tables will implicitly commit any pending transactions.

To export a large table, use the-quick option.

-Triggers

Export the trigger at the same time. This option is enabled by default. Use-skip-triggers to disable it.

For details about other parameters, see the manual. I usually use the following SQL to back up the MyISAM table:

The Code is as follows:	Copy code
/Usr/local/mysql/bin/mysqldump-uyejr-pyejr -Default-character-set = utf8-opt-extended-insert = false -Triggers-R-hex-blob-x db_name> db_name. SQL

Use the following SQL to back up the Innodb table:

The Code is as follows:	Copy code
/Usr/local/mysql/bin/mysqldump-uyejr-pyejr -Default-character-set = utf8-opt-extended-insert = false -Triggers-R-hex-blob-single-transaction db_name> db_name. SQL

In addition, if you want to implement online backup, you can also use the-master-data parameter, as shown below:

The Code is as follows:	Copy code
/Usr/local/mysql/bin/mysqldump-uyejr-pyejr -Default-character-set = utf8-opt-master-data = 1 -Single-transaction-flush-logs db_name> db_name. SQL

It only requests the lock table at the beginning, then refreshes the binlog, and then adds the change master statement to the exported file to specify the binlog location of the current backup, if you want to restore the file to slave, you can use this method.

1.2 restore

The file backed up with mysqldump is an SQL script that can be directly imported. There are two ways to import data.

Directly use the mysql client

For example:

The Code is as follows:	Copy code
/Usr/local/mysql/bin/mysql-uyejr-pyejr db_name <db_name. SQL

Use SOURCE syntax

In fact, this is not a standard SQL syntax, but a function provided by the mysql client, for example:

The Code is as follows:	Copy code
SOURCE/tmp/db_name. SQL;

The absolute path of the file must be specified here, and the file must be read by the mysqld running user (such as nobody ).