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 "
Table: {$row[0]}\n";
system( 'mysqldump $dbname $row[0] > verlag_$row[0].sql');
}
mysql_free_result($result);
解决方案
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
$items = mysql_fetch_row($result);
$vals[$z]="(";
for($j=0; $j
if (isset($items[$j])) { $vals[$z].= "'".mysql_real_escape_string( $items[$j], $link )."'"; } else { $vals[$z].= "NULL"; }
if ($j
}
$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