Recover MySQL Tables after Upgrade/OSX

MySQL^✝ is the database that powers WordPress enabled websites. Other applications which use MySQL databases include: TYPO3, MODx, Joomla, phpBB, MyBB, Drupal and many others. It's a central component of the broadly used LAMP open source web application software stack. LAMP is an acronym for “Linux, Apache, MySQL, Perl/PHP/Python." As such, it is either the 2nd or 3rd most popular database system in the world, depending on who's counting that is.

Application developers frequently setup a LAMP stack on their laptops or home computers so they can work when they are at their creative best, which means anytime of the day or night when inspiration strikes them. If you're anything like me or some of my developer friends, you probably like to keep your software updated with the latest versions or keep multiple versions of software running so you can support specific client installations.

My LAMP stack, which I use for WordPress and PHP development projects, is setup on my MacBook Pro with retina display. OSX comes with a version of the Apache web server preinstalled, but to complete the LAMP stack you have to install MySQL and PHP yourself. There are plenty of instructions on the web for setting up a development environment on OSX. A lot of these instructions reference Homebrew or some other package installer, some even walk you through compiling binaries for yourself. I don't like things to get that complicated. My personal preference is to install the pre-compiled PHP binaries available from http://php-osx.liip.ch/ and the MySQL Community Edition DMG archive.

The Mystery

When I setup MySQL for the first time on this MacBook, the current release was 5.6.16. Since then, there have been four minor point releases. The most current version as of this article is 5.6.20. You would think that upgrading MySQL would be a straightforward process. It should be as simple as downloading the latest stable DMG archive and running it. But things aren't always that simple, are they? If you do this, you will find that when you start the new version your working data tables have mysteriously disappeared!

How It Happens

When you run the MySQL DMG archive, the package installs the software in ~/usr/local/ in a directory named after the version release. For example, version 5.6.16 is installed in ~/usr/local/mysql-5.6.16-osx10.7-x86_64; likewise, version 5.6.20 is in ~/usr/local/mysql-5.6.20-osx10.8-x86_64.

The installation scripts create a complete file structure for the new version in its new directory. This includes the directory where MySQL stores its databases and tables, which is appropriately named “data." The install process does not migrate the old data to the new directory structure. You have to do this yourself. Fortunately, while the install package deletes the older version's binaries, it leaves the old data directory and my.cnf file intact. My.cnf is the MySQL configuration file that contains any environment customizations you may have made.

The Fix

To migrate the old database to the new version, first make sure MySQL is not running:

1. Launch a terminal window and enter

sudo /usr/local/mysql/support-files/mysql.server stop

2. Run the DMG Archive to install the new version of MySQL

3. Change the name of the new data directory to dataold

sudo mv /usr/local/mysql-5.6.20-osx10.8-x86_64/data /usr/local/mysql-5.6.20-osx10.8-x86_64/dataold

4. Copy the old databases to the new location

sudo cp -rf /usr/local/mysql-5.6.16-osx10.7-x86_64/data /usr/local/mysql-5.6.20-osx10.8-x86_64/

5. Set the correct ownership of the copied directory to _mysql

sudo chown -R _mysql:_mysql /usr/local/mysql-5.6.20-osx10.8-x86_64/data

6. Restart MySQL

sudo /usr/local/mysql/support-files/mysql.server start

These steps are all that I needed to upgrade from 5.6.16 to 5.6.20. Sometimes you may run into a specific situation where you have to reset the root password, run mysql_upgrade or repair the tables. Also, if you have customizations in the old my.cnf file, you'll need to copy those over into the new my.cnf file as well.

An Alternative

Of course there's another way to migrate your data. You could just as easily dump your data from the old MySQL before you upgrade. Then after the upgrade is installed, import the data into the new installation. I never remember to do that though. So I inevitably have to go through these steps.