top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to take DataBase backup automatically using mysql?

+3 votes
545 views

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

posted Mar 27, 2015 by Nagaraj

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+3 votes
 
Best answer

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

answer Apr 2, 2015 by Vrije Mani Upadhyay
Similar Questions
+1 vote

I would like to listen your opinion about a situation. There is a function that is able to REMOVE all data from an specific date ?

I mean ... We are developing a demo script website, where users can join and test our system.

We need a database reset every 3 hours ... Delete all new data and back all changes to a default point. Better do that with php or mysql has an internal function that handle this.

0 votes

This is probably a no brainer (I'm new to Navicat) but I have a backup of a database from Navicat.

I want to be able to see if a certain field has changed since this morning in the backup (We are having problems with an order that somehow "duplicated" the items. I need to see if there was only 1 of each item or two removed from inventory). I don't need to do a "restore" into the database, just have a look at the backup.

Is this possible without going through the hoops of creating a copy of the database and restoring to the copy (I assume this is possible) - I DO NOT want to restore into the currently running database :-)

...