PDO MySQL备份功能

Question

this function here http://davidwalsh.name/backup-mysql-database-php

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?

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)

Answer 1

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.= "

".$ifnotexists.";

";


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= "
\t(";

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

//$row[$j] = preg_replace("
","\
",$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="

-- ------------------------------------------------ 

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