in ,

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.

// Create the mysql backup file
// edit this section
$dbhost = "yourhost"; // usually localhost
$dbuser = "yourusername";
$dbpass = "yourpassword";
$dbname = "yourdb";
$sendto = "Webmaster <>";
$sendfrom = "Automated Backup <>";
$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


$message = new Mail_mime();
$text = "$bodyofemail";
$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


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'


Enter password:

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
4. Other Commercial Solutions

Lets take a look over this sample setup

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

Now, assume your FTP login details as below:

FTP server IP:
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.

# System + MySQL backup script
# Full backup day - Sun (rest of the day do incremental backup)
# Copyright (c) 2005-2006 nixCraft <>
# This script is licensed under GNU GPL version 2.0 or above
# Automatically generated by
# ---------------------------------------------------------------------
### System Setup ###
DIRS="/home /etc /var/www"
NOW=$(date +"%d-%m-%Y")
DAY=$(date +"%a")
### MySQL Setup ###
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
### FTP server Setup ###
NCFTP="$(which ncftpput)"
### Other stuff ###
### Start Backup for file system ###
[ ! -d $BACKUP ] && mkdir -p $BACKUP || :
### See if we want to make a full backup ###
if [ "$DAY" == "$FULLBACKUP" ]; then
tar -zcvf $BACKUP/$FILE $DIRS
i=$(date +"%Hh%Mm%Ss")
### Start MySQL Backup ###
# Get all databases name
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
FILE=$BACKUP/mysql-$db.$NOW-$(date +"%T").gz
### Dump backup using FTP ###
#Start FTP backup using ncftp
ncftp -u"$FTPU" -p"$FTPP" $FTPS<<EOF
mkdir $FTPD
mkdir $FTPD/$NOW
mput *
### Find out if ftp backup failed or not ###
if [ "$?" == "0" ]; then
rm -f $BACKUP/*
echo "Date: $(date)">$T
echo "Hostname: $(hostname)" >>$T
echo "Backup failed" >>$T
rm -f $T


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.

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:

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.

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";
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

What do you think?

Written by Leander crow


Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *





External Links for SEO

A Brief Beginner’s SEO Guide: How to Rule Google