Backup MySQL Database PHP Script

12th August 2009 - 7 minutes read time

There are quite a few scripts available on the Internet that allow you to dump data from a database into a format that can be used to replicate that database structure elsewhere. The following function is my take on this commonly occurring script.

<?php 
function datadump($table, $drop = true, $stripapos = true)
{
  $result     = "# Dump of $table \n";
  $result    .= "# Dump DATE : " . date("d-M-Y") ."\n\n";
  if ( $drop ) {
    if ( $stripapos ) {
      $result    .= "DROP TABLE IF EXISTS $table;\n";
      // dump create table
      $createTableQuery = mysql_query("SHOW CREATE TABLE ".$table.";");
      $createTable      = mysql_fetch_row($createTableQuery);
      $result          .= str_replace('`', '', $createTable[1]).";\n\n\n\n";  
    } else {
      $result    .= "DROP TABLE IF EXISTS `$table`;\n";
      // dump create table
      $createTableQuery = mysql_query("SHOW CREATE TABLE ".$table.";");
      $createTable      = mysql_fetch_row($createTableQuery);
      $result          .= $createTable[1].";\n\n\n\n";    
    }
  } else {
    $result    .= "TRUNCATE TABLE $table;\n";
  }
  
  $query      = mysql_query("SELECT * FROM $table");
  $num_fields = @mysql_num_fields($query);
  $numrow     = mysql_num_rows($query);
 
  $columnsRes = mysql_query("SHOW COLUMNS FROM $table;");
  $columns = array();
 
  while ( $row = mysql_fetch_assoc($columnsRes) ) {
    $columns[$row['Field']] = $row;
  }
 
  while ( $row = mysql_fetch_assoc($query) ) {
    $result .= "INSERT INTO ".$table." VALUES(";
 
    $fields = array();
 
    foreach ( $row as $field => $data ) {
      if ( strpos(strtolower($columns[$field]['Type']), 'int') !== false 
        || strpos(strtolower($columns[$field]['Type']), 'float') !== false
        || strpos(strtolower($columns[$field]['Type']), 'tinyint') !== false ) {
        if ( strlen($data) > 0 ) {
          $fields[] = $data;
        } else {
          if ( strtolower($columns[$field]['Null']) == 'no' ) {
            $fields[] = 0;
          } else {
            $fields[] = "NULL";
          }        
        }
      } elseif ( strpos(strtolower($columns[$field]['Type']), 'datetime') !== false ) {
        if ( strlen($data) > 0 ) {       
          $fields[] = "\"".$data."\"" ;
        } else {
          if ( strtolower($columns[$field]['Null']) == 'no' ) {
            $fields[] = '""';
          } else {
            $fields[] = "NULL";
          }
        }
      } elseif ( strpos(strtolower($columns[$field]['Type']), 'time') !== false ) {
        if ( strlen($data) > 0 ) {
          $fields[] = "\"".$data."\"" ;
        } else {
          if ( strtolower($columns[$field]['Null']) == 'no' ) {
            $fields[] = '""';
          } else {
            $fields[] = "NULL";
          }
        }        
      } elseif ( strpos(strtolower($columns[$field]['Type']), 'varchar') !== false
       || strpos(strtolower($columns[$field]['Type']), 'text') !== false
       || strpos(strtolower($columns[$field]['Type']), 'longtext') !== false
       || strpos(strtolower($columns[$field]['Type']), 'mediumtext') !== false ) {
        $data = addslashes($data);
        $data = trim(ereg_replace("\n", "\\n", $data));
        if ( strlen($data) > 0 ) {
          $fields[] = "\"".$data."\"" ;
        } else {
          if ( strtolower($columns[$field]['Null']) == 'no' ) {
            $fields[] = '""';
          } else {
            $fields[] = "NULL";
          }
        }        
      } else {
        // $columns[$field]['Type'] will contain the datatype
        if ( strlen($data) > 0 ) {
          $fields[] = "\"".$data."\"" ;
        } else {
          if ( strtolower($columns[$field]['Null']) == 'no' ) {
            $fields[] = '""';
          } else {
            $fields[] = "NULL";
          }
        }        
      }
    }
    $result .= implode(',', $fields);
    $result .= ");\n";
  }
  return $result . "\n\n\n";
}

You can call the function in the following way. Because this function looks at one table at a time it is necessary to run a SHOW TABLES query in order to get a list of the tables in the database. This is then used to construct the SQL needed to create the tables and insert the data.

$con = mysql_connect('localhost','username','password');
mysql_select_db('thedatabase');
 
