How to Backup a MySQL Database Using mysqldump

Performing a logical backup of a MySQL database is an essential task for ensuring data safety and enabling easy migrations. This article provides a straightforward, step-by-step guide on how to use mysqldump, the default utility provided by MySQL for creating logical backups. You will learn the basic commands to back up a single database, multiple databases, or all databases on a server, as well as how to restore those backups when needed.

What is a Logical Backup?

A logical backup contains the database structure (DDL) and data (DML) represented as a set of SQL statements (such as CREATE TABLE and INSERT). The mysqldump tool is the standard command-line utility used to generate these SQL dump files. Because the output is plain text, logical backups are highly portable across different MySQL versions and CPU architectures.

Backing Up a Single MySQL Database

To back up a single database, open your terminal or command prompt and run the following command:

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

• -u [username]: Specifies the MySQL username (e.g., root).
• -p: Prompts you to enter the password for the specified user.
• [database_name]: The name of the database you want to back up.
• >: The redirection operator that sends the output to a file.
• [backup_file.sql]: The path and name of the resulting backup file.

Example:

mysqldump -u root -p my_company_db > my_company_db_backup.sql

Backing Up Specific Tables

If you only need to back up specific tables from a database, list them after the database name:

mysqldump -u [username] -p [database_name] [table1] [table2] > [backup_file.sql]

Backing Up Multiple Databases

To back up more than one database at the same time, use the --databases option:

mysqldump -u [username] -p --databases [db1] [db2] [db3] > [backup_file.sql]

Backing Up All Databases on the Server

To back up every database managed by your MySQL instance, use the --all-databases option:

mysqldump -u [username] -p --all-databases > all_databases_backup.sql

Recommended Best Practices for mysqldump

To ensure consistent and safe backups, especially in production environments, consider adding these flags to your command:

• --single-transaction: Required for InnoDB tables. It starts a transaction before running, allowing you to back up the database without locking it, meaning your applications can continue reading and writing.
• --quick: Forces mysqldump to retrieve rows from the server one row at a time rather than retrieving the entire row set and buffering it in memory, which is useful for large tables.
• --routines and --triggers: Includes stored procedures, functions, and triggers in the backup.

Optimized Backup Example:

mysqldump -u root -p --single-transaction --quick --routines --triggers my_database > secure_backup.sql

Compressing the Backup File

Logical backups can result in very large text files. You can compress the output on the fly using gzip to save disk space:

mysqldump -u root -p --single-transaction my_database | gzip > database_backup.sql.gz

Restoring a MySQL Logical Backup

To restore a database from a .sql dump file, use the standard mysql command-line client (not mysqldump) to execute the SQL statements contained within the backup file.

First, ensure the target database exists. If it does not, create it:

CREATE DATABASE my_database;

Then, run the restore command from your terminal:

mysql -u [username] -p [database_name] < [backup_file.sql]

If you compressed the backup file with gzip, use gunzip to restore it directly:

gunzip < database_backup.sql.gz | mysql -u root -p my_database