backup mysql tables with php

Question

I would like to backup tables (with PHP) from a db if the table prefix is matching with a sub string. What I was trying and is not working

error_reporting(1);
$dbname = 'wp_dev';

if (!mysql_connect('127.0.0.1', 'root', '')) {
    echo 'Connection Error';
    exit;
}

$sql = "SHOW TABLES FROM $dbname LIKE 'wp_%'";
$result = mysql_query($sql);

if (!$result) {
    echo "DB tables could not be listed\n";
    echo 'MySQL Fehler: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_row($result)) {
    echo "<pre>Table: {$row[0]}\n</pre>";
    system( 'mysqldump $dbname $row[0] > verlag_$row[0].sql');
}

mysql_free_result($result);

Solution

Here is a function for making bakups from db or only some tables

function &backup_tables($host, $user, $pass, $name, $tables = '*'){
  $data = "\n/*---------------------------------------------------------------".
          "\n  SQL DB BACKUP ".date("d.m.Y H:i")." ".
          "\n  HOST: {$host}".
          "\n  DATABASE: {$name}".
          "\n  TABLES: {$tables}".
          "\n  ---------------------------------------------------------------*/\n";
  $link = mysql_connect($host,$user,$pass);
  mysql_select_db($name,$link);
  mysql_query( "SET NAMES `utf8` COLLATE `utf8_general_ci`" , $link ); // Unicode

  if($tables == '*'){ //get all of the 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);
  }

  foreach($tables as $table){
    $data.= "\n/*---------------------------------------------------------------".
            "\n  TABLE: `{$table}`".
            "\n  ---------------------------------------------------------------*/\n";           
    $data.= "DROP TABLE IF EXISTS `{$table}`;\n";
    $res = mysql_query("SHOW CREATE TABLE `{$table}`", $link);
    $row = mysql_fetch_row($res);
    $data.= $row[1].";\n";

    $result = mysql_query("SELECT * FROM `{$table}`", $link);
    $num_rows = mysql_num_rows($result);    

    if($num_rows>0){
      $vals = Array(); $z=0;
      for($i=0; $i<$num_rows; $i++){
        $items = mysql_fetch_row($result);
        $vals[$z]="(";
        for($j=0; $j<count($items); $j++){
          if (isset($items[$j])) { $vals[$z].= "'".mysql_real_escape_string( $items[$j], $link )."'"; } else { $vals[$z].= "NULL"; }
          if ($j<(count($items)-1)){ $vals[$z].= ","; }
        }
        $vals[$z].= ")"; $z++;
      }
      $data.= "INSERT INTO `{$table}` VALUES ";      
      $data .= "  ".implode(";\nINSERT INTO `{$table}` VALUES ", $vals).";\n";
    }
  }
  mysql_close( $link );
  return $data;
}

How to use:

// create backup
//////////////////////////////////////

$backup_file = 'db-backup-'.time().'.sql';

// get backup
$mybackup = backup_tables("myhost","mydbuser","mydbpasswd","mydatabase","*");

// save to file
$handle = fopen($backup_file,'w+');
fwrite($handle,$mybackup);
fclose($handle);

You can modify the line:

$result = mysql_query("SHOW TABLES");

for the table präfix

Comments

[asprin]

Can you tell me why you're using DROP TABLE IF EXISTS `{$table}` statement?

[gth]

Backups are used to restore data. The DROP TABLE command is not being executed in the script, rather it is merely the first command written into the backup file. The second command is the CREATE TABLE command (being the output of the SHOW CREATE TABLE query). After that, actual rows of data are written to the file as an INSERT INTO query. This is intentional, because if the backup needed to be used, this file could be fed directly into MySQL as an SQL script and it would take care of (a) removing any old/incorrect data, (b) creating the table and (c) uploading all the rows.

[Dharman]

Warning: mysql_* extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

[Dharman]

Warning: mysql_* extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

[Excorpion]

Hallo i found this code, and its working doing some changes, BUT, how do i ask for certain tables ?? I can get ONE or ALL, but i need 2 or 3... Any ideas ?

[markus]

use for parameter $tables values like "Table1,Table2,TableX"