How to take MySQL Database Backup: Tips and Tutorials

However, in coming next section of this How to take MySQL Database Backup: Tips and Tutorials post, I am going to present some awesome process to backup MySQL Database either manually or automatically. So, go ahead and check them out as well opt to go with which perfectly suits your need and requirements.

 

How to e-mail yourself an automatic backup of MySQL database table with PHP

This is a superb script for taking MySQL database backup in account of an e-mail which hits your inbox with backup of specific tables along with an attached .sql file. It is highly worthy for a database-driven website as well as you can also set-up an e-mail account in order to congregate these backups.

This script works perfectly with a non-web accessible folder and runs on a daily cron job. However, Cron is a server tool which used to execute scripts periodically at specific times or regularly and hence give up the necessity to access in your browser.

Before, I take you away with the script, let me share some limitation of this script. Yes, it consist capacity of upto 2 MB for storing database, in case it exceed that limit you would required to execute into php mail attachment and php timeouts limits. Even you can compress the backup file prior to store or sending it.

<?php
// Create the mysql backup file
// edit this section
$dbhost = "yourhost"; // usually localhost
$dbuser = "yourusername";
$dbpass = "yourpassword";
$dbname = "yourdb";
$sendto = "Webmaster <webmaster@yourdomain.com>";
$sendfrom = "Automated Backup <backup@yourdomain.com>";
$sendsubject = "Daily Mysql Backup";
$bodyofemail = "Here is the daily backup.";
// don't need to edit below this section

$backupfile = $dbname . date("Y-m-d") . '.sql';
system("mysqldump -h $dbhost -u $dbuser -p$dbpass $dbname > $backupfile");

// Mail the file

include('Mail.php');
include('Mail/mime.php');

$message = new Mail_mime();
$text = "$bodyofemail";
$message->setTXTBody($text);
$message->AddAttachment($backupfile);
$body = $message->get();
$extraheaders = array("From"=>"$sendfrom", "Subject"=>"$sendsubject");
$headers = $message->headers($extraheaders);
$mail = Mail::factory("mail");
$mail->send("$sendto", $headers, $body);

// Delete the file from your server
unlink($backupfile);
?>

Backup MySQL Databases, Web Server Files to a FTP Server Automatically

If you occupy your own web server along with MySQL database server over a committed or VPS server, then it one amongst the perfect backup solution for you. There are so many dedicated hosting service provider which used to bring in practice of NAS or FTP servers in context of offering backup services. Moreover, on private VLAN these hosting service providers may hook you towards redundant centralized array.

Generating MySQL Databases Backup

Before proceeding further, lets know about mysqldump. It is nothing but a script or client program which is very useful in dumping or generating backup for mysql databases:

$ mysql -u root -h localhost -p -Bse 'show databases'

Output

Enter password:
brutelog
cake
faqs
mysql
phpads
snews
test
tmp
van
wp

Now, in account of this mysqldump command you can make backup for each database.

$ mysqldump -u root -h localhost -pmypassword faqs | gzip -9 > faqs-db.sql.gz

Creating A Simple Backup System For Your Installation

One of the great achievement of implementing NAS or FTP backup is the commitment of no data loss. However, you too employ several protocols to make backup of your data.
1. FTP
2. SSH
3. RSYNC
4. Other Commercial Solutions

Lets take a look over this sample setup

Your-server ===> ftp/nas server
IP:202.54.1.10 ===> 208.111.2.5

Now, assume your FTP login details as below:

FTP server IP: 106.121.5.3
FTP Username: webgranth
FTP Password: password
FTP Directory: /home/webgranth (or/)

You opt to store data as follows:
=> /home/webgranth/full/mm-dd-yyyy/files – Full Backup
=> /home/webgranth/incremental/mm-dd-yyyy/files – Incremental backup

Automating Backup With tar

After going through the process of making backup files through mysquldump and tar commands. Now, you must acquainted of shell script which is capable to automate all these procedure.

First of all the script will accumulate data from both MySQL database server as well as temporary directories of file systems.
Then, the script would seek to login in your FTP server and build a directory structure.
Script will create backup of your all files to the FTP server.
It will eradicate the temporary backup directory.
In case of FTP backup failure due to any reason, it will let you notify via an e-mail.

