MySQL Data Export and Import Guide

April 30, 2026 • Category: devops • Tags: mysql, database, backup, restore, docker, devops

A comprehensive practical guide for exporting, importing, backing up, restoring, and migrating MySQL data using mysqldump, mysql, Docker Compose, compressed files, and safer production-oriented workflows.

This guide focuses on MySQL development and migration workflows. Most examples also work with MariaDB, but some options may differ depending on version.

Overview
Export vs Import Concepts
Common Tools
Basic Export with mysqldump
Basic Import with mysql
Export and Import with Docker Compose
Export Specific Tables
Export Schema Only
Export Data Only
Export All Databases
Compressed Export and Import
Recommended Development Backup Command
Recommended Safer Production Export
Importing into a Fresh Database
Importing Large SQL Files
Character Encoding
Users, Permissions, Routines, Triggers, and Events
Verification After Import
Troubleshooting
Cheat Sheet

Overview

MySQL data export/import is commonly used for:

Backing up a development database
Moving data from local to staging
Moving data from staging to local
Migrating a database to another server
Creating test fixtures
Restoring a database after accidental data loss
Copying only schema or only data
Copying selected tables

The most common command-line tools are:

mysqldump for exporting data
mysql for importing SQL files
gzip for compressing large dump files
docker compose exec when MySQL runs inside Docker

Export vs Import Concepts

flowchart LR
    A[(MySQL Database)] -->|Export with mysqldump| B[SQL Dump File]
    B -->|Import with mysql| C[(Target MySQL Database)]

    B --> D[Backup Storage]
    D -->|Restore later| C

An exported SQL dump usually contains SQL statements such as:

CREATE TABLE users (...);
INSERT INTO users (...) VALUES (...);

When imported, MySQL executes those SQL statements to recreate the database objects and data.

Common Tools

Tool	Purpose
`mysqldump`	Exports MySQL databases, tables, schemas, and data
`mysql`	Connects to MySQL and imports SQL files
`gzip`	Compresses SQL dump files
`gunzip`	Decompresses compressed dump files
`docker compose exec`	Runs MySQL commands inside a Docker Compose service

Check if tools are available:

mysql --version
mysqldump --version
docker compose version

Basic Export with `mysqldump`

Export One Database

mysqldump -u root -p my_database > my_database.sql

Explanation:

Part	Meaning
`mysqldump`	Export tool
`-u root`	MySQL username
`-p`	Ask for password
`my_database`	Database to export
`>`	Redirect output to file
`my_database.sql`	Output SQL dump file

sequenceDiagram
    participant User
    participant mysqldump
    participant MySQL
    participant File

    User->>mysqldump: mysqldump -u root -p my_database
    mysqldump->>MySQL: Connect and read schema/data
    MySQL-->>mysqldump: Tables, rows, objects
    mysqldump-->>File: Write my_database.sql

Export with Host and Port

Use this when MySQL is not running on the default local socket.

mysqldump -h 127.0.0.1 -P 3306 -u root -p my_database > my_database.sql

Option	Meaning
`-h 127.0.0.1`	MySQL host
`-P 3306`	MySQL port
`-u root`	Username
`-p`	Prompt for password

Basic Import with `mysql`

Import into an Existing Database

mysql -u root -p my_database < my_database.sql

The database must already exist unless the SQL file contains CREATE DATABASE and USE statements.

sequenceDiagram
    participant User
    participant File
    participant mysql
    participant MySQL

    User->>mysql: mysql -u root -p my_database < my_database.sql
    File-->>mysql: SQL statements
    mysql->>MySQL: Execute SQL
    MySQL-->>mysql: Tables and data restored

Export and Import with Docker Compose

Assume this docker-compose.yml service:

services:
  mysql:
    image: mysql:8.4
    environment:
      MYSQL_ROOT_PASSWORD: root_password
      MYSQL_DATABASE: my_database
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql

volumes:
  mysql_data:

Export from Docker Compose MySQL

docker compose exec mysql mysqldump -u root -p my_database > my_database.sql

You will be prompted for the password.

Export with Inline Password

docker compose exec mysql mysqldump -u root -p'root_password' my_database > my_database.sql

Be careful: putting passwords directly in commands may expose them in shell history.

Import into Docker Compose MySQL

docker compose exec -T mysql mysql -u root -p my_database < my_database.sql

The -T option disables pseudo-TTY allocation and is important when using input redirection.

Import with Inline Password

docker compose exec -T mysql mysql -u root -p'root_password' my_database < my_database.sql

