MySQL Database Backup using PHP

In this tutorial I am going to show you how to take MySQL database backup using PHP programming language. In the world of web it is important to take backup of our site and database frequently. You never know when you would come across unexpected error that may cause your 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!

Prerequisites

PHP 5.4 – 7.4.3, MySQL 5.5 – 8.0.17, Apace HTTP Server 2.4

Project Root Directory

Create a PHP project’s root directory called php-backup-mysql-database under Apache HTTP server’s htdocs folder. I am going to keep all PHP scripts under this directory.

PHP Scripts

Create a PHP file called config.php with the below source code in the project directory. This piece of code checks the project root directory.

<?php

	ini_set('display_errors', 'On');

	error_reporting(E_ALL);

	// setting up the web root and server root
	$thisFile = str_replace('\\', '/', __FILE__);
	$docRoot = $_SERVER['DOCUMENT_ROOT'];

	$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
*/

Create a common.php file with below source code in the project directory. This file actually defines all common functions for the project.

<?php

//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";
            }
        }
        $return.="\n\n\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;
        }
        fclose($handle);
    }
    return FALSE;
}

Backup Script

Create a php web page php-backup-mysql-database.php with the following source code. It calls the database backup function.

The backup SQL file will include all tables with drop statements, create statements and insert statements.

<?php
require_once 'config.php';
?>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8"/>
        <title></title>
    </head>
<body>
    <p><strong>Starting MySQL Database Backup</strong></p>
    <?php
        db_backup('localhost', 'root', 'root', 'roytuts');
    ?>
    <p style="color:green;"><strong>Database Backup Successfully Done</strong></p>
</body>
</html>

Testing the Application

Running the above file using the URL http://localhost/php-backup-mysql-database/php-backup-mysql-database.php in the browser will create an SQL file under the project root directory and you will see the following message on the web page.

mysql database backup php

That’s all about MySQL database backup in PHP.

Source Code

Download

Leave a Comment