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

Question

I need a php code which downloads and uploads one database file.

i.e. I have these databases accounts, information and data.

Backup:

I need a help to download database data as .sql file. And for your extra information, that database contains 5 tables. I want to download all into a single file.

Restore:

I should also be able to upload the same .sql file for restoring purposes. It should replace the available data database.

EDIT

I know its possible via phpmyadmin and cannot use it as I am going to let my clients download the sql file. And I cannot give them my phpmyadmin password.

And more-over its not essential that I need to do it via .sql file, it may be of any format which is good & easy for backing up and restoring the database.

Thank you.

Answer 1

Backup

You can generate a sql dump file with the shell command mysqldump.

mysqldump -u USER -p PASSWORD DATABASE > dump.sql

If you use the example, you will need to replace the words in uppercase letters.

Restore

The restore from a sql file can be done by using the mysql shell command.

mysql -u USER -p PASSWORD < dump.sql

Call shell commands in PHP

Within PHP the shell commands can be called with the function exec():

exec("mysqldump -u USER -p PASSWORD DATABASE > dump.sql");  // backup
exec("mysql -u USER -p PASSWORD < dump.sql");               // restore

Security Aspects

You should only allow trusted users to restore from sql files. If you only have one mysql user for accessing all your databases, restoring directly from a sql file does not have much more security improvement over using phpmyadmin. Using a hosting package with the ability of managing mysql users is a good solution. Otherwise you have to verify an uploaded sql file before restoring from this file.

In the following you will find three suggestions on how you could verify an uploaded file. All of the suggestions build on the assumption that you use a sql file, which has been generated on the server.

1. Create a digital signature at download and validate the signature at upload. For sure, you have to upload the digital signature together with the sql.

2. Store a hash value of the downloaded sql on the server and check for existents of this hash value at upload. Using an ID for the sql files might be helpful.

