PDO MySQL备份功能 [英] PDO MySQL backups function

问题描述

此功能在这里 http://davidwalsh.name/backup-mysql-database-php

已经在互联网上徘徊了一段时间，并且非常有名，但是它在标准mysql中.除了在PDO中，有人有相同的东西吗?如果没有，谁愿意做一个? 甚至有可能，我在某处读到PDO不做SHOW CREATE TABLE的事-是吗?

has been floating around the internets for a while and is pretty famous, but its in standard mysql. does anyone have the same but in PDO? if not does anyone want to make one? is it even possible, i read somewhere that PDO doesnt do SHOW CREATE TABLE - is that right?

最后，有人可以解释一下该功能与使用SELECT * INTO OUTFILE之间的区别吗?

finally, can someone explain wht difference between that function and using SELECT * INTO OUTFILE is?

(请不要因为包含太多问题而将其标记为下来，它们都紧密相关，并确保答案对很多人都有用)

(please dont mark this down for containing too many questions, they are all tightly related and im sure the answer(s) will be useful for many people)

推荐答案

对于正在寻找类似于mysqldump的功能的任何人来说，这是最新的草案，上面/下面的注释中讨论的缺陷得以消除.享受.

To anyone looking for the function which acts like the mysqldump, here is the latest draft, with the imperfections discussed in the comments above/below ironed out. Enjoy.

require 'login.php';
$DBH = new PDO("mysql:host=$db_hostname;dbname=$db_database; charset=utf8", $db_username, $db_password);



//put table names you want backed up in this array.
//leave empty to do all
$tables = array();

backup_tables($DBH, $tables);



function backup_tables($DBH, $tables) {

$DBH->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL );

//Script Variables
$compression = false;
$BACKUP_PATH = "";
$nowtimename = time();


//create/open files
if ($compression) {
$zp = gzopen($BACKUP_PATH.$nowtimename.'.sql.gz', "a9");
} else {
$handle = fopen($BACKUP_PATH.$nowtimename.'.sql','a+');
}


//array of all database field types which just take numbers 
$numtypes=array('tinyint','smallint','mediumint','int','bigint','float','double','decimal','real');

//get all of the tables
if(empty($tables)) {
$pstm1 = $DBH->query('SHOW TABLES');
while ($row = $pstm1->fetch(PDO::FETCH_NUM)) {
$tables[] = $row[0];
}
} else {
$tables = is_array($tables) ? $tables : explode(',',$tables);
}

//cycle through the table(s)

foreach($tables as $table) {
$result = $DBH->query("SELECT * FROM $table");
$num_fields = $result->columnCount();
$num_rows = $result->rowCount();

$return="";
//uncomment below if you want 'DROP TABLE IF EXISTS' displayed
//$return.= 'DROP TABLE IF EXISTS `'.$table.'`;'; 


//table structure
$pstm2 = $DBH->query("SHOW CREATE TABLE $table");
$row2 = $pstm2->fetch(PDO::FETCH_NUM);
$ifnotexists = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $row2[1]);
$return.= "\n\n".$ifnotexists.";\n\n";


if ($compression) {
gzwrite($zp, $return);
} else {
fwrite($handle,$return);
}
$return = "";

//insert values
if ($num_rows){
$return= 'INSERT INTO `'."$table"."` (";
$pstm3 = $DBH->query("SHOW COLUMNS FROM $table");
$count = 0;
$type = array();

while ($rows = $pstm3->fetch(PDO::FETCH_NUM)) {

if (stripos($rows[1], '(')) {$type[$table][] = stristr($rows[1], '(', true);
} else $type[$table][] = $rows[1];

$return.= "`".$rows[0]."`";
$count++;
if ($count < ($pstm3->rowCount())) {
$return.= ", ";
}
}

$return.= ")".' VALUES';

if ($compression) {
gzwrite($zp, $return);
} else {
fwrite($handle,$return);
}
$return = "";
}
$count =0;
while($row = $result->fetch(PDO::FETCH_NUM)) {
$return= "\n\t(";

for($j=0; $j<$num_fields; $j++) {

//$row[$j] = preg_replace("\n","\\n",$row[$j]);


if (isset($row[$j])) {

//if number, take away "". else leave as string
if ((in_array($type[$table][$j], $numtypes)) && (!empty($row[$j]))) $return.= $row[$j] ; else $return.= $DBH->quote($row[$j]); 

} else {
$return.= 'NULL';
}
if ($j<($num_fields-1)) {
$return.= ',';
}
}
$count++;
if ($count < ($result->rowCount())) {
$return.= "),";
} else {
$return.= ");";

}
if ($compression) {
gzwrite($zp, $return);
} else {
fwrite($handle,$return);
}
$return = "";
}
$return="\n\n-- ------------------------------------------------ \n\n";
if ($compression) {
gzwrite($zp, $return);
} else {
fwrite($handle,$return);
}
$return = "";
}



$error1= $pstm2->errorInfo();
$error2= $pstm3->errorInfo();
$error3= $result->errorInfo();
echo $error1[2];
echo $error2[2];
echo $error3[2];

if ($compression) {
gzclose($zp);
} else {
fclose($handle);
}
}

这篇关于PDO MySQL备份功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！