flowchart TD
    A[Host Machine] -->|docker compose exec| B[MySQL Container]
    B --> C[(MySQL Data Volume)]

    A -->|Export| D[my_database.sql]
    D -->|Import through stdin| B

Export Specific Tables

Export One Table

mysqldump -u root -p my_database users > users.sql

Export Multiple Tables

mysqldump -u root -p my_database users orders products > selected_tables.sql

This is useful when:

You only need sample data
You want to migrate selected domain tables
You want to debug a specific feature locally

flowchart LR
    DB[(my_database)] --> T1[users]
    DB --> T2[orders]
    DB --> T3[products]
    DB --> T4[logs]

    T1 --> Dump[selected_tables.sql]
    T2 --> Dump
    T3 --> Dump
    T4 -.not exported.-> X[Excluded]

Export Schema Only

Export only table definitions, indexes, views, triggers, and related structure.

mysqldump -u root -p --no-data my_database > schema.sql

Use this when:

You want to recreate database structure
You do not want production data
You want to compare schema changes
You want to initialize a new environment

Export Data Only

Export only rows, without CREATE TABLE statements.

mysqldump -u root -p --no-create-info my_database > data.sql

Use this when:

The target database schema already exists
You want to refresh data only
Schema is managed separately by migrations

Export All Databases

mysqldump -u root -p --all-databases > all_databases.sql

Import:

mysql -u root -p < all_databases.sql

This dump usually includes CREATE DATABASE and USE statements.

flowchart LR
    A[(MySQL Server)] --> B[db_app]
    A --> C[db_admin]
    A --> D[db_test]

    B --> E[all_databases.sql]
    C --> E
    D --> E

    E --> F[(New MySQL Server)]

Compressed Export and Import

Large SQL files can become very big. Compress them with gzip.

Export Compressed Dump

mysqldump -u root -p my_database | gzip > my_database.sql.gz

Import Compressed Dump

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

Docker Compose Compressed Export

docker compose exec mysql mysqldump -u root -p my_database | gzip > my_database.sql.gz

Docker Compose Compressed Import

gunzip < my_database.sql.gz | docker compose exec -T mysql mysql -u root -p my_database

flowchart LR
    A[(MySQL)] -->|mysqldump| B[SQL Stream]
    B -->|gzip| C[my_database.sql.gz]
    C -->|gunzip| D[SQL Stream]
    D -->|mysql| E[(Restored MySQL)]

Recommended Development Backup Command

For most local development environments:

mysqldump \
  -h 127.0.0.1 \
  -P 3306 \
  -u root \
  -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  my_database > my_database_backup.sql

Why These Options?

Option	Why it is useful
`--single-transaction`	Creates a consistent dump for InnoDB tables without locking them heavily
`--routines`	Includes stored procedures and functions
`--triggers`	Includes triggers
`--events`	Includes scheduled events
`--default-character-set=utf8mb4`	Helps preserve Unicode text such as Korean, emoji, and symbols

Recommended Safer Production Export

For production, prefer a careful process:

mysqldump \
  -h production-db.example.com \
  -u backup_user \
  -p \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  my_database | gzip > my_database_$(date +%Y%m%d_%H%M%S).sql.gz

Additional Production Recommendations

Use a dedicated read-only backup user
Avoid exporting during peak traffic
Test restore regularly
Store backups securely
Encrypt backups if they contain sensitive data
Avoid placing passwords directly in shell commands
Prefer automated backup systems for critical production databases

flowchart TD
    A[Production MySQL] --> B[Read-only Backup User]
    B --> C[mysqldump]
    C --> D[gzip Compression]
    D --> E[Timestamped Backup File]
    E --> F[Secure Storage]
    F --> G[Restore Test Environment]

Importing into a Fresh Database

If the target database does not exist, create it first.

mysql -u root -p

Then inside MySQL:

CREATE DATABASE my_database
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

EXIT;

Then import:

mysql -u root -p my_database < my_database.sql

Docker Compose Version

docker compose exec mysql mysql -u root -p

Inside MySQL:

CREATE DATABASE my_database
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

EXIT;

Then:

docker compose exec -T mysql mysql -u root -p my_database < my_database.sql

Importing Large SQL Files

For large files, avoid web tools like phpMyAdmin if possible. Use terminal import.

mysql -u root -p my_database < large_dump.sql

For compressed files:

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

For Docker Compose:

gunzip < large_dump.sql.gz | docker compose exec -T mysql mysql -u root -p my_database

Useful MySQL Settings for Large Imports

Sometimes large imports fail because of packet size limits.

Check current value:

SHOW VARIABLES LIKE 'max_allowed_packet';

Start MySQL with a larger value if needed:

