How to Backup and Restore your MySQL database

It's recommended that you backup your mysql database as frequently as possible. The instructions below show you how to create a backup file via telnet which you can archive away and use to restore the database or move the data to a new server.

BACKING UP:

Create a folder on your server to hold all of your backup files. For example: home/yoursite/html/backup.

Telnet into your server and move to the "backup" directory.

Execute the following command substituting the words in italics with your actual mysql username, password, and database name:

mysqldump -uUSERNAME -pPASSWORD DATABASENAME > backup.sql

At this point all of your mysql tables should be backed up into a file called "backup.sql" within the backup directory. You can store this file there, FTP it to your home computer, or FTP it to another server to restore the data there.

The only trick with restoring the data is that if the tables already exist in your mysql database, then the restore will not work. Thus, you need to delete or rename any tables that exist before the backup.

From telnet, move into the directory containing the "backup.sql" file and type:

mysql -uUSERNAME -pPASSWORD DATABASENAME < backup.sql

This will recreate all of the tables and insert all of the data. Note that you can use this method to transport the data to a mysql database on another server.

BACKING UP DATA TO TEXT FILES:

You can also backup the database to a series of .txt files so you can view them or import them into another database such as MS Access. Just telnet into your server and execute the line below which will place all the text files in the directory that you specify.

mysqldump -uUSERNAME -pPASSWORD --tab=/home/yoursite/html/backup --opt DATABASENAME

This will export the data into text files - one for each table.