How to import a very large SQL dump file (6 Gb) to a MySQL database using windows command line. If you are using linux it is the same. The process is the following:
Open a command prompt (or shell in Linux) with administrative privilleges
Connect to a mysql instance using command line
# mysql -h 192.168.1.1 --port=3306 -u root -p
if you are in localhost you do not need host and port
# mysql -u root -p
Or if plesk,
# mysql -uadmin -p`cat /etc/psa/.psa.shadow`
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
mysql> set global net_buffer_length=1000000;
Set maximum allowed packet size to a large byte number.The default value may throw errors for such large data files.
mysql> set global max_allowed_packet=1000000000;
Disable foreign key checking to avoid delays,errors and unwanted behaviour
mysql> SET foreign_key_checks = 0; mysql> SET UNIQUE_CHECKS = 0; mysql> SET AUTOCOMMIT = 0;
Import your sql dump file
mysql> use db_name; mysql> source backup-file.sql;
Remember to enable foreign key checks when procedure is complete!
mysql> SET foreign_key_checks = 1; mysql> SET UNIQUE_CHECKS = 1; mysql> 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 started: OK" dumpfile="/home/bob/bobiras.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 jetdb; " 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 ; " echo "Import started: OK" time mysql -h 127.0.0.1 -u root -proot -e "$ddl" # store end date to a variable imeron2=`date` echo "Start import:$imeron" echo "End import:$imeron2"
http://www.ozerov.de/bigdump/ seems good