I'm trying to figure out the quickest way to recover from a replication error.
The production site replicates to a DR site. There are two databases at each site. So prod_master replicates to prod_slave, and then prod_slave replicates to dr_master, which in turn replicates to dr_slave. We also have a test system which replicates from dr_master. I am very reluctant to stop the replication slave on dr_master without a good idea of what is going to happen to the various down stream servers. However, I just discovered that there is a replication error on dr_slave:
Last_Errno: 126 Last_Error: Error 'Incorrect key file for table ...
The insert query includes a date, and replication hasn't been working for around 2 months.
I know the "approved" process is to:
- stop the slave on dr_master
- lock the tables
- backup the database
- note the master position
- unlock the tables
- restore the backup on dr_slave
- reset the position on the slave
- restart replication
There's about 70 GB of data in the database, so I'm looking for an alternative to this, if one exists. The backup would probably take a couple of hours.
I have looked at pt-table-sync, but haven't used it before and I don't know how I could set up a testing environment to get familiar with it.
Are there any alternatives to the lock-backup-restore process?