Backup of Mysql database
It is assumed that you have phpMyAdmin installed since a lot of web service providers use it.
0. Open phpMyAdmin.
1. Click Export in the Menu to get to where you can backup you MySql database. Image showing the export menu.
2. Make sure that you have selected to export your entire database, and not just one table. There should be as many tables in the export list as showing under the database name.
3. Select"SQL"-> for output format, Check "Structure" and "Add AUTO_INCREMENT" value. Check "Enclose table and field name with backquotes". Check "DATA", check use "hexadecimal for binary field". Export type set to "INSERT".
4. Check "Save as file", do not change the file name, use compression if you want. Then click "GO" to download the backup file.
Restoring a backup of a MySql database
1. To restore a database, you click the SQL tab.
2. On the "SQL"-page , unclick the show query here again.
3. Browse to your backup of the database.
4. Click Go.
I found a very easy way to do the backup with large databases: BigDump.php (google, you'll find the download links)
BigDump is a PHP file that you upload to your server with your database information. You then upload SQL files to the same directory, select the file in BigDump and then click "Import." What it does is go through the large file only processing X amount of lines at a time, then waiting Y amount of milliseconds and then processing X amount of lines, etc. My database is about 1gig thanks to my vbulletin attachments table, and I was able to successfully import my database to a new server using BigDump. It was really REALLY easy and I'm thankful that I found this file.
For simple backups without PHPMyAdmin, I have written a short shell script that creates backups including files and MySQL database. It creates a TAR/GZIP archive and sends it by e-mail (to myself of course). For more detail and script source code, please have a look at http://www.kyusl.com/scr.php?mod=01
cron (on UNIX and likes) allows you to schedule just anything that can run without human interaction. I am not sure if PHPMyAdmin allows that, that's because I have written my own script (see link above) and scheduled it in cron.
On a windows server you can create a schedule task and use the mysqldump command:
mysqldump --opt --host=localhost --user=myUser --password=myPass --result-file=C:\Backups\myBackupFile.sql myDatabase
You also could use a backup tool to schedule your backups. I have used MySQLBackupFTP before. If you don't have many databases you could try the free version that allows you to schedule up to two databases.
The tool also has some good features like sending the compressed backup to a remote FTP server and email notification.