How to Import a MySQL Database From the Command Line

Importing a .sql dump file via the command line is considerably more reliable than a browser-based tool like phpMyAdmin for anything beyond a genuinely small file — no upload size limits, no browser timeout, and a clear error message if something in the import actually fails.

Creating the target database first, if it doesn't already exist

mysql -u root -p -e "CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

Explicitly setting the character set and collation at creation time avoids a mismatch with the dump file's own encoding — importing UTF-8 data (including emoji or non-Latin characters) into a database created with an older default charset is a common cause of garbled or truncated text after import.

The basic import command

mysql -u root -p my_database < backup.sql

The < redirect feeds the file's contents into the mysql client's standard input, exactly as if each line had been typed interactively — this is the standard, reliable way to run a large SQL dump.

Importing a compressed dump directly, without extracting first

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

Piping gunzip's decompressed output directly into mysql avoids needing enough free disk space to hold both the compressed file and its fully extracted version simultaneously — genuinely useful for a large database dump on a server with limited available disk space.

Importing with a progress indicator, for a large file

pv backup.sql | mysql -u root -p my_database

pv ("pipe viewer," a small utility that may need separate installation) shows a progress bar and estimated time remaining as data flows through the pipe — genuinely useful for a large import where the plain mysql < file.sql command otherwise gives no feedback at all about how far along it is.

Specifying host and port for a remote database

mysql -h remote-host.example.com -P 3306 -u username -p my_database < backup.sql

Handling a large import that exceeds default timeout or memory settings

mysql --max_allowed_packet=256M -u root -p my_database < backup.sql

max_allowed_packet caps the size of a single SQL statement or row MySQL will accept — a dump file with an unusually large single row (a big BLOB column, for instance) can fail against the server's default packet size limit, and increasing it on the client's connection is the fix for this specific error.

Verifying the import succeeded

mysql -u root -p -e "USE my_database; SHOW TABLES; SELECT COUNT(*) FROM users;"

Checking that expected tables exist and a spot-checked table has a reasonable row count confirms the import actually completed successfully, rather than assuming success just because the command returned without an obvious error message on screen.