Importing Large SQL Files from the Ubuntu Terminal

I often had trouble importing large SQL files through phpMyAdmin or desktop database tools. In my case, an import could stop around 60–70% with a timeout or lost-connection error. After several failed attempts, I switched to the MySQL command-line client.

The terminal path has been more reliable for the dumps I work with because it does not add a browser upload or GUI process between the file and MySQL. It does not remove database, disk, packet, permission, or connection limits, so I still check the command result instead of assuming that reaching the prompt means the data is correct.

The Command I Use Most Often

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

For a dump in the Downloads directory:

mysql -u root -p mydatabase < ~/Downloads/backup.sql

The client asks for the password, reads statements from the file, and returns to the shell when it finishes. I check the exit status immediately:

echo $?

0 means the client process completed successfully. A non-zero value means I need to inspect the error output before continuing.

These restore examples intentionally omit --force. That option tells the client to continue after an SQL error, which can leave a partial import harder to notice.

Importing from the MySQL Prompt

Sometimes I enter the client first:

mysql -u root -p

Then select the database and use MySQL’s source command:

USE database_name;
SOURCE /home/user/backup.sql;

This is convenient when I also need to run a few queries in the same session.

Creating the Database First

If the target database does not exist yet:

mysqladmin -u root -p create database_name
mysql -u root -p database_name < file.sql

The database name, character set, and collation should match the application’s requirements rather than relying blindly on server defaults.

Watching Progress with `pv`

For a large file, pv shows how much input has passed through the pipeline. In Bash, I also enable pipefail:

sudo apt install pv
set -o pipefail
pv file.sql | mysql -u root -p database_name

pipefail matters because a normal shell pipeline can otherwise report only the status of its last command. With it enabled, a failure in pv or the MySQL client makes the pipeline fail.

The progress bar measures bytes read from the file. It is useful feedback, but it is not proof that every statement was accepted or that the restored data is complete.

Compressed Dumps

A gzip-compressed dump can be streamed without first creating a second uncompressed file:

set -o pipefail
gzip -dc backup.sql.gz | mysql -u root -p database_name

For a zip archive containing the SQL file:

set -o pipefail
unzip -p backup.sql.zip backup.sql | mysql -u root -p database_name

Again, I check the pipeline status and error output after it finishes.

When a Packet Is Too Large

If the client reports a packet-size error, its buffer can be adjusted with a client option placed before the database name:

mysql --max-allowed-packet=512M -u root -p database_name < file.sql

The server has its own max_allowed_packet setting. Raising only the client value will not help when the server limit is the one rejecting the statement. I change limits only after the error indicates that this is the problem.

What I Check Afterward

The command line cannot make an invalid or incomplete dump valid. Before using the restored database, I check:

the client or pipeline exit status;
MySQL error output and, when needed, the server log;
whether important tables exist;
whether a few expected records and counts are present;
whether the application can read the restored schema.

This is enough for the practical problem I originally had: getting past GUI imports that repeatedly stopped. The exact validation should become stricter when the dump is used for a migration or another critical workflow.