14/06/2010

MySQL Disaster Recovery

I’ve been asked to recover one of my old projects off my HP DL130 that sadly died around a year ago. The disks in the server are fine so I was able to mount them in a caddy and access the data.

I didn’t have any backups as this isn’t a production server, plus it was mainly university work from my degree that was running on the server. Long story short I needed to find a way to recover MySQL without using a backup.

It turns out MySQL has a “data” directory which contains a folder for each database it hosts. I first attempted to simply move the named database folder from within the data folder to my new server. This partially worked, when I opened MySQL Workbench it saw the database and allowed me to access the majority of the tables, however when I tried accessing some of the newer tables I’d created on the old server it threw an error and said they didn’t exist.

I then downloaded and installed XAMMP on a spare system and moved the whole of the data folder from the old server to my new XAMMP installation. I needed to open my.ini from the bin directory of MySQL and change the size of the log file from 5MB to 170MB to match the actual size of my log file; I also needed to comment out the time zone that XAMMP had setup.

Last thing I needed to do was change the logon username and password for MySQL under phpMyAdmin\config.inc.php this then allowed me to use phpMyAdmin to administer the databases, from here I could export the data in any number of formats and restore it to my new server.

No comments:

Post a Comment