Mysql Incremental Backup

Note that if you accidentally delete a table and want to restore it, you must have the log for creating the table in the recovery log. Otherwise, you cannot reply to the table? That is, logs must be recorded when you create a table? At which point will mysql be recovered? 5.0 does not support Incremental Backup definitions ?? Mysql database performs binary

Note that if you accidentally delete a table and want to restore it, you must have the log for creating the table in the recovery log. Otherwise, you cannot reply to the table? That is, logs must be recorded when you create a table? At which point will mysql be recovered? 5.0 does Incremental Backup not support Incremental Backup definition? ? Mysql database performs binary

Note that if you accidentally delete a table and want to restore it, you must have the log for creating the table in the recovery log. Otherwise, you cannot reply to the table? That is, logs must be recorded when you create a table?

The location to which the data is recovered is calculated based on the location.

Mysql? 5.0 Incremental backup is not supported

Incremental Backup Definition

? ? The mysql database will automatically record user operations on the mysql database to the file in binary form. When the user wants to restore the database, he can use the backup file to reply.

? ? Incremental backup records (dml statements do not record select statements)

? ? Recorded (the time when the operation statement itself operates B? Position of the c Operation)

Instance

? ? Configure the my. ini file or my. conf to enable binary backup

??? Log-bin = e:/mysqldb/logbin. log? ? First, set the mysql Log storage location

? ? After the configuration is completed, the system starts up again. Will there be two more files in the backup directory?

? ? Logbin. index? What are the incremental backups of index files?

???? Logbin.000001? Store the user's database operation files

? ? If you want to see what is in it, we can use a tool under the bin directory of mysql to view it?

? ? Is mysqlbinlog.exe? What about this?

????

? ? Run cmd to enter the bin directory of mysql and then run

? ? Mysqlbinlog e:/mysqldb/logbin.000001

????

Mysql records the time for each of your operations and assigns you a position (pos) at the same time)

In other words, in the future, we can recover data by time point or by location.

For example, if we drop a table, we only need to restore the table to the drop?

Time Point recovery

? ? We can draw a flowchart.

? ? Logbin.000001

? ? Insert ......? 3? ? ? 2013-07-23 17:57:00

? ? Update .....? 4? ? 2013-07-23 17:59:00

????? Insert ......? 100? ? ? 2013-07-23 19:57:00

? ? Drop ?... ? ? ? ? 101? ? 2013-07-23 20:57:00

????

? ? This can be written for recovery.

? ? Mysqlbinlog? -- Stop-datatime = "20:50:00 "? E:/mysqldb/logbin.000001

? ? Which of the following statements is always restored? 2013-07-23 20:50:00? Stop

????? Mysqlbinlog? -- Start-datatime = "20:50:00 "? E:/mysqldb/logbin.000001

? ? Which of the following statements is from 20:50:00? Start recovery

Restore by location

??????? Mysqlbinlog? -- Stop-position = "100 "? E:/mysqldb/logbin.000001? |?

????? ? The preceding statement indicates that the data is restored to the 100 position.

Authenticity Restoration

Restore by location

Mysqlbinlog? -- Stop-position = 4590? E:/mysqldb/logbin.000001? | Mysql-uroot-p

Restore by Time

Mysqlbinlog? -- Stop-datetime = "2013-07-24 18:17:19 "? E:/mysqldb/logbin.000001? | Mysql-uroot-p

It can control the starting time and ending time.

Mysqlbinlog? -- Start-position = 4? -- Stop-position = 106 yueliangd
Ao_binglog.000001> c: \ test1.txt

Can I make a full backup every Monday at work, that is, mysqldump?

Enable Incremental backup to set the expiration time to 7 days

If the database crashes, it can be recovered by time and location.

How to disable mysql Automatic logging

Find log-bin = mysql-bin, and add # above.

Some versions need to be disabled

Binlog_format = add # Before mixed; otherwise, mysql cannot be started.

Author: Link to the original article of ebw123 published on 22:27:12

Read: 109 comments: 0 view comments

Original article address: mysql implements Incremental backup, thanks to the original author for sharing.