Beware of your Backup before upgrading MySQL 8.0.30

Mydbops
Oct 13, 2022
9
Mins to Read
All

Upgrading to MySQL 8.0.30 brings valuable features, but a crucial detail could trip you up: backups with Xtrabackup. This blog post dives into the version incompatibility and guides you towards successful backups after the upgrade.

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.

Safeguard your MySQL data with confidence! Mydbops offers open-source database management tools to streamline backups, upgrades, and other critical tasks. Explore Mydbops' services and empower your MySQL administration.

{{cta}}

No items found.

About the Author

Mydbops

Subscribe Now!

Subscribe here to get exclusive updates on upcoming webinars, meetups, and to receive instant updates on new database technologies.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.