Summary of the method of importing/exporting oversized SQL text files in MySQL

In the actual work, sometimes the MySQL database to import and import operations, but for large SQL file import, phpMyAdmin is not, there are too many restrictions, such as records, memory and so on!

A variety of scenarios are collected as follows:

Scheme one: Client direct command operation (this method is my favorite)

Just restore an electric dealer website MySQL backup data, this backup file has more than 300 m, because phpMyAdmin support upload Limited, file too big ie stop responding, so the local use

The code is as follows	Copy Code
Mysql-u root-p Root jiahuibuydb< c:/yebihai.sql

But did not import, came out a lot of MySQL parameter prompts, do not recognize the input of the command parameters, and then write the following this way, is to remove the parameters and data between the space

The code is as follows	Copy Code
Mysql-uroot-proot jiahuibuydb< C:/yebihai.sql

Import is OK.

Export command:

A) export the entire library

Mysqldump-u user name-p database name > exported file name

The code is as follows	Copy Code
Mysqldump-u root-p Student >d:/yebihai.sql

b) export a table

Mysqldump-u user name-P database name Table name > exported file name

Scenario Two: block-import

The code is as follows

Copy Code

? Large data backup for fast MySQL Before using, first modify the SQL file name, database hostname, database username, password, and database name that you want to import by following code comments At the same time, the database file and this text together FTP guide the Web site directory, and then Web Access to this file can $file _name= "Sql.sql"; The SQL file name to import $dbhost = "localhost"; Database host Name $dbuser = "User"; Database user Name $dbpass = "Pass"; Database Password $dbname = "dbname"; Database name Set_time_limit (0); The timeout is set to 0, which indicates that it has been executed. When PHP is invalid in safe Mode mode, this may cause the import timeout, which requires a segmented import $fp = @fopen ($file _name, "R") or Die ("Cannot open SQL file $file _name");/Open File Mysql_connect ($dbhost, $dbuser, $dbpass) or Die ("Cannot connect to the database $dbhost");/Connection Database mysql_select_db ($dbname) or Die ("Cannot open database $dbname");/Open Database mysql_query (' Set names UTF8 '); echo "is performing an import operation"; while ($SQL =getnextsql ()) { if (!mysql_query ($SQL)) { echo "Execution error Www.111cn.net:". Mysql_error (). "; echo "SQL statement is: ". $SQL." "; }; } echo "Import complete"; Fclose ($fp) or Die ("Can ' t close file $file _name"); Mysql_close (); Take SQL from a file function Getnextsql () { Global $fp; $sql = ""; while ($line = @fgets ($fp, 40960)) { $line = Trim ($line); The following three sentences are not required in a high version of PHP and may need to be modified in some low versions $line = Str_replace ("////", "//", $line); $line = Str_replace ("/", "'", $line); $line = Str_replace ("//r//n", CHR), Chr (a), $line); $line = Stripcslashes ($line); if (strlen ($line) >1) { if ($line [0]== "-" && $line [1]== "-") { Continue } } $sql. = $line. chr. chr (10); if (strlen ($line) >0) { if ($line [strlen ($line) -1]== ";") { Break } } } return $sql; } ?>

Scenario Three: The client uses the source command to operate

A better approach is still to use the MySQL source command:

First, under the client operation:

1, into the client

　　

The code is as follows	Copy Code
2, Mysql>use database name (if not, first build a) 3, mysql>set names ' UTF8 '; (generally see what format files are imported) 4, Mysql>source D:/aaa.sql;

can be normal import, if there is a mistake to see the wrong hint

Second, the PHP file operation:

Establish a.php

It has the following contents

　

The code is as follows	Copy Code
Mysql_connet (' xxxx '); mysql_query ("Set names ' UTF8 '"); mysql_query ("Source D:/aaa.sql '");

Principle above, the main convenience is unable to use the command line user's operation

Plan four: below teaches everybody a simple effective method, suitable for does not use the command window the non-technical person, may import any size MySQL database, theoretically regardless of your database backup file is big, may import
The method is as follows:

1. Upload database backup files (such as Backup.sql) to the site root directory.

2. Save the following code as a mysql.php file and upload it to the site root directory.

The code is as follows	Copy Code
System ("Mysql-hdbhost-udbuser-ppassword dbname < Backup.sql"); Print "Import succeeded";

which

Dbhost to your database server address (small tip: General host default database server address is: localhost)

Dbuser change to your database user name

Password change to your database user password

dbname to your database name (this library must be set up before importing, otherwise it will fail without any prompts)

Backup.sql represents the file name of the database file that was uploaded to the root directory of the Web site via FTP (the file is uncompressed)

3. In the browser to visit the mysql.php, assuming that your site domain name is www.111cn.net, then in the browser input/mysql.php, as long as the browser access to the mysql.php file, the data will begin to import, after the end of data import, will show " The word "Import succeeded". This time is based on the size of the data you want to import, usually a short time