How to Export MySQL Database Using Mysqldump

Use mysqldump to export a MySQL database to an SQL file. Add table names to limit the export, use --databases or --all-databases for broader scope, and use --no-data to export structure without rows. The examples explain the command arguments and password prompt; they do not verify that a dump can be restored.

Export a MySQL Database with mysqldump

mysqldump is a command-line utility for exporting MySQL databases as SQL statements. A dump can include table definitions and data, selected tables, several named databases, every database, or only a database’s structure. The commands below write the dump to a file; they do not verify that the resulting backup can be restored.

Understand mysqldump Syntax and Options

A typical command has this form: mysqldump -u USER -p [OPTIONS] DATABASE [TABLE ...] > FILE.sql. Replace the uppercase placeholders with your account, database, optional table names, and destination path. The database name comes before any table names. The > shell operator redirects standard output to the destination file, replacing that file if it already exists; choose a new path or move an existing backup first if you need to retain it.

The -u option supplies the MySQL username. Use -p by itself to have the utility prompt for the password; do not put a password after -p, where it could be exposed in shell history or process details. The --no-data option omits table rows, --databases selects named databases, and --all-databases selects all databases. See the MySQL mysqldump reference for additional options and details for your installed version.

This self-contained shell example is unexecuted in this rewrite. By shell redirection semantics, it writes sample text to a temporary file and prints that file; it does not connect to MySQL or run mysqldump.

work_dir="$(mktemp -d)"
trap 'rm -rf "$work_dir"' EXIT
printf '%s\n' 'CREATE TABLE customer (id INT);' > "$work_dir/person.sql"
cat "$work_dir/person.sql"

Export a Database

To export one database, run mysqldump -u root -p person > person_backup.sql. When prompted, enter the password for root. The file name is relative to the command’s current directory; use an absolute path, such as /backups/person_backup.sql, to choose a specific destination. Ensure the account can read the database and that the destination directory is writable.

Export Selected Tables

Put the database name first, followed by each table to include. For one table, use mysqldump -u root -p person customer > customer_table.sql. For two tables, use mysqldump -u root -p person customer employee > customer_employee.sql. These commands export the selected tables’ definitions and data by default. Add options documented for your MySQL version if you need different behavior.

Export Multiple or All Databases

Use --databases before the database names when exporting one or more named databases: mysqldump -u root -p --databases person courses > person_courses.sql. This option includes database creation statements in the dump, which helps when importing the named databases.

To export all databases available to the account, use the correctly spelled --all-databases option: mysqldump -u root -p --all-databases > all_databases.sql. The account’s permissions determine which databases and objects can be included. Store the output in a protected location because a full dump can contain sensitive data.

Export a Database Structure Without Data

Use --no-data to export table definitions without their rows: mysqldump -u root -p --no-data person > person_structure.sql. This is useful when you need a schema copy for development or documentation. It still contacts the MySQL server and requires suitable access; it is not a server-free conversion of an existing file.

Conclusion

Choose the command by scope: name a database for a full export, add table names after it to select tables, use --databases for named databases, --all-databases for the full server scope, or --no-data for structure without rows. Enter passwords at the prompt and select a destination that preserves the dump safely. A successful command creates a dump file, but you should test restoration separately before relying on it as a backup.