First things first… during my frantic search for answers, IÂ read a statement similar to this:
“Anybody who’s come across this post looking for information on how to restore their databases, don’t worry – your data is probably not lost.”
In hindsight, I would likely have been much more effective if remembered this, therefore I’ve put it at the top of this post.
Background
My problem arose when I recently came around to the idea of reinstalling one of my servers. It was running FreeBSD 5.2 x86 and I wanted to upgrade to 8.2 x64. It was with great hesitance that I ended the 521 day uptime record.
One reason for the reinstall was that I wanted to get rid of a lot of rubbish that had built up on the server. The flipside of this is that there was also a lot of data that needed to be backed up for restore after the reinstall. Backing up the necessary directories was easy enough, but when it came to the MySQL database server – I’ll never make the same mistake again!
Problem
In order to backup the MySQL server I backed up the directory (/var/db/mysql) where the raw files were stored (at this point not understanding the difference between InnoDB and MyISAM). I also decided to do a mysqldump (better to have 2 backups than none!) of all the databases into a single .sql file. Unfortunately, I forgot to download this .sql file and so after the reinstall I was left only with the raw data files.
I didn’t necessarily think that this would be a problem, as I assumed I would just be able to paste these files into the new data directory and everything would be fine. At this point I will mention that I did forget to backup my my.cnf file too – though I hadn’t thought that I’d need it.
MyISAM>
It turns out that with MyISAM database, yes this will work assuming you’re migrating the files to/from the same version of the MySQL server. If it’s a different version you might need to delete the .MYI (index) and rebuild the index on the table using REPAIR TABLE (http://dev.mysql.com/doc/refman/5.1/en/myisam-repair.html). Finally, you have to add the relevant permissions for that database.
MyISAM Directory Structure
table.frm – Structure
table.MYD – Data
table.MYI – Index
InnoDB
If it’s an InnoDB table you’re trying to restore, you need to restore the ibdata file. This is more difficult and so the way I got around this was to restore my entire backed up data directory to an intermediarry server so that I could then perform SQL dumps and import these into my new server.
This method worked well, and I will now be extremely careful when backing up my server in the future.
Steps
A full restore involves restoring the exact data from the backup (i.e. the server as it was). The versions of mysql must match.
- Stop MySQL
- Decompress the backup
If restoring temporarily (to a production server), ensure that the existing data directory and my.cnf file is renamed first. - Restore the data directory
- Restore the my.cnf file
- Start MySQL
- Check the log file to ensure there were no errors during startup
tail /home/mysql/<server_name>.err
If you have restored to a production server temporarily and wish to return to normal operation:
- Stop mysql
- Return the original data directory and my.cnf file
- Start MySQL and check for errors in the log
