Introduction

For MySQL and compatible servers, command-line export and import usually means mysqldump to create a logical backup and mysql to restore it. The useful part is not the basic syntax but choosing the right options for schema-only dumps, data-only dumps, consistent backups, and safer restores.

Export With mysqldump

The basic export pattern is:

mysqldump -u app_user -p app_db > backup.sql

This prompts for the password and writes the dump to backup.sql.

A few very common variations are:

mysqldump -u app_user -p --no-data app_db > schema.sql
mysqldump -u app_user -p --no-create-info app_db > data.sql
mysqldump -u app_user -p --routines --triggers app_db > full.sql

These options mean:

• '--no-data exports only tables, indexes, and other schema definitions'
• '--no-create-info exports only row data'
• '--routines includes stored procedures and functions'
• '--triggers includes triggers'

Use The Right Options For Consistent Backups

For InnoDB-heavy databases, --single-transaction is a common and useful option:

mysqldump -u app_user -p --single-transaction --routines --triggers app_db > backup.sql

It creates a consistent snapshot without locking every table the way older dump patterns often did.

If you are dumping multiple databases:

mysqldump -u app_user -p --databases app_db reporting_db > multi.sql

Or everything:

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

Those commands are more powerful, so use them carefully and make sure the target restore environment is appropriate.

Import With The mysql Client

To restore a dump into an existing database:

mysql -u app_user -p app_db < backup.sql

If the database does not exist yet, create it first:

mysql -u app_user -p -e "CREATE DATABASE app_db"
mysql -u app_user -p app_db < backup.sql

This redirection pattern is easy to automate in deployment or disaster-recovery workflows.

Export And Import Specific Tables

You do not always need a full database dump. Specific tables can be targeted:

mysqldump -u app_user -p app_db users orders > selected_tables.sql

That file can later be restored the same way:

mysql -u app_user -p app_db < selected_tables.sql

This is useful for moving a subset of the schema, reproducing a bug, or restoring a small part of an environment.

Security And Operational Details

Use -p by itself so the client prompts for the password. Avoid putting the password directly on the command line because it may end up in shell history or process listings.

If you run this often, store non-sensitive defaults in an option file such as ~/.my.cnf, or rely on a secure secret-management workflow instead of hard-coding credentials in scripts.

It is also worth compressing large dump files:

mysqldump -u app_user -p app_db | gzip > backup.sql.gz
gunzip < backup.sql.gz | mysql -u app_user -p app_db

That is often a practical improvement for large logical backups.

Common Pitfalls

One common mistake is forgetting that mysqldump creates a logical SQL export, not a physical snapshot. Very large databases may need a different backup strategy.

Another issue is restoring into the wrong database name because the command line was copied from another environment.

A third problem is assuming schema.sql includes data even when --no-data was used, or assuming a dump contains routines and triggers when those options were omitted.

Finally, users often paste passwords directly after -p on shared systems. It works, but it is weaker operational hygiene than using the interactive prompt.

Summary

• Use mysqldump to export logical SQL backups and mysql to import them.
• '--no-data gives schema only, and --no-create-info gives data only.'
• '--single-transaction is a strong default for consistent InnoDB dumps.'
• Restore with mysql database_name < file.sql after creating the target database if needed.
• Avoid exposing passwords on the command line and compress large dumps when practical.