How to Import an SQL File Into MySQL Database

MySQL is one of the most popular database management systems. It is most often used for web-based application and it is also one of the main components of the LAMP (Linux, Apache, MySQL and PHP) open-source web application stack. In this tutorial, we will show you how to import an SQL file into MySQL database on a Linux VPS. This will help you when you need to transfer your database from one server to another or to restore a database backup.

Before we start, make sure that you have full root access to your Linux server, or at least you have a system user with sudo privileges which you can use to connect to your server. Once you connect to your server via SSH run the following command to check if the MySQL database server is installed and which version it is:

mysql -V

Depending on which version you have installed, the output should be similar to this:

mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper

If you need to connect to the MySQL database server through the command line as user root run the following command:

mysql -u root -p

MySQL will ask you to enter the password for the MySQL root user. In case you don’t have a password set up for the MySQL root user, you can connect with the following command instead:

mysql -u root

Of course, it is always recommended to keep your services secure, so if you haven’t set up the MySQL root password yet, you can do that now using the mysql_secure_installation command:

mysql_secure_installation

Then, follow the on-screen instructions to finish the setup:

Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: y

Please set the password for root here.
New password:

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

All done!

That’s it, you can now connect to the MySQL database server as the MySQL root user using your new password.

Import an SQL file into MySQL database

We will now show you how to import an SQL file into an existing MySQL database.

To list all existing databases in your MySQL database server, first connect to your database server with:

mysql -u root -p

and then run the following command:

mysql> show databases;

This will list all databases created in MySQL, giving you an output similar to this:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

If you want to create a new database for the SQL file, you can do it with the following command:

mysql> CREATE DATABASE DatabaseName;

To create a MySQL user and assign a new password to it, run the following command:

mysql> CREATE USER 'DatabaseUser'@'localhost' IDENTIFIED BY 'password';

To give the new or existing user access to the new databases, run the following:

mysql> GRANT ALL ON DatabaseName.* TO 'DatabaseUser'@"localhost";

Lastly, reload all the privileges with:

mysql> FLUSH PRIVILEGES;

and exit the MySQL server with:

mysql> exit;

Your database is now ready, and we can now import the SQL file.

To import the SQL file, for example, BackupDatabase.sql, into your new database, simply run the following command:

mysql -u DatabaseUser -p DatabaseName < BackupDatabase.sql

You will be asked for the password of the database user to which the database is assigned. Enter your database user password to finish the import.

With this, the BackupDatabase.sql file has been successfully imported.

Export a MySQL database into SQLfile

Additionally, we will also show you how to easily export an existing MySQL database into a SQL file. This can be done with the mysqldump command. For example:

mysqldump -u DatabaseUser -p DatabaseName > BackupDatabase.sql

Again, you will be asked for the database user password, and it will create an SQL file of your database which you can store it as a backup, and import it later if needed.

Of course, you don’t have to import your SQL files in MySQL,  if you are using one of our outsourced Linux server management services, in which case you can simply ask our expert Linux admins to help you. They are available 24×7 and will take care of your request immediately.

PS. If you liked this post on how to Import an SQL file into MySQL Database, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks