Comparing Percona XtraBackup to MySQL Enterprise Backup: Part One

When it comes to backups and data archiving, IT departments are often under stress to meet stringent service level agreements as well as deliver more robust backup procedures that would minimize the downtime, speed up the backup process, cost less, and meet tight security requirements.

There are multiple ways to take a backup of a MySQL database, but we can divide these methods into two groups – logical and physical.

Logical Backups contain data that is exported using SQL commands and stored in a file. It can be, e.g., a set of SQL commands, that, when executed, will result in restoring the content of the database. With some modifications to the output file’s syntax, you can store your backup in CSV files.

Logical backups are easy to perform, solely with a one-liner, you can take a backup of all of your table, database, or all mysql databases in the instance. 

Unfortunately, logical backups have many limitations.  They are usually slower than a physical one. This is due to the overhead needed to execute SQL commands to get the data out and then to execute another set of SQL commands to get the data back into the database.  They are less flexible, unless you write complex backup workloads that would include multiple steps. It doesn’t work well in a parallel environment, provides less security, and so on and so one.

Physical Backups in MySQL World

MySQL doesn’t come with online physical backup for community edition. You can either pay for an Enterprise version or use a third-party tool. The most popular third-party tool on the market is XtraBackup. Those we are going to compare in this blog article.

Percona XtraBackup is the very popular, open-source, MySQL/MariaDB hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. It falls into the physical backup category, which consists of exact copies of the MySQL data directory and files underneath it.

One of the biggest advantages of XtraBackup is that it does not lock your database during the backup process. For large databases (100+ GB), it provides much better restoration time as compared to mysqldump. The restoration process involves preparing MySQL data from the backup files, before replacing or switching it with the current data directory on the target node.

Percona XtraBackup works by remembering the log sequence number (LSN) when it starts and then copies away the data files to another location. Copying data takes time, and if the files are changing, they reflect the state of the database at different points in time. At the same time, XtraBackup runs a background process that keeps an eye on the transaction log (aka redo log) files, and copies changes from it. This has to be done continually because the transaction logs are written in a round-robin fashion, and can be reused after a while. XtraBackup needs the transaction log records for every change to the data files since it began execution.

By using this tool you can:

• Create hot InnoDB backups, that complete quickly and reliably, without pausing your database or adding load to the server
• Make incremental backups
• Move tables between MySQL servers on-line
• Create new MySQL replication slaves easily
• Stream compressed MySQL backups to another server
• Save on disk space and network bandwidth

MySQL Enterprise Backup delivers hot, online, non-blocking backups on multiple platforms. It’s not a free backup tool, but it offers a lot of features. The standard license cost is $5000 (but may vary on your agreement with Oracle.) 

Backup Process Supported Platforms

MySQL Enterprise

It may run on Linux, Windows, Mac & Solaris. What is essential it may also store backup to tape, which is usually a cheaper solution than writes to disks. The direct tape writes supports integration with Veritas Netbackup, Tivoli Storage Manager, and EMC NetWorker. 

XtraBackup

XtraBackup may run only on the Linux platform, which may be undoubtedly a show stopper for those running on windows. A solution here maybe replication to the slave running on Linux and running backup from there. 

Backup Process Main Differences

MySQL Enterprise Backup provides a rich set of back and recovery features and functionality including significant performance improvements over existing MySQL backup methods. 

Oracle shows Enterprise backup to be even 49x faster than mysqldump. That, of course, may vary depending on you data however there are many features to improve the backup process. A parallel backup is definitely one of the biggest differences between mysqldump and Enterprise backup. It increases performance by multi-threaded processing. The most interesting feature, however, is compression.

–compress

Creates a backup in compressed format. For a regular backup, among all the storage engines supported by MySQL, only data files of the InnoDB format are compressed, and they bear the .ibz extension after the compression. Similarly, for a single-image backup, only data files of the InnoDB format inside the backup image are compressed. The binary log and relay log files are compressed and saved with the .bz extension when being included in a compressed backup.

-compress-method=zlib,lz4(default), lzma, punch-hole

--compress-level=LEVEL(0-9)

--include-tables=REGEXP

MySQL Backups with ClusterControl

ClusterControl allows you to schedule backups using XtraBackup and mysqldump. It can store the backup files locally on the node where the backup is taken, or the backup files can also be streamed to the controller node and compressed on-the-fly. It does not support MySQL Enterprise backup however with the extended features of mysqldump and XtraBackup it may be a good option. 

ClusterControl is the all-inclusive open source database management system for users with mixed environments. It provides advanced backup management functionality for MySQL or MariaDB.

With ClusterControl you can:

• Create backup policies
• Monitor backup status, executions, and servers without backups
• Execute backups and restores (including a point in time recovery)
• Control backup retention
• Save backups in cloud storage
• Validate backups (full test with the restore on the standalone server)
• Encrypt backups
• Compress backups
• And many others

Conclusion

As a DBA, you need to make sure that the databases are backed up regularly, and appropriate recovery procedures are in place and tested. Both Percona XtraBackup and MySQL Enterprise Backup provides DBAs with a high-performance, online backup solution with data compression and encryption technology to warrant your data is protected in the event of downtime or an outage

Backups should be planned according to the restoration requirement. Data loss can be full or partial. For instance, you do not always need to recover the whole data. In some cases, you might just want to do a partial recovery by restoring missing tables or rows. With the reach feature set, both solutions would be a great replacement of mysqldump, which is still a very popular method to do the backup. Having mysqldump is also important for partial recovery, where corrupted databases can be corrected by analyzing at the contents of the dump. Binary logs allow us to achieve point-in-time recovery, e.g., up to right before the MySQL server went down. 

This is all for part one, in the next part we are going to test the performance of both solutions and run some real case backup and recovery scenarios.