How to import a large SQL dump file to MySQL ?

1. Open command prompt.

2. [Windows only] Set char set to unicode

chcp 65001

3. Connect to MySQL instance (remote or localhost MySQL instance)

mysql -h 192.165.1.1 --port=3306 -u root -p

mysql -u root -p

4. Set Max allowed packet size to a large number

set global max_allowed_packet=1000000000;

5. Set network buffer length to a large number

set global net_buffer_length=1000000; 

6. Disable checks for foreign key to avoid errors, delays and unusual behaviour 

SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

7. Import your SQL dump file
source C:\shaan\dbdump020320016.sql

8. Enable foreign key checks when procedure is complete

SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;