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

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

Written by Bobby Iliev on Nov 10th, 2021 ・ Views ・ Report Post

Bobby Iliev Visit User Profile

Introduction

In some cases if you have a really large .sql file, it is best to import it via the command line.

You might need to do this in case that you are using a tool like PHPmyAdmin, which more often than not, has a certain limit that the file that you want to import could be.

In this tutorial, you will learn how to import an .sql file

Prerequisites

Before you get started you need to have MySQL installed and an .sql backup.

In case that you don't have an .sql backup of your database, you can follow the steps here on how to do that:

How to export/backup a MySQL/MariaDB database with mysqldump

Creating a blank database

What I usually prefer doing is to create a completely empty database, so that way I know that there will be no existing data or table structure which could cause issues during the import.

To create a new blank database, first access MySQL:

mysql -u your_user -p

And then you can use the following statement:

🤩 Our Amazing Sponsors 👇
DigitalOcean View Website
DigitalOcean offers a simple and reliable cloud hosting solution that enables developers to get their website or application up and running quickly.
Laravel News View Website
Laravel News keeps you up to date with everything Laravel. Everything from framework news to new community packages, Laravel tutorials, and more.
Genesis View Website
A Laravel Starter Kit that includes Authentication, User Dashboard, Edit Profile, and a set of UI Components. 
CREATE DATABASE demo_db;

Finally exit the MySQL prompt:

exit;

Note: make sure to change the demo_db with the name of the database that you want to create.

Importing an .sql file to MySQL

Once you have your plain database in place, you can import your .sql file using the following command:

mysql -h localhost -u username -p demo_db < your_exported_file.sql

Rundown of the command:

  • mysql: first we specify the MySQL client CLI tool
  • -h: this flag is used to specify the host that you want to import the data to. If you have your .sql file stored on the same server where your MySQL service is running on, you can specify localhost as the host, but if you are running your MySQL service on a remote server, make sure to change this accordingly.
  • -u: this flag specifies the MySQL username that you would like to use.
  • -p: this flag indicates that you should be prompted to enter a password once the command is executed.
  • demo_db: this is the name of the database that we've just created in the previous step
  • <: this symbol indicates that we want to import the .sql file to the specified MySQL database
  • finally we have the name of the .sql file. Note that if the file is not in the same directory as your current directory, it is better to provide the full path to the .sql file, eg. /home/devdojo/backups/some_backup.sql.

Once you run the command it might take a while for the operation to complete depending on the size of the file. You would not see any output while the operation is on going.

Conclusion

This is pretty much it! Now you know how to import a .sql file to a MySQL database!

To learn more about SQL, make sure to check out this free eBook here:

💡 Introduction to SQL eBook

In case that you are already an SQL expert, and want to drastically reduce the latency of your data analytics, I would recommend checking out Materialize!

Materialize - a streaming database

Materialize is a Streaming Database for Real-time Analytics. Materialize is a reactive database that delivers incremental view updates and it helps developers easily build with streaming data using standard SQL.

Comments (0)