Import and Export MySQL Databases Using the Command Line

Mastering MySQL database import and export operations from the command line is a non-negotiable skill for any database administrator or backend engineer. The `mysqldump` utility exports a database into a portable `.sql` file containing all DDL and DML statements required to fully reconstruct the schema and data, while the `mysql` client command handles the reverse — streaming that file back into a running MySQL instance.

This guide covers every practical scenario: single-database exports, multi-database dumps, structure-only backups, compressed transfers, character set handling, and safe import workflows — including edge cases that cause silent data corruption or failed restores in production environments.

Prerequisites

Before executing any command in this guide, verify the following:

• MySQL Server (5.7, 8.0, or 8.4) is installed and the `mysqld` process is running
• `mysqldump` and `mysql` binaries are in your system `PATH` (confirm with `which mysqldump`)
• You hold a MySQL account with at minimum `SELECT`, `LOCK TABLES`, `SHOW VIEW`, and `TRIGGER` privileges for export; `CREATE`, `INSERT`, `ALTER`, and `DROP` for import
• Sufficient disk space exists on the destination — a compressed dump can expand 5–10x on import
• You have shell access to the server (local terminal, SSH, or a managed VPS Hosting environment)

Exporting Databases with mysqldump

`mysqldump` is the canonical logical backup tool bundled with MySQL. It serializes database objects into a human-readable SQL script. Unlike physical backup tools such as Percona XtraBackup, `mysqldump` is storage-engine agnostic and works across MySQL versions and even MariaDB forks.

1. Export a Single Database

“`bash

mysqldump -u [username] -p [database_name] > [filename].sql

“`

Parameter breakdown:

• `-u [username]` — MySQL account used for the connection
• `-p` — Triggers an interactive password prompt (never pass the password inline as `-p[password]` on shared systems; it is visible in `ps aux` output)
• `[database_name]` — Target schema to export
• `> [filename].sql` — Redirects stdout to the output file

Example:

“`bash

mysqldump -u root -p mydatabase > mydatabase_backup.sql

“`

This produces a file containing `CREATE TABLE`, `INSERT`, and `ALTER TABLE` statements sufficient to recreate `mydatabase` from scratch.

Critical edge case: By default, `mysqldump` acquires a global read lock (`FLUSH TABLES WITH READ LOCK`) at the start of the dump. On high-traffic InnoDB servers, use `–single-transaction` instead to take a consistent snapshot without blocking writes:

“`bash

mysqldump -u root -p –single-transaction mydatabase > mydatabase_backup.sql

“`

`–single-transaction` only works reliably with InnoDB tables. If your database contains MyISAM tables, the lock is unavoidable.

2. Export Multiple Databases

“`bash

mysqldump -u [username] -p –databases db1 db2 > multiple_databases_backup.sql

“`

The `–databases` flag instructs `mysqldump` to include `CREATE DATABASE` and `USE` statements in the output, making the dump self-contained. Without this flag, those statements are omitted and the dump assumes a target database is already selected at import time.

Example:

“`bash

mysqldump -u root -p –databases db1 db2 > multiple_databases_backup.sql

“`

3. Export All Databases

“`bash

mysqldump -u root -p –all-databases > all_databases_backup.sql

“`

This exports every schema the connecting user can access, including `mysql`, `information_schema`, and `performance_schema` system databases. Avoid importing system databases across major MySQL version boundaries — the privilege table schema changed significantly between MySQL 5.7 and 8.0, and importing the old `mysql` schema into a fresh 8.0 instance will corrupt authentication.

To exclude system schemas:

“`bash

mysqldump -u root -p –all-databases

–ignore-table=mysql.user

–ignore-table=mysql.db

> all_user_databases_backup.sql

“`

4. Export Table Structure Only (No Data)

“`bash

mysqldump -u root -p –no-data mydatabase > structure_only.sql

“`

This is invaluable for schema version control, code reviews, or spinning up a blank staging environment. The output contains only `CREATE TABLE`, `CREATE VIEW`, `CREATE PROCEDURE`, and similar DDL — no `INSERT` rows.

