Restore a MySQL Database from backed up Program Files (.frm, .myd, .myi), no SQL Dump Required!

Restore a MySQL Database from backed up Program Files

This blog is based on mysql 4 which we no longer support. However, learn more about the real cost of Wordpress, the benefits of a DXP and advancing our digital platform proposition with MS Azure.

This really can save a lot of heartache and frustrating effort if you don't have a scheduled MySQL database export to a .SQL backup file. It actually saved a clients bacon this week. A production server had been compromised by malware hosted in a 3rd party hosting company and we only had a tape backup of the C:\ drive to work with!

Note: this recovery strategy works on the premise that you have an offline backup of MySQL data files, (normally stored in C:\Program Files\MySQL\MySQL Server x.x\Data\), saved to a backup tape, SAN or similar device. Also, this will only work for MyISAM tables, it doesn't work on InnoDB tables.

So, first things first. You will need to setup the new MySQL server environment:

1. Install MySQL Server. The same version of MySQL should be used although it is possible to restore to a newer version. In our case this was version 4.1.22, this version and archived versions are downloadable from: http://dev.mysql.com/downloads/mysql/4.0.html
2. Stop MySQL Service. This is required in order to add to the data folders.
3. Copy database folder into new MySQL\data\ folder. (Probably best to restore to a test environment first). Take the required database folder, e.g. 'Northwind', containing the FRM, MYD, and MYI files from your backup tape and drop them into the new servers MySQL folder: C:\Program Files\MySQL\MySQL Server x.x\Data\Northwind\
4. Restart MySQL Service. Hopefully all goes well and the service starts without errors!
5. Check & Repair all database tables. Depending on the version of MySQL you are running you should run MYSQLCHECK or CHECK followed by REPAIR . These steps are necessary in order to ensure that the data is not corrupted. CHECK analyses for errors whilst REPAIR, well it fixes them! It also rebuilds the indexes held in the .MYI files.
6. Backup & Restore to new production server. Export your database to a SQL dump, .SQL file. Good practice is to export the structure and data separately. If you don't perform the CHECK and REPAIR procedures outlined in step 5 you will most likely get errors when doing this step.

That's it! Simple eh?

Supplementary notes: - Use SQLyog for all MySQL DB management functions, its GUI interface is the best out there and it has loads of easy to use features for common tasks. Free community edition is now located as an Open Source project on Google Code: http://code.google.com/p/sqlyog/downloads/list - The my.ini file does not need to be an exact replica of the original production .ini file. In fact, we use a separate port from the default 3306 for MySQL installs, as a security measure.

TIP: When doing a full import of a large database set the max_packet_allowed = 1M, or greater. This allows high volume data imports without the famous "server has gone away" error. - FRM files contain table structure information. - MYD contains the actual table data. - MYI files contain the indexes. I hope you found this article helpful and someday it gets you out of a jam. This got me out of tight spot a few times, in fact too many times where correct backup procedures should have been in place!

While you're here, feel free to Get In Touch if you'd like any more information about what services we provide.