What Is mysqlpump?

The mysqlpump utility was introduced with MySQL 5.7 and is a faster alternative to the older mysqldump utility available in previous versions. Although it has similar functionality as mysqldump, the mysqlpump utility is used to take a backup of large databases using parallelism, which is unavailable in the older mysqldump utility. It can take backups of the entire database or a few select objects only.

What Is mysqlpump?

Introduced in MySQL 5.7, mysqlpump is a client utility that performs logical backups in which a set of SQL statements is produced which can be executed to restore the original database object. It solves the problem of slow backups in mysqldump via support for parallelism and multithreading. Some databases contain terabytes of data, so exporting data to a simple text file can take too long. The mysqlpump utility uses multiple threads in parallel to transfer data to a text file much faster. Because it was introduced years ago, MySQL database developers have made improvements in recent years to provide a more stable mysqlpump utility and offer more options. Even though it’s faster, the mysqlpump utility does not have all the options available in mysqldump so it’s only used when administrators need a fast export of a MySQL database and its data.

How to Install mysqlpump

If you have MySQL 5.7 or later, you already have access to mysqlpump. The utility ships with the MySQL database engine, so no installation is required. If you have an earlier version of MySQL, then you cannot use mysqlpump, but mysqldump is a feasible alternative.

Whether you use MySQL on Windows or Linux, the mysqlpump utility is available with the MySQL database engine. You can use it by opening the command line utility in Windows or Linux and typing “mysqlpump” with the numerous parameter options to customize the way the utility exports data.

Basic Use and Examples of mysqlpump

With MySQL installed on your server, you can access mysqlpump from the command line. Open the command line in Windows or Linux, and you can export a database schema using the following command:

mysqlpump --all-databases --user=root --password > full_backup.sql

The above command exports all database schemas to the file full_backup.sql file. The username and password you use must have access to the databases that you want to export, so make sure you have a high-privilege account when using the mysqlpump utility.

If you’re already authenticated into MySQL, you can also dump database schemas without specifying a username and password:

mysqlpump --all-databases > full_backup.sql

If you prefer to dump a single database, you can use the following command:

mysqlpump myDB

The above command exports the database schema and its data for myDB. You can export multiple databases by separating each database name with a space character.

Administrators might occasionally want to export only specific tables within a database. Dumping table data with mysqlpump can be performed using the following command:

mysqlpump myDB Customer Order

In the example above, the mysqlpump statement exports the Customer and Order tables from the myDB database. Notice that the table names are separated by a space character.

Enterprise administrators work with differential and incremental backups to avoid making a full backup every time. Differential backups contain all data changed since the last full backup. An incremental backup stores all data changed since the last backup, either incremental or full. These two backup types are not possible with mysqlpump, so the utility can only be used to take a full or partial backup of databases and their objects.

Advanced Use of mysqlpump

The mysqlpump utility offers a few options for advanced usage. The first one is compressed output. For large databases, using compressed output will reduce the amount of drive capacity necessary to store backup files. It’s especially useful in enterprise environments where several large files will be stored for full backups throughout the month.

You need to specify a compression algorithm when using mysqlpump. You can use LZ4, which is mainly used for speed. The zlib algorithm is more popular with administrators using gzip or the zlib wrapper. In this example, the zlib algorithm is used to output a gzip file:

mysqlpump --compress --compress-output=zlib myDB > myDB_compressed.sql.gzip

To stay compliant, stored backups must be encrypted. Data at rest—meaning stored on a drive—must be encrypted when it’s sensitive personally identifiable information (PII), financial data, or any data that falls under compliance regulations. In this example, the mysqlpump command is piped and uses OpenSSL to create encrypted output:

mysqlpump --all-databases | openssl  enc -aes-256-cbc -k yourpassword > backup.xb.enc

One aspect of mysqlpump that makes it beneficial for administrators is its ability to dump tables and databases in parallel, meaning it can use multiple computer processes to export data. Think of parallel dumps as several exports happening simultaneously instead of waiting for each object to complete one by one. By using parallel processing, administrators speed up the backup procedure.

To use parallelism with mysqlpump, you must define the number of threads that will be used during the export. The default is two, but you can define more. The following statement uses four threads to export two tables—Customer and Order—from the myDB database:

Mysqlpump -default-parallelism=4 myDB Customer Order > full_backup.sql

mysqlpump vs. mysqldump

The parallelism option in mysqlpump is not available in mysqldump, so administrators get faster backup speeds with mysqlpump. The advantage of mysqlpump is faster backups in a large enterprise environment. Instead of taking several minutes to perform a backup, the parallelism in mysqlpump could shave down the time it takes to back up data to several minutes.

Not every option in mysqldump is available in the newer mysqlpump, and you must have at least MySQL 5.7 installed to use it. Importing doesn’t use parallelism, so you do not have the advantage of speed to import data. Administrators might choose to use alternative utilities for importing data.

Conclusion

Backups are an essential part of database administration, and the mysqlpump utility makes the process much faster. For quick backups of an entire database or a few objects in a database, mysqlpump exports schemas and data, encrypts and compresses it, and uses multiple threads with parallelism. Use it whenever you need to create a full backup of a database or export several tables.