How to Backup MyISAM Data?

If you’re a MySQL DBA, you have probably dealt with all kinds of database engines. Obviously, in this regard MySQL has quite a lot of options: one of the most popular options without doubt is InnoDB, but there’s also other storage engines such as CSV, BLACKHOLE or MyISAM. The MyISAM storage engine is what we’re looking at today.

How does MyISAM Work?

MyISAM is based on ISAM (Indexed Sequential Access Method) which is an indexing algorithm developed by IBM that allows to retrieve information from large sets of data in a fast way. For MyISAM, the main mechanism used for caching is the key cache and one of the main settings is the key_buffer_size.

Should You Use MyISAM?

If you would ask MySQL engineers whether you should go with MyISAM, the answer would probably be “No”. The reason for this is that when MySQL advanced, they added the majority of the features that could previously could be only seen in MyISAM to InnoDB, for example:

Full-text indexes and portable tablespaces have been available in InnoDB since MySQL 5.6.
Spatial indexes and last update for table became available in InnoDB since version 5.7.

Since the majority of the features that could previously only be seen in MyISAM now exists in InnoDB, you probably should look into using InnoDB instead. There is one caveat though - simple

COUNT(*)

queries will probably perform faster on MyISAM than they will on InnoDB because of the fact that MyISAM stores the number in table metadata, InnoDB does not.

To switch your MyISAM tables to InnoDB, figure out what tables are running MyISAM in the first place:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘database_name’ AND ENGINE = ‘MyISAM’;

Then convert them to InnoDB:

ALTER TABLE `table_name` ENGINE = InnoDB;

Backing Up MyISAM Data

To back up MyISAM data, you have a few options:

If your backup is physical, simply copy the
```
.frm, .MYD
```
and .
```
.MYI
```
files to the database folder - that will do the trick.
You can also restore data from MyISAM-based environments through phpMyAdmin or the CLI.

Obviously, performing the above options might sometimes seem tricky. Here’s where Backup Ninja comes into play.

Backing up MySQL Data with Backup Ninja

Backup Ninja can make your MySQL backup processes easy within no-time: to start with, it has a simple UI that allows you to see the progress of your backups:

To perform a backup, first choose what kind of backup do you want to perform - you can back up files alone or databases and files together:

Then create your database user:

Next, install the agent (Backup Ninja will provide you with a short chunk of code that helps you do that):

Finally, check out the servers and click Finish installation:

Now you should be able to schedule your backups (all kinds of backups can be scheduled - this feature is not limited to MySQL):

As you can see, backing up your data with Backup Ninja is convenient because you can also choose the backup type (backups can either be full, partial or incremental), you can include or exclude certain databases or tables and you can choose the server you want to perform the backup on too.

When you have successfully scheduled your backups, you can easily view them:

Summary

Backing up your MyISAM-based data in MySQL might seem to be pretty complex at first, but with the help of Backup Ninja, these kinds of problems can be solved within no-time. Follow these steps to backup and schedule your MySQL backups with Backup Ninja and you should be well on your way to making your backup processes more secure and efficient. As always, if something still isn’t clear or if you have any doubts, take a look through Backup Ninja’s documentation - all of the answers to your questions will most likely be there.

How to Backup MyISAM Data?