Skip to main content

How to backup MySQL Database tables using php

if you want create backup or generate backup file using php, its is very simple and essay understand to everyone. and backup logic is very simple,

Demo link :
http://freeteachnology.hol.es/MysqlBackup/

Code link :
https://drive.google.com/open?id=0BxmTZPVcu72fUjlIRWpUeHlOVzQ

following code demo run as :


One of the most important tasks any developer needs to do often is back up their MySQL database. In many cases, the database is what drives most of the site.

Of course, you'll need to provide database credentials to the function, as well as an array of tables you'd like to backup. If you provide a "*" or no tables, a complete database backup will run. The script does the rest!

In any situation we required a MySQL database tables backup with php scripting. Sometimes its helpful and its very simple to take backup MySQL database tables using php. Easy faster and very simple and clean script for getting your MySQL database backup.

You must have the following details for using the backup MySQL database table script. you should have host name , username ,password and the database name . You can easily customize the script for your requirement and add or remove DROP commands etc.

After setting the required details you will get a file in the current location (where this page running ) .

Yes you have done with backup MySQL database table using php. try it today?


In this article i am going to explore how to download files in php from web server to local machine. Download files in php just a simple process.
You do not need more lines of code to achieve that.
For make easy php works with header to download and read a file.
 For rest of code understanding read code comments of each line of code.

 if you want to create demo then please follow follwoing steps:
 1-header.php
 it include link only bootstrap files and js link,

 Code :
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <title>how to backup MySQL Database tables using php
</title>
   <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" rel="stylesheet">
   <link href="error.css" rel="stylesheet">
   <script   src="https://code.jquery.com/jquery-3.1.1.js"   integrity="sha256-16cdPddA6VdVInumRGo6IbivbERE8p7CQR3HzTBuELA="   crossorigin="anonymous">
   </script>
   <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
    <link href="bootstrap-tour.css" rel="stylesheet">
    <script src="bootstrap-tour.js"></script>
</head>
<body>
<!-- start:header -->
<nav class="navbar navbar-default navbar-fixed-top">
  <div class="container-fluid" id="freeteachnology">
    <!-- Brand and toggle get grouped for better mobile display -->
    <div class="navbar-header">     
      <a class="navbar-brand" href="#">Free Teachnology</a>
    </div>
    <!-- Collect the nav links, forms, and other content for toggling -->
    <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
      <ul class="nav navbar-nav">
        <li class="active"><a href="#">Home <span class="sr-only"></span></a></li>
        <li><a href="#">About us</a></li>   
        <li><a href="#">Contact us</a></li>         
      </ul>
      </div><!-- /.navbar-collapse -->
  </div><!-- /.container-fluid -->
</nav>

2-footer.php :
footer file link only social website :

Code :
<!-- Start::footer  -->
<footer>
        <div class="footer footer-bottom">
        <div class="container">
            <p class="pull-left"> Copyright © <?php echo date('Y');?>. Design and Develop by - Disuza Jen. </p>
            <div class="pull-right">
                <ul class="nav nav-pills payments">
                    <li>
                    <a  target="blank" href="https://www.facebook.com/profile.php?id=100011576444095">
                    <i class="fa fa-facebook"></i></a>
                    </li>
                    <li>
                    <a target="blank"  href="https://plus.google.com/116407769803493244155">
                    <i class="fa fa-google"></i></a>
                    </li>
                    <li>
                         <a target="blank"  href="https://twitter.com/disuzajen1">
                         <i class="fa fa-twitter"></i></a></li>
                    <li>
                        <a target="blank" href="https://plus.google.com/116407769803493244155">
                    <i class="fa fa-link"></i>
                    </a>
                    </li>
                </ul> 
            </div>
        </div>
    </div>
    <!--/.footer-bottom--> 
</footer>
<!-- End::footer  -->
<link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/font-awesome/4.1.0/css/font-awesome.min.css">
</body>
</html>

 3-index.php
