Document directory
- Incremental Backup Process
- Summary
[Objective] Use mysqldump for full backup, and use the BINLOG function provided by MySQL to implement Incremental Backup
For ease of operation, we recommend that you enable two terminal commands, one for MySQL and the other for Linux statements.
Full backup process reference blog: http://blog.csdn.net/jueblog/article/details/9878191
Incremental Backup Process (1) configure the my. CNF File
(Configure the my. ini file in Windows) to enable binary backup:
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:
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.
mysqlbinlog --start-datetime="2013-08-11 23:00:00" --stop-datetime="2013-08-11 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.
D:\Program Files (x86)\MySQL\mylog>mysqlbinlog --stop-position="102" juelog.000001 | mysql -uroot -pEnter 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:
EXPIRE_LOGS_DAYS=7
The backup log is automatically deleted after 7 days.
(8) Enable BINLOG
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:
*************************** 1. row ***************************Variable_name: sql_log_bin Value: ON
(9) Disable BINLOG
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:
*************************** 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.