Log Sequence Number or LSN: The Concept Behind Incremental Backup in MySQL, Percona Server and MariaDB

Data management is one of the most crucial aspects in any IT infrastructure. Protecting this data from hardware failures, human errors or cyber attacks is essential to ensure continuity of operations. In this context, backups play a key role. Among the different types, incremental backups They are one of the most efficient solutions for safeguarding data without unnecessarily burdening storage resources.

In MySQL, Percona Server and MariaDB, the concept of Log Sequence Number (LSN) is the basis of how incremental backups work. Understanding how LSN works is essential to taking full advantage of this technology and ensuring maximum data security.

Why choose incremental backups?

An incremental backup saves only the data that has changed since the previous backup, significantly reducing the time and space required compared to a full backup. But when and why should you choose this mode?

1. Space saving

Databases can grow rapidly, especially in environments where day-to-day operations generate large volumes of data. It’s easy to go from a few gigabytes to hundreds of gigabytes in a short time, and from there to terabytes is a short step, especially in environments with a constant flow of changes or new information. Such rapid growth can put a strain on available resources, making data management and backup a significant challenge.

2. Increased backup frequency

In some scenarios, such as a newspaper that publishes hundreds of articles every day, continuous and frequent data updating is not only important, but absolutely critical to ensure continuity of operations and protection of the work done. Every article, every update or change represents a contribution to the value of the organization, and losing even just a few hours of work can translate into significant damage, both financially and to reputation.

Let's imagine a human error that accidentally leads to the deletion of the database at 20:00 PM, after a busy day of publishing. In such a case, being able to restore data updated up to a few minutes before the accident becomes essential to avoid compromising the work of an entire editorial team. Without an adequate backup strategy, the risk would be to lose not only all the articles published during the day, but also any changes or revisions made to existing articles, with devastating consequences.

3. Reduce backup time

Full backups can take hours, especially when dealing with large databases containing millions of rows or gigabytes of data. This problem is further amplified when using traditional tools such as mysqldump, which, while useful for small databases or occasional exports, are not designed to efficiently handle large volumes of data or to ensure minimal impact on the production system.

Tools like mysqldump They work by exporting the data row by row and generating a readable text file that contains the SQL statements to rebuild the database. While this approach may seem simple and straightforward, it is extremely slow for large databases. Additionally, mysqldump often requires locking tables or slows database performance during the backup process, causing significant disruption to applications and users that rely on the system.

In comparison, advanced technologies such as MySQL Enterprise Backup, Contact XtraBackup o MariaBackup offer incomparable efficiency.

In terms of speed, in the image above you can see backup times and restore times that are about 50 to 80 times faster than logical backups done with MySQLdump. The reported speed is roughly equivalent for the three tools mentioned.

These tools perform physical file-level backups directly from disk, without having to interpret and recreate the data in SQL format. This approach allows for significantly faster performance than mysqldump, especially when dealing with very large databases. In addition, both Percona XtraBackup and MariaBackup are designed to work without interrupting database operations, allowing for “hot” backups that require no downtime and ensure that applications can continue to run without interruption.

Another significant advantage of these tools is their ability to support the incremental backups, which further reduce the time and resources required compared to traditional full backups. Instead of saving the entire database, tools like XtraBackup and MariaBackup focus only on the changes that have occurred since the last backup, thus reducing both the duration of the backup process and the impact on storage space.

What is Log Sequence Number (LSN)?

Il Log Sequence Number (LSN) is a fundamental concept in relational database systems such as MySQL, Tap Server e MariaDB, used to track and manage changes made to the database. It is a monotonically increasing number, which means that it increases continuously and without interruption, representing the logical position of each operation in the database storage system. Each action that modifies data, such as an insert, update, or delete, generates a new LSN, which is recorded in the transaction logs and tablespace files.

This number not only uniquely identifies the sequence of operations, but is also crucial for several internal database processes. For example, the LSN is used to synchronize replicas, recover the database after an unexpected shutdown, and most importantly, to manage the incremental backups, where it allows you to identify exactly which data has changed since the last backup. Thanks to this precision, the LSN allows you to optimize both save and restore operations, reducing the time and space required to protect data.

Every change to the database, such as inserting, updating, or deleting data, increments the LSN. This number is essential for:

• Track changes made to the database.
• Determine what data should be included in an incremental backup.
• Coordinate data recovery during recovery.

The LSN is managed internally by the storage engine (e.g., InnoDB) and is located in both transaction log that in tablespace file of the database.

