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:
|
1 |
[ERROR] While trying to obtain the list of slaves from the master 'x.x.x.x:3306', user 'xxx' got the following error:<br />'the master returned an invalid number of fields for SHOW SLAVE HOSTS' |
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!