Reader Stacks
Hosting & Deployment

How to Import a MySQL Database From the Command Line

Use the mysql client with stdin redirection or source, then add the host, charset, authentication, privilege, backup, and verification safeguards appropriate to the restore.

Published
Jun 21, 2022
Updated
Aug 10, 2026
By
Reader Stacks Editorial

To import a MySQL SQL dump from the command line, feed the file to the mysql client and select the destination database: mysql -u app_user -p database_name < dump.sql. Use -p without a password value so MySQL prompts for it instead of exposing a secret in shell history or process listings. Before a production restore, verify the host and database, preserve any existing data you may overwrite, and rehearse the restore on a staging copy when the database matters.

The basic import command

mysql -u app_user -p database_name < dump.sql

The shell opens dump.sql and streams its contents to the MySQL client through standard input. The statements inside the file decide what is created, changed, or deleted. This is an import operation; mysqldump is an export utility and should not be mixed into the restore command.

Inspect the dump before you run it

Search the beginning of an unfamiliar dump for CREATE DATABASE, USE, DROP TABLE, SET FOREIGN_KEY_CHECKS, DEFINER, triggers, routines, and events. Some dumps select their own database; others expect the database name on the client command. A dump may also contain destructive statements, so “it is only a .sql file” is not a safety boundary.

Create the destination database only when needed

mysql -u admin_user -p -e "CREATE DATABASE app_restore CHARACTER SET utf8mb4;"
mysql -u app_user -p app_restore < dump.sql

The restoring account needs privileges for the statements actually present in the dump. A data-only restore may need much less privilege than a dump that creates schemas, triggers, routines, events, or objects with explicit definers.

Import a gzip-compressed dump

On Linux and macOS, stream decompressed SQL directly into MySQL:

gzip -dc dump.sql.gz | mysql -u app_user -p database_name

gunzip -c is a common equivalent. This avoids creating a second uncompressed file on disk. On Windows, GNU gzip is not a built-in assumption; use a trusted gzip-capable tool/WSL or extract first rather than inventing an unverified text pipeline for a large SQL file.

Windows: input redirection and source

mysql.exe -u app_user -p database_name < C:\backups\dump.sql

MySQL also documents the client source command, which is useful when ordinary shell redirection is awkward:

mysql.exe -u app_user -p database_name -e "source C:/backups/dump.sql"

Inside an interactive MySQL client, source file_name and \. file_name execute statements from a file. Use path syntax appropriate for the client and shell you are actually using.

Remote host, port, and socket

TCP

mysql -h db.example.internal -P 3306 -u app_user -p database_name < dump.sql

Lowercase -h selects the host; uppercase -P selects the TCP port. Verify the hostname before a production restore—sending a correct dump to the wrong environment is still a serious failure.

Unix socket

mysql --socket=/var/run/mysqld/mysqld.sock -u app_user -p database_name < dump.sql

Socket paths are primarily a Unix-like-system concern. Windows installations commonly use TCP and may also use named pipes when configured.

Choose the client character set when necessary

mysql --default-character-set=utf8mb4 -u app_user -p database_name < dump.sql

--default-character-set controls the client connection character set. It does not rewrite incompatible column definitions or make an unknown destination collation suddenly available. If a dump names a collation unsupported by the destination server, compare source and destination MySQL versions and schema definitions.

Keep passwords out of commands

MySQL documents command-line passwords as insecure. For an interactive restore, use -p with no value. For repeatable automation, use an option file or a login path rather than hard-coding a password in a script.

mysql_config_editor set --login-path=restore   --host=db.example.internal   --user=app_user   --password

mysql --login-path=restore database_name < dump.sql

The password is entered interactively while the login path is created and does not appear in the later restore command.

Large dumps: total file size is not the packet limit

The MySQL client streams input, so a multi-gigabyte dump is not automatically too large. Common failure points are an unusually large single statement or BLOB, server resource pressure, disk exhaustion, lock contention, server restarts, or network interruption.

Packet too large

MySQL 8.4 documents separate max_allowed_packet limits for the client and server. A client-side override can be useful:

mysql --max-allowed-packet=64M -u app_user -p database_name < dump.sql

But it cannot force a server configured with a smaller limit to accept the packet. Diagnose the actual failing statement and inspect both limits before changing production configuration.

Do not assume an outer transaction makes every dump reversible

Some MySQL DDL statements cause implicit commits, and dumps may contain their own transaction/session statements. Wrapping an arbitrary dump in START TRANSACTION is therefore not a universal rollback mechanism. Inspect the dump and know which statements and storage engines are involved.

Production restore checklist

  • Backup: preserve the current target if it cannot be reconstructed safely.
  • Staging: restore the same file to a non-production instance first when practical.
  • Application writes: decide whether writes must stop and how changes made during the restore window will be reconciled.
  • Capacity: confirm disk for data, indexes, redo/binary logs, and temporary work.
  • Replication: know whether the import should replicate and what load it creates on replicas.
  • Monitoring: watch database errors, locks, disk pressure, connections, and replication lag during a high-impact restore.

Common import errors

ERROR 1045: Access denied

Check the username, connection host, authentication method, and grants. A user that can log in can still lack privileges required by a later statement in the dump.

ERROR 1049: Unknown database

The selected database does not exist or its name is wrong. Check whether the dump creates/selects its own database before creating a new one.

Packet too large

Inspect client and server max_allowed_packet and identify the oversized statement/value. The dump's total size is not the relevant packet size.

Server has gone away / lost connection

Check MySQL error logs, server uptime, packet limits, network stability, resource exhaustion, and timeouts. A disconnect is not automatically a timeout.

Duplicate-key or foreign-key failures

The target may already contain data, rows may arrive in an unexpected order, or the dump may not be the consistent restore set you expected. Do not reflexively add --force; continuing after SQL errors can leave a partially restored database.

Verify the restored data

mysql -u app_user -p database_name -e "SHOW TABLES;"
mysql -u app_user -p database_name -e "SELECT COUNT(*) AS users_count FROM users;"

For an important restore, also validate representative table counts, migration/schema state, expected routines/triggers, and application-level invariants. A client command completing successfully does not prove that you restored the intended snapshot.

Sources and further reading