Backups can be made by copying database file directly, or by using programs designed for that purpose: mysqldump, mysqlhotcopy, MySQL Administrator, and InnoDB Hot Backup.
A binary backup is a copy of the files in which database contents are stored. Copying these files preserves the databases in exactly the same format in which MySQL itself stores them on disk. Restoration involves copying the files back to their original locations. Techniques for making binary backups include file copy command (such as cp or tar), mysqlhotcopy, and InnoDB Hot Backup.
A text backup is a dump of database contents into text files. Restoration involves loading the file contents back into databases by processing them through the server. Techniques for making text backups include the SELECT … INTO OUTFILE statement, mysqldump, and MySQL Administrator.
It is faster to make a binary backup because it involves only file copy operations that need to know nothing about the internal structure of the files. However, if the backup is to be used for transferring databases to another machine that uses a different architecture, the files must be binary portable. Binary portability means that the files are machine independent and that you can directly copy them from one MySQL server to another on a different machine and the second server will be able to access their contents with no problems. With binary backup, it is necessary to make sure that the server does not modify the file while the backup is in progress.
It is slower to make a text backup because the server must read tables and then write the contents out to disk files itself or send the contents to a client program that writes the tables. Text backups are portable. With text backup methods, the server must be running because it must read the files that are to be backed up.
The procedure for making binary backups depends on which storage engine created the tables, and generally can be used only for local MySQL server.
Text backup procedures are more general and can be used for tables created by any storage engine.
Some methods can be used with either local or remote servers.
To make a binary backup of MyISAM table, copy the .frm, .MYD, and .MYI files that represent the table. When you do this, the table must not be in used. If you leave the server running, use an appropriate locking protocol to prevent server access to the table:
mysql> USE world;
mysql> LOCK TABLES Country READ;
mysql> FLUSH TABLES Country;
Then use your operating system's file copy command to copy the table files. After the copy operation completes, release the lock on the table:
mysql> UNLOCK TABLES;
The preceding strategy works on Unix. On Windows, file-locking behavior is such that you might not be able to copy table files for tables that are locked by the server. In that case, you must stop the server before copying table files.
Another way to make binary MyISAM backup is to use mysqlhotcopy, which does the locking and flushing for you.
To recover a MyISAM table from a binary backup, stop the server, copy the backup table files, and restart the server.
