Backing Up MyISAM Tables by Directly Copying the Files

MyISAM tables are stored as files (.frm for the definition, .MYD for the data, and .MYI for the indexes) inside a directory named after the database, so an easy way to back up the data is to copy the files. Unlike BACKUP, directly copying the files does not automatically lock the tables, so you need to lock the tables yourself to get a consistent picture. Alternatively, you can do a direct copy while the server is down. Once the tables have been locked, you should flush the tables to make sure any unwritten indexes are written to disk. For this example, you'll need to have two windows open.

LOCK and FLUSH the tables from Window1:

mysql> LOCK TABLES sales READ,sales_rep READ,customer READ; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH TABLES sales,sales_rep,customer; Query OK, 0 rows affected (0.02 sec)

And now copy the tables from Window2 (on Unix):

% cd /usr/local/mysql/data/firstdb % cp sales.* /db_backups % cp sales_rep.* /db_backups % cp customer.* /db_backups %

Or on Windows (from Window2):

C:\MySQL\data\firstdb>copy customer.* c:\db_backup customer.frm customer.MYI customer.MYD         3 file(s) copied C:\MySQL\data\firstdb>copy sales.* c:\db_backup sales.frm sales.MYI sales.MYD         3 file(s) copied C:\MySQL\data\firstdb>copy sales_rep.* c:\db_backup sales_rep.frm sales_rep.MYI sales_rep.MYD         3 file(s) copied 

Once you've copied the files, you can release the locks from Window1, as follows:

mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)

Warning 

For the duration of the backup, while the locks are in place, you will not be able to add new records to the tables and will also experience a performance penalty for reads. If at all possible, do not perform backups during peak hours!

Again, to test the backup, you're going to drop a table.

From Window1, drop the table:

mysql> DROP TABLE sales; Query OK, 0 rows affected (0.00 sec)

Copy the tables from Window2 (this example is from Unix):

 % cp /db_backups/sales.* . 

And our table is restored. You can verify this with the following, from Window2:

mysql> SELECT * FROM sales; +------+-----------+------+-------+ | code | sales_rep |   id | value | +------+-----------+------+-------+ |    1 |         1 |    1 |  2000 | |    2 |         4 |    3 |   250 | |    3 |         2 |    3 |   500 | |    4 |         1 |    4 |   450 | |    5 |         3 |    1 |  3800 | |    6 |         1 |    2 |   500 | |    7 |         2 | NULL |   670 | |    8 |         3 |    3 |  1000 | +------+-----------+------+-------+ 8 rows in set (0.00 sec)

There's also a possibility that the Unix permissions could come back to haunt you. If you did not back the files up as the mysql user (you would usually have done this as the root user), you're likely to see the following:

mysql> SELECT * FROM sales; ERROR 1017: Can't find file: './firstdb/sales.frm' (errno: 13) 

The problem is that you've copied the file back, but the mysql user cannot access this file. The following snippet, from Window1, shows that I've backed up the files as the root user.

[root@test firstdb]# ls -l             total 183 ... -rw-r-----   1 root     root          153 May 27 22:27 sales.MYD -rw-r-----   1 root     root         3072 May 27 22:27 sales.MYI -rw-r-----   1 root     root         8634 May 27 22:27 sales.frm -rw-rw----   1 mysql    mysql         156 May 22 21:50 sales_rep.MYD -rw-rw----   1 mysql    mysql        3072 May 22 21:50 sales_rep.MYI -rw-rw----   1 mysql    mysql        8748 May 22 21:50 sales_rep.frm ...

To restore permission to mysql, from Window1 change the owner of the sales table to mysql:

 % chown mysql sales.* % 

And now everything should work correctly, as you can see from running a query in Window2:

mysql> SELECT * FROM sales; +------+-----------+------+-------+ | code | sales_rep |   id | value | +------+-----------+------+-------+ |    1 |         1 |    1 |  2000 | |    2 |         4 |    3 |   250 | |    3 |         2 |    3 |   500 | |    4 |         1 |    4 |   450 | |    5 |         3 |    1 |  3800 | |    6 |         1 |    2 |   500 | |    7 |         2 | NULL |   670 | |    8 |         3 |    3 |  1000 | +------+-----------+------+-------+ 8 rows in set (0.00 sec)

MyISAM tables are platform independent, so they can be transferred to MySQL on a machine with different hardware or a different operating system. MyISAM tables created on an old MySQL version 3 system can be used in version 4, but not the other way round. To move data from MySQL version 4 to MySQL version 3, you need to use an option such as mysqldump, discussed in the next section.