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

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:

  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_MYSQLmysql.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_MYSQLmysql.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…

View original post 78 more words

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s