Skip to content

Instantly share code, notes, and snippets.

@harssh-sparkway
Created April 8, 2014 04:27
Show Gist options
  • Save harssh-sparkway/10090848 to your computer and use it in GitHub Desktop.
Save harssh-sparkway/10090848 to your computer and use it in GitHub Desktop.
Import a large sql dump file to a MySQL database from command line
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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment