How to Backup MySQL Database using PHP

If you are a web developer, then the database backup must be a tough task for you. Therefore, regular database backup averts risk from losing the data, and it will help you to restore the database if there is any mistake. So, back up the database whenever possible is a good idea.

There are a lot of options available to backup the MySQL database in a file, and you can backup the database in a single click from the hosting server. But if you want to take MySQL database backup without login to your hosting server or phpMyAdmin, you can do it from our sample script. In this tutorial, we will create a PHP script to backup the MySQL database and save it in an SQL file.

SEE ALSO: Accessing Webcam and Capture Image using HTML5 and JavaScript

Perform MySQL Database Backup using PHP

Initially, make a list of all the PHP code together in backupDatabaseTables() function. Use backupDatabaseTables() function. So, you can backup specific tables or all tables from a database. It would be best if you had the following parameters to backup a MySQL database using PHP.

• $dbHost – Required, it specifies the host of the database.
• $dbUsername – Required, it specifies the database username.
• $dbPassword – Required, it specifies the database password.
• $dbName – Required, it specifies the database which you want to backup.
• $tables – Optional, it specifies the table names in a comma-separated string or array. Omit this parameter to take a backup of all tables of the database.

<?php

/**
 * @function    backupDatabaseTables
 * @author      Allsweb
 * @link        http://www.allsweb.com
 * @usage       Backup database tables and save in SQL file
 */
function backupDatabaseTables($dbHost,$dbUsername,$dbPassword,$dbName,$tables = '*'){
    //connect & select the database
    $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 

    //get all of the tables
    if($tables == '*'){
        $tables = array();
        $result = $db->query("SHOW TABLES");
        while($row = $result->fetch_row()){
            $tables[] = $row[0];
        }
    }else{
        $tables = is_array($tables)?$tables:explode(',',$tables);
    }

    //loop through the tables
    foreach($tables as $table){
        $result = $db->query("SELECT * FROM $table");
        $numColumns = $result->field_count;

        $return .= "DROP TABLE $table;";

        $result2 = $db->query("SHOW CREATE TABLE $table");
        $row2 = $result2->fetch_row();

        $return .= "\n\n".$row2[1].";\n\n";

        for($i = 0; $i < $numColumns; $i++){
            while($row = $result->fetch_row()){
                $return .= "INSERT INTO $table VALUES(";
                for($j=0; $j < $numColumns; $j++){
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) { $return .= '"'.$row[$j].'"' ; } else { $return .= '""'; }
                    if ($j < ($numColumns-1)) { $return.= ','; }
                }
                $return .= ");\n";
            }
        }

        $return .= "\n\n\n";
    }

    //save file
    $handle = fopen('db-backup-'.time().'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
}

Usage:

Use backupDatabaseTables() function in PHP to generate MySQL database backup and save in a SQL file.

backupDatabaseTables('localhost','root','*****','allsweb');

Also, read our previous blog- One Time Temporary Download Link with Expiration in PHP.