Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 .

share|improve this question
    
"PITR restore"? Is that a point-in-time restore restore? Like an ATM machine or the redundant department of redundancy? :-) –  Aaron Bertrand Oct 15 at 17:46

1 Answer 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 !!!

share|improve this answer
    
mysqldump --start_datetime="2014-02-02 00:00:00" I think you are referring to mysqlbinlog –  Zama Ques Feb 19 at 5:52
    
It is still failing for me . Now with the following error # mysqlbinlog mysql-bin.000034 | mysql -u root -p asdb ERROR 1062 (23000) at line 47: Duplicate entry '768522' for key 'PRIMARY' The issue is not because of 'mysqlbinlog' problem but because of inconsistent data in 'dlfilerank' table as mentioned in original description . I will raise a separate question for this issue –  Zama Ques Feb 19 at 10:27
    
Did you reload he Feb 2 backup before trying my solution ? –  RolandoMySQLDBA Feb 19 at 15:55
    
yeah...I reloaded Feb 2 backup before trying your solution –  Zama Ques Feb 20 at 5:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.