Backup a MySQL Database and Download as a File

Backup a mysql database and download as a file

A very simple solution would be something like (first example):
http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/using-php-to-backup-mysql-databases.aspx

Naturally this will only make a Data dump of the table.

What you could do is use this code:

http://snipplr.com/view/173/mysql-dump/

What this code does is actually gets a description of the table (i.e its structure), creates all the tables and pushes data. pretty much like any other tool does.

Then its just a matter of saving it from string to a file (file_put_contents() for instance or something similar, depending on your preference and need)

PHP Download complete mysql database as .sql backup file

Nevermind, I figured it out myself.

This adds events and tables, triggers/ stored procedures should work in a similar fashion:

function Export_Database($host,$user,$pass,$name,$tables=false,$backup_name=false){
  $mysqli = new mysqli($host,$user,$pass,$name);
  $mysqli->select_db($name);
  $mysqli->query("SET NAMES 'utf8'");
  $queryTables    = $mysqli->query("SHOW TABLES");
  while($row = $queryTables->fetch_row()){
    $target_tables[] = $row['0']; //put each table name into array
  }
  if($tables){
    $target_tables = array_intersect($target_tables, $tables);
  }
  foreach($target_tables as $table){
    $result         =   $mysqli->query('SELECT * FROM '.$table);
    $fields_amount  =   $result->field_count;
    $rows_num       =   $mysqli->affected_rows;
    $res            =   $mysqli->query('SHOW CREATE TABLE '.$table);
    $TableMLine     =   $res->fetch_row();
    $content        = (!isset($content) ?  '' : $content) . "\n\n".$TableMLine[1].";\n\n";

    for ($i = 0, $st_counter = 0; $i < $fields_amount;   $i++, $st_counter=0){
      while($row = $result->fetch_row()){
        //when started (and every after 100 command cycle):
        if ($st_counter%100 == 0 || $st_counter == 0 ){
          $content .= "\nINSERT INTO ".$table." VALUES";
        }
        $content .= "\n(";
        for($j=0; $j<$fields_amount; $j++){
          $row[$j] = str_replace("\n","\\n", addslashes($row[$j]) );
          if (isset($row[$j])){
            $content .= '"'.$row[$j].'"' ;
          } else {
            $content .= '""';
          }
          if ($j<($fields_amount-1)){
            $content.= ',';
          }
        }
        $content .=")";
        //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
        if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num){
          $content .= ";";
        } else {
          $content .= ",";
        }
        $st_counter=$st_counter+1;
      }
    } $content .="\n\n\n";
  }

  $events = $mysqli->query("SHOW EVENTS");
  while($events && ($row = $events->fetch_row())){
    $res = $mysqli->query("SHOW CREATE EVENT ".$row[0].'.'.$row[1]);
    $TableMLine = $res->fetch_row();
    $content .= "\n\n".$TableMLine[3].";\n\n";
  }

  $backup_name = $backup_name ? $backup_name : $name.".sql";
  header('Content-Type: application/octet-stream');
  header("Content-Transfer-Encoding: Binary");
  header("Content-disposition: attachment; filename=\"".$backup_name."\"");
  echo $content;  exit;
}

Restoring MySQL database from physical files

A MySQL MyISAM table is the combination of three files:

• The FRM file is the table definition.
• The MYD file is where the actual data is stored.
• The MYI file is where the indexes created on the table are stored.

You should be able to restore by copying them in your database folder (In linux, the default location is /var/lib/mysql/)

You should do it while the server is not running.

PHP download backup of MySQL database

This can get messy trying to back up a database from PHP, you would be better of letting MySql handle this, and the best way to tell mysql to do this is with a shell script:

#!/bin/bash
# Shell script to backup MySql database
# To backup Nysql databases file to /backup dir and later pick up by your
# script. You can skip few databases from backup too.
# For more info please see (Installation info):
# http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html
# Last updated: Aug - 2005
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2004, 2005 nixCraft project
# Feedback/comment/suggestions : http://cyberciti.biz/fb/
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------

