如何使用php在mysql数据库中导入.sql文件
我怎样才能做到这一点,请帮助我解决这个问题。
这段代码显示这个错误...
There was an error during import. Please make sure the import file is saved in the same folder as this script and check your values:
MySQL Database Name: test
MySQL User Name: root
MySQL Password: NOTSHOWN
MySQL Host Name: localhost
MySQL Import Filename: dbbackupmember.sql
我正在使用这段代码
<?php
//ENTER THE RELEVANT INFO BELOW
$mysqlDatabaseName ='test';
$mysqlUserName ='root';
$mysqlPassword ='';
$mysqlHostName ='localhost';
$mysqlImportFilename ='dbbackupmember.sql';
//DONT EDIT BELOW THIS LINE
//Export the database and output the status to the page
$command='mysql -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' < ' .$mysqlImportFilename;
exec($command,$output=array(),$worked);
switch($worked){
case 0:
echo 'Import file <b>' .$mysqlImportFilename .'</b> successfully imported to database <b>' .$mysqlDatabaseName .'</b>';
break;
case 1:
echo 'There was an error during import. Please make sure the import file is saved in the same folder as this script and check your values:<br/><br/><table><tr><td>MySQL Database Name:</td><td><b>' .$mysqlDatabaseName .'</b></td></tr><tr><td>MySQL User Name:</td><td><b>' .$mysqlUserName .'</b></td></tr><tr><td>MySQL Password:</td><td><b>NOTSHOWN</b></td></tr><tr><td>MySQL Host Name:</td><td><b>' .$mysqlHostName .'</b></td></tr><tr><td>MySQL Import Filename:</td><td><b>' .$mysqlImportFilename .'</b></td></tr></table>';
break;
}
?>
我有另一种方法来做到这一点,试试这个
<?php
// Name of the file
$filename = 'churc.sql';
// MySQL host
$mysql_host = 'localhost';
// MySQL username
$mysql_username = 'root';
// MySQL password
$mysql_password = '';
// Database name
$mysql_database = 'dump';
// Connect to MySQL server
mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ' . mysql_error());
// Select database
mysql_select_db($mysql_database) or die('Error selecting MySQL database: ' . mysql_error());
// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($filename);
// Loop through each line
foreach ($lines as $line)
{
// Skip it if it's a comment
if (substr($line, 0, 2) == '--' || $line == '')
continue;
// Add this line to the current segment
$templine .= $line;
// If it has a semicolon at the end, it's the end of the query
if (substr(trim($line), -1, 1) == ';')
{
// Perform the query
mysql_query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . mysql_error() . '<br /><br />');
// Reset temp variable to empty
$templine = '';
}
}
echo "Tables imported successfully";
?>
这对我来说是有效的,祝你好运
来自Raj的答案是有用的,但是(由于file($filename)的原因)。 如果你的mysql-dump在内存中不合适,它将失败**。
如果你在共享主机上,并且有限制,比如30MB和12s的脚本运行时间,并且你必须恢复x00MB的mysql dump,你可以使用这个脚本:
它将逐个查询转储文件,如果脚本的执行期限临近,它将把当前的文件位置保存在一个临时文件中,自动的浏览器重载将一次又一次地继续这个过程...... 如果发生错误,重新加载将停止,并显示错误信息 ...
如果你从午餐回来,你的数据库将被恢复;-)
the noLimitDumpRestore.php: <?php
// your config
$filename = 'yourGigaByteDump.sql';
$dbHost = 'localhost';
$dbUser = 'user';
$dbPass = '__pass__';
$dbName = 'dbname';
$maxRuntime = 8; // less then your max script execution limit
$deadline = time()+$maxRuntime;
$progressFilename = $filename.'_filepointer'; // tmp file for progress
$errorFilename = $filename.'_error'; // tmp file for erro
mysql_connect($dbHost, $dbUser, $dbPass) OR die('connecting to host: '.$dbHost.' failed: '.mysql_error());
mysql_select_db($dbName) OR die('select db: '.$dbName.' failed: '.mysql_error());
($fp = fopen($filename, 'r')) OR die('failed to open file:'.$filename);
// check for previous error
if( file_exists($errorFilename) ){
die('<pre> previous error: '.file_get_contents($errorFilename));
}
// activate automatic reload in browser
echo '<html><head> <meta http-equiv="refresh" content="'.($maxRuntime+2).'"><pre>';
// go to previous file position
$filePosition = 0;
if( file_exists($progressFilename) ){
$filePosition = file_get_contents($progressFilename);
fseek($fp, $filePosition);
}
$queryCount = 0;
$query = '';
while( $deadline>time() AND ($line=fgets($fp, 1024000)) ){
if(substr($line,0,2)=='--' OR trim($line)=='' ){
continue;
}
$query .= $line;
if( substr(trim($query),-1)==';' ){
if( !mysql_query($query) ){
$error = 'Error performing query \'<strong>' . $query . '\': ' . mysql_error();
file_put_contents($errorFilename, $error."\n");
exit;
}
$query = '';
file_put_contents($progressFilename, ftell($fp)); // save the current file position for
$queryCount++;
}
}
if( feof($fp) ){
echo 'dump successfully restored!';
}else{
echo ftell($fp).'/'.filesize($filename).' '.(round(ftell($fp)/filesize($filename), 2)*100).'%'."\n";
echo $queryCount.' queries processed! please reload or wait for automatic browser refresh!';
}
我已经测试了你的代码,当你已经导入了DB或者有一些同名的表时,这个错误就会出现,另外,显示的Array错误是因为你在exec的括号里加了进去,这里是固定版本:
<?php
//ENTER THE RELEVANT INFO BELOW
$mysqlDatabaseName ='test';
$mysqlUserName ='root';
$mysqlPassword ='';
$mysqlHostName ='localhost';
$mysqlImportFilename ='dbbackupmember.sql';
//DONT EDIT BELOW THIS LINE
//Export the database and output the status to the page
$command='mysql -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' < ' .$mysqlImportFilename;
$output=array();
exec($command,$output,$worked);
switch($worked){
case 0:
echo 'Import file <b>' .$mysqlImportFilename .'</b> successfully imported to database <b>' .$mysqlDatabaseName .'</b>';
break;
case 1:
echo 'There was an error during import.';
break;
}
?>