Simple usage of mysqldump and its principle

mysqldump
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
Recovery:
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