MyUSER="SET-MYSQL-USER-NAME"     # USERNAME
MyPASS="SET-PASSWORD"       # PASSWORD
MyHOST="localhost"          # Hostname

# Linux bin paths, change this if it can not be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

# Backup Dest directory, change this if you have someother location
DEST="/backup"

# Main directory where backup will be stored
MBD="$DEST/mysql"

# Get hostname
HOST="$(hostname)"

# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"

# File to store current backup file
FILE=""
# Store list of databases
DBS=""

# DO NOT BACKUP these databases
IGGY="test"

[ ! -d $MBD ] && mkdir -p $MBD || :

# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST

# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"

for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];
    then
    for i in $IGGY
    do
        [ "$db" == "$i" ] && skipdb=1 || :
    done
    fi

    if [ "$skipdb" == "-1" ] ; then
    FILE="$MBD/$db.$HOST.$NOW.gz"
    # do all inone job in pipe,
    # connect to mysql using mysqldump for select mysql database
    # and pipe it out to gz file in backup dir :)
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
    fi
done

and then you tell php to tell the operating system to execute this script and wait for it to be done, this is usually done with exec or system such as:

exec('/path/to/backup/script.sh');

Then you can simply send the file created by the script to the browser for that user.

Mysql Database backup using PHP and download the backup at a custom specified location at download time

OK found the solution.
Only thning I needed to do is either comment the line or remove

header('Content-Transfer-Encoding: binary');

Now I am getting the backup file correctly. Thanks.

Export and Import all MySQL databases at one time

Export:

mysqldump -u root -p --all-databases > alldb.sql

Look up the documentation for mysqldump. You may want to use some of the options mentioned in comments:

mysqldump -u root -p --opt --all-databases > alldb.sql
mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql

Import:

mysql -u root -p < alldb.sql

How to download and upload SQL database as .sql file using php?

I tried out all the answers. But this following code helped me.

BACK-UP:

I pasted the following code in db_export.php:

##################### 
// CONFIGURATIONS  //
#####################
// Define the name of the backup directory
define('BACKUP_DIR', './myBackups' ) ; 
// Define  Database Credentials
define('HOST', 'localhost' ) ; 
define('USER', 'root' ) ; 
define('PASSWORD', 'password' ) ; 
define('DB_NAME', 'database_name' ) ; 
$files = scandir(BACKUP_DIR);
if(count($files) > 2) {
  for ($i=2; $i < count($files); $i++) { 
    unlink(BACKUP_DIR."/".$files[$i]);
  }
}
/*
Define the filename for the sql file
If you plan to upload the  file to Amazon's S3 service , use only lower-case letters 
*/
$fileName = 'mysqlbackup--' . date('d-m-Y') . '@'.date('h.i.s').'.sql' ; 
// Set execution time limit
if(function_exists('max_execution_time')) {
if( ini_get('max_execution_time') > 0 )     set_time_limit(0) ;
}
###########################  
//END  OF  CONFIGURATIONS//
###########################

// Check if directory is already created and has the proper permissions
if (!file_exists(BACKUP_DIR)) mkdir(BACKUP_DIR , 0700) ;
if (!is_writable(BACKUP_DIR)) chmod(BACKUP_DIR , 0700) ; 

// Create an ".htaccess" file , it will restrict direct accss to the backup-directory . 
//$content = 'deny from all' ; 
//$file = new SplFileObject(BACKUP_DIR . '/.htaccess', "w") ;
//$file->fwrite($content) ;

