Mysql Incremental Backup and point in time recovery

Question

I'm writing a shell script using the concept of binary logs for point in time recovery.

Now, the scenario is such that a full-backup will happen every alternate day and Incremental backup should be done in between.
Suppose full-backup happens on Monday and Wednesday, incremental backup should happen on Tuesday.
For point in time recovery, I'm using mysqlbinlog --starttime --stoptime binary-logs > backup.sql
In the shell script I need to use starttime and log position of the previous full-backup to specify the log files in the statement, how can I retrieve this in a shell script and record the changes done during that time.
Is there anyway I can do this, please help me in this regard.

• Michael - sqlbot: How will you be making your "incremental" backup?
• Rudra: @Michael-sqlbot Incrementalbackup happens using mysqlbinlog. The log files and start time and end time are calculated from last backup.

Answer 1

You can use https://sourceforge.net/projects/mysqlincrementalbackup/ script. Description from its website:

A complete incremental backup for MyISAM and InnodB in a mix environment for those applications use both of engines simultaneously using binary logs and a method that does not affect running database. There is no need to stop or lock the database, It does utilize only binary logs to extract update queries of databases. This tools uses automysqlbackup script as part of solution for its full backup.

Answer 2

It seems to me that it’s more correct not to try to find out the start date for the --starttime parameter, but simply to submit the first binlog that appeared right after the full backup that was created.

To do this, you have to create a full backup in the correct way.

A full backup should be created using the following parameters:

mysqldump --flush-log --master-data=2

--flush-log - creates a new binary log consistent with the data in the full backup (it should be restored right after the full backup).

--master-data=2 - writes the name of the binlog file that was created by the --flush-log option at the very beginning of the full backup. The dump will show a line similar to this:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.002927', MASTER_LOG_POS=120;

Thus, before restoring, you simply open a full backup, check and restore only those binlogs whose numbers are equal or higher than specified in the CHANGE MASTER TO... line.

Here are a couple of articles on how to backup and restore incremental MySQL backups on Windows and on Linux