Now, the following command must be installed :
ncftp ftp client
mysqldump command
GNU tar command

Now, take a look over this sample of script.

#!/bin/sh
# System + MySQL backup script
# Full backup day - Sun (rest of the day do incremental backup)
# Copyright (c) 2005-2006 nixCraft <http://www.cyberciti.biz/fb/>
# This script is licensed under GNU GPL version 2.0 or above
# Automatically generated by http://bash.cyberciti.biz/backup/wizard-ftp-script.php
# ---------------------------------------------------------------------
### System Setup ###
DIRS="/home /etc /var/www"
BACKUP=/tmp/backup.$$
NOW=$(date +"%d-%m-%Y")
INCFILE="/root/tar-inc-backup.dat"
DAY=$(date +"%a")
FULLBACKUP="Sun"
### MySQL Setup ###
MUSER="admin"
MPASS="mysqladminpassword"
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
### FTP server Setup ###
FTPD="/home/vivek/incremental"
FTPU="vivek"
FTPP="ftppassword"
FTPS="208.111.11.2"
NCFTP="$(which ncftpput)"
### Other stuff ###
EMAILID="admin@theos.in"
### Start Backup for file system ###
[ ! -d $BACKUP ] && mkdir -p $BACKUP || :
### See if we want to make a full backup ###
if [ "$DAY" == "$FULLBACKUP" ]; then
FTPD="/home/vivek/full"
FILE="fs-full-$NOW.tar.gz"
tar -zcvf $BACKUP/$FILE $DIRS
else
i=$(date +"%Hh%Mm%Ss")
FILE="fs-i-$NOW-$i.tar.gz"
tar -g $INCFILE -zcvf $BACKUP/$FILE $DIRS
fi
### Start MySQL Backup ###
# Get all databases name
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
FILE=$BACKUP/mysql-$db.$NOW-$(date +"%T").gz
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done
### Dump backup using FTP ###
#Start FTP backup using ncftp
ncftp -u"$FTPU" -p"$FTPP" $FTPS<<EOF
mkdir $FTPD
mkdir $FTPD/$NOW
cd $FTPD/$NOW
lcd $BACKUP
mput *
quit
EOF
### Find out if ftp backup failed or not ###
if [ "$?" == "0" ]; then
rm -f $BACKUP/*
else
T=/tmp/backup.fail
echo "Date: $(date)">$T
echo "Hostname: $(hostname)" >>$T
echo "Backup failed" >>$T
mail -s "BACKUP FAILED" "$EMAILID" <$T
rm -f $T
fi

Creating Backup MySQL Databases using PHP

Here, three distinct ways have been assumed to backup your MySQL Database:

1. Run a Database Backup Query from PHP file.
2. Execute mysqldump in account of system() function
3. Create backup file using phpMyAdmin.

Run a Database Backup Query from PHP file

Take a look through this example presented below which make you acquainted of creating table backup with Select Into Outfile Query.

<?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';
?>

In context of restoring the backup data you are required to execute LOAD DATA INFILE query as shown below:

<?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';
?>

Here, It is quite significant to term a backup file as tablename.sql, which in return make you acquainted about the backup file from which table it concerns.

Execute mysqldump in account of system () function

The system () function puts its significance towards executing an external program. As MySQL is already integrated with tool for creating database backup i.e. mysqldump. Now, experience it in PHP script.

<?php
include 'config.php';
include 'opendb.php';
$backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile";
system($command);
include 'closedb.php';
?>

Create Backup file using phpMyAdmin

This method is very simple, easy and user-friendly to implement, as it never requires any programming skills at your end.
So, lets come, I will take you through this tutorial about creating backup for your MySQL database with phpMyAdmin.
First of all click on ‘export’ link present on phpMyAdmin main page.
Select the database you desire to create backup, analyze the relevant SQL options and provide backup file name.

<style type="text/css"> .style1 { width: 615px; } .style2 { text-align: right; width: 85px; } </style>

However, I opined that How to take MySQL Database Backup: Tips and Tutorials highly valuable for those who are very eager and wanders to create backup file for their database. This post might be highly worthy even for those who have little knowledge about this. So, go ahead and employ these method which literally give up your all hassles and worries about data loss.

How to take MySQL Database Backup: Tips and Tutorials