6.3 Restoring a Master Database in Replication

Let us assume a master database gets corrupt.

  1. Use the backup of the master database, do ibbackup --apply-log yourbackupmy.cnf and put the ibdata and ib_logfile files to the right places. Then put the .frm files to the right place. Let us assume you have a tar file of the .frm files: cd mysqldatadir; tar xvf yourtarfile

  2. Edit the master my.cnf file so that you comment out log-bin in it so that the slaves do not receive twice the binlog needed to recover the master.

  3. Replication in the slaves must be stopped temporarily when you do the piping of the binlog to the master. In the slaves, do

    mysql> STOP SLAVE;
  4. Start the master mysqld on the restored backup:

    $ mysqld
    …
    InnoDB: Doing recovery: scanned up to log sequence number 0 64300044
    InnoDB: Last MySQL binlog file position 0 5585832, file name
    ./omnibook-bin.002

    InnoDB printed the binlog file and position it was able to recover to.

  5. Next you should pipe the remaining binlog files to the restored backup:

    $ mysqlbinlog --start-position=5585832mysqldatadir/omnibook-bin.002 | mysql
    $ mysqlbinlog /mysqldatadir/omnibook-bin.003 | mysql
    
  6. The master database is now recovered. Shut down the master and edit my.cnf to uncomment log-bin.

  7. Start the master again.

  8. Start replication in the slaves again:

    mysql> START SLAVE;