In the world of web it is important to take backup of our site and database frequently. We do not know when we would come across unexpected error which causes our site stop working. Here I will show you in PHP application which will help you to take backup as per your wish whenever you want to take backup. While most web hosting company do a daily backup of a customer’s database and site, relying on them to make backups and provide them at no cost is risky also.

Never take chances when your website is on the line. Make frequent backups !

If you have any query please feel free to send a query.

PHP 5.4
MySQL 5.5
Apace http Server 2.4
Netbeans 7.4 (Optional)

Step 1. Creates a php projects using Netbeans or create a project directory under htdocs.

Step 2. Create a php file called config.php with the below source code in the project directory


ini_set('display_errors', 'On');


// setting up the web root and server root
$thisFile = str_replace('\\', '/', __FILE__);

$webRoot = str_replace(array($docRoot, 'config.php'), '', $thisFile);
$srvRoot = str_replace('config.php', '', $thisFile);

define('SRV_ROOT', $srvRoot);

require_once 'common.php';

* End of file config.php

Step 3. Create a common.php file with below source code in the project directory

//Database backup
function db_backup($host, $user, $pass, $name, $tables = '*') {
    //connect to database using database credentials
    $dbConn = mysqli_connect($host, $user, $pass, $name) or die('MySQL connect failed. ' . mysqli_connect_error());

    //get all of the tables
    if ($tables == '*') {
        $tables = array();
        //fetch tables from database
        $result = mysqli_query($dbConn, 'SHOW TABLES');
        while ($row = mysqli_fetch_row($result)) {
            $tables[] = $row[0];
    } else {
        $tables = is_array($tables) ? $tables : explode(',', $tables);

    $return = '';
    //cycle through tables
    foreach ($tables as $table) {
        //select data from table
        $result = mysqli_query($dbConn, 'SELECT * FROM ' . $table);
        $num_fields = mysqli_num_fields($result);
        //drop table
        $return.= 'DROP TABLE ' . $table . ';';
        $row2 = mysqli_fetch_row(mysqli_query($dbConn, 'SHOW CREATE TABLE ' . $table));
        $return.= "\n\n" . $row2[1] . ";\n\n";

        //insert into statements for each table
        for ($i = 0; $i < $num_fields; $i++) {
            while ($row = mysqli_fetch_row($result)) {
                $return.= 'INSERT INTO ' . $table . ' VALUES(';
                for ($j = 0; $j < $num_fields; $j++) {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = preg_replace("#\n#", "\\n", $row[$j]);
                    if (isset($row[$j])) {
                        $return.= '"' . $row[$j] . '"';
                    } else {
                        $return.= '""';
                    if ($j < ($num_fields - 1)) {
                        $return.= ',';
                $return.= ");\n";

    //create a backup file
    $file = SRV_ROOT . 'db-backup-' . time() . '-' . (md5(implode(',', $tables))) . '.sql';

    //write backup sql file to disk
    if ($handle = fopen($file, 'w+')) {
        if (fwrite($handle, $return)) {
            return TRUE;
    return FALSE;

Step 4. Create a php web page backup.php with the following source code

require_once 'config.php';
<!DOCTYPE html>
        <meta charset="UTF-8"/>
    <p><strong>Starting database backup</strong></p>
        db_backup($dbHost, $dbUser, $dbPass, $dbName);
    <p style="color:green;"><strong>Database backup successfully done.</strong></p>

Step 5. Run the php file backup.php. You will get the backup sql file under the project directory.

That’s all. Thanks for reading.


I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

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