MySQL SLAVE, obey your MASTER!

May 29, 2007 8:29 AM

MySQL, why won't you replicate properly?

Replication doesn't work.

We set up replication on two windows machines running mysql, got everything all synced up, set up the master/slave relationship, and everything worked fine for about two days.

Then it just stops... and the slave doesn't want to catch up with the master. Basic tests seem to indicate that the connection is still live -- we can still see the master's status from the slave, but it won't just grab all the data it needs to be current.

Here are the mysql servers:
master: 4.1 (NT)
slave: 5.0 (Advanced Server 2003)

What can we do to diagnose the problem?

posted by ph00dz to Computers & Internet (6 answers total)

I know something of MySQL server. FWIW, replicating from a lower-version master to a higher-version slave is totally fine. That's supported.

My first question would be, what are the thread states?

How many "host_name-relay-bin.nnnnnn" files do you have?

I assume you've read these sections of the manual: Replication Implementation Details.
posted by cmiller at 9:05 AM on May 29, 2007

MySQL logs replication problems into the server log on the slave. Check that log.

Also, please share the output of SHOW MASTER STATUS on the master and SHOW SLAVE STATUS on the slave with us -- otherwise it's all crazy guessing.

Mixed-version replication is always slightly hazardous, though, in either direction.
posted by mendel at 9:16 AM on May 29, 2007

Response by poster: When I run show processlist the relevant line is:

Command: Binlog Dump
Status: Has sent all binlog to slave; waiting for binlog to be updated


There are 7 of those bin files in the data dir on the slave.
posted by ph00dz at 9:17 AM on May 29, 2007

Response by poster: Here's the master status:
mysql> show master status \G;
*************************** 1. row ***************************
File: td_server-bin.000003
Position: 960993582
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.04 sec)


Here's the slave status:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.102
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: td_server-bin.000003
Read_Master_Log_Pos: 960986863
Relay_Log_File: tdesigns_backup-relay-bin.000002
Relay_Log_Pos: 54975987
Relay_Master_Log_File: td_server-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: traffic
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '216555' for key 1' on query. Default database: 'traffic'. Query: 'INSERT INTO td_lookuplog (id, dateAdded, epid, cuid, ipAddress) VALUES (', NOW(), '1262', '2057113', '192.168.2.136')'
Skip_Counter: 0
Exec_Master_Log_Pos: 544987956
Relay_Log_Space: 475701546
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)




... so, the question is -- does that error kill things?
posted by ph00dz at 9:22 AM on May 29, 2007

Best answer: looks like it. Did you try to slurp in the same binlog twice somehow?
posted by jenkinsEar at 9:26 AM on May 29, 2007

Response by poster: Sweet. Deleted the offending row and everything got all synced back up.

Yay! Thanks!
posted by ph00dz at 9:39 AM on May 29, 2007