How to Backup MySQL Database using PHP

The database backup is a most important task for every web developer. Regular database backup prevents risk to lose the data and it helps to restore the database if any issue occurred. So, backup the database whenever possible is a good idea.

There many ways available to backup MySQL database in a file and you can backup database in one click from hosting server. But if you want to take MySQL database backup without login to your hosting server or phpMyAdmin, you can do it from the script using PHP. In this tutorial, we will build a PHP script to backup MySQL database and save in a SQL file.

Perform MySQL Database Backup using PHP

All the PHP code will be grouped together in backupDatabaseTables() function. Using backupDatabaseTables() function you can backup specific tables or all tables from a database. The following parameters are required to backup MySQL database using PHP.

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

<?php

/**
 * @function    backupDatabaseTables
 * @author      CodexWorld
 * @link        http://www.codexworld.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','*****','codexworld');

Restore / Import MySQL Database from SQL File using PHP