The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Does a script exist to roll forward from a backup restore to most recent

Discussion in 'Database Discussions' started by shumifan50, Oct 10, 2012.

  1. shumifan50

    shumifan50 Member

    Joined:
    Oct 4, 2012
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    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.

    Surely this is a problem faced by all mysql installations and should be solved somewhere.
     
    #1 shumifan50, Oct 10, 2012
    Last edited: Oct 11, 2012
  2. shumifan50

    shumifan50 Member

    Joined:
    Oct 4, 2012
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Re: Does a script exist to roll forward from a backup restore to most recen

    Refer Percona xtrabackup.
     
Loading...

Share This Page