Backup MySQL Database PHP Script

Wednesday, August 12, 2009 - 10:10

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
<?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.

1
2
3
4
5
6
7
8
9
10
$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.

Category: 
philipnorton42's picture

Philip Norton

Phil is the founder and administrator of #! code and is an IT professional working in the North West of the UK.
Google+ | Twitter

Comments

philipnorton42's picture
Submitted by philipnorton42 on Wed, 06/09/2010 - 15:10

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:

1
2
3
4
5
6
7
8
9
10
$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:

1
2
$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.

1
2
3
4
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).

1
2
3
4
5
6
7
8
9
10
11
12
$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.

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 .

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.

Add new comment