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.

Advertisement

2 thoughts on “Beware of your Backup before upgrading MySQL 8.0.30

  1. For what it is worth, MariaDB Server still uses the file name ib_logfile0 for the InnoDB write-ahead log, and downgrades within the same release series are expected to work. For example, downgrading from 10.6.10 to any 10.6.x version should work.

    The format of the log has been radically changed in the 10.5 release (to reduce the log volume and to simplify the parsing) and again in 10.8 (to improve the log writing speed and to simplify the parsing further).

    Liked by 3 people

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s