MySQL Master - Master Broken

Question

I've Inherited a Mysql master master system, I've noticed the second master (lets call it slave from now on as it's running on a 'slave' machine) stopped getting its db's updated. I saw that

Master:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Slave: (with an error I truncated)

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '3' for key 'PRIMARY'' on [...]

I don't know what caused it to process considering we cant get duplicate there. What's important is to resume normal operations;

Right now I've stop slave; on the Master and stop slave; on the Slave because I saw that if I change records on the Slave the changes Do Get Propagated to Master which is in active use.

How do I: Force sync EVERYTHING from master to slave without affecting data on master? Then hopefully have slave pickup replication as usual?

UPDATE OK I Tried deleting all tables on slave then it complained in that error section that the 'table' doesnt exist. So i made a no data dump of Master, and made sure I have only empty tables in Secondary (slave). I start slave; on slave BUT now it's complaining about bloody alter table statements for instance:

Last_Errno: 1060
Last_Error: Error 'Duplicate column name [...] Query: 'ALTER TABLE [...]

How to skip the fracking alter statements I just want to replicate the bloody data and be done with it, my tables have the lates changes already FFS and now its complaining about changes made after the replication seized weeks ago

How do I reset the log or something?

OUTSTANDING Why would this start happening? The "Secondary" is propagating to "Primary". "Primary" is not propagating to "Secondary". But any fixes I tried to do left it in the same state Yes-Yes Yes-No with same Last_Error. I think around that time the server was taken off the network, could that confuse MySQL in some way?

Answer 1

First you need to understand that MySQL replication only synchronizes the CHANGES. Emptying the tables (or deleting them) won't get the data replicated again. You have to start the slave with a consistent set of data files.

The rough process to re-seed your slave is as follows: (don't follow these instructions)

1. Get a consistent snapshot of your data.
2. Copy the snapshot to the slave.
3. Reconfigure and start slave.

If you can't tolerate downtime, then there are 2 ways to accomplish this.

• If you data is small enough that you can mysqldump everything including the data, then these instructions are good. If you have multiple databases, make sure you read the article as I'm not going to replicate those considerations here. But if you only have 1 database, the basic steps are:

  1. On the master, backup your database like this: mysqldump -u root -e -q --single-transaction --master-data database_name
  2. Import this backup in to your slave.
  3. Look at the beginning of the backup file to get the master log name and position.
  4. On the slave in the mysql shell, run something like this, but update with the info from step #3: START SLAVE UNTIL MASTER_LOG_FILE='bin.000029', MASTER_LOG_POS=651322976;

• If your databases are too large for mysql dump, then you will need to snapshot your tables using your OS's volume snapshot features. This will pause your MySQL server for a few seconds so it is best done after hours when it probably won't disturb anyone.

  1. On the master in the mysql shell, take a write lock on all tables to get them consistent using flush tables with write lock. This will effectively pause the server.
  2. Don't quit the mysql shell or you will lose your lock.
  3. In another OS shell, take a snapshot of the mysql data volume.
  4. Back in the mysql shell: SHOW MASTER STATUS to get the current log position, then UNLOCK TABLES to release the lock. Server is now unpaused.
  5. Access the snapshot and copy the mysql data files to the slave.
  6. On the slave in the mysql shell, run something like this, but update with the info from step #4: START SLAVE UNTIL MASTER_LOG_FILE='bin.000029', MASTER_LOG_POS=651322976;