$mysqli = new mysqli(HOST , USER , PASSWORD , DB_NAME) ;
if (mysqli_connect_errno())
{
   printf("Connect failed: %s", mysqli_connect_error());
   exit();
}
// Introduction information //
$return = "";
$return .= "--\n";
$return .= "-- A Mysql Backup System \n";
$return .= "--\n";
$return .= '-- Export created: ' . date("Y/m/d") . ' on ' . date("h:i") . "\n\n\n";
$return = "--\n";
$return .= "-- Database : " . DB_NAME . "\n";
$return .= "--\n";
$return .= "-- --------------------------------------------------\n";
$return .= "-- ---------------------------------------------------\n";
$return .= 'SET AUTOCOMMIT = 0 ;' ."\n" ;
$return .= 'SET FOREIGN_KEY_CHECKS=0 ;' ."\n" ;
$tables = array() ; 
// Exploring what tables this database has
$result = $mysqli->query('SHOW TABLES' ) ; 
// Cycle through "$result" and put content into an array
while ($row = $result->fetch_row()) 
  $tables[] = $row[0] ;
// Cycle through each  table
foreach($tables as $table)
{ 
  // Get content of each table
  $result = $mysqli->query('SELECT * FROM '. $table) ; 
  // Get number of fields (columns) of each table
  $num_fields = $mysqli->field_count  ;
  // Add table information
  $return .= "--\n" ;
  $return .= '-- Tabel structure for table `' . $table . '`' . "\n" ;
  $return .= "--\n" ;
  $return.= 'DROP TABLE  IF EXISTS `'.$table.'`;' . "\n" ; 
  // Get the table-shema
  $shema = $mysqli->query('SHOW CREATE TABLE '.$table) ;
  // Extract table shema 
  $tableshema = $shema->fetch_row() ; 
  // Append table-shema into code
  $return.= $tableshema[1].";" . "\n\n" ; 
  // Cycle through each table-row
  while($rowdata = $result->fetch_row()) 
  { 
    // Prepare code that will insert data into table 
    $return .= 'INSERT INTO `'.$table .'`  VALUES ( '  ;
    // Extract data of each row 
    for($i=0; $i<$num_fields; $i++)
      $return .= '"'.$rowdata[$i] . "\"," ;
     // Let's remove the last comma 
     $return = substr("$return", 0, -1) ; 
     $return .= ");" ."\n" ;
  } 
  $return .= "\n\n" ; 
}
// Close the connection
$mysqli->close() ;
$return .= 'SET FOREIGN_KEY_CHECKS = 1 ; '  . "\n" ; 
$return .= 'COMMIT ; '  . "\n" ;
$return .= 'SET AUTOCOMMIT = 1 ; ' . "\n"  ; 
//$file = file_put_contents($fileName , $return) ; 
$zip = new ZipArchive() ;
$resOpen = $zip->open(BACKUP_DIR . '/' .$fileName.".zip" , ZIPARCHIVE::CREATE) ;
if( $resOpen )
  $zip->addFromString( $fileName , "$return" ) ;
$zip->close() ;
$fileSize = get_file_size_unit(filesize(BACKUP_DIR . "/". $fileName . '.zip')) ; 
$message = <<<msg
  <h2>BACKUP  ready,</h2>
  the archive has the name of  : <b>  $fileName  </b> and it's file-size is :   $fileSize  .
  <br /><a href=\"myBackups/{$fileName}.zip\"><b>Click here to Download</b></a>
msg;
echo $message ; 
// Function to append proper Unit after file-size . 
function get_file_size_unit($file_size){
  switch (true) {
      case ($file_size/1024 < 1) :
          return intval($file_size ) ." Bytes" ;
          break;
      case ($file_size/1024 >= 1 && $file_size/(1024*1024) < 1)  :
          return intval($file_size/1024) ." KB" ;
          break;
      default:
      return intval($file_size/(1024*1024)) ." MB" ;
  }
}

---

RESTORING

I guess it is vulnerable to hand-over database uploading to Clients. I've learned it from this comment. Thank you, Henrik.

---

Other answers and comments:

By the way, thank you to all the comments from,

Rocket Hazmat, Ares Draguna, Mihai, bear, brandelizer, Mike Brant, lolka_bolka (hahaha nice one!), Bill Karwin (equally good as lolka) and especially Henrik.

Also, thank you all the answers by,

Luzan, Serpes, Henrik, Bill Karwin, Adam Fischer, carysun and Herman Nz.

All the comments and answers were useful and valuable in one or other situation.

Thank you.

---

---

Related Topics