FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


MySQL Database Backup Using PHP





welshsteve
Hi everyone,

Is there simple script I can setup as a php file, that when run, will do a MySQL dump of all the tables and data in my database? A bit like the export feature in phpMyAdmin. This would be handy.

I have the following, which does work, but want to make a few tweaks to it and don't know how.

Code:

<?php
$host="HOSTNAME";
$user="USERNAME";
$pass="PASSWORD";
$name="DATABASE";
$file="file.xml";

$command="mysqldump --xml --host=$host --user=$user --password=$pass $data > $file";
system($command);
echo "Database saved to file <a href='$file'>$file</a>";
?>


I'd like it to backup to a text file instead. Tried changing the xml bits to txt but this just results in an empty text file.
jmraker
In php you'll need to run a few sql statements

SHOW TABLES
to get a list of the tables in the database

DESCRIBE `table`
Loop through every table found to get the column names and variable types so you can format them as strings, numbers, binary later and get the primary key

SHOW CREATE TABLE `table`
to get the sql statement that can create the table

SELECT * FROM `table` ORDER BY `primary_key`
To get the table data

Where you output a multi-record insert statement. phpMyAdmin ends and starts a new INSERT statment every 5000 or so records

This function is important to escape strings, it's from phpMyAdmin's source
Code:
   function sqlAddslashes($a_string = ''){    // adapted from phpMyAdmin library
      $search       = array("\x00", "\x0a", "\x0d", "\x1a"); //\x08\\x09, not required
      $replace      = array('\0', '\n', '\r', '\Z');

      $a_string = str_replace('\\', '\\\\', $a_string);
      $a_string = str_replace('\'', '\'\'', $a_string);

      return str_replace($search, $replace, $a_string);
   }


and use the "0x" . bin2hex($data) function to format the binary/blob fields

Code:
   function writeBackupFile(){
      $results = $this->db->run('SHOW TABLES');            // Get list of tables
      while($tableRec = $this->db->fetch($results)){
         $table = $tableRec['Tables_in_' . DB_DATABASE];
         $result2 = $this->db->run('DESCRIBE ' . $table);
         $z = 0;
         $primaryKey = $allFields = '';
         while($rec = $this->db->fetch($result2)){      // Create table of field info
            $z = $rec['Field'];
            $a = explode('(', $rec['Type']);
            $fields[$z] = $a[0];
            $a = explode(')', $a[1]);
            $a = explode(',', $a[0]);
            if($rec['Key'] == 'PRI'){
               if($primaryKey != '')
                                      $primaryKey .= ', ';
               $primaryKey .= $rec['Field'];
            }
            if($allFields == '')
               $allFields = ' (';
            else
               $allFields .= ', ';
            $allFields .= '`' . $rec['Field'] . '`';

         }
         $allFields .= ')';
         if($primaryKey == '')
            $primaryKey = $table . '_id';

         // create the CREATE TABLE command
         $results2 = $this->db->run('SHOW CREATE TABLE ' . $table . '');
         $rec = $this->db->fetch($results2);
         $createTable = $rec['Create Table'];

         if(preg_match('/AUTO_INCREMENT=(.*?) /', $create, $arr))        // remove the AUTO_INCREMENT thing
            $createTable = str_replace($arr[0], '', $createTable);

         $createTable = substr_replace($createTable, ' IF NOT EXISTS', 12, 0);     // Add "IF NOT EXISTS"
         $table2 = ucwords(str_replace('_', ' ', $table));
         $this->write("\n" . '-- ' . $table2 . "\n");
         $this->write($createTable . ';' . "\n");
         $result2 = $this->db->run('SELECT * FROM ' . $table . ' ORDER BY ' . $primaryKey);
         $numRecords = $this->db->numRows($result2);
         if($numRecords == 0)
            $this->write('-- (There are no records)' . "\n");
         else {
            $this->write('INSERT INTO `' . $table . '`' . $allFields . ' VALUES' . "\n");
            $thisRecord = $recordNum = 0;
            while($rec = $this->db->fetch($result2)){   // Go through data
               $fieldNum = 0;
               $thisRecord++;
               $sql = '(';
               foreach($rec as $f=>$v){
                  if($fieldNum > 0)
                     $sql .= ', ';
                  switch($fields[$f]){   // Format based on field type
                     case 'int':case 'tinyint': case 'smallint':
                        $sql .= $v;break;
                     case 'float': case 'double':
                        $sql .= $v;break;
                     case 'varchar': case 'char': case 'text': case 'enum': case 'longtext': case 'set':
                        $v = $this->sqlAddslashes($v);
                        $sql .= '\'' . $v . '\'';break;
                     case 'blob': case 'varbinary':
                        $sql .= '0x' . bin2hex($v);break;
                     default:   // Unhandled field type
                        $sql .= '???' . $fields[$fieldNum]['type'] . '???';
                         die('Unhandled field type ' . $fields[$f] . ':' . $v);
                  }
                  $fieldNum++;
               }
               $sql .= ')';
               $recordNum++;
               if($thisRecord == $numRecords)
                  $sql .= ';';
               elseif($recordNum >= 500){
                  $sql .= ';' . "\n" . 'INSERT INTO `' . $table . '`' . $allFields . ' VALUES';
                  $recordNum = 0;
               }
               else
                  $sql .= ',';
               $sql .= "\n";
               $this->write($sql);
            }
         }
      }
   }
