Null is Love » Blog Archive » Recovering Databases after a MySQL Upgrade

Recovering Databases after a MySQL Upgrade

I ran into an issue with MySQL the other day. After upgrading to a newer version on my development server (a Mac), my databases, user privileges and root password were missing. This isn’t the first time I’ve felt that sinking feeling as I frantically searched for the missing files, and since the fix is easy I thought I should write it up to save you the panic if you ever find yourself in the same situation.

The Symptoms

1) Your old database username and password stop working.

>mysql -u root --password=yourpassword

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

2) You can access MySQL from a command-line as a root user but with no password.

>mysql -u root

Welcome to the MySQL monitor.  Commands end with ; or \g.

Server version: 5.0.45 MySQL Community Server (GPL)

...

3) Once in MySQL as root, typing SHOW DATABASES; does not return all my databases.

The Problem

I’ll walk you through the commands in the command-line to show you the problem

# first determine where MySQL lives

> which mysql

/usr/local/mysql/bin/mysql

# list the files in the local directory

> ls -l /usr/local/

...

mysql -> mysql-5.0.45-osx10.4-i686

mysql-5.0.41-osx10.4-i686

mysql-5.0.45-osx10.4-i686

...

The first mysql is a symbolic link (it’s like an alias or shortcut). This is smart and good. If we had our configuration for mysql look inside /usr/local/mysql-5.0.45-osx10.4-i686/bin/ then we’d have to change the configuration information each time we upgrade. But since our configuration (probably in a .profile, .login, .bash, or .csh file) specifies /usr/local/mysql/bin/, then we can leave the configuration file alone and the upgrade scripts only need to change the symbolic link. (Much cleaner and easier than trying to edit the correct part of a configuration file.)

You might be tempted to simply change the link back to the old directory, but that won’t work. If you take a look inside mysql-5.0.41-osx10.4-i686 there is probably only one folder and it is called ‘data’. If you take a look in mysql-5.0.45-osx10.4-i686 you’ll see many folders including ‘data’ and also ‘bin’ which holds the “actual” mysql command. When the MySQL upgrade took place, everything in the old version was wiped out except your data. At the moment, you have only one working copy of MySQL, but you have two sets of databases—one with all your old databases and one that is almost empty.

You can breathe a sigh of relief now. Your databases are still there. All you need to do is fix things so the new version of MySQL will use them instead of it’s empty, default databases.

The Solution

If you search online for an answer, most sites tell you to backup your databases BEFORE you upgrade and then to restore your data from your backups. That would work great if I had planned ahead! But there’s not a lot of help online for what to do if you didn’t make the backups first like a responsible person should.

You might be tempted to simply copy the ‘data’ folder over from one version folder to the other. For me at least, that doesn’t work. Somehow it misses some files that it needs. Instead you need to package the files up as a .tar archive and then un-package them into the right spot. And, as an added bonus, we’ll make a backup of the databases in the process.

Here it is, step-by-step:

# Stop MySQL completely

mysqladmin shutdown

# Backup the old data to your desktop

cd /usr/local/mysql-5.0.41-osx10.4-i686

sudo tar -cvf ~/Desktop/mysql_backup.tar data

# Unpack the old data into the new MySQL folder

cd /usr/local/mysql-5.0.45-osx10.4-i686

sudo tar -xf ~/Destop/mysql_backup.tar

sudo chown -R mysql data

# relaunch MySQL

sudo /usr/local/mysql/bin/mysqld_safe &

I’m 99% sure you won’t need to reboot, but if it gives you any problems, that’s the first thing you should try.

The Prevention

Like I said, you are supposed to make the backup before you start. Here’s how you do that the next time you upgrade so that you can save yourself the fear that you might have just wiped out everything.

# Backup

mysqldump --all-databases --user=username --password > mysql_db_dump.sql

# Restore

mysql --user=username --password < mysql_db_dump.sql

If you want to configure the backup and restore you can read more about mysqldump.

This entry was posted on Tuesday, October 30th, 2007 at 4:43 pm and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.