GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

RBR and recutting databases

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • RBR and recutting databases

    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.

    Comment


    • #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

      Comment


      • #4
        I usually just shutdown the slave and copy all config, data and relay logs.

        Because it's too easy to mess up the:

        FLUSH TABLES WITH READ LOCK;-- andSHOW SLAVE STATUS;

        To get the latest correct Exec_Master_Log_Pos.

        It's very easy to run the flush and then the lock is accidentally released before you manage to get the information from the SHOW SLAVE STATUS;. Which means that the position you get from SHOW SLAVE STATUS is further on than the state of your data.
        Which means that you have a chunk of relay operations missing on the slave, which will give you the exact error as you got when it tries to modify/delete records that where inserted/updated/deleted in the missing chunk.

        Comment

        Working...
        X