Home → Databases

How to import an SQL file using command line in MySQL?

|In: Databases|Last Updated:
How to import an SQL file using command line in MySQL?
Like Tweet Pin it Share Share Email

Import MySQL database using command line

 

If you have a very large SQL dump file to import and you know very well that it is not very easy to import using phpMyAdmin, even if you split it in many pieces, it is also be hard using PHP script as it will time out after a particular time.

 

In this case there is only one acceptable and easy solution is import the file directly from MySQL command line.
As command Line will always be the most efficient, though not the most user-friendly way to accomplish a database export and import.

 

Follow the below easy steps to make your life easy.

 

The below example I am showing is executed on my local server(local host) and using XAMPP, but same will also work on Linux if you are using Linux for your application but you need to make sure that the file you want to import is of a .sql(.gz, .gzip) format.

 

The below method is perfect if

 

  • You do not have phpMyAdmin installed.
  • You do not have remote web access to the database server.
  • You do have SSH/Putty access.
  • The database file size is very large.
  • You want total control over the process.

Let’s start

 

1. Open a command prompt (or shell in Linux) with administrative privileges.

 

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 (If your MySQL path is different rather than drive D: as I have, you should be in that folder only)


D:\xampp\mysql\bin\mysql -h 192.168.1.1 --port=3306 -u root -p

If you are in local host you do not need host and port


D:\xampp\mysql\bin\mysql -u root -p

 

4. It will now ask for password: enter the password
—> <your-password>

 

5. 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;

 

6. 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;

 

7. Disable foreign key checking to avoid delays, errors and unwanted behavior


SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

 

8.  Select your database as below


use <your-db-name>;

 

If you have not created the database yet, in which you want to import the database then below the above you can created the database using the below MySQL command


create database <your-db-name>; and after then run use <your=db-name>;

 

9. Provide your dump file full path name


source <DbName.sql>

If your file is not is the location you are currently in then provide the complete path of the file as below


source C:\scriptarticledotcom_db\dbdump150404.sql

 

It’s DONE!!

Remember to enable foreign key checks when process is complete else it will set for all of your databases as well and it will work improper!

 

Run the below MySQL commands for reset the global MySQL settings


SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;

 

Hope the above will work, share you feedback or comments if you face any issue or have more suggestion to make the article more useful for developers.

 

 

Recommended Posts for You