$result = mysql_query("SHOW TABLES;");
while ( $row = mysql_fetch_row($result) ) {
  $table = $row[0];
 
  // dump data
  echo datadump($table, true, true);
}

The datadump() function takes three parameters. The first is simply a string containing the name of the table. The second is a boolean that causes the outputted SQL to drop and recreate the tables if set to true, otherwise a truncate is used to clear the tables. The third value is a boolean that causes the function to remove any ` in the SQL, which can cause issues sometimes. Only the first parameter is mandatory, the second two default to true, meaning that the tables are dropped and recreated.

The function works by taking the table name and then using this to fetch the syntax needed to recreate the table (if this option is set) before fetching all of the data from the table. It then finds out the structure of the table and uses this to output an insert statement that escapes the data correctly, even if the value is null.

The data_dump() function returns a string so it could potentially write the contents to a file rather than just output them. Printing everything out works if your dataset is quite small. This script does tend to fall over if there is a lot to do so it might be worth writing to a file in that instance as well as increasing the amount of time a script can run for.

Note that you shouldn't rely on the data created by this script. If you are going to use it then I suggest you do a dry run to make sure that everything inserts as expected. This script has undergone several iterations and simple testing on different databases but it is not fully tested.

PHP
MySQL

Comments

If you use this script and find that you are having memory or time out issues and you don't want to or have access to the server to change the settings then here is the solution.

The problem is probably occurring because there is just too much data in some of your tables. So the simplest way to get around this is to get a list of all of the tables you have, the following will do this:

$con = mysql_connect('localhost','username','password');
mysql_select_db('thedatabase');
 
$result = mysql_query("SHOW TABLES;");
while ( $row = mysql_fetch_row($result) ) {
  $table = $row[0];
 
  // dump data
  echo $table . "\n";
}

Once you have this list you can split it into groups of tables, something like this:

$tables1 = array('table1', 'table2');
$tables2 = array('table3','table4');

You can then feed each tables array into the data dump loop one at a time, until you have your data.

foreach ($tables2 as $table ) {
  // dump data
  echo datadump($table, true, true);
}

You can also try to write the output of this script to a file (which is probably a better approach than my original method of dumping the output to the write buffer).

$result = mysql_query("SHOW TABLES;");
$output = '';
while ( $row = mysql_fetch_row($result) ) {
  $table = $row[0];
 
  // dump data
  $output .= datadump($table, true, true);
}

$fh = fopen("dbdump.sql", 'w');
fwrite($fh, $output);
fclose($fh);

This method might seem a little clunky, but if you just *have* to get your data off of an old clunky server then they will get the job done.

Philip Norton

Submitted by giHlZp8M8D on Wed, 06/09/2010 - 14:10

Permalink
How can i see or do anything with my old query! i run query with database in my domain for every action. after that i change my domain to new domain name and i put my database in new domain but i forgot my query to do with new domain .

Submitted by ponlork on Thu, 08/26/2010 - 14:03

Permalink
Great!! This is good example for get the MySQL database backup. I have been searching for exactly this solution. It saved lot of time. Thanks.

Submitted by Php Specialist on Wed, 08/16/2017 - 06:37

Permalink

Add new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
3 + 6 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.

Related Content

Generating Colour Palettes From Images In PHP

4th August 2024

A common web design pattern is to incorporate an image into the design of the page. This creates a tighter integration with the image and the rest of the page.

The main issue in designing a page around the image is that the colours of the page must match the image. Otherwise this creates a dissonance between the image and the styles of the site.

Read more

Validating XML Files With XML Schema Definitions In PHP

21st July 2024

XML is a useful format for configuration, data storage, and transmitting data from one system to another. As a human readable format that can be easily read by machines it quickly gained favor in lots of different systems as a mechanism for data storage.

Read more

Approximating Pi Using A Circle And A Square

26th May 2024

Pi day was a few weeks ago, but I came across this simple approximation of pi recently and decided to put together an example in PHP since it seemed pretty simple.

This approximation of pi centers around a real world example, but we can simulate this using some code.

Read more

Drawing A Parabolic Curve With Straight Lines In PHP

12th May 2024

A parabolic curve is a type of curve where every point is an equal distance from a focal point. There a number of different way to generate this sort of curve using math, but one of the simplest is to use straight lines to create the illusion of the curve.

Read more

Recreating Spotify Wrapped In PHP

17th March 2024

I quite like the end of the year report from Spotify that they call "Wrapped". This is a little application in which they tell you what your favorite artist was and what sort of genres you listened to the most during the year.

Read more