MariaDB mysqldump - backup with bzip2 compression

Colleagues hello to all.

In today's article, we're going to talk about how you can easily back up a MariaDB database with the mysqldump utility using the bzip2 compression program. The bzip2 program can compress files by encoding the data in the file using fewer bits. One downside to using bzip2 compression is that it takes a little longer to create a backup than you do with a normal backup.

Article content:

1. Syntax.
2. Installing the bzip2 package.
3. Mysqldump examples using bzip2.

1. Syntax.

mysqldump -u [user_name] –p [password] [options] [database_name] [tablename] | bzip2 > [filename.sql.bz2]

All files will be created with filename.sql.bz2 extension

2. Installing the bzip2 package.

We will install the bzip2 package through the yum package manager.

$. yum install bzip2 -y

I installed the bzip2 package before, so linux tells me that it cannot install this package again.

3. Mysqldump examples using bzip2.

3.1. A simple example of creating all databases using compression.

$. mysqldump -u root -p --all-databases | bzip2 > /app/mariadb_backup/dump.sql.bz2

The --all-databases option means that the backup will include all databases.

3.2. In this example, we will backup only one database called test using compression.

$. mysqldump -u root -p --databases test | bzip2 > /app/mariadb_backup/dump.sql.bz2

3.3. In this example, we are backing up the two databases test and test2 using compression.

$. mysqldump -u root -p --databases test test2 | bzip2 > /app/mariadb_backup/dump.sql.bz2

3.4. In this example, we are backing up and logging warnings and errors that may occur with compression.

$. mysqldump -u root -p --databases test --log-error=/app/mariadb_backup/log_error.log | bzip2 > /app/mariadb_backup/dump.sql.bz2

3.5. In this example, we create a backup with the addition of the binary log position and filename, using compression.

$. mysqldump -u root -p --databases test --master-data=2 | bzip2 > /app/mariadb_backup/dump.sql.bz2

3.6. In this example, we create a backup that will upload the data in a consistent state using compression.

$. mysqldump -u root -p --databases test --single-transaction | bzip2 > /app/mariadb_backup/dump.sql.bz2

Thank you all, I hope that my article helped you in some way.