Import an SQL File Using the Command Line in MySQL

1. Overview

Importing an SQL file is a basic operation for any MySQL user to restore a database from a backup, transfer data, share a database schema or configure a new environment. Although Graphical User Interface (GUI) applications such as phpMyAdmin are available, command line usage provides more control, efficiency, and reliability, especially for big files or scripted operations.

In this tutorial, we’ll discuss commonly used command-line approaches to import a .sql file.

2. Using Shell Redirection

The most straightforward method is employing the redirection operator of the shell (<). This method redirects the contents of the SQL file directly to the mysql client program from the command line. Below is a one-line command to import the SQL file:

$ mysql -u [username] -p [database_name] < /path/to/file.sql

The above command prompts to enter the password for the specified MySQL user. It’s crucial to have the target database already created before running this command, or else, the import fails.

The primary advantage of this method is its simplicity. It’s a quick, single-line command that is easy to use in scripts. For small to medium-sized files, it’s highly efficient. But, if we’re importing a large file over a network, this method can sometimes be slower due to network latency, as it streams each query to the server one by one rather than as a single block.

3. Using the source Command

The second method is to log in to the MySQL command-line client first and then use the source command. It has the advantage of offering an interactive session, with more control prior to starting the import:

$ mysql -u [username] -p [database_name]

When we’re logged in, we’ll get the mysql> prompt. Using this prompt, we can execute the source command using the path to the SQL file:

mysql> source /path/to/file.sql;

Again, we should check that the database [database_name] already exists before we start the process. The source command offers an immediate feedback, which generally displays the output of each command being run from the SQL file. This can be very reassuring to see during a large import.

MySQL, by default, executes with autocommit turned on. That is, every individual SQL statement, like an INSERT or an UPDATE, is executed as its own transaction and is automatically committed to the database right away. For a big SQL file containing thousands of statements, this leads to an enormous number of individual transactions, each of which takes a log flush to disk. This constant disk I/O is one of the major performance bottlenecks.

The below command snippet first selects the appropriate database and then imports the SQL file by treating it as a single transaction:

mysql> use db_name;
mysql> SET autocommit=0 ; source file.sql ; COMMIT ;

In the above command, we first disable the autocommit. By doing so, we ensure that all these statements are buffered and not written to disk immediately. All the updates from the SQL file are committed to the database in a single, bulk operation with the COMMIT statement. It needs only one log flush to disk, not one for each individual statement, which is where the huge performance improvement comes from.

One of the big drawbacks to turning off autocommit is that the import is now an “all-or-nothing” process. If one of the statements in the SQL file fails, the whole transaction will be rolled back.

4. Conclusion

In this article, we compared various means of importing an SQL file from the command line in MySQL. For imports of small to medium-sized files, the redirection method is a great option for its simplicity. For large files, imports from a less stable network, or when we need more interactive feedback and control, the source command is the more secure and powerful choice.