index.php files include header and footer link as well as link of action.php files,
following code is very simple and understand no need to extra explaination,

Code :
<?php include('header.php');?>
<!-- end:header -->
<br><br><br>
<style>
#loader {
    bottom: 0;
    height: 175px;
    left: 0;
    margin: auto;
    position: absolute;
    right: 0;
    top: 0;
    width: 175px;
}
#loader {
    bottom: 0;
    height: 175px;
    left: 0;
    margin: auto;
    position: absolute;
    right: 0;
    top: 0;
    width: 175px;
}
#loader .dot {
    bottom: 0;
    height: 100%;
    left: 0;
    margin: auto;
    position: absolute;
    right: 0;
    top: 0;
    width: 87.5px;
}
#loader .dot::before {
    border-radius: 100%;
    content: "";
    height: 87.5px;
    left: 0;
    position: absolute;
    right: 0;
    top: 0;
    transform: scale(0);
    width: 87.5px;
}
#loader .dot:nth-child(7n+1) {
    transform: rotate(45deg);
}
#loader .dot:nth-child(7n+1)::before {
    animation: 0.8s linear 0.1s normal none infinite running load;
    background: #00ff80 none repeat scroll 0 0;
}
#loader .dot:nth-child(7n+2) {
    transform: rotate(90deg);
}
#loader .dot:nth-child(7n+2)::before {
    animation: 0.8s linear 0.2s normal none infinite running load;
    background: #00ffea none repeat scroll 0 0;
}
#loader .dot:nth-child(7n+3) {
    transform: rotate(135deg);
}
#loader .dot:nth-child(7n+3)::before {
    animation: 0.8s linear 0.3s normal none infinite running load;
    background: #00aaff none repeat scroll 0 0;
}
#loader .dot:nth-child(7n+4) {
    transform: rotate(180deg);
}
#loader .dot:nth-child(7n+4)::before {
    animation: 0.8s linear 0.4s normal none infinite running load;
    background: #0040ff none repeat scroll 0 0;
}
#loader .dot:nth-child(7n+5) {
    transform: rotate(225deg);
}
#loader .dot:nth-child(7n+5)::before {
    animation: 0.8s linear 0.5s normal none infinite running load;
    background: #2a00ff none repeat scroll 0 0;
}
#loader .dot:nth-child(7n+6) {
    transform: rotate(270deg);
}
#loader .dot:nth-child(7n+6)::before {
    animation: 0.8s linear 0.6s normal none infinite running load;
    background: #9500ff none repeat scroll 0 0;
}
#loader .dot:nth-child(7n+7) {
    transform: rotate(315deg);
}
#loader .dot:nth-child(7n+7)::before {
    animation: 0.8s linear 0.7s normal none infinite running load;
    background: magenta none repeat scroll 0 0;
}
#loader .dot:nth-child(7n+8) {
    transform: rotate(360deg);
}
#loader .dot:nth-child(7n+8)::before {
    animation: 0.8s linear 0.8s normal none infinite running load;
    background: #ff0095 none repeat scroll 0 0;
}
#loader .lading {
    background-image: url("../images/loading.gif");
    background-position: 50% 50%;
    background-repeat: no-repeat;
    bottom: -40px;
    height: 20px;
    left: 0;
    position: absolute;
    right: 0;
    width: 180px;
}
@keyframes load {
100% {
    opacity: 0;
    transform: scale(1);
}
}
@keyframes load {
100% {
    opacity: 0;
    transform: scale(1);
}
}

</style>
<div class="cleafix"></div>
<div class="container">
<div class="alert alert-info">
    <strong>Hi..</strong> how to backup MySQL Database tables using php
</div>
       <div class="alert alert-warning">
        <a href="https://drive.google.com/open?id=0BxmTZPVcu72fUjlIRWpUeHlOVzQ
