Understanding the different methods of MySQL backups

A comprehensive comparison of all the alternatives

Posted by Walter Garcia on September 26, 2017

There are different methods to backup a MySQL server (or MariaDB), depending on the size of the data, your available hardware and other constraints you might have. In this blog post we will see how each alternative has different tradeoffs, so follow along this blog post to see which one suits you best. In the upcoming posts we will deep dive in each one of the backup methods, so stay tuned for more.

Logical vs Physical Backups

All backup alternatives can be broadly divided between logical and physical backups.

Logical backups scan all the rows in your database and generate one INSERT statement for each row and table. Usually, they also generate all the CREATE TABLE and CREATE INDEX statements, among all other necessary definitions for the database. The output of the backup is therefore a plain text file or stream that can be restored by using your standard database CLI program.

The advantages of logical backups are their small size, as indexes are not backed up (only the data), and the ability to use the backup interchangeably with other database versions, and even different vendors in some cases, as their syntax is usually the same (but not their binary storage formats). Because of this, they are often used for database migrations.

The biggest disadvantage is how slow the process is, particularly for restores, as the database has to replay all the insert statements, which is not only slow but generates a lot of load as well. A database restore for a big website (with a database approaching 1 TB of data) can easily take more than a day !

Physical backups involve copying the actual database files from disk and, because of that, they are also known as binary backups. Since the database itself is not part of the backup process, special care must be taken to make a consistent backup, since updates can happen in the middle of a copy operation. Cold backups solve this by either locking the entire database (preventing writes) or shutting down MySQL entirely, making the process very fast and simple (if you can afford some downtime). Hot backups copy the files while the database is still running, so they rely on complex mechanisms to avoid inconsistencies.

The advantage of physical backups is that they perform much better than logical backups, while also generating less load on the server. A 1TB database can be backed up in less than 2 hours and restored in about 4 hours while the logical alternative could take ten times that.

The disadvantage is that, since the binary files are copied, the backed up files cannot be restored in different database versions, let alone different vendors, as the way they store the data on disk is usually not compatible with each other. Another disadvantage is that the backup file size is usually much bigger than logical backups, as the entire database is backed up (with all the indexes and even deleted rows not yet optimized by the storage engine).

Backup Methods

mysqldump

It is probably the simplest and most common backup method, partly because it’s part of the MySQL Client Programs, so it will be installed by default.

It does logical backups and can backup all databases, one database and all tables or one or more tables from the same database.

The dump is printed to the standard output, so you can pipe the output to other programs for interoperability. For example you can pipe the output to gzip, as mysqldump does not compress the output. It is also common to pipe it to openssl to encrypt the backup.

The biggest downside is that is painfully slow on big databases, not only because it does logical backups, but also because both the backup and the restore processes are single threaded.

The dump contains SQL statements to create the tables, populate them with data, or both.

mydumper

If you want or need logical backups but mysqldump is too slow for you, you can install this third-party software from https://github.com/maxbube/mydumper/releases. This software has been around for a long time and is widely used in production.

It’s multi-threaded so it’s considerably faster. The dump it creates consists of one or more compressed files for each table, which might or might not be useful for you. For example, you cannot pipe the backup to other programs, as the dump is written directly to disk instead of standard output. However, having different files per table makes it more flexible when importing certain tables.

It can also create a consistent backup to use for a new slave, as long as all tables use the InnoDB storage engine.

mysqlpump

Starting with MySQL 5.7.8, the MySQL client programs come bundled with this new utility called mysqlpump. This tool is similar to mysqldump as it produces a logical backup via standard outpu but, as described by the MySQL team:

The goal of mysqlpump is to have a modern utility that is extendable and has native support for parallelization. We felt that the best way to achieve this was to write an entirely new tool where we would be free to break compatibility with mysqldump and where we would not be forced to implement some of the legacy functionality that it provides. mysqlpump executes all of the operations related to dumping multiple databases and the objects inside them in parallel in order to drastically reduce the total time needed to perform a logical backup.

It’s a relatively new tool, but it’s still worth checking if you need logical backups. Keep in mind that this tool doesn’t come with other forks of MySQL (like MariaDB).

Percona xtrabackup

All the previous backup methods do logical backups, so they become unviable once your database grow over 100GB or so. Xtrabackup is the most popular MySQL physical backup software. It can create a hot backup (meaning you don’t have to stop your database server), is much faster and supports completely non-blocking backups, as long as all tables are InnoDB or XtraDB storage engine.

It can create local backups files or a standard output stream, so it’s very versatile. For example, using tools like gof3r you can stream the backups to Amazon S3 and upload your backup on-the-fly (without storing it locally).

The size of the backups is almost as big as your entire database, but xtrabackup supports compressed backups using qpress, reducing the final size considerably. Another great feature is that it can encrypt the backups or stream with AES128, AES192 and AES256.

Which one should I use?

Well, it all depends on the size of your data, for bigger sizes (say, 100GB or more) we recommend to use Percona xtrabackup because it’s faster to backup and much faster to restore. While it has it’s quirks, it is very mature and used by most of the serious sites using MySQL.

In the following posts we are going to explain each one of these methods with more detail.

Our Cloud Backup tool supports these and several other backup methods out of the box, you can check them HERE in our docs.


Want to Automate your database backups and manage your servers in a Web interface?
Contact Us Here and request beta access to our tool !


Tags

  • MySQL
  • MariaDB
  • Backups
  • xtrabackup
  • mydumper
  • mysqldump
  • mysqlpump