Updated: MySQL 5.0 and 5.1 Side-By-Side

March 02, 2011

Cross-posted from http://devblog.meebo.com.

As part of working on the Meebo Operations team, I manage a bunch of database servers at Meebo. You may remember Dave's post a couple months ago about how we're starting to use NoSQL technologies for data storage. Though CouchDB and Cassandra are more new and exciting, Meebo still relies heavily upon MySQL for much of our infrastructure.

56 of the ~160 DB servers I manage are running MySQL, for now. A lot of those are running an old version of MySQL: 5.0.77. The version is old mostly because it's the default provided by our Linux distribution of choice, CentOS. I’ve been in the process of upgrading to something slightly less ancient (a "non-stock" version of MySQL 5.1 built and maintained by Percona, available here) for about a year. It’s slow-going, though, mostly because the servers aren't catching fire by staying on 5.0.77, so it’s lower in my priorities.

A couple months ago, I split out one of our highest traffic databases onto a pair of servers running MySQL 5.1. Thing is, I set up multi-headed MySQL servers running multiple MySQL processes on the same machine. As of this writing, we have 120 MySQL processes running on the 56 machines. This high-traffic app had a remote replica on one of these multi-headed servers. So, I had two 5.1 servers trying to replicate to a 5.0 server using an incompatible protocol. How is that possible, you ask? Well I made a mistake. When I set up the new pair of servers, I spaced and made them use a non-backwards-compatible replication protocol, Row-Based Replication. That meant I had to upgrade part of the multi-headed server to 5.1! Uh oh!

I had sort of done this in the past, but never in a live, production environment. Based on past experience, I knew the steps were something like this:

1) Install the package(s) to a different location:

mkdir /opt/mysql51
rpm --relocate /usr/=/opt/mysql51/ -i --nodeps Percona-Server*.rpm

Note: you may run into a problem with the /etc/my.cnf file. I recommend moving it somewhere safe so nothing clobbers it.

2) Make sure you specify which mysqladmin and mysqld to use in /etc/my.cnf, otherwise the "default" ones will always be used, in my case those were the 5.0.77 binaries.

3) This is the one that really killed me: specify the full path to the new language file you want in your configuration file (usually /etc/my.cnf on RedHatty systems)

language=/opt/mysql51/share/mysql/english

Running two versions of MySQL side-by-side is neat and all, but it's pretty difficult to manage if you can't leverage existing tools like the SysVinit script, or even mysqld_multi. mysqld_multi is the recommended way to manage multi-headed database servers. It doesn't come with an init script, but it's pretty easy to create a "Meh, it kinda works" one based on a Fedora example init script. As for mysqld_multi, it's really not that difficult to set it up to manage any version of MySQL, so long as the RPMs were installed to non-conflicting locations. Here's my example /etc/my.cnf file:

[mysqld1]
language=/opt/mysql51/share/mysql/english
mysqld=/opt/mysql51/sbin/mysqld
mysqladmin=/opt/mysql51/bin/mysqladmin
datadir=/var/lib/mysql51
socket=/var/run/mysqld/51.sock
pid-file=/var/run/mysqld/51.pid
log-error=/var/log/mysqld-51.log
port=3306
[mysqld2]
language=/usr/share/mysql/english
mysqld=/usr/libexec/mysqld
mysqladmin=/usr/bin/mysqladmin
datadir=/var/lib/mysql50
socket=/var/run/mysqld/50.sock
pid-file=/var/run/mysqld/50.pid
log-error=/var/log/mysqld-50.log
port=3307
[mysqld_multi]
log=/var/log/mysqld.log
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Note that I'm using the format for mysqld_multi, not for a regular single-process setup. Also note that these parameters are the absolute minimum to get mysqld_multi to start the processes. It is always required to tune MySQL the rest of the way. This typically ends up being many duplicated lines in /etc/my.cnf specifying the InnoDB buffer pool size, the thread concurrency limits of infinity, etc. Also be careful not to use non-backward-compatible configuration parameters in the 5.0.x section!

In order to get the processes to start, you need to run mysql_install_db, but you need to use different versions, so watch out! Here's how I did that:

1) For the 5.0 version, since that's the package installed to the default location, I can just run this:

mysql_install_db --datadir=/var/lib/mysql50 --basedir=/usr

2) For the 5.1 version, I need to specify the other path:

/opt/mysql51/bin/mysql_install_db --datadir=/var/lib/mysql51 --basedir=/opt/mysql51

3) Finally, start the processes:

mysqld_multi start

Once the processes start happily, I can then start, stop and check the status of the MySQL processes, regardless of version.

There you are! You can now run any version of MySQL you like side-by-side with existing legacy processes. I did this out of necessity, but realized how much it can help with my ongoing upgrade to 5.1. This gives you the option to do the MySQL 5.0 to 5.1 upgrade almost in-place, so long as your server can handle the extra load of a command like this:

mysqldump -S /var/run/mysqld/5.0.sock --all-databases | mysql -S /var/run/mysqld/5.1.sock

I could even write a migration daemon that goes around our MySQL servers to upgrade them all... if I didn't know what was good for me.

-Adam