Import a large sql dump file to a MySQL database using command line

Steps to import large amount of data (dump ) file to mysql using command line. 

1. Open a command prompt (or shell) with administrative privileges

2. Connect to a MySQL instance using command line

If you are using online/hosted sql like (AWS)

$PATH_TO_MYSQL\mysql.exe -h 198.166.1.9 --port=3306 -u root -p

If you are in localhost you do not need host and port

$PATH_TO_MYSQL\mysql.exe -u root -p

3. Check your network buffer length value.

            SHOW VARIABLES LIKE 'net_buffer_length';

4. If the value is less than 1000000 .Set network buffer length to a large byte number. The default value may throw errors for such large data files

set global net_buffer_length=1000000;

5. Set maximum allowed packet size to a large byte number. The default value may throw errors for such large data files.

set global max_allowed_packet=1000000000;

6. Disable foreign key checking to avoid delays, errors and unwanted behavior

SET foreign_key_checks = 0;

SET UNIQUE_CHECKS = 0;

SET AUTOCOMMIT = 0;

7. Import your sql dump file

source C:\db_dumpfolder\filename.sql

Here “C:\db_dumpfolder\filename.sql” is a fully qualified path of your sql file.

8. Once total sql file execution completed. Don’t forget to enable foreign key checks when procedure is complete!

      SET foreign_key_checks = 1;

      SET UNIQUE_CHECKS = 1;

      SET AUTOCOMMIT = 1;

If you want to edit large amount of sql file(if you are unable to open large .sql file) use below Notepad.

http://www.editpadlite.com/

This Source from the:

https://cmanios.wordpress.com/2013/03/19/import-a-large-sql-dump-file-to-a-mysql-database-from-command-line/