1

I am facing some issue with PITR restore using mysqlbinlog . It is failing with the following error . 

 # mysqlbinlog mysql-bin.000035 | mysql -u root -p db
 Enter password:
 ERROR 1032 (HY000) at line 48: Can't find record in 'dlfilerank'

dlfilerank is used by Liferay CMS App and the total records for this table keeps on changing when Liferay Portal is being accessed.

Following are the latest few binary log files generated by MySQL in my DB Server. 

-rw-rw---- 1 mysql mysql 1073742407 Dec 27 18:26 /var/lib/mysql/mysql-bin.000033
-rw-rw---- 1 mysql mysql 1073742182 Feb  2 18:58 /var/lib/mysql/mysql-bin.000034
-rw-rw---- 1 mysql mysql        665 Feb  2 18:58 /var/lib/mysql/mysql-bin.index
-rw-rw---- 1 mysql mysql  422392027 Feb 18 15:50 /var/lib/mysql/mysql-bin.000035

From the above listing , we can see that the latest binary log (mysql-bin.0000350) was created around Feb 2,19:00 and all DB transactions from this timestamp onwards are being written to mysql-bin.000035

Now since latest binary log was created on Feb 2, , so I am considering full backup taken using mysqldumpon Feb 2. For full backup , we have an automated script using mysqldump which runs every midnight to take the backup.

So , to test restore for this setup , I am doing the following steps:

  1. Restore from full backup taken using mysqldump on Feb 2
  2. Next step , I execute mysqlbinlog generated on Feb 2 for PITR restore but it is failing with the error mentioned in original description.

Kindly suggest how to resolve the issue ? Do I need to manually generate a new binary log file before taking full backup .

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
Zama Ques
  • 173
  • 3
  • 7

1 Answers1

1

PROBLEM

You are starting from the wrong log. You should start with mysql-bin.000034 not mysql-bin.000035

The timestamp on each binlog shows the last time a binlog event was written to it

  • mysql-bin.000033 was last written Dec 27 18:26
  • mysql-bin.000034 was last written Feb 2 18:58. That's 18 hrs 58 min after Feb 2 00:00
  • mysql-bin.000035 had to be created Feb 2 18:58

Given the timestamps mentioned, mysql-bin.000034 is the log you need to start with.

SOLUTION

You should collect the binlog events from Feb 2 00:00

Please run the following

MYCHANGES=ChangesSinceFeb2Backup.sql
BINLOGS="mysql-bin.000034 mysql-bin.000035"
cd /var/lib/mysql
mysqlbinlog --start_datetime="2014-02-02 00:00:00" ${BINLOGS} > ${MYCHANGES}

Restore the Feb 2 mysqldump Backup, then play all the events

mysql -u root -p db < ${MYCHANGES}

This will bring back everything up to Feb 18 15:50

If you want to do the PITR up to today at 12:45, you do this 

MYCHANGES=ChangesSinceFeb2Backup.sql
cd /var/lib/mysql
mysqlbinlog --start_datetime="2014-02-02 00:00:00" --stop_datetime="2014-02-18 12:45:00" ${BINLOGS} > ${MYCHANGES}

SUGGESTION

Note that mysql-bin.000035 contains several days worth of binlog events.

Before you run the midnight backup, you should run

FLUSH BINARY LOGS;

That way, you can clearly identify what binary logs to use in PITR

Give it a Try !!!

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536