How to Export a MySQL Database to an SQL File with MySQLdump

mysqldump is a built-in backup and recovery utility for MySQL. It exports a database’s schema and data to a text-based file that can be used to restore the database later. The exported file typically contains SQL statements such as CREATE, INSERT, and DROP, but it can also support non-SQL formats for further processing.

In this article, you will use mysqldump to export a single database or multiple databases to an .sql file or a non-SQL format such as .csv or .txt, and then restore the database using the MySQL client.

Prerequisites

Before you begin, you need to:

• Have access to an existing MySQL database server.
• A MySQL client installed.

mysqldump Command Syntax

mysqldump is part of the MySQL client package and allows you to export MySQL databases using the following syntax:

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

• -h [host]: The MySQL database host. Optional when connecting to a local database server.
• -u [user]: A MySQL user with SELECT, LOCK, VIEW, and TRIGGER privileges on the target database.
• -P [port]: The MySQL server port. Optional for local servers.
• -p: Prompts for the MySQL user password.
• [database]: The database to export.
• > [export-file]: Writes the exported database to the specified file.

Common Export File Formats

You can export MySQL data to various formats using mysqldump, including:

• .sql: Plain SQL file with CREATE, INSERT, and SELECT statements.
• .sql.gz: Gzip-compressed SQL dump.
• .sql.zip: ZIP-compressed SQL dump.
• .sql.tar: Tar-archived SQL dump.
• .bak: Generic backup file.
• .csv: Comma-separated values (CSV) file containing table data.
• .txt: Plain text file.
• .xml: XML-formatted dump.
• .dump: Full schema and data dump, similar to .sql.

Prepare the MySQL Database to Export

Before exporting a database with mysqldump, log in to the MySQL database server, verify that the database exists, and prepare it for export if it is in use with existing applications. The following steps demonstrate how to access the MySQL database server and prepare the target database for export.

1. Log in to the MySQL database server as a user with sufficient privileges, such as root, with SELECT, LOCK, VIEW, and TRIGGER privileges on the target database.

   CONSOLE

   Copy

   $ mysql -u root -p

   Explain Code

   Powered by Vultr AgentBeta

   When prompted, enter the mysql root user password.

2. List the databases the user can access.

   SQL

   Copy

   mysql> SHOW DATABASES;

   Explain Code

   Powered by Vultr AgentBeta

   Identify the target database, for example exampledb, in the output:

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

3. Switch to the target database.

   SQL

   Copy

   mysql> USE exampledb;

   Explain Code

   Powered by Vultr AgentBeta

4. List all tables in the database.

   SQL

   Copy

   mysql> SHOW TABLES;

   Explain Code

   Powered by Vultr AgentBeta

5. Verify that the user has SELECT privileges by listing the records in any table.

   SQL

   Copy

   mysql> SELECT * FROM table_name;

   Explain Code

   Powered by Vultr AgentBeta

Export a MySQL Database to an SQL File with mysqldump

You can export one or more databases with mysqldump if your MySQL user has the required privileges to the target database. mysqldump connects to the database, dumps a copy of the database, and redirects the output to your target file. In the following steps, export a MySQL database to an SQL file with mysqldump.

1. Create a directory named database-backups to store your backup files.

   CONSOLE

   Copy

   $ mkdir database-backups

   Explain Code

   Powered by Vultr AgentBeta

2. Navigate to the directory.

   CONSOLE

   Copy

   $ cd database-backups

   Explain Code

   Powered by Vultr AgentBeta

3. Export a single database like exampledb to a .sql file using mysqldump.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

   • To prevent changes during export (useful if the database is active), add the --lock-all-tables option:

     CONSOLE

     Copy

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

     Explain Code

     Powered by Vultr AgentBeta

   Run the above command again for each database, specifying a unique filename.

4. Export all databases to a single .sql file using mysqldump.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

   The above command exports all databases the user can access on the MySQL database server.

5. List the files in your working directory and verify that the .sql file is available.

   CONSOLE

   Copy

   $ ls

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   exampledb.sql  all-databases.sql

Export a MySQL Database to a Non-SQL File

The mysqldump tool only produces SQL-formatted output, even if the file extension is .csv. To generate an actual CSV file, use a SQL query with the INTO OUTFILE clause from within the mysql shell.

Export a Single Table to CSV