Fire Boar
Just remove the --xml part: there is no --txt option for mysqldump, so it throws an error. You should probably change the extension in $file too. Oh, and you're defining $name, but using $data when building $command. That's wrong. Fixed in the code below.

Also, if you have any binary data stored in the database, you should also include the option --hex-blob after mysqldump. I generally include it anyway, just to be safe. So that's

Code:
<?php
$host="HOSTNAME";
$user="USERNAME";
$pass="PASSWORD";
$name="DATABASE";
$file="file.txt";

$command="mysqldump --hex-blob --host=$host --user=$user --password=$pass $name > $file";
system($command);
echo "Database saved to file <a href='$file'>$file</a>";
?>


However, a backup is useless without a way of restoring it, and while you could use phpMyAdmin, here's how to do it in PHP:

Code:
<?php
$host="HOSTNAME";
$user="USERNAME";
$pass="PASSWORD";
$name="DATABASE";
$file="file.txt";

$command="mysql --host=$host --user=$user --password=$pass $name < $file";
system($command);
echo "Database in file $file restored.";
?>


Almost identical really.

One other thing... @badai, that's completely off-topic. It should have a new thread.
marron28
this would make it simple..

Code:
backup_tables('localhost','username','password','blog');


/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
   
   $link = mysql_connect($host,$user,$pass);
   mysql_select_db($name,$link);
   
   //get all of the tables
   if($tables == '*')
   {
      $tables = array();
      $result = mysql_query('SHOW TABLES');
      while($row = mysql_fetch_row($result))
      {
         $tables[] = $row[0];
      }
   }
   else
   {
      $tables = is_array($tables) ? $tables : explode(',',$tables);
   }
   
   //cycle through
   foreach($tables as $table)
   {
      $result = mysql_query('SELECT * FROM '.$table);
      $num_fields = mysql_num_fields($result);
      
      $return.= 'DROP TABLE '.$table.';';
      $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
      $return.= "\n\n".$row2[1].";\n\n";
      
      for ($i = 0; $i < $num_fields; $i++)
      {
         while($row = mysql_fetch_row($result))
         {
            $return.= 'INSERT INTO '.$table.' VALUES(';
            for($j=0; $j<$num_fields; $j++)
            {
               $row[$j] = addslashes($row[$j]);
               $row[$j] = ereg_replace("\n","\\n",$row[$j]);
               if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
               if ($j<($num_fields-1)) { $return.= ','; }
            }
            $return.= ");\n";
         }
      }
      $return.="\n\n\n";
   }
   
   //save file
   $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
   fwrite($handle,$return);
   fclose($handle);
}
Related topics
script backup database
Implementing your database access in PHP as a layer.
Cron Job to Backup MySQL Database
MySQL DB Backup script - minor problem
Need Help in PHP
Delete data from MySQL database problem!!
Problem connecting to MySQL database with PHP [RESOLVED]
How to search in a MySQL - database
PHP that creates subdomain, ftp account, and mysql database?
mysql database schema, blogs, forums and their text
Is there a better way to restore mysql database?
"Drop Down List Box" Using PHP and MySql
Easy script to backup mysql database, and can attached to ow
Connecting to a MySQL database using Python
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.