| {{blogData.modified_date.trim().split(" ")[0] | formatDate}}

By: Emma

What Is mysqldump in MySQL

mysqldump is a command-line utility that exports MySQL databases as plain-text .sql files. These files contain SQL statements — such as CREATE TABLE and INSERT INTO — that recreate your database structure and data on any compatible MySQL server.

Why Use mysqldump

Portability: The .sql output is plain text, so it moves easily across different operating systems and server environments.
Version control: Because the output is text-based, you can track schema changes using Git or any version control system.
Flexibility: You can export an entire server, a single database, or just specific tables, depending on what you need.

Basic mysqldump Command to Export a Database

Before diving into more advanced options, it helps to understand the core syntax first. The command follows a simple structure: you specify your MySQL user, name the database you want to dump, and tell the terminal where to save the output file.

mysqldump Export Database Syntax

The basic command looks like this:

mysqldump -u root -p mydb > backup.sql

Breakdown of each component:

mysqldump — The utility itself.
-u root — Your MySQL username. Replace root with your actual username if different.
-p — Prompts for your password after you press Enter. Leave the password out of the command itself to avoid exposing it in your terminal history.
mydb — The name of the database you want to export. Replace this with your actual database name.
> — The shell redirection operator. It writes the command output to a file instead of printing it to the screen.
backup.sql — The name and path of the output file. You can rename this to anything you like.

How to Export a MySQL Database with mysqldump (Step-by-Step)

Follow these steps to export a MySQL database from the command line. This process works on Windows, macOS, and Linux, though the way you open the terminal varies by system.

Step 1: Open Your Terminal or Command Prompt

Windows: Press Win + R, type cmd, and press Enter.
macOS: Press Cmd + Space, type Terminal, and press Enter.
Linux: Press Ctrl + Alt + T, or search for Terminal in your applications menu.

Step 2: Navigate to the MySQL Bin Directory

If mysqldump isn’t recognized as a command, your system may not have MySQL added to its PATH. In that case, navigate to the MySQL installation folder first.

On Windows, the default path is usually:

cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

Tip: Run mysqldump --version from your terminal. If it returns a version number, MySQL is already in your PATH and you can skip this step.

Step 3: Run the Export Command

Run the following command, replacing root and my_database with your actual username and database name:

mysqldump -u root -p my_database > backup.sql

You can also modify the command depending on what you need to export:

Goal	Command
Export structure only	`mysqldump -u root -p my_database --no-data > backup.sql`
Export data only	`mysqldump -u root -p my_database --no-create-info > backup.sql`
Export multiple databases	`mysqldump -u root -p --databases db1 db2 > backup.sql`
Export all databases	`mysqldump -u root -p --all-databases > full_backup.sql`
Export specific tables	`mysqldump -u root -p my_database table1 table2 > backup.sql`

Step 4: Enter Your Password

The terminal will prompt you to enter your MySQL password. Type it and press Enter.

Note: No characters will appear as you type — this is normal. It’s a standard security feature in command-line tools.

Step 5: Verify the Export

The export is complete when the cursor returns to a blank line. To confirm the file was created successfully:

Locate the .sql file in your current folder.
Check that the file size is greater than 0 KB.
Optionally, open it in a text editor such as Notepad++ or VS Code to confirm the SQL statements are present.

How to Import a Database from a mysqldump File

Once you’ve exported a database with mysqldump, you’ll eventually need to restore it. Note that while mysqldump creates the export file, you use the mysql client — not mysqldump — to import it.

The Import Syntax

To import a .sql file into a database, run:

mysql -u root -p my_database < backup.sql

Replace root, my_database, and backup.sql with your actual username, target database name, and file path.

The key difference from the export command is the direction of the operator. The < sign feeds the contents of the .sql file into the mysql client, whereas > writes output to a file.

Create the target database first

Unless you exported with --all-databases, the .sql file typically doesn’t include a CREATE DATABASE statement. You’ll need to create the database manually before importing:

CREATE DATABASE my_new_database;

Match the character set

If your export used a specific character set (such as UTF-8), make sure the target database is configured to match. A mismatch can result in garbled or unreadable text.

Check for errors if the import fails

Common causes include insufficient user permissions (Access Denied) or a file size that exceeds the server’s max_allowed_packet limit. Increase this value in your MySQL configuration file if needed.

Simplify and Secure MySQL Database Export with i2Backup

mysqldump is a reliable tool for one-off exports and manual backups. But for teams that need scheduled, automated, and centrally managed database protection, running commands manually doesn’t scale. That’s where a dedicated backup solution like i2Backup comes in.

Key Features of i2Backup for MySQL Protection

Scheduled and real-time backup: Instead of running mysqldump manually, i2Backup lets you configure automated backup schedules (hourly, daily, weekly, and more). It also captures redo logs and archive logs continuously for near-zero RPO.
Multiple backup destinations: Save backups to local storage, NAS, or cloud simultaneously to eliminate single points of failure.
Secure transmission: All data is transferred using AES or SM4 encryption, keeping your database exports protected in transit.
Centralized management: A web-based console lets you monitor backup status, schedule tasks, and manage multiple database instances from one place: no command line required.
Broad database compatibility: In addition to MySQL, i2Backup supports Oracle, SQL Server, IBM DB2, MongoDB, and more, making it a unified solution for mixed database environments.

Whether you’re protecting a single MySQL instance or managing backups across multiple servers, i2Backup gives your team consistent, automated coverage without the manual overhead of running mysqldump exports.

FREE Trial for 60-Day

FAQ

Q1: How do I export an entire MySQL database?

Run the following command, replacing root and my_database with your actual username and database name:

mysqldump -u root -p my_database > backup.sql

To export every database on the server at once, use the --all-databases flag instead:

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

Q2: How do I use mysqldump to back up a database?

Open your terminal, navigate to the MySQL bin directory if needed, and run the mysqldump command with your username, database name, and a > operator pointing to your output file. Make sure your MySQL user has the necessary SELECT and LOCK TABLES privileges on the target database.

Q3: How do I export a specific table from a MySQL database?

Add the table name after the database name in your command:

mysqldump -u root -p my_database my_table > table_backup.sql

To export multiple tables, list them one after another separated by spaces.

Q4: How do I export only the database structure without data?

Use the --no-data flag to export table definitions without any rows:

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

This is useful when setting up a new environment that needs the same schema but not the existing data.

Q5:Can I use mysqldump on Windows?

Yes. Open Command Prompt and run the same mysqldump commands as on Linux or macOS. If the command isn’t recognized, navigate to your MySQL bin directory first (e.g., cd "C:\Program Files\MySQL\MySQL Server 8.0\bin") or add MySQL to your system PATH…

Conclusion

mysqldump is a straightforward and flexible tool for exporting MySQL databases from the command line. Whether you’re backing up a single table, migrating an entire server, or exporting just the schema, the commands and options covered in this guide should handle most common scenarios.

That said, a backup is only useful if it can be restored successfully. Always verify your .sql files after exporting. For production environments where consistency and automation matter, pairing mysqldump with a dedicated solution like i2Backup helps reduce manual overhead and the risk of human error.

{{ blogData.title}}

What Is mysqldump in MySQL