[SQL] using mysqlbinlog for incremental backup and recovery in MySQL _ MySQL-mysql tutorial

[SQL] MySQL uses mysqlbinlog for incremental backup and recovery details bitsCN.com

[SQL] MySQL uses mysqlbinlog for incremental backup and recovery

Incremental Backup process

(1) configure the My. cnf file

(Configure the my. ini file in Windows) to enable binary backup:

[SQL]

Log-bin = D:/Program Files (x86)/MySQL/mylog/juelog

Specify the file to which the backup file is stored.

(2) restart the MySQL service.

After the restart, two binary configuration files will appear under the configured file directory.

Juelog. index indicates the backup file index and specifies which backup files are available. juelog.000001 indicates the backup file, which stores all user operations on the database.

The content in the index file juelog. index is as follows:

(3) automatic update of backup logs

When you perform operations on the database, the backup log is updated accordingly. This can be observed from the backup file time.

After detection, the backup file records the statements for creating tables, statements for Deleting tables, insert statements, delect statements, and update statements, but does not record select statements.

(4) view the backup log content

You can use the mysqlbinlog program that comes with MySQL to view the content in the backup file.

Go to the cmd console:

[SQL]

Path of mysqlbinlog backup file

To view the contents of the backup file.

We can see that MySQL records the time of each operation and assigns a position at the same time.

Therefore, we can restore database files by time or location.

(5) export recovery logs by time.

[SQL]

Mysqlbinlog -- start-datetime = "23:00:00" -- stop-datetime = "23:20:59" juelog.000001-r Test2. SQL

The preceding operations import all the operations from 23:00:00 to 23:20:59 into Test2. SQL.

After the statement is executed successfully, the Test2. SQL file is generated in the backup directory.

Among them, -- start-datetime and -- stop-datetime are optional.

The same is true for exporting logs by location.

(6) restore by location:

To verify the restoration effect, we can clear the table first.

[SQL]

D:/Program Files (x86)/MySQL/mylog> mysqlbinlog -- stop-position = "102" juelog.0000

01 | mysql-uroot-p

Enter password :***

The table content can be restored.

The same is true for time-based recovery.

(7) automatically delete backup logs.

Because the backup log occupies a lot of hard disk space, we need to regularly delete the backup log.

You can set the EXPIRE_LOGS_DAYS parameter in the my. cnf File (my. ini file configured in Windows.

For example:

[SQL]

EXPIRE_LOGS_DAYS = 7

The backup log is automatically deleted after 7 days.

(8) enable binlog

[SQL]

Mysql-hlocalhost-uroot-pjue-e "set global SQL _log_bin = 1 ";

Mysql-hlocalhost-uroot-pjue-e "show global variables like 'SQL _ log_bin'/G ";

Execution result:

[SQL]

* *************************** 1. row ***************************

Variable_name: SQL _log_bin

Value: ON

(9) disable binlog

[SQL]

Mysql-hlocalhost-uroot-pjue-e "set global SQL _log_bin = 0 ";

Mysql-hlocalhost-uroot-pjue-e "show global variables like 'SQL _ log_bin'/G ";

Execution result:

[SQL]

* *************************** 1. row ***************************

Variable_name: SQL _log_bin

Value: OFF

Summary

The Mysql database automatically records user operations on the mysql database in binary format to the backup file.

You can use backup files to recover data when you want to recover the data.

The backup file records the statements for creating tables, statements for Deleting tables, insert statements, delect statements, and update statements, but does not record select statements.

The incremental backup records include:

(1) operation statement itself.

(2) operation time.

(3) operation location.

BitsCN.com