Backup of MySQL database with a PHP script | LAMPDev

Sometimes you need to make a mysqldump backup without having SSH access or PHPMyAdmin. It can be done with PHP by means of SHOW TABLES/SHOW CREATE TABLE mysql queries. Below is an improved solution proposed here.

Improvements:
[list style=”upper-alpha tick”]
[list_item]Got rid of memory_limit – writing to file on every iteration[/list_item]
[list_item]gzip support – you don’t have to download huge uncompressed .sql[/list_item]
[list_item]PHP notice fixed[/list_item]
[list_item]added IF EXISTS to DROP TABLE[/list_item]
[list_item]ereg_replase -> str_replace[/list_item]
[list_item]added set_time_limit(0) to work with large DB’s[/list_item]
[/list]

In order to make a dump:

[list style=”upper-alpha tick”]
[list_item]change mysql login/password/database on this line:

backup_tables('localhost','user','password','db_name');

[/list_item]
[list_item]upload the file to your hoster[/list_item]
[list_item]Create “backup” folder with write permissions to web server. If you’re not sure what permissions to set, just set 777 – the folder should be deleted after you’re done with backup[/list_item]
[list_item]Navigate to host.com/backup.php – assuming no rewrite urls[/list_item]
[list_item]Download your backup from “backup” folder[/list_item]
[/list]

Here is the script:

<?php
error_reporting(E_ALL);
ini_set('display_errors',1);
ini_set('memory_limit','1500M');
set_time_limit(0);
 
backup_tables('localhost','user','xxxxxxx','xxxxxxxxxx');
 
/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
  //save file
  $handle = gzopen(getcwd() . DIRECTORY_SEPARATOR . 'backup' . DIRECTORY_SEPARATOR . 'db-backup-'.time().'.sql.gz','w9');
 
  $link = mysql_connect($host,$user,$pass);
  mysql_select_db($name,$link);
 
  //get all of the tables
  if($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);
  }
 
  //cycle through
  foreach($tables as $table)
  {
    $result = mysql_query('SELECT * FROM '.$table);
    $num_fields = mysql_num_fields($result);
 
    $return = 'DROP TABLE IF EXISTS '.$table.';';
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    $return .= "\n\n".$row2[1].";\n\n";
    gzwrite($handle,$return);
    for ($i = 0; $i < $num_fields; $i++) 
    {
      while($row = mysql_fetch_row($result))
      {
        $return = 'INSERT INTO '.$table.' VALUES(';
        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.= ");\n";
        gzwrite($handle,$return);
      }
    }
    $return ="\n\n\n";
    gzwrite($handle,$return);
  }
 
  gzclose($handle);
}

vijay April 12, 2013 at 5:10 am

Awesome work. I m using this since i have seen this friest time. Nice to see improment using delay statements.

Felipe May 24, 2013 at 4:45 pm

Thanks for the script! Works great, I only had to add a “SET FOREIGN_KEY_CHECKS = 0;” to the beginning because my tables has FKs.

Pink May 25, 2016 at 5:08 am

I might be beanitg a dead horse, but thank you for posting this!

Leave a Reply Click here to cancel reply.