There are several ways to backup MySQL data. Following are the few
How to Backup MySQL Database automatically (for Linux users)
15 2 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip
> /mnt/disk2/database_`data ' %m-%d-%Y'`.sql.gz
Backup Your MySQL Databases Automatically With AutoMySQLBackup
The installation is very simple: just download (http://sourceforge.net/projects/automysqlbackup/ ) the one file bash script and save it somewhere, customize it to fit your setup (only some basic changes are needed: like the MySQL user and password, backup location), make it executable and activate it in cron as needed (daily for example).
Here are the variables that I usually setup:
# Username to access the MySQL server e.g. dbuser
USERNAME=dbuser
# Username to access the MySQL server e.g. password
PASSWORD=password
# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost
# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="all"
# Backup directory location e.g /backups
BACKUPDIR="/var/backup/mysql"
# Mail setup
MAILCONTENT="quiet"
run it from cron by placing in /etc/crontab something like:
#MySQL Daily backup
45 5 * * * root /opt/automysqlbackup.sh >/dev/null 2>&1
Using PHP To Backup MySQL Database
Execute a database backup query from PHP file. Below is an example of using SELECT INTO OUTFILE query for creating table backup:
<?php
include 'config.php';
include 'opendb.php';
$tableName = 'mypet';
$backupFile = 'backup/mypet.sql';
$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);
include 'closedb.php';
?>
To restore the backup you just need to run LOAD DATA INFILE query like this :
<?php
include 'config.php';
include 'opendb.php';
$tableName = 'mypet';
$backupFile = 'mypet.sql';
$query = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
$result = mysql_query($query);
include 'closedb.php';
?>
For more information:http://www.noupe.com/development/10-ways-to-automatically-manually-backup-mysql-database.html