[SQL] MySQL uses mysqlbinlog for Incremental backup and recovery

[SQL] MySQL uses mysqlbinlog for Incremental backup and recovery detailed explanation of the Incremental backup process (1) Configure My. cnf file (my. INI file), enable binary backup: [SQL] log-bin = D:/Program Files (x86)/MySQL/mylog/juelog here to 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 operations are performed on the database, the backup logs are 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) to view the backup log Content, you can use the mysqlbinlog program of MySQL to view the content in the backup file. Log on to the cmd console and view the contents of the backup file in the [SQL] mysqlbinlog backup file path. 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 = "2013-08-11 23:00:00" -- stop-datetime = "2013-08-11 23:20:59" juelog.000001-r Test2. SQL above all operations from 23:00:00 to 23:20:59 import it to 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 position: 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, if [SQL] EXPIRE_LOGS_DAYS = 7, the backup log that exceeds 7 days is automatically deleted. (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 log [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 Mysql database automatically records user operations on 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 record includes: (1) operation statement itself. (2) operation time. (3) operation location.