Data backup scheme for MySQL

MySQL is divided into logical and physical backups in the form of backup recovery

A logical backup is a backup SQL statement that performs a backup of the SQL statement at the time of recovery to realize the recurrence of the database data

Physical backup is the backup data file, the comparison image Point is the CP under the data file, but the real backup when the natural is not the CP so simple

These 2 kinds of backup have advantages and disadvantages, generally speaking, physical backup recovery faster, occupy space is relatively large, logical backup speed is relatively slow, occupy space is relatively small

Here are 3 common methods of filing

Mysqldump Tool Backup

Mysqldump is also the most commonly used backup tool for MySQL databases, since it is a MySQL-backed backup tool. Supports INNODB based hot backup. But because it's a logical backup, it's not fast enough for a scenario with a smaller amount of backup data.

Mysqldump full backup + binary log-> implementation Point-in-time recovery

Win Bei:

In the use of the MyISAM engine, you can only use warm backup, this time to prevent data writing, so first add read lock

At this point you can enter the database manually read the lock. This is more cumbersome, and there is a direct option to lock in the Mysqldump tool

Mysqldump--databases mydatabase--lock-all-tables--flush-logs>/tmp/backup-' Date +%f-%h-%m '. sql

If you are backing up a table, just add the table name to the database name.

Note here, to achieve point-in-time recovery, plus the--flush-logs option, after restoring using the backup file, and then restoring the point-in-time based on the binary log

The method of restoring time point

Mysqlbinlog mysql-bin.000000x >/tmp/pointtime.sql

Then you can import the SQL script with the MySQL command.

Standby

If you are using the InnoDB engine, you do not have to lock the database operation, plus an option for hot backup:--single-transaction

Mysqldump--databases mydb--single-transaction--flush-logs--master-data=2 >/tmp/backup-' Date +%F-%H-%M '. sql

Pay attention.

Close binary log at time of recovery

Mysql>set sql_log_bin=0;

Because this is based on logical backup, the SQL statement is inserted into the data when the log is recovered, and the log that inserts the data does not make sense when it is restored.

Based on LVM snapshot backup

In a physical backup, there is a physical backup based on the file system (a snapshot of LVM), or it can be packaged directly with commands such as tar. But these are only cold backups.

Different storage engines can be backed up differently, MyISAM can be backed up to the table level, and InnoDB can only back up the entire database without opening each table file.

The following is a brief introduction to using the LVM snapshot feature for backup

To safely apply read locks to the database first

Mysql>flush TABLES with READ LOCK;

Refresh binary log for point-in-time recovery