반응형
PHP를 사용하여 mysql 데이터베이스에서 .sql 파일을 가져 오는 방법
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";
?>
이것은 나를 위해 일하고 있습니다, Good Luck
Raj의 대답은 유용하지만 (file ($ filename) 때문에) mysql-dump가 메모리에 맞지 않으면 실패 합니다.
당신이 경우 공유 호스팅에 와있다 제한 30메가바이트 및 12S 스크립트 런타임과 같습니다 : 당신이해야 x00MB mysql을 덤프를 복원 ,이 스크립트를 사용할 수 있습니다 :
쿼리에 대한 덤프 파일 쿼리를 수행하고 스크립트 실행 기한이 가까워지면 현재 파일 위치를 tmp 파일에 저장하고 자동 브라우저 다시로드가이 프로세스를 반복해서 계속합니다. 오류가 발생하면 다시로드가 중지됩니다. 오류가 표시됩니다 ...
점심 식사에서 돌아 오면 db가 복원됩니다 ;-)
noLimitDumpRestore.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!';
}
아래와 같이 mysqli multi_query 함수를 사용할 수 있습니다.
$sql = file_get_contents('mysqldump.sql');
$mysqli = new mysqli("localhost", "root", "pass", "testdb");
if (mysqli_connect_errno()) { /* check connection */
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* execute multi query */
if ($mysqli->multi_query($sql)) {
echo "success";
} else {
echo "error";
}
<?php
$host = "localhost";
$uname = "root";
$pass = "";
$database = "demo1"; //Change Your Database Name
$conn = new mysqli($host, $uname, $pass, $database);
$filename = 'users.sql'; //How to Create SQL File Step : url:http://localhost/phpmyadmin->detabase select->table select->Export(In Upper Toolbar)->Go:DOWNLOAD .SQL FILE
$op_data = '';
$lines = file($filename);
foreach ($lines as $line)
{
if (substr($line, 0, 2) == '--' || $line == '')//This IF Remove Comment Inside SQL FILE
{
continue;
}
$op_data .= $line;
if (substr(trim($line), -1, 1) == ';')//Breack Line Upto ';' NEW QUERY
{
$conn->query($op_data);
$op_data = '';
}
}
echo "Table Created Inside " . $database . " Database.......";
?>
Adminer 스크립트를 언급 할 가치가 있습니다. 단일 파일에있는 데이터베이스 managemenet 도구입니다.
간단히 FTP를 통해 서버에 php 파일을 놓기 만하면 데이터베이스를 가져오고 SQL 명령을 실행할 수있는 전체 GUI가 생깁니다. 좋습니다!
<?php
system('mysql --user=USER --password=PASSWORD DATABASE< FOLDER/.sql');
?>
Grain Script는 훌륭하고 내 하루를 구합니다. 한편 mysql은 감가 상각되고 PDO를 사용하여 Grain 답변을 다시 작성했습니다.
$server = 'localhost';
$username = 'root';
$password = 'your password';
$database = 'sample_db';
/* PDO connection start */
$conn = new PDO("mysql:host=$server; dbname=$database", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->exec("SET CHARACTER SET utf8");
/* PDO connection end */
// your config
$filename = 'yourFile.sql';
$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
($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)==';' ){
$igweze_prep= $conn->prepare($query);
if(!($igweze_prep->execute())){
$error = 'Error performing query \'<strong>' . $query . '\': ' . print_r($conn->errorInfo());
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를 가져 왔거나 동일한 이름을 가진 일부 테이블이있을 때 표시됩니다. 또한 표시되는 배열 오류는 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;
}
?>
// Import data
$filename = 'database_file_name.sql';
import_tables('localhost','root','','database_name',$filename);
function import_tables($host,$uname,$pass,$database, $filename,$tables = '*'){
$connection = mysqli_connect($host,$uname,$pass)
or die("Database Connection Failed");
$selectdb = mysqli_select_db($connection, $database) or die("Database could not be selected");
$templine = '';
$lines = file($filename); // Read entire file
foreach ($lines as $line){
// Skip it if it's a comment
if (substr($line, 0, 2) == '--' || $line == '' || substr($line, 0, 2) == '/*' )
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) == ';')
{
mysqli_query($connection, $templine)
or print('Error performing query \'<strong>' . $templine . '\': ' . mysqli_error($connection) . '<br /><br />');
$templine = '';
}
}
echo "Tables imported successfully";
}
// Backup database from php script
backup_tables('hostname','UserName','pass','databses_name');
function backup_tables($host,$user,$pass,$name,$tables = '*'){
$link = mysqli_connect($host,$user,$pass);
if (mysqli_connect_errno()){
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
mysqli_select_db($link,$name);
//get all of the tables
if($tables == '*'){
$tables = array();
$result = mysqli_query($link,'SHOW TABLES');
while($row = mysqli_fetch_row($result))
{
$tables[] = $row[0];
}
}else{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}
$return = '';
foreach($tables as $table)
{
$result = mysqli_query($link,'SELECT * FROM '.$table);
$num_fields = mysqli_num_fields($result);
$row_query = mysqli_query($link,'SHOW CREATE TABLE '.$table);
$row2 = mysqli_fetch_row($row_query);
$return.= "\n\n".$row2[1].";\n\n";
for ($i = 0; $i < $num_fields; $i++)
{
while($row = mysqli_fetch_row($result))
{
$return.= 'INSERT INTO '.$table.' VALUES(';
for($j=0; $j < $num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = str_replace("\n", '\n', $row[$j]);
if (isset($row[$j])) {
$return.= '"'.$row[$j].'"' ;
} else {
$return.= '""';
}
if ($j < ($num_fields-1)) { $return.= ','; }
}
$return.= ");\n";
}
}
$return.="\n\n\n";
}
//save file
$handle = fopen('backup-'.date("d_m_Y__h_i_s_A").'-'.(md5(implode(',',$tables))).'.sql','w+');
fwrite($handle,$return);
fclose($handle);
}
사용자 인터페이스가 필요하고 PDO를 사용하려는 경우
여기에 간단한 해결책이 있습니다.
<form method="post" enctype="multipart/form-data">
<input type="text" name="db" placeholder="Databasename" />
<input type="file" name="file">
<input type="submit" name="submit" value="submit">
</form>
<?php
if(isset($_POST['submit'])){
$query = file_get_contents($_FILES["file"]["name"]);
$dbname = $_POST['db'];
$con = new PDO("mysql:host=localhost;dbname=$dbname","root","");
$stmt = $con->prepare($query);
if($stmt->execute()){
echo "Successfully imported to the $dbname.";
}
}
?>
확실히 내 쪽에서 일하고 있습니다. 시도할만한 가치가 있습니다.
솔루션 특수 문자
$link=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());
//charset important
mysql_set_charset('utf8',$link);
이 코드를 사용해 볼 수 있습니다.이 코드는 제 경우에 적용됩니다.
<?php
$con = mysqli_connect('localhost', 'root', 'NOTSHOWN', 'test');
$filename = 'dbbackupmember.sql';
$handle = fopen($filename, 'r+');
$contents = fread($handle, filesize($filename));
$sql = explode(";", $contents);
foreach ($sql as $query) {
$result = mysqli_query($con, $query);
if ($result) {
echo "<tr><td><br></td></tr>";
echo "<tr><td>".$query."</td></tr>";
echo "<tr><td><br></td></tr>";
}
}
fclose($handle);
echo "success";
?>function restoreDatabase($db_name,$file_path)
{
//checking valid extension file
$path_parts = pathinfo($file_path);
$ext_file = $path_parts['extension'];
$filename = $path_parts['basename'];
if($ext_file == "sql")
{
$c = new Config();
$confJson = $c->getConfig();
$conf = json_decode($confJson);
$dbhost = "127.0.0.1";
$dbuser = $conf->db_username;
$dbpwd = $conf->db_password;
$dbname = $db_name;
$dumpfile = $file_path;
$is_file = file_exists($file_path);
if($is_file == TRUE)
{
//passthru("/usr/bin/mysqldump --opt --host=$dbhost --user=$dbuser --password=$dbpwd $dbname < $dumpfile");
//passthru("tail -1 $dumpfile");
system('mysql --user='.$dbuser.' --password='.$dbpwd.' '.$db_name.' < '.$file_path);
return "Database was restored from $filename ";
}
else
{
return "Restore database was aborted due ".$filename." does not exist!";
}
}
else
{
return "Invalid file format.Require sql file to restore this ".$db_name." database. ".$filename." is not sql file format\n(eg. mybackupfile.sql).";
}
}
나는 이것을 사용하고 그것은 나를 위해 작동합니다
나는 이것이 가장 짧은 코드라고 생각합니다
$user = "root";
$pass = "";
$host = "localhost";
$dbname = "dbname";
$sql_file = fopen('database.sql', 'r');
$sql = fread($sql_file, filesize('database.sql'));
$conn = new PDO("mysql:host=" . $host . ";dbname=" . $dbname , $user, $pass);
$conn->exec($sql);
이 코드를 사용하고 SUCCESS FULL을 실행합니다.
$filename = 'apptoko-2016-12-23.sql'; //change to ur .sql file
$handle = fopen($filename, "r+");
$contents = fread($handle, filesize($filename));
$sql = explode(";",$contents);//
foreach($sql as $query){
$result=mysql_query($query);
if ($result){
echo '<tr><td><BR></td></tr>';
echo '<tr><td>' . $query . ' <b>SUCCESS</b></td></tr>';
echo '<tr><td><BR></td></tr>';
}
}
fclose($handle);
참고 URL : https://stackoverflow.com/questions/19751354/how-to-import-sql-file-in-mysql-database-using-php
반응형
'Program Tip' 카테고리의 다른 글
| 파이썬, 왜 elif 키워드일까요? (0) | 2020.12.13 |
|---|---|
| 기계적 인조 인간. (0) | 2020.12.13 |
| AngularJS UI-Router : 매개 변수로 상태의 절대 URL 가져 오기 (0) | 2020.12.13 |
| IIS의 ASP.NET MVC3 및 Windows Auth가 계속 / Account / Login으로 리디렉션됩니다. (0) | 2020.12.13 |
| NSDictionary를 NSString으로 변환 (0) | 2020.12.13 |