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.
- Open CMD/Shell as Administrator.
- 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
- 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
- For localhost MySQL instance, use the following command.
$PATH_TO_MYSQL\mysql.exe -u root -p
- After that, it will ask for a password. Please provide your password or leave it blank if there is no password. Then press ENTER.
- You will see MySql shell opened on the same cmd window as below.
mysql->
- 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;
- You may set it higher depending on your file size.
- 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;
- 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;
- Finally, import your SQL file with the following command.
source $PATH_TO_YOUR_SQL_FILE_DIRECTORY\your_file.sql
- Wait until import finished. Total time for importing may differ depending on the size of the file.
- 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;
- You are all set and ready to browse your database.
November 20, 2018 at 7:07 am
Great article. Thanks man for sharing knowledge
November 22, 2018 at 1:44 pm
That’s great if it helped you. Thank you for your review.
December 17, 2018 at 5:29 am
Thanks. Please share our blog with your friends.