I am trying to understand the process of how percona does hot backup. from what in understand is, they use innodb crash recovery mechanism and execute the following steps.

  1. store/remember start LSN from redo logs
  2. copy data files
  3. execute LOCK BINLOG FOR BACKUP: to block all operations that might change either binary log position
  4. copying the REDO log files and fetch the binary log coordinates
  5. completed xtrabackup will unlock the binary log and tables.

I want to understand what is the role of LSN from redo logs, can't we directly check the binary log position at step 1?

What about the end LSN, is it not used anywhere in the process?

1 Answer 1


The LSN (Log Sequence Number) is analogous to binary log position, but the redo log is a different log. It applies only to InnoDB pages, and it contains physical diffs of the pages affected. During crash recovery, it can reconstruct pages by applying the diffs to the original pages. It can also detect if a change has already been applied to a page, because pages are stamped with the LSN. It handles the case of pages that were flushed before the crash, which don't necessarily occur in any order. So starting from the earlier LSN, it doesn't need to apply every change in the redo log.

The binary log applies to tables of any storage engine, and it contains logical changes. It may contain row based events or statement based events. It is not used for crash recovery. Binary logs are strictly ordered, so if you use it for syncing a database (e.g. replication or point-in-time recovery), then it assumes all changes are to be applied from the starting position you give it.

There's no need for Percona XtraBackup to record the last LSN, because crash recovery just processes all the redo log up to the end.

[I] think the process of a commit would be as: commit command -> update data in innodb buffer (in memory) -> append to (redo log file + binary log) -> respond success to client

No, that's not correct. Keep in mind transactions have no limit on the number of rows modified, or the number of changes. So the changes must be queued somehow to allow the size of a transaction to be large.

In the case of the binary log, changes are written to a binary log cache. Each session is allocated its own binary log cache. This is in memory by default, but if the transaction is larger than the cache in memory, it can be copied to disk. As you execute INSERT/UPDATE/DELETE, these changes are appended to the binary log cache for the respective session.

On COMMIT, all the changes are finally copied from the session's binary log cache to the binary log. Therefore changes do not appear in the binary log until COMMIT.

The redo log is separate, and this is implemented only in the InnoDB storage engine. When you execute INSERT/UPDATE/DELETE, pages of data in the buffer pool are modified. At that time, these uncommitted changes to pages are also written to the redo log. Since the redo log has fixed size, you could make too many changes to fit in the redo log, and it wraps around to the beginning. To prevent losing changes in the event of a crash, modified pages in the buffer pool must be flushed to disk before the redo log wraps around.

On COMMIT, InnoDB does not need to copy any logs or flush any modified pages. Pages may remain modified in the buffer pool. Only the transaction is noted as being committed, by writing a small record written to the redo log. Therefore if crash recovery is necessary, it knows which pages contain committed changes. Pages that were not committed at the time of the crash are marked with a transaction id which is not recorded as committed in the redo log, so those pages don't need to be recovered.

[I] want to understand at which point would this redo log would be helpful and what is use of LSN in this.

The redo log is written to continuously, as you do INSERT/UPDATE/DELETE, and it is written to during COMMIT to mark each transaction as committed. But the redo log is not read, except during crash recovery. Its sole purpose is in reconstructing modified pages in the buffer pool.

The use of the LSN is so crash recovery knows where to start processing the redo log. For example, if most of the modified pages were already flushed at the time of the crash, there is no need to process the redo log prior to the oldest page that was still modified in the buffer pool. Crash recovery can skip directly to the LSN in the redo log corresponding to the oldest modified page. This helps crash recovery run a little faster.

Percona XtraBackup uses the same redo log processing code, because preparing a backup is similar to crash recovery. When Percona XtraBackup starts copying pages for the backup, some of those pages may have modified copies in the buffer pool. The copying takes a while, and some pages may be flushed or may become modified during that time, as subsequent query traffic executes INSERT/UPDATE/DELETE. When the XtraBackup prepare runs, it must inspect all the redo log back to the oldest modified page corresponding to the LSN at the time of the start of the backup, because any of those could be "lost" pages, i.e. only the page from disk was copied to the backup, without the modified version in the buffer pool.

when is the updated data persisted in tables? (is it when redo log is flushed?)

Modified pages are flushed to the tablespace continually. Read https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-flushing.html

Hopefully, the rate of flushing modified pages is at least fast enough that the redo log doesn't get 100% full. InnoDB does not allow un-flushed modified pages to exist in the buffer pool if they are not recoverable by the redo logs in case of a crash.

If the redo log reaches 100% full, then writes to the redo log must pause until some flushing of pages can catch up. This means all INSERT/UPDATE/DELETE action is blocked temporarily. You would have to overload your MySQL instance pretty badly to get to this state (i.e. load it with a higher rate of writes than it can sustain).

  • need some more help in understanding above. i think the process of a commit would be as: commit command -> update data in innodb buffer (in memory) -> append to (redo log file + binary log) -> respond success to client want to understand at which point would this redo log would be helpful and what is use of LSN in this . Commented Nov 21, 2023 at 17:15
  • when is the updated data persisted in tables? (is it when redo log is flushed?) what happens when a un-committed transaction is going on and redo log reaches the fixed size, are the modified pages in buffer pool still flushed to disk? (given the transaction could rollback or the system could crash at this point) Commented Nov 22, 2023 at 15:08
  • 1
    I've written replies to your comments, but I suggest you should just read the InnoDB section of the manual straight through to understand it further. Also read the book High Performance MySQL 3rd Edition which explains a lot of internals of InnoDB (at least as of MySQL 5.5). That book is how I learned about InnoDB. Commented Nov 22, 2023 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.