MySQLdump Incremental backup, full backup, and recovery (1)

It is important to back up your database when database tables are lost or damaged. In the event of a system crash, you must be able to restore as much data as possible from your table to the State at the time of the crash. Scenario: perform a full backup every Sunday, and execute the MySQLdump Incremental backup at one o'clock P.M. every day.

MySQLdump Incremental Backup configuration

The precondition for performing Incremental backup is that MySQL enables the log-bin switch, for example, adding

Log-bin =/opt/Data/MySQL-bin

The string after "log-bin =" is the log recording directory. It is generally recommended that it be placed on a disk different from the MySQL data directory.

MySQLdump Incremental Backup

Assume that a full backup is performed at one o'clock P.M. on Sunday, which is applicable to the MyISAM storage engine.

MySQLdump-lock-all-tables-flush-logs-master-data = 2-u root-p test> backup_sunday_1_PM. SQL

Replace-lock-all-tables with-single-transaction for InnoDB
Flush-logs generates a new log file to end the current log.
The master-data = 2 Option records the name of the new log file after full backup in the output SQL statement,

For future recovery reference, for example, the output backup SQL file contains:

Change master to MASTER_LOG_FILE = 'mysql-bin.000002 ', MASTER_LOG_POS = 106;

Other descriptions of MySQLdump Incremental Backup:

If-delete-master-logs is added to MySQLdump, the previous logs are cleared to free up space. However, if the server is configured as an image copy master server, it is dangerous to use MySQLdump-delete-master-logs to delete the MySQL binary log, because the slave server may not completely process the binary log. In this case, it is safer to use the purge master logs.

Create a log by using MySQLadmin flush-logs on a daily basis and end the previous log writing process. And back up the previous log, such as the log file MySQL-bin.000002 under the data directory in the previous example ,...