Backup of MySQL database with a PHP script

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.

[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]

In order to make a dump:

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


[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 – assuming no rewrite urls[/list_item]
[list_item]Download your backup from “backup” folder[/list_item]

Here is the script:

/* 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);
  //get all of the tables
  if($tables == '*')
    $tables = array();
    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result))
      $tables[] = $row[0];
    $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";
    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";
    $return ="\n\n\n";
  • vijay

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

  • Felipe

    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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.