5. Export Specific Tables

“`bash

mysqldump -u root -p mydatabase table1 table2 > tables_backup.sql

“`

Note that when exporting specific tables this way, `mysqldump` does not include `CREATE DATABASE` or `USE` statements. You must ensure the target database exists before importing.

6. Export with Stored Procedures, Triggers, and Events

By default, `mysqldump` includes triggers but omits stored procedures, functions, and scheduled events. For a complete application backup:

“`bash

mysqldump -u root -p

–routines

–triggers

–events

–single-transaction

mydatabase > full_backup.sql

“`

Forgetting `–routines` is one of the most common causes of broken application restores — the schema and data are present but the business logic is missing.

7. Compressed Export

Pipe the output directly through `gzip` to reduce file size by 60–80%:

“`bash

mysqldump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz

“`

For maximum compression on large databases (at the cost of CPU time):

“`bash

mysqldump -u root -p mydatabase | gzip -9 > mydatabase_backup.sql.gz

“`

Importing Databases with the mysql Client

The `mysql` command-line client accepts an SQL file via stdin redirection and executes every statement sequentially against the target server.

1. Import into an Existing Database

“`bash

mysql -u [username] -p [database_name] < [filename].sql

“`

Example:

“`bash

mysql -u root -p mydatabase < mydatabase_backup.sql

“`

Important: If the `.sql` file was exported with `–databases` or `–all-databases`, it already contains `CREATE DATABASE` and `USE` directives. In that case, do not specify a database name on the command line — doing so creates a conflict:

“`bash

mysql -u root -p < all_databases_backup.sql

“`

2. Import into a New Database

The target database must exist before you can import into it. MySQL will not auto-create it from a bare table dump.

Step 1 — Create the database:

“`bash

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

“`

Step 2 — Import the dump:

“`bash

mysql -u root -p newdatabase < mydatabase_backup.sql

“`

Always specify the character set and collation explicitly at database creation time. Relying on server defaults is a frequent source of encoding mismatches, especially when migrating between servers with different `character_set_server` configurations.

3. Import a Compressed Dump

“`bash

gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

Or using `zcat` (equivalent on most Linux distributions):

“`bash

zcat mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

4. Import with Progress Monitoring

For large dumps, the standard `mysql` client gives no feedback. Use `pv` (pipe viewer) to display a real-time progress bar:

“`bash

pv mydatabase_backup.sql | mysql -u root -p mydatabase

“`

Install `pv` with `apt install pv` or `yum install pv`. On a Dedicated Server handling multi-gigabyte production databases, this visibility is operationally critical.

mysqldump vs. Alternative Backup Methods

For environments running multiple production databases on a managed VPS with cPanel, `mysqldump` remains the most universally supported option due to its compatibility and simplicity.

Advanced Configuration and Edge Cases

Handling Character Sets Correctly

Character set mismatches are responsible for a disproportionate share of corrupted imports. The safest approach is to be explicit at every stage:

“`bash

mysqldump -u root -p

–default-character-set=utf8mb4

mydatabase > mydatabase_backup.sql

“`

“`bash

mysql -u root -p

–default-character-set=utf8mb4

mydatabase < mydatabase_backup.sql

“`

Note: `utf8` in MySQL is a 3-byte subset that cannot store 4-byte Unicode characters (emoji, certain CJK ideographs). Always use `utf8mb4` for new databases.

Speeding Up Large Imports

By default, MySQL performs a full commit after every `INSERT` statement in the dump. For large datasets, this is catastrophically slow. Add the following to the beginning of your import session:

“`bash

mysql -u root -p mydatabase <<EOF

SET foreign_key_checks = 0;

SET unique_checks = 0;

SET autocommit = 0;

SOURCE /path/to/mydatabase_backup.sql;

COMMIT;

SET foreign_key_checks = 1;

SET unique_checks = 1;

EOF

“`

Alternatively, export with `–extended-insert` (enabled by default) and `–disable-keys` to batch inserts and defer index rebuilding until after data load.

Automating Backups with Cron

