taken from http://www.webmasterbase.com/article/325
:
MySQL is a popular database for use on Unix and Linux machines; the Windows version has also recently become open-source (with MySQL going the GPL way). When combined with PHP, the two form a powerful platform for you to write interactive web-based applications.
There are three main ways to back up a MySQL database on Unix/Linux (Windows users should find the commands are reasonably similar):
Use the mysqldump command.
If you have Telnet/SSH access to your MySQL server, log in and issue the following command for each database you want to back up:
shell> mysqldump -u user -ppassword --opt -full database_name > backupfile.sql
Then move the resulting file(s) to your preferred backup areas. If you require more information on the mysqldump command, then simply check out this URL:www.mysql.com/documentation/mysql
Copy all the relevant table files.
If the server isn\'t updating anything (or you\'ve deliberately killed mysqld for this purpose) then you can copy all the files with the following extensions in your MySQL data directory:
.frm
.myd
*.myi
Make sure you restart the MySQL daemon once you finish copying and downloading the files to your preferred backup areas.
TIP: once you\'ve completed the backup, restart MySQL with the --log-update switch. This will allow you to keep track of all modifications done in the MySQL tables since your last \'dump\'.
To restore your dumps, you should either restore to an existing database or create a new database using
shell> mysqladmin create database_name
then issue the following command :
shell> mysql -u user -ppassword database_name < backup-file.sql
If you don\'t have access to Telnet/SSH and you\'re unable to do backups using the methods described above, you should ask your host if it is possible for them to do a backup for you and put the backups in a separate directory so that you can easily FTP your backups to your selected backup areas. Otherwise, if you have access to phpMyAdmin, you can use the following procedure:
Access phpMyAdmin, and select the database you wish to \'dump\' (backup).
Scroll down and you will see a bulleted point saying: \"View dump (schema) of database\" along with some radio and check boxes. Choose \'Structure and data\', then click on \'Add Drop Table\' and \'Send\' and click \'Go\'. This will save the \'dump\' to your hard drive.
To restore a dump using phpMyAdmin, simply insert the file in the correct place once you have chosen the correct database by doing the following:
Choose the database you will insert your data into, or create a new database.
Insert the appropriate SQL queries you already have, or just paste the name of the .sql file you have on your hard drive into the text box under \'Location of the textfile\', and fire away!
Tip : use a crontab job to schedule backups periodically.
RED
