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

Source: Internet
Author: User
[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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.