Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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

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

share|improve this answer
    
Would the steps change much in my case where there is currently only a production master, there are no slaves or second masters? Thank you for this great writeup Rolando, it is very appreciated! –  xref Nov 26 '12 at 23:13
    
Do you have binary logging enabled on the production master right now? (If you do not know, login to mysql and run SHOW MASTER STATUS;. If you get Empty set, then it is off. –  RolandoMySQLDBA Nov 26 '12 at 23:15
    
Can you tolerate downtime ??? –  RolandoMySQLDBA Nov 26 '12 at 23:21
    
Is all you data InnoDB ??? –  RolandoMySQLDBA Nov 26 '12 at 23:21
    
empty set, and I see everything in Replication/Backup settings is commented out except 'server-id=1'. Also yes we can tolerate downtime, especially during the holiday season which is why this comes up now :). Most data is InnoDB, we do use a few myisam tables for speed where we don't need foreign key checks –  xref Nov 26 '12 at 23:22
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.