backup mysql tables with php-ThrowExceptions

Exception or error:

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);
How to solve:

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

Answer:

I set up simple functions ‘openDb()’ and get($SQL) to open databases and execute PHP mysql queries. There may be instances where the row by row copy is more useful but you can also use, if it works (using my simple functions above):

 openDb($db,$user,$pw);
 get("CREATE TABLE `TABLE1_backup` LIKE `TABLE1`");
 get("INSERT INTO `TABLE1_backup` (SELECT * FROM `TABLE1`)");

(NB This is a skeletal description. Add DROPs etc as needed. The ‘get’ returns T/F depending on success so for debugging it may be necessary to test for false and provide the MySQLi_ERROR)

Answer:

and with poo

<?php
//backup
//function &backup_tables($host, $user, $pass, $name, $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";
 include "connexion.php";

$myquery="SET NAMES `utf8` COLLATE `utf8_general_ci`";
$result = $mylink->query($myquery);

if($tables == '*'){ //get all of the tables
$tables = array();
$myquery="SHOW TABLES";
$result = $mylink->query($myquery);

while($row = mysqli_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";

$myquery="SHOW CREATE TABLE `{$table}`";
$result = $mylink->query($myquery);

$row = $result->fetch_row();
$data.= $row[1].";\n";

$myquery="SELECT * FROM `{$table}`";
$result = $mylink->query($myquery);
$num_rows = $result->num_rows;


if($num_rows>0){
  $vals = Array(); $z=0;
  for($i=0; $i<$num_rows; $i++){
    $items = mysqli_fetch_row($result);
    $vals[$z]="(";
    for($j=0; $j<count($items); $j++){
      if (isset($items[$j])) { $vals[$z].= "'".$mylink->real_escape_string($items[$j]) ."'"; } 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";
  }
  }
 $mylink->close();
 return $data;
 }


//***********
// called by :

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

// get backup

$host = "yourhost";
$user = "youruser";
$pass = "your_pw";
$name = "your base";
$tables= 'your_table';
 $mybackup = backup_tables($host,$user,$pass,$name,$tables);

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

//affichage des résultats, pour savoir si la modification a marchée:
 echo("La sauvegarde à été correctement effectuée") ;

?>

where connection.php is :
<?php
 $mylink = new mysqli("host", "user", "pw", "base");
if ($mylink->connect_errno) {
echo "Echec lors de la connexion à MySQL : " . $mylink->connect_error;
exit();
} 

Answer:

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

if ($tables == '*') { //get all of the tables
    $tables = array();
    $result = mysql_query("SHOW TABLES");
    while ($row = mysql_FetchRecordsFromCSV_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}`");
    $row = mysql_fetch_row($res);
    $data.= $row[1] . ";\n";

    $result = mysql_query("SELECT * FROM `{$table}`");
    $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]) . "'";
                } 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 );
$handle = fopen($filepath, 'w+');
fwrite($handle, $data);
fclose($handle);

}

Answer:

Same as user3365179 but using PDO.

Change ISO-8859-1 to utf8

$mylink is in $GLOBALS[‘db_pdo’]

function backup_tables_pdo($host, $user, $pass, $name, $tables) {   
    $data = "\n/*---------------------------------------------------------------".
          "\n  SQL DB BACKUP ".date("d.m.Y H:i")." ".
          "\n  HOST: {$_SERVER['SERVER_NAME']}".
          "\n  DATABASE: {$name}".
          "\n  TABLES: {$tables}".
          "\n  ---------------------------------------------------------------*/\n";
    // include "connexion.php";

    $myquery="SET NAMES `ISO-8859-1` COLLATE `latin1_spanish_ci`";
    $result = $GLOBALS['db_pdo']->query($myquery);

    if($tables == '*'){ //get all of the tables
        $tables = array();
        $myquery="SHOW TABLES";
        $result = $GLOBALS['db_pdo']->query($myquery);

        while($row = mysqli_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";

        $myquery="SHOW CREATE TABLE `{$table}`";
        $result = $GLOBALS['db_pdo']->query($myquery);

        $row = $result->fetch(); // fetch_row()
        $data.= $row[1].";\n";

        $myquery="SELECT * FROM `{$table}`";
        $result = $GLOBALS['db_pdo']->query($myquery);
        $num_rows = $result->rowCount();  // num_rows

        if($num_rows>0) {
          $vals = Array(); $z=0;
          for($i=0; $i<$num_rows; $i++) {
            $items = $result->fetch(PDO::FETCH_NUM);  // mysqli_fetch_row($result)
            //var_dump($items); exit;
            $vals[$z]="(";
            for($j=0; $j<count($items); $j++) {
                if (isset($items[$j])) { 
                    $vals[$z].= $GLOBALS['db_pdo']->quote($items[$j]); //real_escape_string($items
                } 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";
        }
    }
    return $data;
}

Leave a Reply

Your email address will not be published. Required fields are marked *