Import a large sql dump file to a MySQL database from Terminal

Today I had to import a very large SQL dump file (16 Gb) to a MySQL database using osx terminal. If you are using linux or window command line it is the same. The process is the following:

1. Open a terminal (or shell in Linux or command prompt in window with administrative) privilleges
2. If you are in Windows set character set to unicode. Linux is using UTF-8 by default.

   chcp 65001

3. Connect to a mysql instance using command line

   $PATH_TO_MYSQL/mysql -h 192.168.1.1 --port=3306 -u root -p

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

   $PATH_TO_MYSQL/mysql -u root -p

4. You are now in mysql shell. 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 behaviour

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

7. Import your sql dump file

   source /Users/madman/Web/dbdump151225.sql

You are done! Remember to enable foreign key checks when procedure is complete!

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

If you are in Linux you can create a Bash script which will do the dirty job and write to stdout start and end time of import:

#!/bin/sh 
# store start date to a variable
imeron=`date`
echo "Import starting..."
dumpfile="/Users/madman/Webs/mobigate.sql" 
ddl="set names utf8; "
ddl="$ddl set global net_buffer_length=1000000;"
ddl="$ddl set global max_allowed_packet=1000000000; "
ddl="$ddl SET foreign_key_checks = 0; "
ddl="$ddl SET UNIQUE_CHECKS = 0; "
ddl="$ddl SET AUTOCOMMIT = 0; "
# if your dump file does not create a database, select one
ddl="$ddl USE mobigate; "
ddl="$ddl source $dumpfile; "
ddl="$ddl SET foreign_key_checks = 1; "
ddl="$ddl SET UNIQUE_CHECKS = 1; "
ddl="$ddl SET AUTOCOMMIT = 1; "
ddl="$ddl COMMIT ; "
time mysql -h 127.0.0.1 -u root -psa -e "$ddl" 
# store end date to a variable
imeron2=`date`
echo "End import:$imeron2"