October 11th, 2012, 05:28 AM
Does a script exist to roll forward from a backup restore to most recent
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 and how to synchronise the mysqldump and the subsequent binlog files.
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.
If I am going about this the wrong way, help in improving the approach will also be appreciated.
Surely this is a problem faced by all mysql installations.