MySQL, Percona and MariaDB Backup. The xbstream and mbstream format

In the world of database management, data protection is crucial to ensure business continuity and information security. With the increasing use of MySQL and MariaDB, having an efficient and reliable backup strategy is a necessity to avoid data loss due to hardware failures, human errors or cyber attacks. In this context, tools such as Contact XtraBackup e MariaBackup are widely used to perform physical backups of MySQL and MariaDB. In particular, the backup formats xbstream e mbstream are gaining more and more attention for their efficiency in managing backups and restores. This article explores in detail the backup of MySQL and MariaDB databases, focusing on the use of xbstream and mbstream, and the role of tools such as Mariabackup, which is a fork of Percona XtraBackup.

The Importance of Backup in MySQL and MariaDB

MySQL and MariaDB are among the most widely used relational databases in the world. However, as with any system, it is essential to have a well-structured backup plan to protect your data from unforeseen events. Regular backups of MySQL and MariaDB help mitigate the risks associated with:

• Human errors: Accidental or incorrect changes to data can compromise the integrity of the database. With a backup, you can quickly restore the database to its previous state.
• Hardware failures: Hard drives or other hardware components can fail, leading to data loss. Having an up-to-date backup helps minimize downtime.
• Cyber ​​attacks: Databases can be vulnerable to security breaches or ransomware attacks. A backup allows you to recover your data without paying ransoms or suffering serious losses.
• System Updates: Software or hardware updates can introduce bugs or compatibility issues. Backups allow you to restore your database to a working state in the event of a problem.

MySQL and MariaDB Backup Tools

There are several tools for backing up MySQL and MariaDB databases, each with unique capabilities, execution modes, and purpose. Each tool has its own strengths and weaknesses depending on the context, database size, and recovery needs. Some of the most common tools include:

mysqldump

mysqldump It is a native and widely used tool for performing the logical backup MySQL and MariaDB databases. It works by exporting data into a readable format, such as SQL script files or CSV files, which can later be used to restore the database. During the export, mysqldump creates SQL commands that recreate table structures, insert data, and apply any primary keys, indexes, and constraints.

Advantages of mysqldump:

• Portability: Backups produced by mysqldump are easily portable to other servers or platforms, since the SQL format is universally readable by any instance of MySQL or MariaDB.
• Flexibility: You can backup individual tables, specific databases, or an entire database server, customizing the output to suit your needs.
• Ease of use: Being a native tool, it comes pre-installed in every MySQL and MariaDB distribution, requires no additional software, and has a low learning curve.

Disadvantages of mysqldump:

• Slow for large databases: The main drawback of mysqldump is its inefficiency with large databases. Since it exports data in a text format, the time required for export and restore increases significantly as the database size increases.
• Lack of incremental backups: mysqldump does not support incremental backups, which means that each backup is complete and contains all the information. This increases the time and storage required compared to physical solutions that offer incremental backups.
• Blocks: In some scenarios, especially when not used in non-blocking mode (--single-transaction for InnoDB databases), may cause database locks, temporarily interrupting access to the data while the backup is running.

Contact XtraBackup

Contact XtraBackup which we talked about a lot in this post MySQL backup slow and server down when Google goes by, is one of the most popular physical backup tools for MySQL and MariaDB. It is developed by Percona, a company specializing in open-source solutions for MySQL, MariaDB and MongoDB. This tool is especially popular in production environments because it performs physical backups without locking the database, allowing the database to remain up and running during the backup process.

Benefits of Percona XtraBackup:

• Backup without blocks: One of the main advantages is that it performs backups without requiring a database lock. This means that read and write operations can continue normally while the backup is running.
• Physical backups: Since XtraBackup directly copies data files (such as .ibd for InnoDB tables), the backup process is much faster than mysqldump, especially for large databases.
• Incremental backups: XtraBackup supports incremental and differential backups, reducing backup time and disk space. With incremental backups, only data that has changed since the last full backup is copied, greatly improving efficiency.
• Ripristino rapido: Because restoring a physical backup involves only copying the data files, you can restore a large database much faster than from a logical backup.
• Compression and encryption: XtraBackup also supports backup compression to reduce storage space usage and encryption to protect sensitive data.

Disadvantages of Percona XtraBackup:

• Greater complexity: As an advanced tool, XtraBackup requires a deeper understanding of database configurations and operations, especially regarding log management and applying changes during restore.
• Does not natively support all MariaDB configurations: While XtraBackup supports many of MariaDB's features, it is primarily designed for MySQL and Percona Server, so some MariaDB-specific features may not be fully compatible.

MariaBackup

