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 of any IT infrastructure. Protecting this data from hardware failures, human error, or cyberattacks is essential to ensuring operational continuity. Backups play a key role in this context. Among the various types, incremental backups are one of the most efficient solutions for safeguarding data without unnecessarily burdening storage resources.

In MySQL, Percona Server, and MariaDB, the concept of the Log Sequence Number (LSN) is the foundation of how incremental backups work. Understanding how the LSN works is essential to fully exploit this technology and ensure 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 for large databases containing millions of rows or gigabytes of data. This problem is further exacerbated when using traditional tools like mysqldump , which, while useful for small databases or occasional exports, are not designed to efficiently handle large volumes of data or ensure minimal impact on the production system.

Tools like mysqldump work by exporting data row by row and generating a readable text file containing 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 disruptions for applications and users who rely on the system.

In comparison, advanced technologies such as MySQL Enterprise Backup, Percona XtraBackup or 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 incremental backups , which further reduces 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)?

The Log Sequence Number (LSN) is a fundamental concept in relational database systems like MySQL , Percona Server , and MariaDB , used to track and manage database changes. It is a monotonically increasing number, meaning it grows 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 incremental backups , where it allows you to identify exactly which data has changed since the last backup. Thanks to this precision, the LSN optimizes both backup and restore operations, reducing the time and space required for data protection.

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 the transaction logs and the database tablespace files .

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, the database storage engine (e.g., InnoDB) automatically records every data change in its internal log, assigning an updated Log Sequence Number (LSN) to each change. The backup tool doesn't directly monitor these changes; instead, the database engine 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

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

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, Percona XtraBackup , is one of the most popular choices for incremental backups of MySQL and Percona Server , thanks to its reliability and zero-downtime capability. Designed for hot physical backups, XtraBackup allows you to save database data without interrupting operations, a key advantage for businesses that cannot afford downtime.

Its core functionality is based on the use of the Log Sequence Number (LSN) , which XtraBackup reads directly from 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 is a fork of Percona XtraBackup , specifically designed to work with MariaDB 's unique features , making it the ideal choice for those using this platform. Like XtraBackup, Mariabackup allows for hot physical backups, keeping the database operational throughout the entire backup process.

It offers similar features, such as support for LSN-based incremental backups , which save only the data that has changed since the previous backup, optimizing storage space usage and reducing backup times. However, Mariabackup has been specifically adapted to MariaDB's unique features, 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

The Log Sequence Number (LSN) is the core of incremental backups in MySQL, Percona Server, and MariaDB. This concept allows incremental backups to be implemented, offering an efficient and scalable method for protecting data, saving significant time and space. Whether you're a large company with critical databases, a news organization managing constant updates, or a small e-commerce business requiring a robust backup strategy, adopting this technology can make the difference between rapid and effective data recovery and potentially irreparable data loss.

Tools like Percona XtraBackup and 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 filesystem level , such as those offered by ZFS or OpenZFS.

One of the most powerful features of ZFS is the ability to create virtually instantaneous snapshots of the filesystem. These snapshots are point-in-time copies of the data state, which can be created in a matter of 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 data state quickly and efficiently, minimizing the impact on system performance.

It should be noted, however, that ZFS snapshots should not be confused with backups . Snapshots 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 used 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 transferred to external or remote storage.

By combining the benefits of LSN-based incremental backup and advanced tools like Percona XtraBackup or MariaBackup with the flexibility of ZFS snapshots, you can create a highly resilient infrastructure . By carefully planning the use of both approaches, you can achieve the best balance between recovery speed, 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.