How does LSN-based incremental backup work?

When you perform an incremental backup, the backup software uses the LSN to identify changes made to the database since the last backup. Here's how the process works:

1. Initial full backup

The process always begins with a full backup, which is the basis for any incremental backup strategy. This first backup captures all the data in the database at any given point in time, along with the current LSN, which represents the state of the database at the time of the backup. It is essential to perform this step carefully, as a reliable full backup is the starting point for all subsequent incremental backups. A failure or corruption at this stage could jeopardize the entire recovery process. To ensure maximum data integrity, tools such as Percona XtraBackup or MariaBackup can perform “hot” backups, keeping the database operational while the data is being saved.

2. Recording changes

After a full backup is performed, the database storage engine (e.g. InnoDB) automatically records every data change in its internal log, assigning a Log Sequence Number (LSN) updated with each change. It is not the backup tool that directly monitors these changes, but rather the database engine that tracks updates via the transaction log and tablespaces.

When performing an incremental backup, the backup tool (such as Percona XtraBackup or MariaBackup) simply reads the LSN recorded in the last backup taken. Using this LSN as a reference point, the software identifies data that has changed or been added from that LSN up to the current LSN. This allows the tool to capture only the relevant changes, without having to scan or save the entire contents of the database. In other words, the backup tool knows where to start reading data (i.e., the reference LSN) and continues saving only the information it needs until the new state of the database.

3. Incremental backup

When performing an incremental backup, the software uses the LSN to identify only data that has changed or been added since the last backup, avoiding duplicating information that has already been saved. This comparison of the current LSN to the one recorded in the last backup allows you to dramatically reduce the amount of data to be saved, speeding up the process and saving storage space. For example, if the initial full backup recorded an LSN of 1000 and at the time of the incremental backup the LSN is increased to 1500, the software will save only the data associated with LSNs between 1001 and 1500. This approach maximizes efficiency without sacrificing the granularity of the data saved. Additionally, incremental backups can be scheduled at regular intervals, allowing you to maintain an accurate history of changes without interrupting database operations.

4. Database Restore

Database recovery is the final and crucial step that demonstrates the value of a well-designed backup strategy. During this phase, incremental backups are applied sequentially to the initial full backup. This process uses the LSN to determine the correct order in which to apply changes, thereby reconstructing the state of the database at a specific point in time. For example, if a database has a full backup with LSN 1000 and three incremental backups with LSNs 1500, 2000, and 2500, recovery follows this sequence to ensure no changes are lost. It is essential that each backup file is intact and available; the loss of a single incremental backup would break the chain, making full recovery impossible. Tools like Percona XtraBackup or MariaBackup automate much of this process, ensuring that recovery is accurate and fast, while minimizing downtime for the organization.

Incremental backup tools

There are several tools to manage incremental backups in MySQL, Percona Server and MariaDB. Some of the more popular ones include:

1. MySQL Enterprise Backup

Powered by Oracle, MySQL Enterprise Backup is an advanced solution that allows you to perform incremental backups based on the LSN, thus optimizing data saving operations. This tool is designed to provide a hot physical backup, which does not interrupt database operations, making it ideal for environments where downtime is not acceptable.

In addition to support for incremental backups, MySQL Enterprise Backup offers additional features such as backup compression to reduce storage space usage, encryption to increase data security, and support for point-in-time backups, which allows you to restore your database to a specific state.

However, this powerful option requires a commercial license, making it more suitable for businesses that can afford the cost of ensuring maximum reliability and performance in managing backups. For those who need a robust solution with official support and a wide range of features, MySQL Enterprise Backup is a top choice.

2. Percona XtraBackup

This open-source tool, Contact XtraBackup, is one of the most popular choices for incremental backups of MySQL e Tap Server, thanks to its reliability and ability to operate without downtime. Designed to perform “hot” physical backups, XtraBackup allows you to save database data without interrupting operations, a key advantage for companies that cannot afford interruptions.

Its main functionality is based on the use of the Log Sequence Number (LSN), which XtraBackup reads directly from the log and tablespace files to determine exactly what data to include in the backup. This approach ensures that only the most recent changes are saved, making incremental backups extremely space and time efficient.

Due to its open-source nature, Percona XtraBackup is a highly accessible and community-supported solution, while offering enterprise-level performance and flexibility. For those looking for a free yet powerful solution to manage incremental backups, XtraBackup is an excellent option.

How does Percona XtraBackup work?

