Mysqldump the correct posture for logical backups

In an in-depth study of the MySQL command-line tool in the previous article, we mysqldump the parameters and fundamentals of mysqldump. So how do we best use it? What kind of pits does it have?

1. Use mysqldump for logical backup

1) Full Logical backup:

Mysqldump-uxxx-p--flush-logs--delete-master-logs--all-databases > Alldb.sql (Full-time every night)

2) Incremental Backup:

Mysqladmin flush-logs (Brush every hour , save, make an incremental backup)

3) Disadvantages:

1>--all-databases contains the MySQL database, which contains the permissions of the data, so we should add--flush-privileges, when the restoration, the permissions to take effect;

Note--all-databases includes the MySQL database, but does not include the INFORMATION_SCHEMA and Performance_schema two databases.

2> because--lock-tables is enabled by default for mysqldump, it causes read locks on all tables during backup: Lock table TB Read local, so all update,delete statements

will be blocked. However, the SELECT statement and the INSERT statement are not blocked.

After the 3>--delete-master-logs backup, the purge logs to statement is executed. The binary log on master after the backup was deleted. Generally speaking, we do not recommend deleting binary log randomly.

We should save them instead of deleting them directly. Just in case, leave a retreat.

4> The backup method, although the lock table TB read Local is held during the entire backup process, the INSERT statement can still be executed. So the result is not a consistent backup . Although not the

Consistent backup, but since flush log, all operations are also credited to the new binary log, so if all new binary logs are used for full recovery, the last recovered data

is consistent as well . Of course, inconsistent backups cannot be used to build slave.

If you want a consistent backup, you need to use--lock-all-tables or use the--single-transaction option. The former uses a global read lock and does not allow any modification operations. The latter uses

The transactional nature of the transaction to get a consistent backup.

So we should improve the backup method above.

2. Best posture for using mysqldump backup

1) Optimize locks and get consistent backups:

We can use the combination of--single-transaction 、--master-data=2 、--flush-logs to achieve a significant reduction in lockout time. At the same time there is a consistent backup, and the consistency of the backup and flush log is consistent;

2) Remove the--delete-master-logs option, and then, after the backup, move all the flushed binary logs to a single place and save them;

3) because the--single-transaction option is used, only the INNODB data is available, but the MySQL data is the MyISAM engine, so we'd better separate the backup of the MySQL database,

It also operates specifically for the MySQL database. Of course do not separate to backup, probably also no problem.

4) also add--routines to back up stored procedures and functions, and triggers are backed up by default.

After optimization, we get:

Mysqldump-uxxx-p--singel-transaction--master-data=2--flush-logs--routines--databases db1 DB2 db3 > Alldb.sql;

Mysqldump-uxxx-p--databases mysql > mysql.sql;

How to back up MySQL together:

Mysqldump-uxxx-p--singel-transaction--master-data=2--flush-logs--routines--all-databases > alldb.sql;

3. Use mysqldump to build slave environment

Place

4. Use the results of the mysqldump backup to recover

Scattered places

Mysqldump the correct posture for logical backups