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:
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
-uis 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.