How to Backup MySQL Database using PHP

Database backup is the most important task for every web developer. Regular backups prevent the risk of losing data and help restore the database if any issue occurs. So, backing up the database whenever possible is a good idea.

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

Perform MySQL Database Backup using PHP

All the PHP code will be grouped together in the backupDatabaseTables() function. Using the 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.

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 
    $return = ''; 
    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] = preg_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); 
    return true; 
}

Usage:
Call the backupDatabaseTables() function in PHP to generate MySQL database backup and save it in a SQL file.

// Database credentials 
$DB_HOST    = 'localhost'; 
$DB_USERNAME = 'root'; 
$DB_PASSWORD = 'root'; 
$DB_NAME    = 'codexworld_db'; 
 
// Generate DB backup 
backupDatabaseTables($DB_HOST, $DB_USERNAME, $DB_PASSWORD, $DB_NAME);

Restore / Import MySQL Database from SQL File using PHP