MySQL – Replicate MySQL 5.0 Master to 5.5 Slave

MySQLmysql-5.0mysql-5.5replication

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?

Best Answer

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

Related Solutions

MySQL 5.0 Replication – Addressing Bandwidth Concerns

SUGGESTION #1 : Use Distribution Masters

A Distribution Master is a mysql slave with log-bin enabled, log-slave-updates enabled and contains only tables with the BLACKHOLE Storage Engine. You can apply replicate-do-db to the Distribution Master and create binary logs at the Distribution Master that contains only the DB schema(s) you want binlogged. In this way you reduce the size of outgoing binlogs from the Distribution Master.

You can setup a Distribution Master as follows:

mysqldump your database(s) using --no-data option to generate a schema-only dump.
Load the schema-only dump to the Distribution Master.
Convert every table in the Distribution Master to the BLACKHOLE storage engine.
Setup replication to the Distribution Master from a master with real data.
Add replicate-do-db option(s) to /etc/my.cnf of the Distribution Master.

For steps 2 and 3 you could also edit the schema-only dump and replace ENGINE=MyISAM and ENGINE=InnoDB with ENGINE=BLACKHOLE and then load that edited schema-only dump into the Distribution Master.

In step 3 only, if you want to script the conversion of all MyISAM and InnoDB tables to BLACKHOLE in the Distribution Master, run the following query and output it to a text file:

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name', ENGINE=BLACKHOLE;') BlackholeConversion FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') AND engine <> 'BLACKHOLE'" > BlackholeMaker.sql

An added bonus to scripting the conversion of table to the BLACKHOLE storage engine is that MEMORY storage engine tables are converted as well. While MEMORY storage engine table do not take up disk space for data storage, it will take up memory. Converting MEMORY tables to BLACKHOLE will keep memory in the Distribution Master uncluttered.

As long as you do not send any DDL into the Distribution Master, you can transmit any DML (INSERT,UPDATE,DELETE) you so desire before letting clients replicate just the DB info they want.

I already wrote a post in another StackExchange site that discusses using a Distribution Master.

SUGGESTION #2 : Use Smaller Binary Logs and Relay Logs

If you set max_binlog_size to something ridiculously small, then binlogs can be collected and shipped out in smaller chunks. There is also a separate option to set the size of relay logs, max_relay_log_size. If max_relay_log_size = 0, it will default to whatever max_binlog_size is set to.

SUGGESTION #3 : Use Semisynchronous Replication (MySQL 5.5 only)

Setup your main database and multiple Distribution Masters as MySQL 5.5. Enable Semisynchronous Replication so that the main database can quickly ship binlogs to the Distribution Master. If ALL your slaves are Distribution Masters, you may not need Semisynchronous Replication or MySQL 5.5. If any of the slaves, other than Distribution Masters, have real data for reporting, high availability, passive standby or backup purposes, then go with MySQL 5.5 in conjunction with Semisynchronous Replication.

SUGGESTION #4 : Use Statement-Based Binary Logging NOT Row-Based

If an SQL statement updates multiple rows in a table, Statement-Based Binary Logging (SBBL) stores only the SQL statement. The same SQL statement using Row-Based Binary Logging (RBBL) will actual record the row change for each row. This makes it obvious that transmitting SQL statements will save space on binary logs doing SBBL over RBBL.

Another problem is using RBBL in conjunction with replicate-do-db where table name has the database prepended. This cannot be good for a slave, especially for a Distribution Master. Therefore, make sure all DML does not have a a database and a period in front of any table names.

Mysql – Some queries slower in MySQL 5.5 than 5.0

Disabling the binary log on the slave fixed it. This makes me worry about when we upgrade the master to 5.5, but for now things are working much much better.

Edit: The issue was sync_binlog; now that it's set to zero, everything's happy.