MyISAM is one of the most popular engines in MySQL. The engine is based on ISAM (Indexed Sequential Access Method) which is an indexing algorithm that was developed by IBM.

For some developers, this engine might be the next popular choice after InnoDB - the MyISAM storage engine was the only storage engine provided by MySQL in 2005. It was also the default storage engine up until MySQL 5.5. In this blog post we will try to answer how you should restore your MyISAM-based data from physical files.

How do You Restore Data from MySQL Engines?

Before we actually answer the question how should you restore MyISAM data from physical files, we should probably answer the following: how do you actually restore data from MySQL engines in the first place?

In a nutshell, there are two backup types: physical and logical backups of your data. Physical backups backup data at a filesystem level while logical backups backup statements that re-create the data. The way you take logical backups for data in InnoDB and in MyISAM does not differ much, but the way you take physical backups does: since InnoDB has a lot of files associated with the engine storing data needed for the engine to function correctly, you need to back up all of the directories and files related to the engine and then restore the engine, MyISAM however can be restored more easily.

Restoring MyISAM Data from Physical Files

Thankfully, restoring MyISAM data from physical files is not as complicated as restoring InnoDB data. To restore MyISAM-based data from physical files, perform the following. First take a backup of your MyISAM data:

1. Acquire a table-level lock for MyISAM tables. Bear in mind that transaction isolation levels, START TRANSACTION, COMMIT and ROLLBACK have an effect on InnoDB table behaviour (I stands for Isolation in ACID), but have no effect on MyISAM table behaviour since MyISAM does not support transactions.
2. Copy the .frm, .MYD and .MYI files for every table running the MyISAM engine. You might also want to take a copy of the my.cnf file contents.
3. Kill the SLEEP statement after you have copied the above files.

After you have a copy of your MyISAM data, simply copy the files back to your MySQL database directory and execute FLUSH TABLES. After you have restored your data, verify your data by issuing SHOW TABLES statements.

Restoring MyISAM Data Using Backup Ninja

Data can also be restored using Backup Ninja - this section of the documentation should provide you with some more information on how to do that. Backup Ninja also supports multiple backup tools with multiple backup types (mysqldump, Percona XtraBackup and MariaDB Backup), but that’s not within the scope of this blog - you can learn more about it here.

Summary

Restoring MyISAM data from physical files is not as complex as restoring, say, InnoDB data. As far as physical backups are concerned, one could say that MyISAM is much more “lenient” than InnoDB in that scenario since you simply have to perform a couple of steps and that’s it - you’re done! Backup Ninja can also assist you in your data restoration process - have a look through the documentation and decide what you need for yourself.