1. Log in to the MySQL server.

   CONSOLE

   Copy

   $ mysql -u root -p

   Explain Code

   Powered by Vultr AgentBeta

2. Run a SELECT statement with INTO OUTFILE to export the table.

   SQL

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

   This creates a CSV file at /tmp/example_table.csv on the database server. You may need to adjust file permissions or use sudo to copy it.

3. Exit the MySQL console.

   SQL

   Copy

   mysql> EXIT;

   Explain Code

   Powered by Vultr AgentBeta

4. Copy the file to your current directory.

   CONSOLE

   Copy

   $ sudo cp /tmp/example_table.csv .

   Explain Code

   Powered by Vultr AgentBeta

Export Multiple Tables to CSV Using a Bash Script

1. Create the script file.

   CONSOLE

   Copy

   $ nano export_all_csv.sh

   Explain Code

   Powered by Vultr AgentBeta

2. Add the following content to the file:

   BASH

   Copy

   #!/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

   Explain Code

   Powered by Vultr AgentBeta

   You can modify the script to customize delimiters, escape characters, or output locations. Make sure the mysql-files directory has proper write permissions for MySQL and copy access for your user.

3. Make the script executable.

   CONSOLE

   Copy

   $ chmod +x export_all_csv.sh

   Explain Code

   Powered by Vultr AgentBeta

4. Run the script.

   CONSOLE

   Copy

   $ sudo ./export_all_csv.sh

   Explain Code

   Powered by Vultr AgentBeta

Test and Restore a MySQL Backup File

After exporting a MySQL database using mysqldump, verify that the backup file is intact before restoring. This section walks you through testing the dump using a temporary database, checking table integrity, and then restoring into the intended target database.

1. List the backup file and confirm it is not empty.

   CONSOLE

   Copy

   $ ls -lh

   Explain Code

   Powered by Vultr AgentBeta

2. Log in to the MySQL database server.

   CONSOLE

   Copy

   $ mysql -u root -p

   Explain Code

   Powered by Vultr AgentBeta

3. Create a test database such as restore_db.

   SQL

   Copy

   mysql> CREATE DATABASE restore_db;

   Explain Code

   Powered by Vultr AgentBeta

4. Exit the MySQL console.

   SQL

   Copy

   mysql> EXIT;

   Explain Code

   Powered by Vultr AgentBeta

5. Restore the backup to the restore_db database.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

6. Log in to the MySQL database server with the restore_db database.

   CONSOLE

   Copy

   $ mysql -u root -p restore_db

   Explain Code

   Powered by Vultr AgentBeta

7. List the tables in the database and verify that they match the original database.

   SQL

   Copy

   mysql> SHOW TABLES;

   Explain Code

   Powered by Vultr AgentBeta

8. Check the integrity of the table data for corruption or restoration errors.

   SQL

   Copy

   mysql> CHECK TABLE table_name;

   Explain Code

   Powered by Vultr AgentBeta

9. Drop the restore_db test database after verifying a successful restoration.

   SQL

   Copy

   mysql> DROP DATABASE restore_db;

   Explain Code

   Powered by Vultr AgentBeta

10. Exit the MySQL console.

    SQL

    Copy

    mysql> EXIT;

    Explain Code

    Powered by Vultr AgentBeta

11. Back up the current production database.

    CONSOLE

    Copy

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

    Explain Code

    Powered by Vultr AgentBeta

12. Restore the SQL file into the target database.

    CONSOLE

    Copy

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

    Explain Code

    Powered by Vultr AgentBeta

    If the database does not exist, create it first.

    CONSOLE

    Copy

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

    Explain Code

    Powered by Vultr AgentBeta

13. Log in to verify the restored database.

    CONSOLE

    Copy

    $ mysql -u root -p exampledb

    Explain Code

    Powered by Vultr AgentBeta

14. Check the integrity of the restored tables.

    SQL

    Copy

    mysql> CHECK TABLE table_name;

    Explain Code

    Powered by Vultr AgentBeta

15. Exit the MySQL console.

    SQL

    Copy

    mysql> EXIT;

    Explain Code

    Powered by Vultr AgentBeta

Conclusion

In this article, you used mysqldump to export a MySQL database, verified the resulting SQL files, and tested data restoration to ensure structural and data integrity. These steps form the foundation of a reliable backup workflow in production environments. For extended options such as partial exports, triggers, or custom character sets, consult the mysqldump reference manual.