Simple usage of mysqldump and its principle

Source: Internet
Author: User

Production Backup:
Build Master-Slave: mysqldump-a-b-r--master-data=2 >backup.sql

Backup library (contains build-up statement): Mysqldump-b database name--single-transaction >db.sql

Backing up a library: (does not include a build statement): mysqldump db_name--single-transaction >db_date.sql

Data and structure of backup table: Mysqldump-u Username-ppassword database--tables table_name1 table_name2 table_name3 >create_date.sql

Backup the structure of the specified table: mysqldump-u username-ppassword-d database--tables table_name1 table_name2 table_name3 >create_table.sql

Backing up data for a specified table: mysqldump-u username-ppassword-t database--tables table_name1 table_name2 table_name3 >table_data.sql

In the database, except for some tables, the remaining tables need to be exported
Mysqldump-h ip-u Username-ppassword--default-character-set=utf8
--database database_name--ignore-table=database_name.table_name1
--ignore-table=database_name.table_name2--ignore-table=database_name.table_name3 >ignore_table.sql
Good server, database
SOURCE Backup.sql;

Mysqldump principle: (You can open the general log at dump, observe the output of the log)

1. The essence of mysqldump is to get the data of the table through the SELECT * from tab.

2. Start TRANSACTION/*!40100 with consistent SNAPSHOT * * must be placed between the flush TABLES with READ lock and unlock TABLES, which will cause the start TR The DML statements executed between the Ansaction/*!40100 with consistent SNAPSHOT * * and flush TABLES with READ lock are lost and placed behind, causing the data to be repeatedly inserted from the library.

3. mysqldump only suitable for low-peak business, if the backup process of data operation is very frequent, will cause the undo table space is more and more large, undo table space By default is placed in the shared table space, and the Ibdata feature is that once increased, will not shrink.

4. Mysqldump efficiency is still relatively low, START TRANSACTION/*!40100 with consistent SNAPSHOT * * can only wait until all tables are finished after backup, in fact, the high efficiency is to back up a table to submit once, This frees up the space taken up by the undo table space Snapshot as soon as possible. However, you cannot implement a consistent backup of all tables.

Simple usage of mysqldump and its principle

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: 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.