Import SQL in Ubuntu using Command Line

I often struggled when importing large SQL files. Initially, I used GUI tools like phpMyAdmin, Laragon, or DBeaver, but they frequently failed midway. The import would only reach 60-70%, then encounter timeout or connection lost errors. This frustrated me because I needed 100% of the data.

After several failures, I decided to switch to the command line. The results were far better and more reliable. No more timeout issues or imports stopping halfway. That’s why I want to share the methods I currently use.

The Simplest Method

This is the method I use most often—simple and never fails:

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

For example, if you have a backup.sql file in your Downloads folder and want to import it to the mydatabase database:

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

You’ll be prompted for the MySQL password—just enter it. Then wait until the process completes. Unlike GUI tools, this method always finishes 100%, no stopping midway.

Why Command Line is Better?

Based on my experience:

No timeout: phpMyAdmin and other GUI tools have execution time limits. If the SQL file is large, it often doesn’t finish in time.
Memory limit isn’t an issue: GUI tools usually have strict memory limitations. Command line is much more flexible.
Progress tracking available: We can use tools like pv to see real-time progress.
More stable: No JavaScript errors, no browser connection drops.

Importing from Within MySQL

Sometimes I prefer to enter the MySQL shell first, then import from inside. Here’s how:

mysql -u root -p

After entering, select the database to fill, then run the SOURCE command:

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

This method is convenient because we can directly execute other queries if needed, without repeatedly exiting and re-entering MySQL.

If the Database Doesn’t Exist Yet

This happens often—we get an SQL file but the database hasn’t been created yet. The solution is simple, create it first:

# Create new database
mysqladmin -u root -p create database_name

# Then import
mysql -u root -p database_name < file.sql

Or you can also create the database directly from the MySQL shell:

mysql -u root -p -e "CREATE DATABASE database_name;"
mysql -u root -p database_name < file.sql

Tips for Very Large SQL Files

This is what used to make me fail when using GUI tools. SQL files over 500MB? Forget using phpMyAdmin. Better to go straight to command line with special parameters:

mysql -u root -p database_name < file.sql --max_allowed_packet=512M

Or if you want something more interesting, use pv to see the progress bar. This is really important for large files, so we know how many percent it has progressed:

pv file.sql | mysql -u root -p database_name

Install pv first:

sudo apt install pv

It’s great to see the progress bar moving, unlike GUI tools that sometimes just get stuck at one number then suddenly error out.

Compressed SQL Files

Sometimes SQL files are compressed to .gz to save storage. No need to extract first, you can import directly:

gunzip < backup.sql.gz | mysql -u root -p database_name

Or if it’s in .zip format:

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

This also saves time and storage, because we don’t need to extract first which would require double the space.

If You Encounter Problems

Access Denied? Check your username and password again. Or maybe the user doesn’t have access to that database.

Database doesn’t exist? Just create the database first using the method I explained above.

max_allowed_packet too small? Add the --max_allowed_packet parameter or edit the MySQL config file at /etc/mysql/mysql.conf.d/mysqld.cnf.

Import stops but no error? Try checking the MySQL log at /var/log/mysql/error.log to find out what the problem is.

Conclusion

Since switching to command line, I’ve never experienced the drama of SQL imports failing midway again. If you’re still using GUI tools and often experience the same problems I used to have, try switching to command line. It may look complicated at first, but once you get used to it, it’s much more practical and guaranteed to succeed.

The important thing is you now know the various options—just choose what’s most comfortable for you. Thanks!