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 mysqldump
on 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:
- Restore from full backup taken using
mysqldump
onFeb 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 .
Best Answer
PROBLEM
You are starting from the wrong log. You should start with
mysql-bin.000034
notmysql-bin.000035
The timestamp on each binlog shows the last time a binlog event was written to it
mysql-bin.000033
was last writtenDec 27 18:26
mysql-bin.000034
was last writtenFeb 2 18:58
. That's 18 hrs 58 min afterFeb 2 00:00
mysql-bin.000035
had to be createdFeb 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
Restore the Feb 2 mysqldump Backup, then play all the events
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
SUGGESTION
Note that
mysql-bin.000035
contains several days worth of binlog events.Before you run the midnight backup, you should run
That way, you can clearly identify what binary logs to use in PITR
Give it a Try !!!