3. Save the whole sql files on the server and only allow restores from these files. Uploads aren`t needed anymore.

Answer 2

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.

---

Answer 3

To export database use

function export_tables($host,$user,$pass,$name,  $tables=false, $backup_name=false )
{
$link = mysqli_connect($host,$user,$pass,$name);
// Check connection
if (mysqli_connect_errno())   {   echo "Failed to connect to MySQL: " . mysqli_connect_error();   }

mysqli_select_db($link,$name);
mysqli_query($link,"SET NAMES 'utf8'");

//get all of the tables
if($tables === false)
{
    $tables = array();
    $result = mysqli_query($link,'SHOW TABLES');
    while($row = mysqli_fetch_row($result))
    {
        $tables[] = $row[0];
    }
}
else
{
    $tables = is_array($tables) ? $tables : explode(',',$tables);
}
$return='';
//cycle through
foreach($tables as $table)
{
    $result = mysqli_query($link,'SELECT * FROM '.$table);
    $num_fields = mysqli_num_fields($result);

    $row2 = mysqli_fetch_row(mysqli_query($link, 'SHOW CREATE TABLE '.$table));
    $return.= "\n\n".$row2[1].";\n\n";

    for ($i = 0; $i < $num_fields; $i++) 
    {
        $st_counter= 0;
        while($row = mysqli_fetch_row($result))
        {
            //create new command if when starts and after 100 command cycle
            if ($st_counter%100 == 0 || $st_counter == 0 )  {
                $return.= "\nINSERT INTO ".$table." VALUES";
            }


            $return.="\n(";
            for($j=0; $j<$num_fields; $j++) 
            {
                $row[$j] = addslashes($row[$j]);
                $row[$j] = str_replace("\n","\\n",$row[$j]);
                if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                if ($j<($num_fields-1)) { $return.= ','; }
            }
            $return.=")";


            //create new command if when starts and after 100 command cycle (but detect this 1 cycle earlier !)
            if ( ($st_counter+1)%100 == 0  && $st_counter != 0 )    {   $return.= ";";  }
            else                                                {   $return.= ",";  }
            //+++++++
            $st_counter = $st_counter +1 ;
        }
        //as we cant detect WHILE loop end, so, just detect, if last command ends with comma(,) then replace it with semicolon(;)
        if (substr($return, -1) == ',') {$return = substr($return, 0, -1). ';'; }
    }
    $return.="\n\n\n";
}

//save file
$backup_name = $backup_name ? $backup_name : $name."___(".date('H-i-s')."_".date('d-m-Y').")__rand".rand(1,11111111).'.sql';
file_put_contents($backup_name,$return);
die('SUCCESS. Download BACKUP file: <a target="_blank" href="'.$backup_name.'">'.$backup_name.'</a> <br/><br/>After download, <a target="_blank" href="?delete_filee='.$backup_name.'">Delete it!</a> ');

}

if (!empty($_GET['delete_filee'])){ chdir(dirname(__file__));       
if  (unlink($_GET['delete_filee'])) {die('file_deleted');} 
else                                {die("file doesnt exist");}
}

and execute using

export_tables("localhost","username","password","db_name");

to import data base use

function import_tables($host,$user,$pass,$dbname,$sql_file,  $clear_or_not=false )
{
if (!file_exists($sql_file)) {
    die('Input the SQL filename correctly! <button onclick="window.history.back();">Click Back</button>');}

// Connect to MySQL server
    //$link = mysqli_connect($host,$user,$pass,$name);
    //mysqli_select_db($link,$mysqli);
$mysqli = new mysqli($host, $user, $pass, $dbname);
// Check connection
if (mysqli_connect_errno())   {   echo "Failed to connect to MySQL: " . mysqli_connect_error();   }

if($clear_or_not) 
{
    $zzzzzz = $mysqli->query('SET foreign_key_checks = 0');
    if ($result = $mysqli->query("SHOW TABLES"))
    {
        while($row = $result->fetch_array(MYSQLI_NUM))
        {
            $mysqli->query('DROP TABLE IF EXISTS '.$row[0]);
        }
    }
    $zzzzzz = $mysqli->query('SET foreign_key_checks = 1');
}

$mysqli->query("SET NAMES 'utf8'");
// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($sql_file);
// Loop through each line
foreach ($lines as $line)
{
    // Skip it if it's a comment
    if (substr($line, 0, 2) == '--' || $line == '')
        continue;
    // Add this line to the current segment
    $templine .= $line;
    // If it has a semicolon at the end, it's the end of the query
    if (substr(trim($line), -1, 1) == ';')
    {
        // Perform the query
        $mysqli->query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . $mysqli->error . '<br /><br />');
        // Reset temp variable to empty
        $templine = '';
    }
}
 echo 'Tables imported successfully. <button onclick="window.history.back();">Go Back</button>';
}

and to execute it use:

import_tables("localhost","username","pasword","db_name","my_filename.sql", true);  //this will delete all exiting tables, and writes the imported database
import_tables("localhost","username","pasword","db_name","my_filename.sql", false); //dont delete the exiting tables, just add those, which doesnt exist

Source:

Answer 4

I don't think every user can run the 'exec' function on the server where his projects ran on.As you already have the mysql account as well as the permission of running php website. Why not try to install phpmyadmin in the web server.It can satisfy you with various kind of functionalities.It also written in php.

Answer 5

Try this to download:

    <form method="POST" action="">
    <b>Path to export </b>
    <br />
    <input name="txtpath" id="txtpath" type="text" size="71">
    <br />
    <span class="buram">ex: public_html/dbbackup/</span>database.sql
    <br />
    <br />
    <input type="submit" name="cmddownld" id="cmddownld" value="Backup">
    </form>
    <br />
    <br />
<?php
    $mysqldbname ="database";
    $usrname ="username";
    $mypassz ="password";
    $remhost ="localhost";
    $exportpath = strip_tags($_POST["txtpath"]);
    //ex: $exportpath ="public_html/dbbackup/dbsample.sql";
    if (isset($_POST["cmddownld"])){
        if (empty($_POST["txtpath"])){
            echo "Unclear path!";
            }
        else{
            $cmdrun="mysqldump --opt -h" .$remhost ." -u" .$usrname ." -p" .$mypassz ." " .$mysqldbname ." > ~/" .$exportpath;
            exec($cmdrun,$outpath,$worked);
            switch($worked){
            case 0:
            echo "Database <b>" .$mysqldbname ."</b> successfully exported to <b>~/" .$exportpath ."</b>";
            break;
            case 1:
            echo "Error occured: <b>" .$mysqldbname ."</b> to <b>~/" .$exportpath ."</b>";
            break;
            case 2:
            echo "There must be a mistake with your db login";
            break;
            }
        }
    }
    ?>

Answer 6

I would suggest to use dibi DB layer. You can download here: https://github.com/dg/dibi

Than just use as:

dibi::connect(); dibi::loadFile( 'dump.sql' ); 

//Can be read as a compressed file:

dibi::loadFile( 'compress.zlib://dump.sql.gz' ); 

Answer 7

It's easier if you don't use php for this. I don't like use the command exec but ...

exec("mysqldump -u USER -p PASSWORD --databases accounts information data > /YOURHOME/dumpfile.sql");

I would compress that file:

exec("tar czvf /YOURHOME/dumpfile.sql.tar.gz /YOURHOME/dumpfile.sql");

And next echo the file or do what you want.

I suggest that you write a bash script that do what I've wrote before. It could send the dump to another server or whatever you want. Use php for this is a bit strange.