Various Backup Compression Methods Using Mysqlpump

Backup Compression Methods Using MysqlpumpMysqlpump is a client program that was released with MySQL 5.7.8 and is used to perform logical backups in a better way. Mysqlpump supports parallelism and it has the capability of creating compressed output. Pablo already wrote a blog about this utility (The mysqlpump Utility), and in this blog, I am going to explore the available compression techniques in the Mysqlpump utility.

Overview

Mysqlpump has three options to perform the compression backup.

–compress: Used to compress all the information sent between client and server.

–compression-algorithm: It was added in MySQL 8.0.18. Used to define the compression algorithm for all incoming connections to the server. (available options: zlib, zstd, uncompressed )

–compress-output: Used to define the compression algorithm for the backup file (available options: lz4, zlib)

Here, “–compress-output” is the option used to define the compression algorithm for the backup file. Which has two algorithms.

  • Lz4
  • Zlib

Lz4: LZ4 is a lossless data compression algorithm that is focused on compression and decompression speed.

Zlib: zlib is a software library used for data compression. zlib compressed data are typically written with a gzip or a zlib wrapper. 

Lab Setup

To experiment with both compression techniques, I have installed the MySQL (8.0.22) server in my local environment. I also created the table “percona_test.mp_test” which has an 11GB size.

Now, I am going to experiment with both compression algorithms.

Compression with Lz4

I am going to take the backup (table: mp_test) using the lz4 compression algorithm.

It took 9.1 minutes to complete. And, the file size is 1.1 GB, looks like 10x compression.

Compression with Zlib

Now, I am going to start the backup with “zlib” algorithm.

It took 10.6 minutes to complete the process. And the file size is the same 1.1 GB (10x compression).

 

 

How to Decompress the Backup

MySQL community provides two utilities to decompress the backups.

  • zlib_decompress ( for zlib compression files )
  • lz4_decompress ( for lz4 compression files )

lz4_decompress

lz4 took 45 seconds to decompress the backup file.

zlib_decompress

zlib took 36 seconds to decompress the backup file.

This is the procedure we have to compress/decompress the backups with Mysqlpump. It seems both the algorithms provide the 10x compression. Also, there is not much difference in the execution time as well, but it may be the big one with a large dataset. 

Share this post

Comments (2)

Leave a Reply