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

import_large_dump

Import a large sql dump file to a MySQL database from command line
Posted on March 19, 2013 by cmanios
Today I had 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
If you are in Windows set character set to unicode. Linux is using UTF-8 by default.
chcp 65001
Connect to a mysql instance using command line
$PATH_TO_MYSQL\mysql.exe -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.exe -u root -p
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;
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;
Disable foreign key checking to avoid delays,errors and unwanted behaviour
SET foreign_key_checks = 0;
Import your sql dump file
source C:\bob_db\dbdump150113.sql
You are done! Remember to enable foreign key checks when procedure is complete!

SET foreign_key_checks = 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:

1
2
3
4
5
6
7
8
9
10
11
12
#!/bin/bash 
 
imeron=`date`
 
echo "Start import:$imeron"
echo "Recorded start date: OK"
 
echo "Import started: OK"
mysql -h 127.0.0.1 -u root -proot -e " use cars; set names utf8; set global net_buffer_length=1000000; set global max_allowed_packet=1000000000; SET foreign_key_checks = 0; source /home/bob/mydump.sql; SET foreign_key_checks = 1 ;"
 
imeron=`date`
echo "End import:$imeron"