MariaBackup It's a fork of Contact XtraBackup, specifically developed to manage physical backups of MariaDB. The creation of Mariabackup was made necessary by the progressive divergence between MySQL and MariaDB, two databases that, while sharing common origins, have introduced different features over time. Because of this divergence, Percona XtraBackup, which was initially compatible with both databases, has progressively lost support for the most recent versions of MariaDB. Mariabackup was therefore born to fill this gap and provide a specific backup tool for MariaDB installations.

Advantages of Mariabackup:

• Optimized for MariaDB: Mariabackup was specifically designed to handle changes in MariaDB's InnoDB storage engine and to support other system-specific features, such as Aria tables and new MariaDB features that are not present in MySQL.
• Backup without blocks: Like XtraBackup, Mariabackup also performs physical backups without locking the database, which means it can be used in production environments without interrupting the service.
• Compatibility with MariaDB engines: In addition to InnoDB, Mariabackup also supports Aria table backup and other unique MariaDB features, making it the ideal tool for those who have chosen this database.
• Incremental backups: Mariabackup inherits the ability to perform incremental backups from XtraBackup, which means you can perform efficient and fast backups by copying only the changed data.

Disadvantages of Mariabackup:

• Less community support: Although Mariabackup is developed by the MariaDB team, it does not yet have the same popularity and community support as tools like Percona XtraBackup or mysqldump.
• Focus only on MariaDB: Unlike XtraBackup, which can be used for MySQL, MariaDB, and Percona Server, Mariabackup is specific to MariaDB and cannot be used to backup other versions of MySQL.

Mariabackup is the MariaDB equivalent of Percona XtraBackup for MySQL, and thanks to its continuous development, it is able to keep up with the new features introduced in the latest versions of MariaDB.

Logical Backups vs. Physical Backups

When it comes to database backups, we can distinguish two main approaches: logical backups e physical backups, each with its own advantages and disadvantages depending on the infrastructure needs and database size.

Logical backups

Logical backups are created by exporting database data into a readable format, such as SQL or CSV. This type of backup not only saves the data, but also captures the structure of the tables, including indexes, constraints, and in some cases, foreign keys. Because they are in a text format, they are easy to read, modify, and interpret. They are especially useful when you want to migrate data to another platform or a different version of the database.

Benefits:

• Portability: Since logical backups are saved in SQL format, they can be easily transferred and restored to other servers or even databases that use different versions of the software. This makes logical backups particularly suitable for migration scenarios.
• Flexibility: They allow the extraction and recovery of individual tables or specific portions of data, facilitating selective database update operations or partial restores, useful for granular data recovery operations.

disadvantages:

• Inefficiency for large databases: As the database grows in size, logical backups become less practical. Creating a backup takes time, since all the data must be converted into SQL commands, and the resulting file can be very large, taking up more storage space than a physical backup.
• Long recovery times: Restoring a database from a logical backup is time consuming, as each individual SQL command must be re-executed to recreate the tables, data, and indexes. This can make restores particularly slow for large databases, compromising disaster recovery times.

Physical backups

Physical backups are created by directly copying the binary files that the database uses to store data and structures (for example, InnoDB .ibd files or redo log files). This method is more efficient than logical backups, as it does not require converting the data into text format. The database files are copied exactly as they are on the disk, providing a fast and compact backup, especially useful for large databases or databases with a high volume of data.

Benefits:

• Racing: Since the data is not converted into an intermediate format such as text, copying binary files is much faster than copying logical backups. This is a great advantage especially for databases with large data volumes or in environments where downtime must be kept to a minimum.
• Space efficiency: Physical backups require less storage space, as they copy only essential files and can be compressed during the backup process. This allows for more optimized space management, reducing the load on limited storage.
• Ripristino rapido: The process of restoring from a physical backup is much faster. It mainly involves copying the binaries to the server and applying the redo logs to ensure database consistency, avoiding reprocessing all the data as in logical backups.

disadvantages:

• Less portability: Physical backups are strictly tied to the specific database version and operating system configurations. They cannot be easily migrated between different database versions (for example, between MySQL and MariaDB or different versions of the same DBMS) without proper compatibility between the systems.
• Greater complexity: Managing physical backups, especially when implementing advanced strategies such as incremental or differential backups, requires in-depth technical expertise. Administrators must be able to properly manage log files and ensure that backups are consistent, which adds complexity to configuring and maintaining the backup system.

Tools like Contact XtraBackup e MariaBackup They use physical backups to ensure speed and efficiency, without requiring the database to be locked during the backup process, making them ideal for production environments with large amounts of data and the need to minimize downtime.

Percona XtraBackup and the xbstream format

Contact XtraBackup is an open-source tool designed to perform consistent physical backups of MySQL, MariaDB and Percona Server databases. It works by copying the database binaries and applying logs to maintain data consistency. One of the main advantages of XtraBackup is the ability to perform backups without blocking database operations, a critical aspect in production environments.

