RBR and recutting databases

MySQL & MariaDB Other MySQL® Questions
1

Hi there,

I am new to using RBR and am having problems recutting a database that uses it.

Previously, with statement based replication, we would just stop another slave database at a know slave position, take a snapshot of the disk pack and then mount this snapshot up on the machine I am recutting and copy all the data over. We’ve never had any problems using this method.

I have trying to do this on a large-enough-to-be-annoying (700GB) database, but when I have copied all the data over and put it back into replication I get a whole bunch of errors like so:

Last_Error: Could not execute Update_rows event on table dbJbossQueue.JBM_MSG_REF; Can’t find record in ‘JBM_MSG_REF’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log vmbu04-bin.002645, end_log_pos 32580193

They are not all updates, the errors happen with Delete’s as well. I have also done the cut twice to try and eliminate me messing up.

I am starting to wonder whether it is possible to recut a database that uses RBR using this method, or whether I need to use mysqldump or similar instead?

p.s. I am using Percona-Server-5.5.13-rel20.4-138.Linux.x86_64 and XtraDB

Thanks,
Josh

2

Are you absolutely sure that you stopped the slave that you where copying from? Since it still sounds like you didn’t get a proper snapshot.

Are you sure that you got all data, all relay-logs and the master.info and the relay-log.info file at the same time while the slave was shut down?

If you didn’t then I recommend to shut down mysql on the slave you are copying from to be absolutely sure that you get a consistent snapshot.

3

I did shut down MySQL but didn’t take the relay logs, I have never needed to grab them in the past however, since you just start up replication at the appropriate Exec_Master_Log_Pos from a show slave status.

Why would it need the relay logs?

(I should add that I am not putting this in replication with the server I am cutting from, but that servers master).

I’ve had to do it using mysqldump for now since I couldn’t leave the database out of service any more but I am going to do some testing in our staging environment.

I’ll give it a bash with the logs

Ta
Josh