Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Storage Engines: MyISAM, InnoDB and BDB

By: FYIcenter.com

Part:   1  2   3  4  5  6 

(Continued from previous part...)

How To Backup Tables by Copying MyISAM Table Files?

To easiest way to backup MyISAM tables is to copy the data files to a backup directory. But this is not the recommended way to do backups. Read the backup FAQ collections for more details.

The following tutorial exercise shows you how to add a new index to "fyi_isam" table, add two rows, and copy "fyi_isam" table files to a backup directory:

>\mysql\bin\mysql -u dev -piyf fyi

mysql> CREATE INDEX fyi_isam_count ON fyi_isam (count);
Query OK, 1 row affected (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO fyi_isam (id) VALUES(-1);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO fyi_isam (id, count) VALUES(-2, 987);
Query OK, 1 row affected (0.03 sec)

mysqk> quit

>mkdir \mysql\backup
>mkdir \mysql\backup\fyi

>copy \mysql\data\fyi\my_isam.* \mysql\backup\fyi
\mysql\data\fyi\fyi_isam.frm
\mysql\data\fyi\fyi_isam.MYD
\mysql\data\fyi\fyi_isam.MYI
        3 file(s) copied.

>dir \mysql\backup\fyi
   8,620 fyi_isam.frm
      40 fyi_isam.MYD
   3,072 fyi_isam.MYI

How To Restore Tables by Copying MyISAM Table Files?

If you have old copies of MyISAM table files, you can restore them easily by copying them back to the data directory to replace the current table files. However you may need to shutdown MySQL server to do this, because the current table files might be locked on MySQL server. But this is not the recommended way to do backups. Read the backup FAQ collections for more details.

The following tutorial exercise shows you how to delete a data row, and restored it by copying back the table files:

>\mysql\bin\mysql -u dev -piyf fyi

mysql> DELETE FROM fyi_isam WHERE id = -1;
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM fyi_isam;
+----+-------+-------+
| id | title | count |
+----+-------+-------+
| -2 | NULL  |   987 |
+----+-------+-------+
1 row in set (0.00 sec)

mysql> quit;

>copy \mysql\backup\fyi\fyi_isam.* \mysql\data\fyi
\mysql\backup\fyi\fyi_isam.frm
\mysql\backup\fyi\fyi_isam.MYD
\mysql\backup\fyi\fyi_isam.MYI
        3 file(s) copied.

>\mysql\bin\mysql -u dev -piyf fyi

mysql> SELECT * FROM fyi_isam;
+----+-------+-------+
| id | title | count |
+----+-------+-------+
| -1 | NULL  |  NULL |
+----+-------+-------+
1 row in set (0.00 sec)

Looks like we restored one row (id=-1). But we also lost one row (id=-2). This will happen if your MySQL server is still running during the backup and storing processes.

(Continued on next part...)

Part:   1  2   3  4  5  6