How to Import a Large (14 Gb) MySQL Dump File into a New MySQL Database

How to import a 10GB file to MySQL?

Set the max_allowed_packet and net_buffer_length through mysql command line client

set global net_buffer_length=10000000; 

set global max_allowed_packet=10000000000;

How to import 5 GB MySQL Dump file in phpmyadmin

Most probably you won't be able to import a 5GB database on PHPMyAdmin. Try BigDump. You can download it here. Step are mentioned below.

Download and unzip bigdump.zip on your PC.

Open bigdump.php in a text editor, adjust the database configuration and dump file encoding.

Drop the old tables on the target database if your dump doesn't contain “DROP TABLE” (use phpMyAdmin).

Create the working directory (e.g. dump) on your web server

Upload bigdump.php and the dump files (*.sql or *.gz) via FTP to the working directory (take care of TEXT mode upload for bigdump.php and dump.sql but BINARY mode for dump.gz if uploading from MS Windows).

Run the bigdump.php from your web browser via URL like http://www.yourdomain.com/dump/bigdump.php.

Now you can select the file to be imported from the listing of your working directory. Click “Start import” to start.

BigDump will start every next import session automatically if JavaScript is enabled in your browser.

Relax and wait for the script to finish. Do NOT close the browser window!
IMPORTANT: Remove bigdump.php and your dump files from your web server.

Else try the command line

mysql -u user_name -p database_name < path/to/the/file.sql

Importing 40GB+ .sql file

Step 1: Make changes to the configuration file: my.cnf (located in C:\ProgramData directory in Windows if running MySQL V5.6, Note: this directory is hidden by default, you must enable visibility of hidden files in Folder Options).

max_allowed_packet=2000M

Step 2: Restart MySQL service

Step 3: Use the following dump command:

mysql -hhostname -uusername -ppassword db_name
< C:\sql_input_path\db.sql > C:\error_log_output_path\error.txt

MySQL Server has gone away when importing large sql file

As stated here:

Two most common reasons (and fixes) for the MySQL server has gone away
(error 2006) are:

Server timed out and closed the connection. How to fix:

1. check that wait_timeout variable in your mysqld's my.cnf configuration file is large enough. On Debian: sudo nano
/etc/mysql/my.cnf, set wait_timeout = 600 seconds (you can
   tweak/decrease this value when error 2006 is gone), then sudo
/etc/init.d/mysql restart. I didn't check, but the default value for
   wait_timeout might be around 28800 seconds (8 hours).

2. Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, it assumes that something has
   gone wrong with the client and closes the connection. You can increase
   the maximal packet size limit by increasing the value of
   max_allowed_packet in my.cnf file. On Debian: sudo nano
/etc/mysql/my.cnf, set max_allowed_packet = 64M (you can
   tweak/decrease this value when error 2006 is gone), then sudo
/etc/init.d/mysql restart.

Edit:

Notice that MySQL option files do not have their commands already available as comments (like in php.ini for instance). So you must type any change/tweak in my.cnf or my.ini and place them in mysql/data directory or in any of the other paths, under the proper group of options such as [client], [myslqd], etc. For example:

[mysqld]
wait_timeout = 600
max_allowed_packet = 64M

Then restart the server. To get their values, type in the mysql client:

> select @@wait_timeout;
> select @@max_allowed_packet;

Can't import database through phpmyadmin file size too large

Its due to PHP that has a file size restriction for uploads.

If you have terminal/shell access then the above answers @Kyotoweb will work.

one way to get it done is that you create an .htaccess/ini file file to change PHP settings to get the sql file uploaded through PHPmyAdmin.

php_value upload_max_filesize 120M //file size
php_value post_max_size 120M
php_value max_execution_time 200
php_value max_input_time 200

Note you should remove this file after upload.

---

Related Topics