" class="btn btn-xs btn-warning pull-right" target="blank();">Click</a>
        <strong> Code download link-</strong>
    </div>
     <br><br>  
 </div>
<div class="container">
  <div class="row">     
       <a href="action.php"> 
        <button type="button" class="btn btn-primary" onclick="waitingDialog.show();setTimeout(function () {
          waitingDialog.hide();}, 3000);">Create Backup
        </button>
        </a>
  </div>
</div>
<br><br>  <br><br>  <br><br>  <br><br>  
 <link href="//maxcdn.bootstrapcdn.com/font-awesome/4.2.0/css/font-awesome.min.css" rel="stylesheet">
</br>
<?php include('footer.php');?>
<script>
/**
 * Module for displaying "Waiting for..." dialog using Bootstrap
 *
 * @author Eugene Maslovich <ehpc@em42.ru>
 */
var waitingDialog = waitingDialog || (function ($) {
    'use strict';
  // Creating modal dialog's DOM
  var $dialog = $(
    '<div class="container"><div class="row"><div id="loader"><div class="dot"></div><div class="dot"></div><div class="dot"></div><div class="dot"></div><div class="dot"></div><div class="dot"></div>      <div class="dot"></div><div class="dot"></div><div class="lading"></div></div></div></div>');

  return {
    /**
     * Opens our dialog
     * @param message Custom message
     * @param options Custom options:
     *          options.dialogSize - bootstrap postfix for dialog size, e.g. "sm", "m";
     *          options.progressType - bootstrap postfix for progress bar type, e.g. "success", "warning".
     */
    show: function (message, options) {
      // Assigning defaults
      if (typeof options === 'undefined') {
        options = {};
      }
      if (typeof message === 'undefined') {
        message = 'Loading';
      }
      var settings = $.extend({
        dialogSize: 'm',
        progressType: '',
        onHide: null // This callback runs after the dialog was hidden
      }, options);

      // Configuring dialog
      $dialog.find('.modal-dialog').attr('class', 'modal-dialog').addClass('modal-' + settings.dialogSize);
      $dialog.find('.progress-bar').attr('class', 'progress-bar');
      if (settings.progressType) {
        $dialog.find('.progress-bar').addClass('progress-bar-' + settings.progressType);
      }
      $dialog.find('h3').text(message);
      // Adding callbacks
      if (typeof settings.onHide === 'function') {
        $dialog.off('hidden.bs.modal').on('hidden.bs.modal', function (e) {
          settings.onHide.call($dialog);
        });
      }
      // Opening dialog
      $dialog.modal();
    },
    /**
     * Closes dialog
     */
    hide: function () {
      $dialog.modal('hide');
    }
  };
})(jQuery);
</script>

loader will be show when click on button and generate mysql backup

5-action.php :

action.php files show or create MySQL backup ,database connection first create and assign new name of file

Code :
<?php
$backup_file = 'db-backup-'.time().'.sql';
 // get backup
 $hostname="localhost";
  $username="";
  $password="";
  $dbname="";
?>
  after that call function backup_tables, function call when check table in exist in database as well as make final array of table and structure,

  Code :
