cómo importar el archivo .sql en la base de datos mysql usando php

cómo importar el archivo .sql en la base de datos mysql usando php

¿Cómo puedo hacer esto? Por favor, ayúdame a solucionar este problema. Gracias.

este código muestra este error …

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 

estoy usando este código

 <?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 ' .$mysqlImportFilename .' successfully imported to database ' .$mysqlDatabaseName .''; 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:

MySQL Database Name:' .$mysqlDatabaseName .'
MySQL User Name:' .$mysqlUserName .'
MySQL Password:NOTSHOWN
MySQL Host Name:' .$mysqlHostName .'
MySQL Import Filename:' .$mysqlImportFilename .'
'; break; } ?>

Tengo otra forma de hacer esto, prueba esto

 ' . $templine . '\': ' . mysql_error() . '

'); // Reset temp variable to empty $templine = ''; } } echo "Tables imported successfully"; ?>

Esto está funcionando para mí, buena suerte

la respuesta de Raj es útil, pero (debido a file ($ filename)) fallará si tu mysql-dump no cabe en la memoria

Si está en un alojamiento compartido y existen limitaciones como el tiempo de ejecución de Script de 30 MB y 12s y tiene que restaurar un volcado de mysql de x00MB , puede usar este script:

desplazará la consulta del archivo de volcado para consulta, si la fecha límite de ejecución del script está cerca, guarda la actualposición del archivo en un archivo tmp y una recarga automática del navegador continuará este proceso una y otra vez … Si se produce un error, la recarga se detendrá y se muestra el error …

si vuelves del almuerzo tu DB será restaurado 😉

el 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('
 previous error: '.file_get_contents($errorFilename)); } // activate automatic reload in browser echo ' 
'; // 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 \'' . $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 restred!'; }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!'; }

Puede usar la función mysqli multi_query de la siguiente manera:

 $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"; } 
 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......."; ?> 

Vale la pena mencionar el script de administrador . Es una herramienta de gestión de bases de datos en un solo archivo.

Simplemente suelte el archivo php en su servidor a través de FTP o de otra manera, y tiene una GUI completa donde puede importar una base de datos, ejecutar comandos sql, etc. ¡Agradable!

  

He probado su código, este error muestra cuando ya tiene el DB importado o con algunas tablas con el mismo nombre, también el error Array que se muestra es porque usted agrega en el paréntesis del exec, aquí está la versión fija:

 ' .$mysqlImportFilename .' successfully imported to database ' .$mysqlDatabaseName .''; break; case 1: echo 'There was an error during import.'; break; } ?> 

Grain Script es excelente y salva mi día. Mientras tanto, mysql se deprecia y reescribí la respuesta de Grain usando PDO.

  $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('
 previous error: '.file_get_contents($errorFilename)); } // activate automatic reload in browser echo ' 
'; // 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 \'' . $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 restred!'; }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!'; }

 // 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 \'' . $templine . '\': ' . mysqli_error($connection) . '

'); $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); }

Solución de caracteres especiales

  $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); 

I Thing puedes probar este código, se ejecuta para mi caso:

 
"; echo "".$query.""; echo "
"; } } fclose($handle); echo "success"; ?>
 function restreDatabase($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)."; } } 

Yo uso esto y funciona para mí

Creo que este es el código más corto

 $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); 

Uso este código y RUN 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 '
'; echo '' . $query . ' SUCCESS'; echo '
'; } } fclose($handle);