MySQL restore the database using the mysqldump command backup

First, the common operation:

Back up the entire database

Format:
MYSQLDUMP-H host name-P Port-u username-p password (–database) database name > filename. 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

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.
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 MySQL database to backup
Format: Mysqldump-h{hostname}-u{username}-p{password} {databasename} | gzip > {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

Backing up a MySQL database (some) tables
Format: MYSQLDUMP-H host name-P Port-u username-p password (–tables |–quick) database Name Table name 1 (table Name 2 ...) > filename. SQL (default 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

Backup 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

Just back up the database structure
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 to restore 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

Restoring a compressed MySQL database
Format: Gunzip < {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

Transferring a database to a new server
Mysqldump–u{username}–p{password} {DatabaseName} | mysql–host=*.*.*.*–c {DatabaseName}

II. Other:
1, if the port is the default 3306, you can omit the-p {port number}.

2, the contents of the command line format {} are variables
Host name: {hostname}
Port: {port} (general default 3306, can default)
User name: {user} {username} (for example, root)
Password: {password}
Database name: {databasename}
Table name: {table} {table1} {table2}
File name: {backfile.sql}

Related knowledge:

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

The free backup tools currently supported by MySQL are: mysqldump, mysqlhotcopy, and can be backed up with SQL syntax: Backup TABLE or SELECT into outfile, or backup binary log (binlog), It can also be a direct copy of the data file and the associated configuration file. MyISAM tables are stored in the form of files, so they are relatively easy to back up, and several of the above mentioned methods are available. Innodb All tables are stored in the same data file ibdata1 (or multiple files, or separate tablespace files), relatively difficult to backup, the free scheme can be copied data files, backup Binlog, or with mysqldump.

1, mysqldump

1.1 Backup

Mysqldump is a SQL-level backup mechanism that guides data tables into SQL script files and is relatively appropriate for upgrades between different versions of MySQL, which is also the most common method of backup.

Now let's talk about some of the main parameters of mysqldump:

–compatible=name

It tells Mysqldump which database or older version of the MySQL server the exported data will be compatible with. Values can be ANSI, mysql323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, no_tables_options, no_field_options, etc. , you use a few values to separate them with commas. Of course, it does not guarantee full compatibility, but is as compatible as possible.

–complete-insert,-c

The exported data takes a complete INSERT with the field name, which means that all values are written on one line. This can increase the efficiency of the insertion, but may be affected by the Max_allowed_packet parameter and cause the insertion to fail. Therefore, you need to use this parameter with caution, at least I do not recommend it.

–default-character-set=charset

Specifies what character set to use when exporting data, and if the datasheet is not the default Latin1 character set, you must specify this option when exporting, otherwise the garbled problem will occur after you import the data again.

–disable-keys

Tell mysqldump to add/*!40000 ALTER table Table DISABLE KEYS at the beginning and end of the INSERT statement. and/*!40000 ALTER table table to ENABLE the KEYS * *; statement, which greatly increases the speed of the INSERT statement because it rebuilds the index after all the data has been inserted. This option is only suitable for MyISAM tables.

–extended-insert = True|false

By default, Mysqldump opens the –complete-insert mode, so if you don't want to use it, use this option to set its value to false.

–hex-blob

Exports a binary string field using hexadecimal format. You must use this option if you have binary data. The field types that are affected are BINARY, VARBINARY, and blobs.

–lock-all-tables,-x

Commit the request to lock all tables in all databases before starting the export to ensure data consistency. This is a global read lock and automatically turns off the –single-transaction and –lock-tables options.

–lock-tables

Similar to –lock-all-tables, it locks the currently exported datasheet instead of locking the table under all the libraries at once. This option applies only to MyISAM tables, and if the Innodb table can use the –single-transaction option.

–no-create-info,-t

Exports only data without adding a CREATE TABLE statement.

–no-data,-d

Does not export any data, only the database table structure is exported.

–opt

This is just a shortcut option, 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 quickly export data, 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.

–quick,-q

This option is useful when exporting large tables, forcing mysqldump to obtain records directly from a server query instead of having all the records cached in memory.

–routines,-r

Export stored procedures and custom functions.

–single-transaction

This option submits a BEGIN SQL statement before the data is exported, and the begin does not block any applications and guarantees the consistency state of the database at the time of export. It applies only to transaction tables, such as InnoDB and BDB.

This option and the –lock-tables option are mutually exclusive, because lock tables will cause any pending transactions to be implicitly committed.

To export a large table, you should use the –quick option in combination.

–triggers

The trigger is also exported. This option is enabled by default, and it is disabled with –skip-triggers.

For additional parameter details please refer to 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 an online backup, you can also use the –master-data parameter to implement the following:

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 simply requests the lock table at the very first moment, then refreshes the binlog, and then adds the change MASTER statement to the exported file to specify the Binlog location of the current backup, which can be done if you want to restore the file to slave.

1.2 Restore

A file backed up with Mysqldump is a SQL script that can be poured directly, and there are two ways to import the data.

Directly with MySQL client

For example:

The code is as follows	Copy Code
/usr/local/mysql/bin/mysql-uyejr-pyejr Db_name < Db_name.sql

Using the SOURCE syntax

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

The code is as follows	Copy Code
Source/tmp/db_name.sql;

Here you need to specify the absolute path to the file, and it must be a file that mysqld run the user (for example, nobody) has permission to read