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

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

 $output = '';
 foreach($_POST["table"] as $table)
  $show_table_query = "SHOW CREATE TABLE " . $table . "";
  $statement = $connect->prepare($show_table_query);
  $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);
  $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);
 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));

<!DOCTYPE html>
  <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" />
  <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>
    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 class="form-group">
      <input type="submit" name="submit" id="submit" class="btn btn-info" value="Export" />
  var count = 0;
    count = count + 1;
  if(count > 0)
   alert("Please Select Atleast one table for Export");
   return false;

1 comment:

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