A production-grade automated backup entry in `/etc/cron.d/mysql-backup`:

“`bash

0 2 * * * root mysqldump -u backup_user -p'StrongPass'

–single-transaction –routines –triggers –events

mydatabase | gzip > /backups/mydatabase_$(date +%F).sql.gz

“`

Use a dedicated MySQL user with the minimum required privileges rather than `root`. Create it with:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPass';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Securing Credentials with .my.cnf

Passing passwords on the command line exposes them in shell history and process lists. Store credentials in `~/.my.cnf`:

“`ini

[client]

user=root

password=YourSecurePassword

“`

Set permissions immediately:

“`bash

chmod 600 ~/.my.cnf

“`

With this in place, all `mysqldump` and `mysql` commands pick up credentials automatically without the `-u` and `-p` flags.

Remote Database Export

To dump a database from a remote MySQL server:

“`bash

mysqldump -h remote.server.com -P 3306 -u remoteuser -p remotedatabase > remote_backup.sql

“`

Ensure the remote MySQL instance allows connections from your IP address and that port 3306 is open in the firewall. For encrypted transfers, tunnel through SSH:

“`bash

ssh -L 3307:127.0.0.1:3306 user@remote.server.com -N &

mysqldump -h 127.0.0.1 -P 3307 -u remoteuser -p remotedatabase > remote_backup.sql

“`

Practical Decision Matrix

Key Technical Takeaways

• Always use `–single-transaction` for InnoDB databases to avoid blocking application writes during export
• Always specify `utf8mb4` explicitly — never rely on server default character set assumptions
• Include `–routines`, `–triggers`, and `–events` in every full application backup or you risk losing business logic
• Never import system schema tables (`mysql.*`) across major MySQL version boundaries
• Store credentials in `~/.my.cnf` with `chmod 600` — never pass passwords as inline arguments
• For databases exceeding 10 GB, evaluate `mysqlpump` or MySQL Shell's parallel dump utilities as `mysqldump` will become a bottleneck
• Verify every backup by performing a test restore into an isolated environment before relying on it for disaster recovery
• When hosting multiple client databases, isolate environments using separate VPS Control Panels to prevent cross-tenant access during restore operations
• Pair your database backup strategy with a valid SSL Certificate on any web-facing application layer that connects to MySQL to prevent credential interception in transit

FAQ

What is the difference between mysqldump and mysqlpump?

`mysqldump` is single-threaded and produces a single SQL file — reliable and universally compatible. `mysqlpump`, introduced in MySQL 5.7.8, supports parallel export of multiple databases and tables simultaneously, significantly reducing dump time on multi-core servers. However, `mysqlpump` has known issues with consistent backups of mixed-engine databases and is less suitable for cross-version migrations.

Can I import a MySQL 5.7 dump into MySQL 8.0?

Yes, with caveats. User data and application schemas import cleanly. However, never import the `mysql` system database directly — the authentication plugin changed from `mysql_native_password` to `caching_sha2_password` in 8.0, and importing old privilege tables will break authentication. Recreate users manually using `CREATE USER` and `GRANT` statements.

Why does my import fail with "ERROR 1005: Can't create table" due to foreign key constraints?

This happens when tables are imported in an order that violates foreign key dependencies. The fix is to prepend `SET foreign_key_checks = 0;` to your import session and append `SET foreign_key_checks = 1;` after completion. Alternatively, export with `–single-transaction` which preserves referential integrity in the dump file itself.

How do I export only the data without the CREATE TABLE statements?

Use the `–no-create-info` flag: `mysqldump -u root -p –no-create-info mydatabase > data_only.sql`. This is useful when you need to reload data into an existing schema without altering its structure.

What is the safest way to transfer a MySQL dump between two remote servers?

Pipe the dump directly over SSH without writing an intermediate file to disk: `mysqldump -u root -p sourcedb | ssh user@destination.server "mysql -u root -p targetdb"`. This is both faster and more secure than copying a plaintext `.sql` file, especially when operating on a Shared Web Hosting environment where disk quotas are a constraint.