• Performs a physical backup of data without locking the database.
• Use the LSN to identify changes.
• Supports point-in-time recovery by combining full and incremental backups.

3. Mariabackup

MariaBackup It's a fork of Contact XtraBackup, specifically designed to work with the unique features of MariaDB, which makes it the ideal choice for those who use this platform. Just like XtraBackup, Mariabackup allows you to perform “hot” physical backups, keeping the database operational during the entire saving process.

It offers similar features, such as support for incremental backups based on the LSN, which allow you to save only the data that has changed since the previous backup, optimizing the use of storage space and reducing execution times. However, Mariabackup has been specifically adapted to the peculiarities of MariaDB, ensuring full compatibility with its ecosystem and supporting the latest innovations introduced by the MariaDB development team.

As an open-source software, Mariabackup is freely accessible and supported by an active community, making it a versatile and reliable option for managing backups in MariaDB-based environments. For those who need a native MariaDB backup solution, Mariabackup is a great value option.

Advantages and challenges of incremental backup

LSN-based incremental backups offer many advantages, but they also present some challenges.

Benefits

• Efficiency in space and time: Saving only the changed data significantly reduces the workload.
• Quick recovery: Allows you to quickly restore data in case of emergency.
• Downtime Minimization: Does not interrupt database operations.

Challenges

• Complexity of recovery: During recovery, you need to apply all incremental backups correctly, which can take time and attention.
• Accurate management of backup files: Losing any of the incremental backups makes a full restore impossible.
• Scripting skills for proper use of the tool: Although tools like Percona Xtrabackup, MariaBackup and similar are very complete, only a specific integration in scripting languages ​​such as (Bash Scripting) can lead to a profitable and optimal use of the tools without errors.

Example use case: a newspaper

Imagine a newspaper that manages a database with hundreds of articles published every day. In this scenario:

• Frequency of changes: Articles are continuously created, updated and published.
• Importance of data: Losing data, even for a short period, would cause significant damage.
• Need for frequent backups: A backup every 24 hours is not enough; the company must ensure frequent backups without impacting resources.

Using LSN-based incremental backups, the header can:

1. Perform a full backup every day.
2. Schedule incremental backups every hour to safeguard the most recent changes.
3. Quickly restore data in the event of failure or human error, minimizing information loss.

Conclusion

Il Log Sequence Number (LSN) is the heart of incremental backup in MySQL, Percona Server, and MariaDB. This concept enables incremental backups to be implemented, providing an efficient and scalable method of protecting data, while saving significant time and space. Whether you are a large company with critical databases, a news organization that manages constant updates, or a small e-commerce business that needs a robust backup strategy, adopting this technology can make the difference between fast and effective data recovery and potentially irreparable data loss.

Tools like Contact XtraBackup e MariaBackup, thanks to their ability to work without downtime and support LSN-based incremental backup, are essential for building a reliable infrastructure.

However, for particularly complex scenarios or for very large datasets (on the order of hundreds of gigabytes or terabytes), it is possible to integrate complementary approaches based on the file system, such as those offered by ZFS o OpenZFS.

One of the most powerful features of ZFS is the ability to create snapshot virtually instantaneous filesystem. These snapshots are point-in-time copies of the state of the data, which can be created in a few milliseconds or seconds regardless of the size of the dataset.. This makes them particularly useful for scenarios where you need to capture the state of a very large database at a high frequency. For example, on datasets exceeding a terabyte, a ZFS snapshot allows you to freeze the state of the data quickly and efficiently, while minimizing the impact on system performance.

It should be emphasized, however, that the ZFS snapshots should not be confused with backupsSnapshots are tightly coupled to the filesystem on which they were created and do not protect data in the event of catastrophic hardware failure, disk corruption, or accidental deletion of the ZFS pool. For this reason, ZFS snapshots are best suited as a temporary protection tool or as a quick recovery point for recent software errors or accidental deletions. For complete protection, snapshots should be combined with a backup strategy that includes copies of the data migrated to external or remote storage.

By combining the benefits of LSN-based incremental backup and advanced tools such as Percona XtraBackup or MariaBackup with the flexibility of ZFS snapshots, you can create an extremely resilient infrastructure.. By carefully planning the use of both approaches, you can achieve the best balance between rapid recovery, data protection, and resource optimization.

Investing in these technologies is not just a good practice, but a necessity for all those companies that consider data as their most valuable asset. The combination of incremental backups, instant snapshots and distributed storage strategies represents a modern and complete solution to ensure business continuity and data security in any scenario.