How to Export a MySQL Database Using mysqldump?

This guide explains how to use the mysqldump command-line utility to create a complete backup, or "dump," of your MySQL database. This is a powerful and efficient method for backing up, restoring, or migrating database content.

What is mysqldump?

mysqldump is a command-line client program that creates a logical backup of a MySQL database. It generates a single text file with a .sql extension that contains a series of SQL statements. When executed, this file can recreate the original database's tables, data, and other objects.

Prerequisites

Before you begin, you will need the following:

Command-Line Access: You need access to a terminal or command prompt on the server where the database is located or from a machine that can connect to it remotely.
MySQL Client Tools: The mysqldump utility must be installed. It is part of the standard MySQL client tools package.
Database Credentials: You must know the following:
- Database username
- Database password
- Database name
- Database hostname (usually localhost if you are on the same server)

The Basic Export Command

The fundamental structure of the mysqldump command is:

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

Let's break down each part:

mysqldump: The command itself.
-u [username]: The flag to specify your MySQL username. Replace [username] with your actual username.
-p: The flag to indicate that you will provide a password. For security reasons, do not type your password directly after the -p flag. If you just use -p, the system will securely prompt you to enter the password afterward, so it won't be saved in your command history.
[database_name]: The name of the database you want to export.
>: This is a standard output redirection operator. It tells the system to "pipe" the output of the mysqldump command into the file that follows.
[filename].sql: The name of the file you want to create. This file will be saved in the directory where you are currently located in the terminal. It's a convention to use the .sql extension.

Practical Examples:

Standard Local Database Export (Recommended Method)

This is the most common and secure way to perform an export.

Open your terminal.
Type the following command, replacing db_user and my_database with your credentials.

mysqldump -u db_user -p my_database > my_database_backup.sql

Press Enter. The system will prompt you for your password:
```
Enter password:
```
Type your password and press Enter. (Note: The password will not be visible as you type).

A file named my_database_backup.sql will be created in your current directory.

Exporting a Database on a Remote Server

If your database is hosted on a different server, you need to specify its hostname or IP address using the -h flag.

mysqldump -h 192.168.1.100 -u remote_user -p remote_database > remote_db_backup.sql

Replace 192.168.1.100 with your server's IP address or hostname. You will be prompted for the password for remote_user

Exporting Only Specific Tables

If you don't need the entire database, you can specify one or more table names after the database name.

mysqldump -u db_user -p my_database wp_posts wp_users > specific_tables_backup.sql

This command will only export the wp_posts and wp_users tables from the my_database database.

How to Import the .sql File

Check out this article:
Import SQL file using the command line in MySQL?

Common Troubleshooting

command not found: mysqldump: This means the mysqldump utility is not installed or your system's PATH variable does not include the directory where it's located.
ERROR 1045 (28000): Access denied for user...: This indicates your username, password, or hostname is incorrect. Double-check your credentials. The user may also lack the necessary privileges to access the database.
Permission Denied (when creating the file): If you get an error trying to create the .sql file, it means you don't have write permissions in the current directory. Navigate to a directory where you do have permission (like your home directory) and try again.