Backup Tables by Copying MyISAM Files in MySQL

Q

How To Backup Tables by Copying MyISAM Table Files in MySQL?

✍: FYIcenter.com

A

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

 

Storage Engines: MyISAM, InnoDB and BDB in MySQL

⇒⇒MySQL Database Tutorials

2017-09-01, 243👍, 0💬