Summary of importing/exporting large SQL text files in mysql

You can use many methods to import/export ultra-large SQL text files in mysql, such as direct command operations on the client, multipart import, and source command operations on the client.

In practice, sometimes mysql Database Import and import operations are performed frequently. However, phpmyadmin does not work when large SQL files are imported, and there are too many restrictions, such as records and memory!

Various solutions are collected as follows:

Solution 1: Direct Client commands (I prefer this method)

I just recovered a mysql backup data for an e-commerce website. The backup file is more than 300 mb. Because phpmyadmin supports limited uploads and the file is too large, IE stops responding, so it is used locally

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

But it was not imported in, and a lot of mysql parameter prompts were generated. If you did not recognize the input command parameters, it was written as below, that is, the space between the parameters and data was removed.

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

Import is OK.

Export command:

A) export the entire database

Mysqldump-u username-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

 

Solution 2: multipart Import

The Code is as follows:

Copy code

<? // Used for fast Mysql Big Data Backup // Modify the SQL file name, Database Host Name, database user name, password, and database name to be imported according to the code comment before use. // Simultaneously export the database file and the text file together with ftp to the website directory, and then access the file in web mode. $ File_name = "SQL. SQL"; // name of the SQL file to be imported $ Dbhost = "localhost"; // Database Host Name $ Dbuser = "user"; // database username $ Dbpass = "pass"; // Database Password $ Dbname = "dbname"; // Database Name Set_time_limit (0); // set the timeout value to 0, indicating that the operation is always executed. If php is invalid in safe mode, the import may time out. In this case, you need to import data in multiple parts. $ Fp = @ fopen ($ file_name, "r") or die ("SQL File $ file_name" cannot be opened); // open the file Mysql_connect ($ dbhost, $ dbuser, $ dbpass) or die ("cannot connect to database $ dbhost"); // connect to database Mysql_select_db ($ dbname) or die ("You cannot open the database $ dbname"); // open the database Mysql_query ('set names utf8 '); Echo "importing operation in progress "; While ($ SQL = GetNextSQL ()){ If (! Mysql_query ($ SQL )){ Echo "execution error www.hzhuti.com:". mysql_error ()." "; Echo "SQL statement: ". $ SQL ." "; }; } Echo "Import completed "; Fclose ($ fp) or die ("Can't close file $ file_name"); // close the file Mysql_close (); // Obtain the SQL statement from the file one by one Function GetNextSQL (){ Global $ fp; $ SQL = ""; While ($ line = @ fgets ($ fp, 40960 )){ $ Line = trim ($ line ); // The following three statements are not required in the php version. You may need to modify them in some earlier versions. $ Line = str_replace ("//", "//", $ line ); $ Line = str_replace ("/'", "'", $ line ); $ Line = str_replace ("// r // n", chr (13). chr (10), $ line ); // $ Line = stripcslashes ($ line ); If (strlen ($ line)> 1 ){ If ($ line [0] = "-" & $ line [1] = "-"){ Continue; } } $ SQL. = $ line. chr (13). chr (10 ); If (strlen ($ line)> 0 ){ If ($ line [strlen ($ line)-1] = ";"){ Break; } } } Return $ SQL; } ?>

Solution 3: run the source command on the client.

The better method is to use the mysql source command:

1. operations on the client:

1. Access the client

　　

The Code is as follows:	Copy code
2. mysql> use Database Name (if not, create one first) 3. mysql> set names 'utf8) 4. mysql> source d:/aaa. SQL;

The data can be imported normally. If there are any errors, you can see the error prompt.

Ii. php file operations:

Create a. php

It contains the following content:

　

The Code is as follows:	Copy code
Mysql_connet ('xxx '); Mysql_query ("set names 'utf8 '"); Mysql_query ("source d:/aaa. SQL '");

The same principle is used to facilitate operations that cannot be performed by command line users.

 

Solution 4:The following is a simple and effective method. It is suitable for non-technical personnel who do not use the command window and can import mysql Databases of any size. Theoretically, no matter how large your database backup files are, you can import them.
The method is as follows:

1. Upload the database backup files (such as backup. SQL) to the root directory of the website.

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

The Code is as follows:	Copy code
System ("mysql-hdbhost-udbuser-ppassword dbname <backup. SQL "); Print "imported successfully ";

Where

Change dbhost to your database server address (Note: The default database server address of the host is localhost)

Change dbuser to your database username

Change password To Your Database User password

Change dbname to your database name (this database must be created before import, otherwise it will fail and no prompt will be given)

Backup. SQL indicates the file name of the database file uploaded to the root directory of the website through ftp (this file is the decompressed file)

3. access mysql in a browser. php: assume that your website domain name is www. bKjia. c0m, enter/mysql in the browser. php, as long as the browser accesses this mysql. in the PHP file, the data is imported. After the data is imported, the words "imported successfully" are displayed. This time is determined by the data size you want to import. Generally, the time is short.