Tuesday 27 February 2018

Backup MySQL Database Using PHP



Mysql Database Backup is a very required work of most of the web developer who has use Mysql database in their web development. If you have take regular database backup then it will reduce the risk of losing of data and if we have store mysql database back up then we can easily restore database if any emergency issue has occurred. So, please take Mysql database backup on regular interval to prevent loss of important data.

There are many different method available to get backup of Mysql database in SQL file and we can get that file in a single click from database hosting server. But here we can get Mysql database backup from without login into server and we can get from our web application. So for this we have make this PHP script by using this script we can backup Mysql database from our web application and we have not login into our database hosting account or phpMyAdmin. This PHP script will make sql file from Mysql database and download in our local computer.

Here we have use simple PHP script for backup of Mysql database in a single click from our web application. In this PHP script we have PHP PDO for make of this script. We have also use PHP file system function for write SQL script in file and after this by using header() function we have force download file in local computer. In this script we have use simple Mysql query for fetch Mysql database and make sql file and after write that sql script in file and force download in local computer. So, this is simple script for Backup mysql database using PHP.






Source Code



<?php
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
$get_all_table_query = "SHOW TABLES";
$statement = $connect->prepare($get_all_table_query);
$statement->execute();
$result = $statement->fetchAll();

if(isset($_POST['table']))
{
 $output = '';
 foreach($_POST["table"] as $table)
 {
  $show_table_query = "SHOW CREATE TABLE " . $table . "";
  $statement = $connect->prepare($show_table_query);
  $statement->execute();
  $show_table_result = $statement->fetchAll();

  foreach($show_table_result as $show_table_row)
  {
   $output .= "\n\n" . $show_table_row["Create Table"] . ";\n\n";
  }
  $select_query = "SELECT * FROM " . $table . "";
  $statement = $connect->prepare($select_query);
  $statement->execute();
  $total_row = $statement->rowCount();

  for($count=0; $count<$total_row; $count++)
  {
   $single_result = $statement->fetch(PDO::FETCH_ASSOC);
   $table_column_array = array_keys($single_result);
   $table_value_array = array_values($single_result);
   $output .= "\nINSERT INTO $table (";
   $output .= "" . implode(", ", $table_column_array) . ") VALUES (";
   $output .= "'" . implode("','", $table_value_array) . "');\n";
  }
 }
 $file_name = 'database_backup_on_' . date('y-m-d') . '.sql';
 $file_handle = fopen($file_name, 'w+');
 fwrite($file_handle, $output);
 fclose($file_handle);
 header('Content-Description: File Transfer');
 header('Content-Type: application/octet-stream');
 header('Content-Disposition: attachment; filename=' . basename($file_name));
 header('Content-Transfer-Encoding: binary');
 header('Expires: 0');
 header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize($file_name));
    ob_clean();
    flush();
    readfile($file_name);
    unlink($file_name);
}

?>
<!DOCTYPE html>
<html>
 <head>
  <title>How to Take Backup of Mysql Database using PHP Code</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
 </head>
 <body>
  <br />
  <div class="container">
   <div class="row">
    <h2 align="center">How to Take Backup of Mysql Database using PHP Code</h2>
    <br />
    <form method="post" id="export_form">
     <h3>Select Tables for Export</h3>
    <?php
    foreach($result as $table)
    {
    ?>
     <div class="checkbox">
      <label><input type="checkbox" class="checkbox_table" name="table[]" value="<?php echo $table["Tables_in_testing"]; ?>" /> <?php echo $table["Tables_in_testing"]; ?></label>
     </div>
    <?php
    }
    ?>
     <div class="form-group">
      <input type="submit" name="submit" id="submit" class="btn btn-info" value="Export" />
     </div>
    </form>
   </div>
  </div>
 </body>
</html>
<script>
$(document).ready(function(){
 $('#submit').click(function(){
  var count = 0;
  $('.checkbox_table').each(function(){
   if($(this).is(':checked'))
   {
    count = count + 1;
   }
  });
  if(count > 0)
  {
   $('#export_form').submit();
  }
  else
  {
   alert("Please Select Atleast one table for Export");
   return false;
  }
 });
});
</script>

17 comments:

  1. great was looking for this already long time
    hopefully there is one coming on how to restore from mysql file to the database?

    ReplyDelete
  2. super. vos cours sont trop top

    ReplyDelete
  3. thank you for the code
    but when i change the table name in line 2 it want work it only work with table that called "testing" pls help

    ReplyDelete
  4. And how to restore a Backup.sql using PDO in PHP

    ReplyDelete
  5. i am getting "There is an error in Database Import" this msg even after successfully imported

    ReplyDelete
  6. hello i have names arabic for export what is the solution for this ????? ????

    ReplyDelete
  7. hello, thank you
    i have names arabic for export what is the solution for this ????? ????

    ReplyDelete
  8. great solution, usually I use this one: https://github.com/SergheiPogor/BackUp-MySQL

    ReplyDelete
  9. it helped me thouogh but,
    hey if the records consists apostrophe, data like father's Name , then the backup query gets all jumbled up, means the insert query gets compromised after apostrophe, considered as end of statement and all.
    whats the resolution

    example:
    INSERT INTO web_mail_keywords (KEY, FIELDNAME, DISPLAYNAME, TABLENAME, TMPTYPE) VALUES ('7','FNAME','Father's Name','mas_employee','0');

    ReplyDelete
  10. the NULL values are appended as Empty String (NULL) -->'' in insert query

    ReplyDelete
  11. hello sir, sorry.. can you send file SQL... sorry can't work.. can you help me.. thank

    ReplyDelete
  12. Notice: Undefined index: Tables_in_testing in C:\xampp\htdocs\testing\index.php on line 76

    how to solve this error?

    thanks

    ReplyDelete
  13. Notice: Undefined index: tables_in_testing in E:\xampp\htdocs\erratum\save_bdd.php on line 76

    ReplyDelete
  14. Notice: Undefined index: tables_in_testing in E:\xampp\htdocs\erratum\save_bdd.php on line 76

    How to fix it?

    PS: Line 76:
    input type="checkbox" class="checkbox_table" name="table[]" value="" />

    ReplyDelete