© 2003, Groundbreak.com. All rights reserved.
Any comments, suggestions, or mistakes: firstname.lastname@example.org
to Backup and Restore your MySQL database
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.
Create a folder
on your server to hold all of your backup files. For example: home/yoursite/html/backup.
your server and move to the "backup" directory.
following command substituting the words in italics with your actual
mysql username, password, and database name:
-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.
RESTORING YOUR DATA :
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.
move into the directory containing the "backup.sql" file
-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
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.
-pPASSWORD --tab=/home/yoursite/html/backup --opt DATABASENAME
This will export
the data into text files - one for each table.