<?php 
  function &backup_tables($host, $user, $pass, $name, $tables = '*'){
  $data = "\n/*---------------------------------------------------------------".
          "\n  SQL DB BACKUP ".date("d.m.Y H:i")." ".
          "\n  HOST: {$host}".
          "\n  DATABASE: {$name}".
          "\n  TABLES: {$tables}".
          "\n  ---------------------------------------------------------------*/\n";
  $link = mysql_connect($host,$user,$pass);
  mysql_select_db($name,$link);
  mysql_query( "SET NAMES `utf8` COLLATE `utf8_general_ci`" , $link ); // Unicode

  if($tables == '*'){ //get all of the tables
    $tables = array();
    $result = mysql_query("SHOW TABLES");
    while($row = mysql_fetch_row($result)){
      $tables[] = $row[0];
    }
  }else{
    $tables = is_array($tables) ? $tables : explode(',',$tables);
  }

  foreach($tables as $table){
    $data.= "\n/*---------------------------------------------------------------".
            "\n  TABLE: `{$table}`".
            "\n  ---------------------------------------------------------------*/\n";           
    $data.= "DROP TABLE IF EXISTS `{$table}`;\n";
    $res = mysql_query("SHOW CREATE TABLE `{$table}`", $link);
    $row = mysql_fetch_row($res);
    $data.= $row[1].";\n";
    $result = mysql_query("SELECT * FROM `{$table}`", $link);
    $num_rows = mysql_num_rows($result);    
    if($num_rows>0){
      $vals = Array(); $z=0;
      for($i=0; $i<$num_rows; $i++){
        $items = mysql_fetch_row($result);
        $vals[$z]="(";
        for($j=0; $j<count($items); $j++){
          if (isset($items[$j])) { $vals[$z].= "'".mysql_real_escape_string( $items[$j], $link )."'"; } else { $vals[$z].= "NULL"; }
          if ($j<(count($items)-1)){ $vals[$z].= ","; }
        }
        $vals[$z].= ")"; $z++;
      }
      $data.= "INSERT INTO `{$table}` VALUES ";      
      $data .= "  ".implode(";\nINSERT INTO `{$table}` VALUES ", $vals).";\n";
    }
  }
  mysql_close( $link );
 return $data;
}
?>

file download function is very simple if create new database file :
Code :
<?php
//start::file downmload fnction:-
$file = $backup_file;
if (file_exists($file)) {
    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename="'.basename($file).'"');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize($file));
    readfile($file);
    exit;
}
//end::file download
?>

following is full code of action.php files :

Code :


<?php 
// create backup
//////////////////////////////////////
ini_set("memory_limit", "-1");
set_time_limit(0);
$backup_file = 'db-backup-'.time().'.sql';
 // get backup
 $hostname="";
  $username="";
  $password="";
  $dbname="";

$mybackup = backup_tables($localhost,$username,$password,$dbname,"*");
// save to file
$handle = fopen($backup_file,'w+');
// chmod($handle, 755);   // decimal; probably incorrect
fwrite($handle,$mybackup);
fclose($handle);
//start::file downmload fnction:-
$file = $backup_file;
if (file_exists($file)) {
    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename="'.basename($file).'"');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize($file));
    readfile($file);
    exit;
}
//end::file download
//create database backup::-
function &backup_tables($host, $user, $pass, $name, $tables = '*'){
  $data = "\n/*---------------------------------------------------------------".
          "\n  SQL DB BACKUP ".date("d.m.Y H:i")." ".
          "\n  HOST: {$host}".
          "\n  DATABASE: {$name}".
          "\n  TABLES: {$tables}".
          "\n  ---------------------------------------------------------------*/\n";
  $link = mysql_connect($host,$user,$pass);
  mysql_select_db($name,$link);
  mysql_query( "SET NAMES `utf8` COLLATE `utf8_general_ci`" , $link ); // Unicode

  if($tables == '*'){ //get all of the tables
    $tables = array();
    $result = mysql_query("SHOW TABLES");
    while($row = mysql_fetch_row($result)){
      $tables[] = $row[0];
    }
  }else{
    $tables = is_array($tables) ? $tables : explode(',',$tables);
  }

  foreach($tables as $table){
    $data.= "\n/*---------------------------------------------------------------".
            "\n  TABLE: `{$table}`".
            "\n  ---------------------------------------------------------------*/\n";           
    $data.= "DROP TABLE IF EXISTS `{$table}`;\n";
    $res = mysql_query("SHOW CREATE TABLE `{$table}`", $link);
    $row = mysql_fetch_row($res);
    $data.= $row[1].";\n";
    $result = mysql_query("SELECT * FROM `{$table}`", $link);
    $num_rows = mysql_num_rows($result);    
    if($num_rows>0){
      $vals = Array(); $z=0;
      for($i=0; $i<$num_rows; $i++){
        $items = mysql_fetch_row($result);
        $vals[$z]="(";
        for($j=0; $j<count($items); $j++){
          if (isset($items[$j])) { $vals[$z].= "'".mysql_real_escape_string( $items[$j], $link )."'"; } else { $vals[$z].= "NULL"; }
          if ($j<(count($items)-1)){ $vals[$z].= ","; }
        }
        $vals[$z].= ")"; $z++;
      }
      $data.= "INSERT INTO `{$table}` VALUES ";      
      $data .= "  ".implode(";\nINSERT INTO `{$table}` VALUES ", $vals).";\n";
    }
  }
  mysql_close( $link );
  return $data;
}
?>

