Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Pls check question again I've tried making it sync data only but it wants to sync the bloody alter events as well –  Recc Dec 19 '12 at 18:08
    
Mind you, when I do dumps and imports I don't do the mysql database in order not to lockout the replication user, or at least that's my assumption that will happen. –  Recc Dec 19 '12 at 18:11
    
Do you know how to use filesystem snapshots on your server OS? –  longneck Dec 19 '12 at 18:13
    
No but it's a regular debian (the servers are in a primary-secondary configuration my "second MYSQL master" is on Seconday hence my interchanging terminology above). I'm sure i can get someone to do it but I dont think that'll be necessary im sure there are still options within mysql –  Recc Dec 19 '12 at 18:20
1  
Please, don't fiddle around yourself, but use the Percona Toolkit and the pt-table-sync to get your replication back in sync. –  gertvdijk Dec 19 '12 at 20:06
show 4 more comments

1 Answer

up vote 1 down vote accepted

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;
share|improve this answer
    
This sounds good, thanks a lot, will try tomorrow and come back here to update. I can make a dump of everything. But if it does only the changes.. I didn't know that I must create new tables on both instances then –  Recc Dec 19 '12 at 19:35
    
Yeah this is more for master -> slave relation rather. What could be tried is perhaps wipe both servers data + bin logs; reimport a dump on one and replicate to the other? –  Recc Dec 20 '12 at 9:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.