[MySQL] using mysqldump to create a text backup _ MySQL

[MySQL] using mysqldump for text backup mysqldump

BitsCN.com

[MySQL] using mysqldump for text backup

When using mysqldump to back up MySQL, the backup file is a text file in SQL format, which consists of a series of create table and insert statements. When restoring, you only need to use the SQL file as the input of the mysql program, as shown below:

[Plain]

Mysqldump mydb mytbl> mytbl. SQL

Mysql mydb <mytbl. SQL

Note: Do not try to use mysqlimport to load the backup file generated by mysqldump! Mysqlimport can only read data rows and cannot be used to read SQL statements.

When you press mysqldump in the command line without adding any parameters, the following message is displayed:

[Plain]

[Root @ lx203 ~] # Mysqldump

Usage: mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS] -- databases [OPTIONS] DB1 [DB2 DB3. ..]

OR mysqldump [OPTIONS] -- all-databases [OPTIONS]

For more options, use mysqldump -- help

The above prompts provide three usage methods. we will introduce them one by one:

1) mysqldump [OPTIONS] database [tables]

This is the most common method of use. It provides a database name and one or more table names after it, indicating to export the specified tables in the database.

2) mysqldump [OPTIONS] -- databases [OPTIONS] DB1 [DB2 DB3. ..]

The second method uses the -- databases parameter, which exports all the tables in the listed Database

3) mysqldump [OPTIONS] -- all-databases [OPTIONS]

The -- all-databases parameter indicates to export all tables in all databases, including permission tables in the mysql database. therefore, be careful when using this parameter.

When you press mysqldump -- help in the command line, all parameters will appear. The following describes the main parameters:

1) -- opt

[Plain]

-- Opt Same as -- add-drop-table, -- add-locks, -- create-options,

-- Quick, -- extended-insert, -- lock-tables, -- set-charset,

And -- disable-keys. Enabled by default, disable

-- Skip-opt.

From what you can explain, we can know that -- opt will enable multiple options to speed up the backup process for optimization. it is started by default and is generally recommended to start it, the -- skip-opt option can be disabled.

However, note that the -- opt option adds a read lock to the backup table to speed up the backup, which will make it difficult for other users to modify. when the service is busy, do not enable this option for backup!

The following explains the options -- opt enabled:

-- Add-drop-table: add the drop table if exists statement before each create table statement;

-- Add-locks: indicates that the lock command is added before and after the generated insert statement;

-- Extended-insert: an insert statement that inserts multiple rows at a time.

-- Lock-tables: indicates that the read lock is added to the backup table;

2) -- single-transaction

When exporting innodb and Falcon tables, it is best to add this option to ensure a stable backup.

3) -- no-create-info -- no-data

As mentioned above, by default, the text file exported by mysqldump contains the create table and insert into statements. if -- no-create-info is added, the create table statement is not required. Similarly, -- no-data indicates that the insert into statement is not required.

4) -- routines -- triggers -- events

By default, only the trigger is included in the exported text file. if you need to export other objects, you can add corresponding options.

The three options also have the corresponding -- skip format, indicating to exclude it from the export file.

BitsCN.com