MySQL Dump


 / Published in: PHP
 

Creates a backup of a MySQL database in SQL format.

  1. if (!function_exists('mysql_dump')) {
  2.  
  3. function mysql_dump($database) {
  4.  
  5. $query = '';
  6.  
  7. $tables = @mysql_list_tables($database);
  8. while ($row = @mysql_fetch_row($tables)) { $table_list[] = $row[0]; }
  9.  
  10. for ($i = 0; $i < @count($table_list); $i++) {
  11.  
  12. $results = mysql_query('DESCRIBE ' . $database . '.' . $table_list[$i]);
  13.  
  14. $query .= 'DROP TABLE IF EXISTS `' . $database . '.' . $table_list[$i] . '`;' . lnbr;
  15. $query .= lnbr . 'CREATE TABLE `' . $database . '.' . $table_list[$i] . '` (' . lnbr;
  16.  
  17. $tmp = '';
  18.  
  19. while ($row = @mysql_fetch_assoc($results)) {
  20.  
  21. $query .= '`' . $row['Field'] . '` ' . $row['Type'];
  22.  
  23. if ($row['Null'] != 'YES') { $query .= ' NOT NULL'; }
  24. if ($row['Default'] != '') { $query .= ' DEFAULT \'' . $row['Default'] . '\''; }
  25. if ($row['Extra']) { $query .= ' ' . strtoupper($row['Extra']); }
  26. if ($row['Key'] == 'PRI') { $tmp = 'primary key(' . $row['Field'] . ')'; }
  27.  
  28. $query .= ','. lnbr;
  29.  
  30. }
  31.  
  32. $query .= $tmp . lnbr . ');' . str_repeat(lnbr, 2);
  33.  
  34. $results = mysql_query('SELECT * FROM ' . $database . '.' . $table_list[$i]);
  35.  
  36. while ($row = @mysql_fetch_assoc($results)) {
  37.  
  38. $query .= 'INSERT INTO `' . $database . '.' . $table_list[$i] .'` (';
  39.  
  40. $data = Array();
  41.  
  42. while (list($key, $value) = @each($row)) { $data['keys'][] = $key; $data['values'][] = addslashes($value); }
  43.  
  44. $query .= join($data['keys'], ', ') . ')' . lnbr . 'VALUES (\'' . join($data['values'], '\', \'') . '\');' . lnbr;
  45.  
  46. }
  47.  
  48. $query .= str_repeat(lnbr, 2);
  49.  
  50. }
  51.  
  52. return $query;
  53.  
  54. }
  55.  
  56. }

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: ki4ngel on July 19, 2008

Instead of using a "DESCRIBE ... " you can use "SHOW CREATE TABLE ... " which is easier.

Posted By: brucealdridge on January 29, 2009

and to be a pain mysqllisttables has been deprecated, as per php docs (http://nz2.php.net/mysqllisttables) , instead use "SHOW TABLES FROM $database"

Posted By: FederikoNavarro on September 3, 2010

It works!!! You saved my mysql project. I will recommend you web site to my friends.

You need to login to post a comment.