In this tutorial, I will show you how to import a large SQL file (1GB<) to MySQL database using Windows Command Line. This tutorial will cover both Windows and Linux platform as those commands are the same for both OS. I will guide you step by step procedure for importing.

  1. Open CMD/Shell as Administrator.
  2. In windows please run the following command to set the character set to Unicode. For Linux following command is not required as it has default character encoding UTF-8.
    chcp 65001
  3. Now connect MySQL instance for a remote location, using the following command.
    $PATH_TO_MYSQL\mysql.exe -h your_remote_server_ip --port=server_port -u root -p
  4. For localhost MySQL instance, use the following command.
    $PATH_TO_MYSQL\mysql.exe -u root -p
  5. After that, it will ask for a password. Please provide your password or leave it blank if there is no password. Then press ENTER.
  6. You will see MySql shell opened on the same cmd window as below.
    mysql->
  7. As you have a large SQL file, so it is necessary to set a large buffer length for MySQL shell. Otherwise, it will throw an error for a large file. Please run the following command to increase the MySQL buffer length.
    set global net_buffer_length=1000000;
  8. You may set it higher depending on your file size.
  9. 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;
  10. To minimize the total time for importing the large file it is good practice that you disable all kind SQL relational checks. Please run the following commands to check a few features to minimize the time. (Optional)
    SET foreign_key_checks = 0;
    SET UNIQUE_CHECKS = 0;
    SET AUTOCOMMIT = 0;
  11. Finally, import your SQL file with the following command.
    source $PATH_TO_YOUR_SQL_FILE_DIRECTORY\your_file.sql
  12. Wait until import finished. Total time for importing may differ depending on the size of the file.
  13. Please set the following commands to 1 if you have set to 0 previously to maintain relational database functionality.
    SET foreign_key_checks = 1;
    SET UNIQUE_CHECKS = 1;
    SET AUTOCOMMIT = 1;
  14. You are all set and ready to browse your database.