How to Use Mysqldump Gzip to Create a Compressed File for a MySQL Database

1. Use mysqldump gzip to Create a Compressed File for a MySQL Database
2. Use crontab to Automate Compressed Backups

This tutorial guides using mysqldump gzip with and without the crontab to take compressed backups of a specified MySQL database.

Use mysqldump gzip to Create a Compressed File for a MySQL Database

The mysqldump and gzip are Linux operating system utilities. The mysqldump is used for taking a backup of the database.

We use the gzip as follows if we can save the backup as a compressed file in the current directory.

Example Code One:

-- Syntax: mysqldump -u username -ppassword db_name | gzip > dumpfilename.sql.gz
mysqldump -u root -p test | gzip > dump_test_db.sql.gz

Now, use the ls command to confirm that the compressed file is created. Let’s have another example where data goes through different files and is saved in the last file.

Example Code Two:

-- Syntax: mysqldump -u username -ppassword db_name | gzip > file1.gz > file2.gz
mysqldump -u root -p test | gzip > file1.gz > file2.gz > file3.gz

Output:

$> ls -l
-rw-r--r--  1 root  root     0 14:35 21 May file1.gz
-rw-r--r--  1 root  root     0 14:35 21 May file2.gz
-rw-r--r--  1 root  root   453 14:35 21 May file3.gz

In this example, the mysqldump gets executed, and the generated output is redirected using the pipe (|) symbol. Then, the pipe sends the standard output into the gzip command as standard input.

The > is the output redirection operator that continues data redirection until the last filename is found, and this would be the file where data will be saved.

See the output given above; the last file is file3, which contains the size 453 data, while the size of file1 and file2 is 0. Here is a detailed guide about the different parameters of the gzip and their uses.

We can also automate the compressed backups using crontab. Let’s learn that in the following section.

Use crontab to Automate Compressed Backups

We create a cron job to automate the procedure of taking compressed backups of the database where the cron job will execute the mysqldump command at the particular time that you will assign.

We need to follow the steps to set up automated compressed backups for the MySQL database via cron job.

Step 1: Create a .my.cnf file in the user’s home directory.

sudo nano ~/.my.cnf

Write the exact text as given below.

[client]
user = db_username
password = db_password

Do not forget to update the db_username and db_password with your credentials. For instance, the username is root, and the password is 12345.

Example Code:

[client]
user = root
password = 12345

Once it is done, save and exit the file.

Step 2: Restrict the credentials file’s permission. In this way, the specified user will have access to it.

$ chmod 600 ~/.my.cnf

Step 3: Next, create a directory to store all the backups in one place.

$ mkdir ~/database_backups

Step 4: Open the crontab file now.

$ crontab -e

Write the following job in the crontab file you just opened. It will create database compressed backups daily at 3 AM.

In the following job, the username is the root, the database name is the test, and the database_backups folder stores all the backups.

$ 0 3* * * /usr/bin/mysqldump -u root test | gzip > /home/root/database_backups/test-$(date +\%Y\%m\%d).gz