Advanced Search

Database dump using PHP

/ Published in: PHP
Save to your folder(s)
Attempt 1 is a command-line attempt. Likely not to work due to php often running as "nobody". Attempt 2 is a php way, going through teach table in the database and writing out to a file.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. <?php
  2. require_once('wp-config.php');
  3.  
  4. //Attempt 1: Via command-line
  5. /*
  6. $backupFile = './art-ice-db-' . date("Y-m-d-H-i-s") . '.gz';
  7. $command = "mysqldump --opt -h DB_HOST -u DB_USER -p DB_PASSWORD DB_NAME | gzip > $backupFile";
  8. system($command, $ret);
  9.  
  10. echo $ret;
  11. */
  12.  
  13. //Attempt 2: Via PHP connecting to mysql
  14. backup_tables(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME);
  15.  
  16.  
  17. /* backup the db OR just a table */
  18. function backup_tables($host,$user,$pass,$name,$tables = '*')
  19. {
  20.  
  21.   $link = mysql_connect($host,$user,$pass);
  22.   mysql_select_db($name,$link);
  23.  
  24.   //get all of the tables
  25.   if($tables == '*')
  26.   {
  27.     $tables = array();
  28.     $result = mysql_query('SHOW TABLES');
  29.     while($row = mysql_fetch_row($result))
  30.     {
  31.       $tables[] = $row[0];
  32.     }
  33.   }
  34.   else
  35.   {
  36.     $tables = is_array($tables) ? $tables : explode(',',$tables);
  37.   }
  38.  
  39.   //cycle through
  40.   foreach($tables as $table)
  41.   {
  42.     $result = mysql_query('SELECT * FROM '.$table);
  43.     $num_fields = mysql_num_fields($result);
  44.  
  45.     $return.= 'DROP TABLE '.$table.';';
  46.     $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
  47.     $return.= "\n\n".$row2[1].";\n\n";
  48.  
  49.     for ($i = 0; $i < $num_fields; $i++) 
  50.     {
  51.       while($row = mysql_fetch_row($result))
  52.       {
  53.         $return.= 'INSERT INTO '.$table.' VALUES(';
  54.         for($j=0; $j<$num_fields; $j++) 
  55.         {
  56.           $row[$j] = addslashes($row[$j]);
  57.           $row[$j] = ereg_replace("\n","\\n",$row[$j]);
  58.           if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
  59.           if ($j<($num_fields-1)) { $return.= ','; }
  60.         }
  61.         $return.= ");\n";
  62.       }
  63.     }
  64.     $return.="\n\n\n";
  65.   }
  66.  
  67.   //save file
  68.   $handle = fopen('art-ice-db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
  69.   fwrite($handle,$return);
  70.   fclose($handle);
  71. }

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.