In real-world scenarios, like the one pictured below where available disk space is limited, such as a web server with a MariaDB server that runs an 83 gigabyte database, but only has 14 gigabytes of free disk space, using a local backup would not be practical. Normally, you could not generate a local copy of the database, compress it, and transfer it to an external SAN, due to the lack of space. However, using the xbstream o mbstream, you can overcome this limitation by directly transferring the physical backup via SSH to an external SAN, without the need to first generate a local copy.

The format xbstream It allows you to split the backup files into smaller compressed blocks and transfer them in real time, taking advantage of the streaming of data. This allows you to back up and transfer it to another system, reducing the load on the local disk and solving the problem of limited space, avoiding having to generate a large file locally.

An example command to create a backup with Percona XtraBackup and the xbstream format is:

xtrabackup --backup --stream=xbstream --target-dir=/path/to/backup > backup.xbstream

This command backs up the data and writes it in xbstream format, which can be compressed to reduce the space required.

To restore backup from xbstream format:

xbstream -x < backup.xbstream

This will extract the binary files, which can then be used to restore the database.

Mariabackup: A fork of Percona XtraBackup for MariaDB

MariaBackup It is a derivative version of Contact XtraBackup, developed and maintained by the MariaDB team to provide specific support for MariaDB. Mariabackup was born as a fork of XtraBackup to bridge the growing divergence between MySQL and MariaDB, as Percona XtraBackup was designed primarily for MySQL and Percona Server, while Mariabackup is optimized to work with MariaDB.

MariaDB and MySQL followed separate development paths after the original project split, with MariaDB introducing features and improvements not found in MySQL. Because of these differences, Percona XtraBackup was no longer fully compatible with newer versions of MariaDB. As a result, the MariaDB team created Mariabackup as a dedicated tool to manage physical backups of MariaDB.

MariaBackup works similarly to Percona XtraBackup and also uses the xbstream format to perform consistent physical backups without interrupting the database operation. This means that, like Percona XtraBackup, Mariabackup can perform incremental, differential and compressed backups, making it an ideal solution for MariaDB backup needs in production environments.

An example command to perform a backup with Mariabackup:

 
mariabackup --backup --stream=xbstream --target-dir=/path/to/backup > backup.xbstream

Mariabackup offers a solution optimized for MariaDB, with support for MariaDB-specific features, such as the InnoDB data format and Aria tables.

The mbstream format

The format mbstream is a streaming format similar to xbstream, but slightly more general-purpose. It is used to handle physical backups, allowing for parallel backup and restore, improving performance in large environments. One of the main differences between xbstream and mbstream is the latter's ability to leverage multiple threads to perform backups, making it ideal for servers with large databases and multithreaded infrastructures.

The mbstream format supports native compression and splitting of backup files into smaller blocks, just like xbstream, offering a flexible alternative for those who want to streamline their backup process.

To perform a backup using the mbstream format with XtraBackup or Mariabackup, use the following command:

xtrabackup --backup --stream=mbstream --target-dir=/path/to/backup > backup.mbstream

The recovery process follows a similar logic:

mbstream -x < backup.mbstream

Comparison between xbstream and mbstream

Both formats xbstream e mbstream have their advantages in terms of managing MySQL and MariaDB backups, but they have some differences that should be considered when choosing which format to use.

Advantages of xbstream:

• Optimal compatibility with XtraBackup and Mariabackup: xbstream is tightly integrated with these tools, offering optimal performance when performing physical backups of MySQL and MariaDB.
• Native compression: xbstream includes compression support, reducing the storage space needed for backups.
• Ease of use: For those using XtraBackup or Mariabackup, xbstream is the default format and therefore the simplest and most immediate choice.

Advantages of mbstream:

• Parallel backups: mbstream supports parallel backup and restore, improving performance on multi-threaded machines and servers with large databases.
• More flexibility: mbstream can be used with a wider range of tools and is not strictly tied to XtraBackup or Mariabackup.

Conclusions

Ensuring regular and reliable backup of MySQL and MariaDB databases is essential for the business continuity of any IT infrastructure. Tools such as Contact XtraBackup e MariaBackup offer robust solutions for managing physical backups, while the formats xbstream e mbstream provide efficient methods for compressing and managing backup data. The choice between xbstream and mbstream depends mainly on the specific needs of your system, such as database size, server performance, and restore requirements. However, for those working with MariaDB, using MariaBackup is a natural choice, given its specific optimization for this database system.

Having a solid backup strategy is vital to protecting your data from unforeseen disasters, and using modern tools and formats like xbstream and mbstream ensures that backups are performed efficiently, reducing size, downtime, and minimizing data loss.