Import and Export SQL file into MYSQL using Command Line

271
import and export mysql command line

In this short article, we will discuss how to import SQL file into MySQL database using command line. MYSQL is open-source relational database management system.

Importing and Exporting SQL file from MYSQL database is easy. If you are using phpMyAdmin, you can simply do it using few clicks. This is pretty quick and easy for small databases. However, it the size of database is large, then we get various errors such as timeout, memory exhausted, and so on. In such case, command line comes to save us while importing large SQL file into MYSQL databases.

Import SQL File into Database

Let’s get straight to the point without wasting time. First of all, go the bin folder inside mysql. If you are using xampp, you can go to the installation directory:

D:\xampp\mysql\bin>

However, if you are using software like HeidiSQL, you can run the following command from anywhere because mysql.exe services are always running from the startup. Open up your command line, and enter the following command and enter when prompt.

Note: Please create an empty Database before executing the command. Also, you need to remove the SQL query creating a database from your SQL file. If your SQL file is in different directory, don’t forget to give full path of the file.

$ mysql -u root -p database_name < your_sql_file.sql

Here,

  • -u is a short form for username.
  • root is the username of my database
  • -p is a short form of password
  • database_name is the name of your database
  • your_sql_file.sql is the name of your SQL file to import

Export SQL File from Database

Similar to import, we can also export database using the command tool. For this, we will use mysqldump  command line. Simply run the following command in your terminal:

mysqldump -u root -p database_name > new_sql_filename.sql

If you are getting error like command not found, add the path of your MySQL bin directory to your environment variables. After running the command, you will be prompted for password. After that, a new file with the given name will be exported.

Read More Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.