使用PHP提高数据库的导出速度

Question

我试图创建一个导出mysql数据库的函数。我研究了很多方法来做到这一点。我已经到达了下面的函数。这样做的问题是导出速度很慢，我的数据库大小只有10mb。有没有办法让它更快或更好?

public function backup_database($tables = '*'){
if ($this->databaseConnection()) {
$return = '';
if($tables == '*') {
$tables = array();
$sql = $this->db_connection->prepare('SHOW TABLES');
if($sql->execute()){
while($row = $sql->fetch()){
$tables[] = $row[0];
}
}
}
else {
$tables = is_array($tables) ? $tables : explode(',',$tables);
}
foreach($tables as $table) {
$result = $this->db_connection->prepare('SELECT * FROM ' . $table);
$result->execute();
$num_fields = $result->rowCount();
$return .= 'DROP TABLE ' . $table . ';';
$row2 = $this->db_connection->prepare('SHOW CREATE TABLE '.$table);
$row2->execute();
$row2 = $row2->fetch();
$return .= "nn".$row2[1].";nn";
for ($i = 0; $i < $num_fields; $i++){
while($row = $result->fetch())
{
$return .= 'INSERT INTO '. $table .' VALUES(';
for($j = 0; $j < $num_fields; $j++){
$row[$j] = addslashes($row[$j]);
$row[$j] = preg_replace("/\n/","\n",$row[$j]);
if (isset($row[$j])) { $return .= '"'. $row[$j] .'"' ; } else { $return .= '""'; }
if ($j<($num_fields-1)) { $return .= ','; }
}
$return .= ");n";
}
}
$return .="nnn";
}
if (!file_exists('database_backups')) {
mkdir('database_backups', 0777, true);
}

$filename = 'database_backups/db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql.gz';
$handle = fopen($filename,'w+');
$gzdata = gzencode($return, 9);
fwrite($handle,$gzdata);
fclose($handle);

return '1';
}

Answer 1

如果你想一次导出所有的数据库表，你可以使用下面的php代码——

$backup_file = "/var/www/html/DB_backup/evidyaa" . date("Y-m-d-H-i-s") . '.sql'; //this would the path of the filename for backup
$command = "mysqldump --user=dbuser --password=dbpass --host=dbhost ". "dbname > $backup_file";

passthru($command);
echo 'done';

如果您使用的是windows，那么命令将是

$backup_file = "/var/www/html/DB_backup/evidyaa" . date("Y-m-d-H-i-s") . '.sql'; //this would the path of the filename for backup
$command = "c:xamppmysqlbinmysql.exe --user=dbuser --password=dbpass --host=dbhost ". "dbname > $backup_file";