MySQL replication gotchas

We recently rolled out a MySQL master/slave replication solution (upgrading from version 5.0 to 5.5 along the way), and we ran into a few unexpected surprises that I thought I’d share.

 

Version compatibility

We had a need to replicate from our new 5.5 server to an older 5.0 server (as a part of our rollback strategy).  It is possible to replicate between 5.5 and 5.0, even with 5.5 as the master and 5.0 as the slave.  However, you have to watch your point releases.  We were using 5.0.45, and when we issued the “START SLAVE” command, we’d get this error in the log:

An upgrade to 5.0.77 fixed the problem.

 

It turns out that some versions of 5.0 expected 5 fields in the SHOW SLAVE HOSTS results.  At some point (MySQL 5.4?), the fifth field was removed, and this change must have been backported to the 5.0 tree.

Temp tables

Be very careful about stopping the slave MySQL process.  If you do that while a temp table is open, you may find it very difficult to resume replication.  Read the relevant manual pages carefully!

Leave a Reply

Your email address will not be published. Required fields are marked *