i hope that code is working very code and create backup very fast and esaiy way,if you like my blog plaese comment and share.

Labels:

Comments

Post a Comment

Popular posts from this blog

using PDO database connection add,update,delete,edit operation

PDO advantage : 1-Object Oriented 2-Bind parameters in statements (security) 3-Allows for prepared statements and rollback functionality (consistency) 4-Throws catcheable exceptions for better error handling (quality) 5-Exception mode; no need to check error state after each API call. It's best to tell PDO how you'd like the data to be fetched. You have the following options: 1-PDO::FETCH_ASSOC: returns an array indexed by column name. 2-PDO::FETCH_BOTH: (default):returns an array indexed by both column name and number. 3-PDO::FETCH_BOUND:Assigns the values of your columns to the variables set with the ->bindColumn() method. 4-PDO::FETCH_CLASS: Assigns the values of your columns to properties of the named class. It will create the properties if matching properties do not exist. 5-PDO::FETCH_INTO:Updates an existing instance of the named class. 6-PDO::FETCH_LAZY: Combines. 7-PDO::FETCH_BOTH/PDO:FETCH_OBJ, creating the object variable names as t...
Post a Comment
Read more

Profile Share Fixing the Thumbnail Image, Title and Description for Shared Links

Profile Share Fixing the Thumbnail Image, Title and Description for Shared Links user want to share any information then use following code  and read step by step Profile Share Fixing the Thumbnail Image, Title and Description for Shared Links if you want share profile on following social link : 1-Facebook 2-twitter.com 3-LinkedIn 4-google +, Code link : https://drive.google.com/open?id=1IzTZZh_0euDqFSHL_vPRiQePlNTw3h-q Demo link : http://freeteachnology.hol.es/socialshare/ To modify a page's thumbnail image, description, and additional metadata for these services, you can provide meta tags in the HTML code of the page.Implementing Open Graph Meta Tags You can implement meta tags in a number of ways. In content management systems might  be allow you to modify a page's meta tags , then use following code in meta section of your project code, <link href="bootstrap.min.css" rel="stylesheet"> <link href="bootstrap-tour.m...
Post a Comment
Read more

GUID for globally unique identifier

How to create GUID in php 1-guid stands for globally unique identifier generally used to create random unique strings in php, create access token in php 2-Mostly use of GUID for generating access token, generate unique id, generating unique string in php. Using this article how to create guide in php you can create a random string for any use to keep unique 3-GUID consists of alphanumeric characters only and is grouped in five groups separated by hyphens as seen in this example: 3F2504E0-4F89-11D3-9A0C-0305E82C3301 Eg:- <?php /** * Generate Globally Unique Identifier (GUID) * E.g. 2EF40F5A-ADE8-5AE3-2491-85CA5CBD6EA7 * * @param boolean $include_braces Set to true if the final guid needs * to be wrapped in curly braces * @return string */ function generateGuid($include_braces = false) { if (function_exists('com_create_guid')) { if ($include_braces === true) { return com_create_guid(); } else { return substr(com_cr...
2 comments
Read more