How to Backup MySQL Databases with Compression
To backup a MySQL database with compression, use the
mysqldump command piped into a compression tool like gzip. For example, mysqldump -u user -p database_name | gzip > backup.sql.gz creates a compressed backup file.Syntax
The basic syntax to backup a MySQL database with compression uses mysqldump combined with a compression tool like gzip or bzip2. The command structure is:
mysqldump -u [username] -p [database_name]: Dumps the database.|: Pipes the output to the next command.gzip > [backup_file].sql.gz: Compresses the dump and saves it as a file.
bash
mysqldump -u username -p database_name | gzip > backup_file.sql.gz
Example
This example shows how to backup a database named mydb using user root. It prompts for the password, dumps the database, compresses it with gzip, and saves it as mydb_backup.sql.gz.
bash
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz
Output
Enter password:
# After entering password, no output is shown but the file mydb_backup.sql.gz is created.
Common Pitfalls
Common mistakes when backing up with compression include:
- Not using the pipe
|correctly, which causes the dump to not compress. - Forgetting to specify the output file with
>, resulting in no saved backup. - Using compression tools not installed on the system.
- Not having enough disk space for the compressed file.
Always verify the backup file exists and test restoring it.
bash
mysqldump -u root -p mydb gzip > mydb_backup.sql.gz # Incorrect: missing pipe mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz # Correct
Quick Reference
| Command Part | Description |
|---|---|
| mysqldump -u username -p database_name | Exports the database content. |
| | | Pipes output to next command. |
| gzip | Compresses the output stream. |
| > backup.sql.gz | Saves compressed output to a file. |
Key Takeaways
Use mysqldump piped to gzip to create compressed backups efficiently.
Always include the pipe symbol | to connect mysqldump and gzip commands.
Verify the backup file is created and test restoring it to ensure success.
Ensure compression tools like gzip are installed on your system.
Remember to provide the correct username, database name, and output file.