mysqld --max_allowed_packet=512M

Or configure MySQL server:

[mysqld]
max_allowed_packet=512M

Character Encoding

Use utf8mb4 to preserve full Unicode, including Korean and emoji.

Export with UTF-8

mysqldump \
  -u root \
  -p \
  --default-character-set=utf8mb4 \
  my_database > my_database.sql

Import with UTF-8

mysql \
  -u root \
  -p \
  --default-character-set=utf8mb4 \
  my_database < my_database.sql

Recommended Database Creation

CREATE DATABASE my_database
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

For older MySQL versions, you may need:

CREATE DATABASE my_database
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Users, Permissions, Routines, Triggers, and Events

Include Routines, Triggers, and Events

mysqldump \
  -u root \
  -p \
  --routines \
  --triggers \
  --events \
  my_database > my_database_full_objects.sql

Export User Accounts and Grants

mysqldump my_database does not usually migrate MySQL users and permissions.

To check grants:

SHOW GRANTS FOR 'app_user'@'%';

You can recreate users manually:

CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON my_database.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

For migration between servers, be careful with:

User host values such as 'user'@'localhost' vs 'user'@'%'
Authentication plugins
Password policies
Privilege differences between MySQL versions

Verification After Import

After import, verify the result.

mysql -u root -p my_database

Inside MySQL:

SHOW TABLES;

Check row count:

SELECT COUNT(*) FROM users;

Check important records:

SELECT * FROM users LIMIT 10;

Check table status:

SHOW TABLE STATUS;

Check routines:

SHOW PROCEDURE STATUS WHERE Db = 'my_database';
SHOW FUNCTION STATUS WHERE Db = 'my_database';

Check triggers:

SHOW TRIGGERS;

flowchart TD
    A[Import SQL Dump] --> B[Check Tables]
    B --> C[Check Row Counts]
    C --> D[Check Important Records]
    D --> E[Check Routines and Triggers]
    E --> F[Run Application Smoke Test]
    F --> G{Everything OK?}
    G -->|Yes| H[Import Complete]
    G -->|No| I[Investigate Logs and Errors]

Troubleshooting

1. `Access denied for user`

Possible causes:

Wrong username
Wrong password
User does not have permission
Host mismatch, such as 'root'@'localhost' vs 'root'@'%'

Try:

mysql -u root -p

Check grants:

SHOW GRANTS FOR CURRENT_USER();

2. `Unknown database 'my_database'`

The database does not exist.

Create it first:

CREATE DATABASE my_database;

Then import again:

mysql -u root -p my_database < my_database.sql

3. Docker Import Hangs or Fails

Use -T:

docker compose exec -T mysql mysql -u root -p my_database < my_database.sql

Without -T, input redirection can behave incorrectly.

4. Character Text Looks Broken

Use utf8mb4 during export and import:

mysqldump -u root -p --default-character-set=utf8mb4 my_database > my_database.sql
mysql -u root -p --default-character-set=utf8mb4 my_database < my_database.sql

Also check database/table character set:

SHOW CREATE DATABASE my_database;
SHOW FULL COLUMNS FROM users;

5. Duplicate Key Errors During Import

Example:

ERROR 1062 (23000): Duplicate entry ...

Possible causes:

Target database already contains data
Importing data-only dump into non-empty tables
Running the same import twice

Options:

Import into a fresh database
Drop and recreate tables
Use TRUNCATE TABLE carefully
Review whether duplicate data should be preserved

6. Foreign Key Constraint Errors

Example:

ERROR 1452 (23000): Cannot add or update a child row

Possible causes:

Tables imported in wrong order
Missing parent rows
Existing inconsistent data

For controlled restore only, you can temporarily disable foreign key checks:

SET FOREIGN_KEY_CHECKS = 0;
SOURCE my_database.sql;
SET FOREIGN_KEY_CHECKS = 1;

Use carefully. Do not use this to hide real data integrity problems.

7. `max_allowed_packet` Error

Example:

ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet'

Increase server setting:

[mysqld]
max_allowed_packet=512M

Then restart MySQL.

8. Definer Errors

Example:

ERROR 1227 (42000): Access denied; you need SUPER privilege

This may happen with views, triggers, routines, or events that include a DEFINER.

Possible solutions:

Dump and restore with compatible users
Recreate the missing definer user
Edit the dump carefully to replace DEFINER
Use a migration process that normalizes definers

Suggested Backup File Naming

Use timestamped names:

my_database_20260430_153000.sql
my_database_20260430_153000.sql.gz

Shell example:

mysqldump -u root -p my_database > my_database_$(date +%Y%m%d_%H%M%S).sql

