Backup MySQL Database PHP Script

12th August 2009 - 9 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.

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.

Phil Norton

philipnorton42

Wed, 06/09/2010 - 14:10
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 .

ponlork

Thu, 08/26/2010 - 14:03
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.

Php Specialist

Wed, 08/16/2017 - 06:37

Add new comment

The content of this field is kept private and will not be shown publicly.

Related Content

A Look At Flood Fill Algorithms In PHP

8th June 2025

If you have ever used a paint program then you might have used a flood fill algorithm. This is a mechanism by which an area of an image can be filled with a different colour and is normally depicted by a paint can pouring paint.

Read more

A Look At Benford's Law

11th May 2025

Benford's Law is an interesting heuristic in data analysis. It states that in any large collection of numbers that are created naturally, you should expect to see numbers starting with the number 1 about 30% of the time. The frequency distribution of numbers states that 2 should appear about 17% of the time, down to 9 being seen just 5% of the time.

Read more

Protecting A Page From Being Directly Accessed With PHP

27th April 2025

I was thinking recently about the number of ways in which I could restrict access to a page using PHP.

The obvious option is to create a user authentication system, but in some situations that is overkill for what is required. If you just want to prevent users from going directly to a certain page then there are a few options open to you.

Read more