Backup/Restore MySql database using PHP

MySQL is an open-source relational database management system (RDBMS). MySQL is a central component of the LAMP open-source web application software stack. LAMP is an acronym for “Linux, Apache, MySQL, Perl/PHP/Python”.

In current scenario, databases constitutes a major backbone in software development. We need to backup databases regularly in order to recover in case of any failure or crash. Manually backing up database can be time consuming and hectic job.

In this post, we will be backing up and restoring database easily using PHP. The basic idea is to dump database table structure and data into a SQL file for backing up. While restoring, the SQL queries dumped into the file are executed one after another.

You may also like: Bulk Insert Update into MySQL using PHP

PHP Source Code for backing up Database

<?php

$connection = mysqli_connect('localhost','user','pass','quiz');

$tables = array();
$result = mysqli_query($connection,"SHOW TABLES");
while($row = mysqli_fetch_row($result)){
  $tables[] = $row[0];
}

$return = '';
foreach($tables as $table){
  $result = mysqli_query($connection,"SELECT * FROM ".$table);
  $num_fields = mysqli_num_fields($result);
  
  $return .= 'DROP TABLE '.$table.';';
  $row2 = mysqli_fetch_row(mysqli_query($connection,"SHOW CREATE TABLE ".$table));
  $return .= "\n\n".$row2[1].";\n\n";
  
  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]);
        if(isset($row[$j])){ $return .= '"'.$row[$j].'"';}
        else{ $return .= '""';}
        if($j<$num_fields-1){ $return .= ',';}
      }
      $return .= ");\n";
    }
  }
  $return .= "\n\n\n";
}

//save file
$handle = fopen("backup.sql","w+");
fwrite($handle,$return);
fclose($handle);
echo "Successfully backed up";

?>

Source Code for Restoring the Database

<?php

$connection = mysqli_connect('localhost','root','','test');

$filename = 'backup.sql';
$handle = fopen($filename,"r+");
$contents = fread($handle,filesize($filename));

$sql = explode(';',$contents);
foreach($sql as $query){
  $result = mysqli_query($connection,$query);
  if($result){
      echo '<tr><td><br></td></tr>';
      echo '<tr><td>'.$query.' <b>SUCCESS</b></td></tr>';
      echo '<tr><td><br></td></tr>';
  }
}
fclose($handle);
echo 'Successfully imported';

?>

In this way, you can backup database and restore it when needed.

You can follow this video for detail guidance :