How to Import SQL File From the Terminal

Databases are critical infrastructures in modern applications. Hence, regular backups are very essential. Data is constantly changing, and there is a large margin of error, especially when executing queries. Although relational databases are ACID compliant and can protect against crashes and power failures, keeping a backup copy of your databases is a good idea.

In this article, we will stroll through the MySQL backup and restoration procedure and learn how to dump databases to a file and restore it.

Requirements

Before we proceed with this tutorial, we are assuming that you have:

MySQL Server
Sufficient privileges on the target db.

Once you are ready, let dive in.

Exporting MySQL Database

The first step is to learn how to export your database. Luckily, in MySQL, you can use the mysqldump utility, which allows you to specify the database you wish to backup, and it will automatically write it to a file.

Before running the mysql dump utility, ensure that you have access to the database you wish to access. Second, ensure that no connections are reading or writing to the database.

This will prevent any client from making modifications during the backup operation.

Once you are ready, run the command below to backup a specific database:

$ mysqldump -u username -p target_database> file.sql

We start by invoking the mysqldump command followed by the username used to log in to the MySQL server. This is followed by the -p, allowing MySQL to prompt you for the specified user’s password.

Finally, we specify the name of the database we wish to backup and the name of the file to which we want to save the database.

For example, suppose we wish to back up the database called hello_world. Then, we can run the command as shown:

mysqldump -u root -p hello_world > hello_world_db.sql

The command above should use the root user to back up the hello_world database into the hello_world_db.sql file.

Keep in mind that if the command is successful, it will not provide any terminal output.

Check the contents of the exported file with cat:

$ cat filename.sql

An example output is as shown:

Keep in mind that the output may vary greatly depending on the data stored in the database.

Importing MySQL database from Exported SQL File – Method 1

Like all backups, you will encounter an instance where you must restore the database. Whether you are repairing a corrupted database or reverting the database to a specific point, you can use the exported file to accomplish this.

Before importing the SQL file, ensure that the database in which you wish to restore the data already exists. Otherwise, execute the create database query to initialize a new schema.

Once the database is ready, run the command below to restore the SQL file.

$ mysql -u username -p database_name < exported_file.sql

The command above requires you to specify the database name you wish to restore and the path to the SQL file.

For example:

mysql -u root -p hello_world < hello_world_db.sql

The command will import the SQL file and recreate the database in the specified database.

Import SQL File – Method 2

You can also use the mysqlimport utility to restore a SQL file. The command syntax is as shown below:

$ mysqlimport -u username -p database_name /path/to/sq/file.sql

Unlike the first method, the command does not require using the < operator. However, you do no need to define the path to the SQL file.

Example:

mysqlimport -u root -p hello_world .\hello_world_db.sql

You have successfully restored your database.

Conclusion

As you discovered in this tutorial, backing up and restoring a MySQL database is relatively straightforward.