How to Export and Restore MySQL Databases Using mysqldump

The mysqldump utility is a native MySQL tool used for backing up and restoring databases. It extracts both structure and data from a database into a text-based file, which can later be used to re-import the data. Typically, this output contains SQL commands like CREATE, INSERT, and DROP, though mysqldump can also generate files in alternative, non-SQL formats for extended use cases.

This tutorial guides you through exporting one or multiple MySQL databases into formats such as .sql, .csv, or .txt, and restoring them using the MySQL command-line interface.

Prerequisites

Before you proceed, ensure the following requirements are met:

• You have access to a functioning MySQL database server.
• A MySQL client is already installed on your system.

mysqldump Syntax Overview

The mysqldump utility comes bundled with the MySQL client tools and supports the following command structure:

mysqldump -h [host] -u [user] -P [port] -p [database] > [export-file]

Explanation of parameters:

• -h [host]: Optional. Defines the hostname of the MySQL server. Default is localhost.
• -u [user]: Specifies the MySQL user with appropriate privileges (SELECT, LOCK, VIEW, TRIGGER).
• -P [port]: Optional. Indicates the MySQL server’s port number.
• -p: Prompts for the password of the given user.
• [database]: The name of the database to export.
• > [export-file]: Path to the file where the exported data will be saved.

Supported Export File Types

Using mysqldump, you can generate backup files in various formats, including:

• .sql: Standard SQL script with database structure and data.
• .sql.gz: Gzipped SQL output.
• .sql.zip: Zipped SQL export.
• .sql.tar: Tar-archived SQL file.
• .bak: General-purpose backup format.
• .csv: Comma-separated text format for tabular data.
• .txt: Plaintext data dump.
• .xml: XML-structured dump.
• .dump: Full dump equivalent to a typical SQL file.

Preparing a MySQL Database for Export

Prior to exporting, connect to your MySQL server and confirm the existence of the database. If the database is currently in use, it’s a good idea to lock it or take it offline if possible. Here are the steps:

1. Access the MySQL server using a privileged user account:

$ mysql -u root -p

After that, enter the password when prompted.

2. View all available databases:

mysql> SHOW DATABASES;

Sample output may look like:

+--------------------+
| Database           |
+--------------------+
| exampledb          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

3. Select the target database:

mysql> USE exampledb;

4. List the tables it contains:

mysql> SHOW TABLES;

5. Confirm SELECT access by querying a table:

mysql> SELECT * FROM table_name;

Exporting a MySQL Database Using mysqldump

To back up one or more databases using mysqldump, follow the steps below. Ensure that your MySQL user has adequate permissions for the databases you wish to export.

1. Create a directory for your backup files:

$ mkdir database-backups

2. Move into that directory:

$ cd database-backups

3. Export a single database (e.g., exampledb):

$ mysqldump -u root -p exampledb > exampledb.sql

To lock all tables during export (to ensure consistency):

$ mysqldump -u root -p --lock-all-tables exampledb > exampledb.sql

4. Repeat for additional databases with unique filenames.
5. Export all available databases into a single file:

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

This command includes every database that the MySQL user has access to.

6. List the backup files in your current directory:

$ ls

Expected output:

exampledb.sql  all-databases.sql

Exporting a MySQL Database as CSV or Plain Text Files

Although mysqldump is designed to produce SQL-style exports only, even when using a .csv extension, true CSV output must be generated with SQL queries using INTO OUTFILE directly in the MySQL shell.

Exporting One Table as a CSV File

Start by connecting to the MySQL server:

$ mysql -u root -p

Run a SQL command to extract the table’s data as a CSV file:

mysql> SELECT * FROM example_table
       INTO OUTFILE '/tmp/example_table.csv'
       FIELDS TERMINATED BY ',' ENCLOSED BY '"'
       LINES TERMINATED BY '\n';

This command saves the table’s content into /tmp/example_table.csv on the server. If necessary, adjust the file’s permissions or use sudo to access it.

Close the MySQL session:

mysql> EXIT;

Copy the file to your current working directory:

$ sudo cp /tmp/example_table.csv .

Exporting Multiple Tables to CSV with a Bash Script

Begin by creating a new script file:

$ nano export_all_csv.sh

Insert the following script content:

#!/bin/bash

DB_NAME="exampledb"
MYSQL_USER="root"
MYSQL_PASS="yourpassword"
OUTPUT_DIR="/var/lib/mysql-files"

tables=$(mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW TABLES IN $DB_NAME;" | tail -n +2)

for table in $tables; do
    mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "
    SELECT * FROM $DB_NAME.$table
    INTO OUTFILE '$OUTPUT_DIR/${table}.csv'
    FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
    LINES TERMINATED BY '\n';
    "
    echo "Exported: $table -> $OUTPUT_DIR/${table}.csv"
done

This script can be adjusted for different delimiters, custom characters, or alternative save paths. Ensure that mysql-files is writable by MySQL and readable by your user account.

Grant the script executable permissions:

$ chmod +x export_all_csv.sh

Execute the script:

$ sudo ./export_all_csv.sh

Testing and Restoring a MySQL Backup

After exporting your database with mysqldump, it’s crucial to validate the backup before applying it to production. Below are steps to test and restore the dump file safely:

First, ensure the backup file exists and is not empty:

$ ls -lh

Log into the MySQL server:

$ mysql -u root -p

Create a temporary test database for restoring the dump:

mysql> CREATE DATABASE restore_db;

Exit the MySQL console:

mysql> EXIT;

Import the SQL file into the new test database:

$ mysql -u root -p restore_db < exampledb.sql

Log in to verify the restored database:

$ mysql -u root -p restore_db

View the table list:

mysql> SHOW TABLES;

Check the health of a specific table:

mysql> CHECK TABLE table_name;

Once confirmed, delete the test database:

mysql> DROP DATABASE restore_db;

Exit MySQL:

mysql> EXIT;

Restoring a Backup to Production

Before restoring the dump file to a production database, create a fresh backup:

$ mysqldump -u root -p exampledb > backup-before-restore.sql

Apply the new SQL file to the production database:

$ mysql -u root -p exampledb < exampledb.sql

If the database doesn’t exist yet, create it first:

$ mysql -u root -p -e "CREATE DATABASE exampledb;"

Log in to inspect the restored contents:

$ mysql -u root -p exampledb

Verify table health once again:

mysql> CHECK TABLE table_name;

Exit the session:

mysql> EXIT;

Conclusion

In this guide, you learned to use mysqldump for MySQL backups, validated export integrity, and performed safe restores using test environments. These procedures are essential for maintaining a secure and reliable backup and recovery process. For more advanced features such as partial dumps, trigger exports, or specific character encoding, refer to the official mysqldump documentation.