Beware of your Backup before upgrading MySQL 8.0.30

MySQL 8.0.30 was released on 26-07-2022. It has a good list of features.

• Generated Invisible Primary Key
• Dynamic Redo log Sizing
• Instruments to monitor group replication memory
• Support for Ubuntu 22.04

A most notable feature for Database Administrators and database reliability engineers is Dynamic redo logging.

The more widely adopted open-source backup tool is Xtrabackup. The change in the redo log design has affected this hot backup tool.

2022-10-04T18:40:08.211998+05:30 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql 
2022-10-04T18:40:08.212264+05:30 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --password=* --user=sysbench --password=* --backup=1 --target-dir=/root/backup --no-server-version-check=1 
xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
	LANGUAGE = (unset),
	LC_ALL = (unset),
	LC_CTYPE = "UTF-8",
	LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
220908 18:40:08  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'sysbench'  (using password: YES).
220908 18:40:08  version_check Connected to MySQL server
220908 18:40:08  version_check Executing a version check against the server...
220908 18:40:08  version_check Done.
2022-10-04T18:40:08.322073+05:30 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: sysbench, password: set, port: not set, socket: not set
2022-10-04T18:40:08.330837+05:30 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.30
2022-10-04T18:40:08.334162+05:30 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2022-10-04T18:40:08.336817+05:30 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2022-10-04T18:40:08.336864+05:30 0 [Note] [MY-011825] [Xtrabackup] cd to /var/lib/mysql
2022-10-04T18:40:08.336886+05:30 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 0, set to 1024
2022-10-04T18:40:08.337255+05:30 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration:
2022-10-04T18:40:08.337266+05:30 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2022-10-04T18:40:08.337274+05:30 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2022-10-04T18:40:08.337314+05:30 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./
2022-10-04T18:40:08.337322+05:30 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2
2022-10-04T18:40:08.337333+05:30 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 50331648
2022-10-04T18:40:08.337941+05:30 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2022-10-04T18:40:08.339067+05:30 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2022-10-04T18:40:08.515699+05:30 0 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-10-04T18:40:08.515754+05:30 0 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-10-04T18:40:08.515766+05:30 0 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2022-10-04T18:40:08.515778+05:30 0 [ERROR] [MY-012646] [InnoDB] File ./ib_logfile0: 'open' returned OS error 71. Cannot continue operation
2022-10-04T18:40:08.515795+05:30 0 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.

The backup will fail with the error code

“2022-10-04T18:40:08.515778+05:30 0 [ERROR] [MY-012646] [InnoDB] File ./ib_logfile0: ‘open’ returned OS error 71. Cannot continue operation”

Xtrabackup is seeking ib_logfile0 , the new design (8.0.30) do not have the ib_logfile0.

The redo log format change in 8.0.20 has affected the previous versions of Xtrabackup. The reason for redo log format change in 8.0.20

• InnoDB: Redo log records for modifications to undo tablespaces increased in size in MySQL 8.0 due to a change in undo tablespace ID values, which required additional bytes. The change in redo log record size caused a performance regression in workloads with heavy write I/O. To address this issue, the redo log format was modified to reduce redo log record size for modifications to undo tablespaces. (Bug #29536710)

https://forums.percona.com/t/mysql-8-0-20-support/7584/2

Post this change Xtrabackup has made the Xtrabackup Version match with respect to the Source MySQL version. So by default Xtrabackup validates the MySQL version before the backup initialization. A new variable –no-server-version-check was introduced to disable the version checks.

https://docs.percona.com/percona-xtrabackup/8.0/using_xtrabackup/comparison.html

Key takeaways:

As MySQL 8.0 minor version upgrades are ONE WAY. Please do a vital check of backup tools before upgrading MySQL minor version in productions.