How To Create a Compressed MySQL Backup on Linux

March 3, 2025

Create a compressed backup of mysql databases

Backing up MySQL databases is essential for data security and recovery. Creating a compressed MySQL backup on Linux helps save storage space and speeds up data transfer. This guide explains different methods to back up and compress MySQL databases efficiently.

Why Compress MySQL Backups?

Compressed backups reduce disk space usage, improve backup speed, and make data transfers more efficient. When dealing with large MySQL databases, compressing backups is crucial for optimizing storage and performance.

Table of Contents

Using `mysqldump` with `gzip` to Create a Compressed MySQL Backup

One of the easiest ways to create a compressed MySQL backup is by using the mysqldump command and piping the output to gzip. This method is simple and effective.

Command:

mysqldump -u [username] -p[password] [database_name] | gzip > [backup_file].sql.gz

Explanation:

mysqldump -u [username] -p[password] [database_name] generates a logical backup of the specified database.
The | pipe operator sends the dump output directly to another command.
gzip compresses the backup on the fly.
> [backup_file].sql.gz saves the compressed output to a .gz file.

Example:

mysqldump -u root -p my_database | gzip > my_database_backup.sql.gz

This command prompts for the MySQL root password and creates a compressed backup named my_database_backup.sql.gz.

Restoring the Compressed Backup:

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

This decompresses the backup and restores it to my_database.

Using `tar` for Multi-Database Backups

If you need to back up multiple databases, you can dump them into a directory and compress the entire folder with tar.

Steps:

Create a backup directory:

mkdir -p /backup/mysql

Dump databases into separate files:

mysqldump -u root -p database1 > /backup/mysql/database1.sql
mysqldump -u root -p database2 > /backup/mysql/database2.sql

Compress the entire folder:

tar -czf mysql_backup.tar.gz /backup/mysql/

To restore, extract the files first:

tar -xzf mysql_backup.tar.gz
mysql -u root -p database1 < /backup/mysql/database1.sql

Automating MySQL Backups with `cron`

To automate MySQL backups, schedule a cron job to run the backup command at a specific time.

Edit the cron jobs:

crontab -e

Add a daily backup job at 2 AM:

0 2 * * * mysqldump -u root -p my_database | gzip > /backup/my_database_$(date +\%F).sql.gz

This ensures the backup runs automatically every day at 2 AM.

FAQ

How can I automate MySQL backups with compression?

Use a cron job to schedule backups. Example: crontab -e then add 0 2 * * * mysqldump -u root -p mydb | gzip > /backup/mydb_$(date +\%F).sql.gz. This runs daily at 2 AM.

What is the best way to restore a compressed MySQL backup?

Use gunzip < backup.sql.gz | mysql -u user -p database_name. This decompresses and restores the backup in one command.

How do I verify a MySQL backup after compression?

Use gunzip -c backup.sql.gz | head -n 20 to check if the SQL dump starts with -- MySQL dump. You can also restore it to a test database and compare data.

Can I back up multiple MySQL databases in one compressed file?

Yes, use mysqldump -u user -p --all-databases | gzip > all_databases.sql.gz. This includes all databases in a single compressed file.

How do I back up a large MySQL database without running out of memory?

Use mysqldump --quick --single-transaction -u user -p database_name | gzip > backup.sql.gz. The --quick flag retrieves rows one at a time to reduce memory usage.

Is there a way to split large MySQL backups into smaller files?

Yes, use mysqldump -u user -p database | gzip | split -b 500M - backup_part_. This splits the file into 500MB chunks, which can be recombined using cat backup_part_* > full_backup.sql.gz.

By using these methods, you can efficiently create and manage compressed MySQL backups on Linux while ensuring data security and recovery.