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

2 minute read

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:

  1. Open a command prompt (or shell in Linux) 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.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
    
  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 C:\bob_db\dbdump150113.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 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"

Leave a Comment