MySQL Forums
Forum List  »  Install & Repo

Does a script exist to roll forward from a backup restore to most recent
Posted by: Gerhard Smith
Date: October 10, 2012 07:11AM

I have found many suggestions on the web to take mysqldump backups of a database, many of which will work with some caveats. However, I have found nothing that helps with rolling forward a database from the point at which one of these backups are restored with the most recent updates(rolled forward) using the log_bin files. The Mysql website has an overview of how to do it, but it is very convoluted and not something I would like to go through when my database is down.
Do any such scripts exist and if so where can I find one?

Please note that I am not just after applying the backup, that is easy, I am more interested in the roll forward.

I understand the following to be the process to enable roll forward. Any input from mysql experts will be greatly appreciated.

ENSURE that mysql is started with log_bin enabled

As much as I can work out the following steps are required to keep track of what is going on:
1. To make sure you have a consistent backup(using mysqldump), stop mysql.
2. Archive all the existing log_bin files(default mysql-bin.*) to an archive directory and also copy the most recent mysqldump files to that directory(in case you need to go back more than one mysqldump).
3. Take the mysqldump to current dump directory.
4. Restart mysql server.

If you need to restore mysql:
1. Stop mysql if not already stopped to guarantee consistency.
2. Restore the mysqldump backup in the current dump directory (assuming you dont need to go back further).
3. Using mysqlbinlog do: mysqlbinlog <log1> <log2> <log3> ..... | mysql -u<user> -p
Mysql recommend processing the logfiles in one instance of mysqlbinlog to ensure consistency. <log1>etc are the logs in the current log directory(or in the same as the mysqldump file restored). If a previous(older) mysqldump is restored, all logfiles in all subsequent archive directories have to be processed as well.
4. Restart mysql

Any comments welcome.

Edited 1 time(s). Last edit at 10/10/2012 07:27AM by Gerhard Smith.

Options: ReplyQuote

Written By
Does a script exist to roll forward from a backup restore to most recent
October 10, 2012 07:11AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.