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);

you will need to give mysqldump the location where you want to save db backup i.e. mysqldump d://databasebackup/wp_dev.sql — Naeem Ul Wahhab, Dec 01 '12 at 12:56
You might have to try something like system( 'mysqldump' . $dbname . ' ' . $row[0] . ' > 'verlag_' .$row[0] . '.sql'); — ethrbunny, Dec 01 '12 at 12:56

score 9 · Accepted Answer · answered Jan 25 '13 at 18:26

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

Can you tell me why you're using `DROP TABLE IF EXISTS \`{$table}\`` statement? — asprin, Dec 19 '13 at 11:31
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. — gth, Jul 12 '18 at 05:23
**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](https://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](https://www.php.net/manual/en/book.pdo.php) extension should be used. See also the [MySQL API Overview](https://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API. — Dharman, Mar 12 '20 at 15:31
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 ? — Excorpion, Dec 21 '21 at 15:10
use for parameter $tables values like "Table1,Table2,TableX" — markus, Dec 22 '21 at 16:11

score 0 · Answer 2 · 2016-09-14T19:24:45.340

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)

score 0 · Answer 3 · answered Feb 08 '17 at 16:43

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();
}

score -1 · Answer 4 · answered Apr 11 '18 at 07:33

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);

}

**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](https://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](https://www.php.net/manual/en/book.pdo.php) extension should be used. See also the [MySQL API Overview](https://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API. — Dharman, Mar 12 '20 at 15:30

score -3 · Answer 5 · answered Mar 12 '20 at 15:27

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;
}

backup mysql tables with php

5 Answers5

Linked