About a month ago, I set up a data warehouse for an old 4.1.21 system that can't be upgraded. I decided to use the latest 5.1 beta to take advantage of partitioning, but within the first day replication stopped rather suddenly and with a nondescript error:
[ERROR] Error running query, slave SQL thread aborted.Using mysqlbinlog, I compared the relay log and the masters binary log around that position, and noticed the relay log appeared corrupt! I don't recall the exact steps I took, but I isolated the problem to a LOAD DATA INFILE statement, and filed my first issue with MySQL Enterprise support.
Fix the problem, and restart the slave SQL thread with
"SLAVE START". We stopped at log 'xxx' position xxx
The great folks at MySQL Support had a response for me within 30 minutes: it is a known bug, documented at http://bugs.mysql.com/bug.php?id=31240. Since skipping a major version is not a recommended practice, fixing it wasn't a priority for them. Thankfully there is a fairly easy workaround - create a "blackhole relay" to translate the logs into a format 5.1 can read.
In essence, since 5.0 can read logs from 4.1, and 5.1 can read from 5.0, stick a 5.0 server in the middle and save on resources by converting all its tables to the blackhole engine. The steps to do this are pretty simple, and the hardest [optional] part is running two versions of mysql on the same server side by side. mysqld_multi does a good job at managing multiple instances, and since that is fairly well documented - and it would work just as well to have the relay on it's own box - I'll skip the details of my setup and get straight to the replication configuration.
- Use your preferred method to initialize the data on the new 5.1 slave, but do not START SLAVE yet. You will need the initial replication position later, however.
- Use mysqldump --nodata to get just the DDL from the 4.1 master. Load this into the 5.0 server.
- Convert the tables on the 5.0 relay that you want to replicate to the blackhole engine. Of course, don't do this to the mysql.* tables. I used a cheesy bash script:
for t in `mysql $SCHEMA -NBre 'show tables'`; do
echo "alter table $SCHEMA.$t engine=blackhole;" >> runme.sql;
cat runme.sql | mysql
- Tell the blackhole relay to read from your 4.1 master at the point-in-time that you initialized the 5.1 slave. If you created the 5.1 slave by using "mysqldump --master-data --single-transaction", then the positions you want can be read from the first few lines of the output of mysqldump.
- Set the 5.1 slave to read from the blackhole relay, making sure that it does not read all those ALTER statements. I suggest a quick RESET MASTER on the relay.
- Start replication on both the 5.0 and 5.1 servers!
I've been running this configuration for over a month now with no problems :)