Replicate MySQL 5.0 Master to 5.5 Slave?

Question

We're planning out a series of server/software upgrades and were curious.

We have a RHEL5 install running MySQL 5.0 that we'd like to replicate to a new (virtualized) server running CentOS 6 and MySQL 5.5. Then, in the future, we would bump the RHEL5 machine to CentOS6 and MySQL5.5. We want to get this replication going first if possible.

So the short question is, has anyone tried replicating a MySQL 5.0 master to a 5.5 slave?

Answer 1

I have done that dozens of times for my employer's clients. In fact, I just did this a week ago. The client was using MySQL 5.0.81 and had degraded performance in InnoDB.

• I create a new DB Cluster
  • running MySQL 5.5.27
  • Two DB Servers (ServerA and ServerB)
  • Configure InnoDB for Multicore Engagement
• Master/Slave (ServerA Master and ServerB Slave)
• I enable binary logging and log_slave_updates on Production's Slave
• mysqldump all databases (except mysql schema) from Production's Slave to /root/MySQLData.sql
• run pt-show-grants on Production's Master to /root/MySQLGrants.sql
• load ServerA with /root/MySQLGrants.sql
• load ServerA with /root/MySQLData.sql
• I replicate from Production's Slave (MySQL 5.0.81) to ServerA (MySQL 5.5.27)

All that was left to do was

• Have the client shutdown the app
• Remove DBVIP from Master running MySQL 5.0.81
• Bring up DBVIP on Master running MySQL 5.5.27
• Have the client start up the app

CONCLUSION

MySQL 5.5 can handle replication from previous major releases, and not the other way around.

I have discussed this before

• Feb 04, 2011 : MySQL master binlog corruption
• Jun 09, 2011 : Yum update mysql from 5.1 to 5.5
• Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?

GOOD NEWS !!!

I already answered a post back on Feb 06, 2012 ( How to setup replication(Master/slave) in MySQL 5.5.20? ) with all the steps you need.

BTW with regards to the step about dumping the MYSQL grants as SQL commands, here is my personal emulation of pt-show-grants:

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Give it a Try !!!

UPDATE 2012-11-27 11:07 EDT

I wanted to add additional posts I made for setting up Circular Replication should you decide to setup the two DB servers as Master/Master

• Sep 24, 2012 : Setting up MySQL circular replication in existing replication topology?
• May 07, 2012 : Setting Circular Replication in mysql