MySQL 8 Upgrade and Implications on BackupRecently, we performed a database engine major version upgrade in one of our customers’ environments from MySQL 5.7.26 to 8.0.27. After this version upgrade, we experienced issues with backups and replication for one of the nodes.

In this article, I will explain these issues in detail and recommend a way to take backups from a replication environment.

To begin with, we upgraded all the database nodes from 5.7.26 to 8.0.27 and as a recommended way we have a backup set-up from one of the replica nodes. Physical backups are being taken using Percona XtraBackup (PXB) so it does not lock the database during the backup.

With MySQL 5.7, a backup was taken using PXB 2.4. Due to the new data dictionaries, redo log and undo log in MySQL 8.0, we also upgraded PXB to 8.0.27 to avoid compatibility issues, after upgrading the database to 8.0.

Before we discuss our original issue in detail, let us look at some of the options that should be used with PXB when you are taking backups from a replication environment to ensure consistency. These options are being used in this case as well.

–slave-info

It prints and stores the binary log position of the source server which can be useful to set up new replicas from the source.

–safe-slave-backup

This option is useful to handle temporary tables on replicas and when used it stops the SQL thread and waits until there are no temporary tables before taking a backup.

–lock-ddl

This option blocks all the DDL operations on replicas when a backup is running, so any DDL events do not corrupt the backups. Any DML events continue to occur, and only DDL events are blocked.

Now coming back to our original issue, after the database upgraded successfully to 8.0.27, we noticed several issues related to backups. One of the major issues that we noticed was replication failure on the replica node during the backup.

We identified below things to make sure that nothing changed after the upgrade:

  1. No change in the backup command
  2. All the tables were InnoDB as before and no engine change was performed

Further, we noticed that whenever PXB starts taking backups, the replication stops on the backup server. Upon checking further, we noticed that only the SQL thread gets stopped when the backup starts, and the IO thread keeps running. This pointed us to the fact that this replication break is happening because of PXB running with the –safe-slave-backup option, which is a recommended way of taking backups from the replica node as mentioned earlier.

However, we noticed that in the 5.7 environment, the backup was also running with the –safe-slave-backup option, and backups were running fine at that time and replication also had no issues. So with this major version upgrade, the only change we did with respect to the backup was upgrading the PXB packages to 8.0.27 from PXB 2.4.20.

In order to get more insights, I did a simple test and performed the backup using PXB 8.0.27 and PXB 2.4.26. During this test, I noticed that with PXB 8.0, –safe-slave-backup stops the SQL thread right after the backup starts (even before copying InnoDB files). In this test scenario, a complete backup takes around 20 seconds to finish and the SQL thread was stopped for the entire period of time.

However, with the PXB 2.4 version, the SQL thread gets stopped only while copying the non-InnoDB files, and when it takes a backup of InnoDB files SQL thread was not stopped. So for the entire duration of 20 seconds of backup, the SQL thread was stopped only for four seconds.

The PXB safe-slave-backup option is designed to ensure consistent backups of a database replica. In versions prior to 8.0.22, this option would stop the SQL slave thread after backing up InnoDB tables and before copying non-InnoDB data files. However, this could result in a corrupt backup if a replicated DDL (Data Definition Language) statement was executed on the backup replica during the backup process.

To address this issue, the behavior of the safe-slave-backup option was changed in version 8.0.22. Now, when this option is specified, the “STOP SLAVE SQL_THREAD” command is issued at the beginning of the backup process. This prevents any replicated DDL statements from being executed during the backup, ensuring a consistent and reliable backup.

This is the reason that after upgrading the PXB packages to 8.0, replication was getting stopped when the backup process started and it stopped the SQL thread even though all the tables were InnoDB.

Conclusion

As of Percona XtraBackup 8.0.22-15.0, using a safe-slave-backup option stops the SQL replica thread before copying the InnoDB files. This behavior prevents any replicated DDL statements from being executed during the backup, ensuring a consistent and reliable backup.

Percona XtraBackup is a free, online, open source, complete database backup solution for all versions of Percona Server for MySQL and MySQL.

Try Percona XtraBackup Today

Subscribe
Notify of
guest

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Tamas

It is unclear or something is wrong in the article.
First you say it was failing than it was not. What was the fix?

1
Reply