Missing MySQL data after updating

When updating MySQL, say for example from 4.1.20 to 5.0.45 on OS X, you might find that your data goes missing. If you are the think-ahead safe network admin that you think you are, you probably backed up your data with mysqldump before performing the upgrade. Restoring your data at this point is easy.

Unfortunately, you're not the think-ahead safe network admin you thought you were. You are merely human.

Fear not, human! There is still hope.

The MySQL documentation clearly states that you're data is not lost, the update process is merely incomplete. Your data is safe and sound in it's old directory. The new installation just defaults to using a newer, less content-filled database to begin with for the sole purpose of making you shit yourself.

If we cd [as root] into /usr/local/ we see two MySQL folders:

mysql-5.0.45-osx10.4-powerpc
mysql-standard-4.1.20-apple-darwin8.6.0-powerpc

Inside the 5.0 folder, we find all the necessary files to run a MySQL server. Inside the 4.1 folder we find only the a data folder ...

So, to be safe, here's what we do:

1. Shut down the mysql daemon (from system prefs if you installed the option)
   
2. Copy the data folder in the 5.0 folder to data_bak
3. Copy the data folder from the 4.1 folder to the 5.0 folder.
4. You may also have to chown all the files to mysql
   

Assuming you know how to shut down the daemon from system prefs, the commands for steps 2 through 4 are as follows:

1. cd /usr/local/mysql-5.0.45-osx10.4-powerpc
2. cp -r data/ data_bak/
3. cp -r ../mysql-standard-4.1.20-apple-darwin8.6.0-powerpc/data/ ./data/
4. chown -R mysql data
   

Now start that MySQL service back up and look at all your beautifully restored data. If for some reason you're unable to start the daemon from the system preferences, just use the command: ./bin/mysqld_safe

If the system prefs thing doesn't work after a reboot ... uhh ... just get over it. Have a beer.