How to take DataBase backup automatically using mysql?

Question

How to take backup of Database once in a day using MySql.

Answer 1

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