Implementing MySQL logical Backup and recovery using Mysqlbump

Mysqldump as an important MySQL backup tool, the functionality is quite powerful. Backup parameters, recovery strategies, need to be studied carefully.

(1) Basic grammar

To back up a single database or a specified table in a single database:

mysqldump [OPTIONS] database [tables]

Example:

1. Backing up a single database mysql:mysqldump-uroot-p mysql > Mysql.sql

2. Backing up a single database MySQL's specified table user:mysqldump-uroot-p mysql user > Mysql.user.sql

To back up multiple databases:

mysqldump [Options]--databases [options] DB1 [DB2 DB3 ...]

Example:

1. Back up multiple databases world, Imkh:mysqldump-uroot-p--databases World imkh > World+imkh.sql

Back Up all databases:

mysqldump [Options]--all-databases [options]

Example:

1. Back up all databases: Mysqldump-uroot-p--all-databases > All-databases.sql

(2) Description of options [options]

-default-character-set=charset

Specifies which character set to export the data in, and if the data table is not in the default Latin1 character set, you must specify this option when exporting, or you will have garbled problems after importing the data again.

Example:

mysqldump-uroot-p -- Default-character-set=utf8 Employees Employees>employees.sql

-lock-all-tables,-x

Before starting the export, the commit request locks 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.

-lock-tables

Similar to-lock-all-tables, but locks the currently exported data table instead of locking the table under all libraries. This option applies only to the MyISAM table, if the Innodb table is available with the-single-transaction option.

-single-transaction

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

-no-create-info,-t

Exports only data without adding a CREATE TABLE statement.

-no-data,-D

Exports only the database table structure and does not export any 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 can occur if you export a large database.

-quick,-q

This option is useful when exporting large tables, forcing mysqldump to cache records directly from the server query instead of getting all the records back into memory.

-routines,-r

Export stored procedures and custom functions.

This option and the-lock-tables option are mutually exclusive because lock tables causes any pending transactions to be implicitly committed.
To export large tables, you should use the-quick option together.

-triggers

Export the trigger at the same time. This option is enabled by default and is disabled with-skip-triggers.

2. Recovering a Database

MySQL implements a logical backup of the instance level, and you can see that the extracted SQL files are all the databases in the instance. Then perform the import operation of the database:

Mysql-hhostname-uusername-ppassword [DatabaseName] < Backupfile.sql