Compressed:

mysqldump -u root -p my_database | gzip > my_database_$(date +%Y%m%d_%H%M%S).sql.gz

Example End-to-End Migration

Move a local database from one machine to another.

flowchart LR
    A[Source Machine] --> B[Export SQL Dump]
    B --> C[Compress Dump]
    C --> D[Copy to Target Machine]
    D --> E[Create Target Database]
    E --> F[Import Dump]
    F --> G[Verify Data]
    G --> H[Run Application]

On Source Machine

mysqldump \
  -u root \
  -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  my_database | gzip > my_database.sql.gz

Copy to Target Machine

scp my_database.sql.gz user@target-machine:/path/to/backups/

On Target Machine

Create database:

mysql -u root -p

CREATE DATABASE my_database
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
EXIT;

Import:

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

Verify:

mysql -u root -p my_database

SHOW TABLES;
SELECT COUNT(*) FROM users;

Security Notes

Do not casually store production dumps on your laptop or shared drives if they contain:

User personal information
Password hashes
Payment-related information
Private messages
Access tokens
Business-sensitive data

Recommended practices:

Encrypt backup files
Restrict file permissions
Use dedicated backup users
Rotate old backups
Test restore in a safe environment
Avoid committing .sql files into Git

Set restrictive permissions:

chmod 600 my_database.sql
chmod 600 my_database.sql.gz

Cheat Sheet

Local MySQL

# Export database
mysqldump -u root -p my_database > my_database.sql

# Import database
mysql -u root -p my_database < my_database.sql

# Export all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Import all databases
mysql -u root -p < all_databases.sql

# Export schema only
mysqldump -u root -p --no-data my_database > schema.sql

# Export data only
mysqldump -u root -p --no-create-info my_database > data.sql

# Export compressed
mysqldump -u root -p my_database | gzip > my_database.sql.gz

# Import compressed
gunzip < my_database.sql.gz | mysql -u root -p my_database

Docker Compose MySQL

# Export from container
docker compose exec mysql mysqldump -u root -p my_database > my_database.sql

# Import into container
docker compose exec -T mysql mysql -u root -p my_database < my_database.sql

# Export compressed from container
docker compose exec mysql mysqldump -u root -p my_database | gzip > my_database.sql.gz

# Import compressed into container
gunzip < my_database.sql.gz | docker compose exec -T mysql mysql -u root -p my_database

Recommended Development Export

mysqldump \
  -u root \
  -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  my_database > my_database_backup.sql

Final Recommendation

For everyday development, use:

mysqldump \
  -u root \
  -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  my_database > my_database_backup.sql

Then restore with:

mysql -u root -p my_database < my_database_backup.sql

For Docker Compose, use:

docker compose exec mysql mysqldump -u root -p my_database > my_database.sql
docker compose exec -T mysql mysql -u root -p my_database < my_database.sql

Table of Contents

Overview

Export vs Import Concepts

Common Tools

Basic Export with mysqldump

Export One Database

Export with Host and Port

Basic Import with mysql

Import into an Existing Database

Export and Import with Docker Compose

Export from Docker Compose MySQL

Export with Inline Password

Import into Docker Compose MySQL

Import with Inline Password

Export Specific Tables

Export One Table

Export Multiple Tables

Export Schema Only

Export Data Only

Export All Databases

Compressed Export and Import

Export Compressed Dump

Import Compressed Dump

Docker Compose Compressed Export

Docker Compose Compressed Import

Recommended Development Backup Command

Why These Options?

Recommended Safer Production Export

Additional Production Recommendations

Importing into a Fresh Database

Docker Compose Version

Importing Large SQL Files

Useful MySQL Settings for Large Imports

Character Encoding

Export with UTF-8

Import with UTF-8

Recommended Database Creation

Users, Permissions, Routines, Triggers, and Events

Include Routines, Triggers, and Events

Export User Accounts and Grants

Verification After Import

Troubleshooting

1. Access denied for user

2. Unknown database 'my_database'

3. Docker Import Hangs or Fails

4. Character Text Looks Broken

5. Duplicate Key Errors During Import

6. Foreign Key Constraint Errors

7. max_allowed_packet Error

8. Definer Errors

Suggested Backup File Naming

Example End-to-End Migration

On Source Machine

Copy to Target Machine

On Target Machine

Security Notes

Cheat Sheet

Local MySQL

Docker Compose MySQL

Recommended Development Export

Final Recommendation

Basic Export with `mysqldump`

Basic Import with `mysql`

1. `Access denied for user`

2. `Unknown database 'my_database'`

7. `max_allowed_packet` Error