xtrabackup主备报错cannot open file ibdata2

项目场景：

MySQL主从复制，xtrabackup备份还原
mysql-8.0.25
percona-xtrabackup-80-8.0.13

---

问题描述

主从复制时，使用xtrabackup准备数据ok，但恢复数据报错，报错如下：

[root@slave mysql]# xtrabackup --copy-back  --target-dir=/opt/111/ --defaults-file=/data2/mysql/conf/my.cnf 
xtrabackup: Error: --defaults-file must be specified first on the command line
[root@slave mysql]# xtrabackup --copy-back  --target-dir=/opt/111/ --defaults-file=/data2/mysql/conf/my.cnf ^C
[root@slave mysql]# xtrabackup --defaults-file=/data2/mysql/conf/my.cnf --copy-back  --target-dir=/opt/111/
xtrabackup: recognized server arguments: --server-id=111 --datadir=/data2/mysql/data --tmpdir=/data2/mysql/tmp/ --open_files_limit=65535 --innodb_data_home_dir=/data2/mysql/data --innodb_data_file_path=ibdata1:512M;ibdata2:512M:autoextend --innodb_buffer_pool_size=256M --innodb_flush_log_at_trx_commit=1 --innodb_io_capacity=600 --innodb_log_buffer_size=8M --innodb_log_file_size=200M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=85 --innodb_read_io_threads=8 --innodb_write_io_threads=8 --innodb_file_per_table=1 --innodb_undo_directory=/data2/mysql/data --innodb_log_group_home_dir=/data2/mysql/data --log_bin=/data2/mysql/binlog/binlog --log-bin-index=/data2/mysql/binlog/binlog.index 
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/opt/111/ 
xtrabackup version 8.0.13 based on MySQL server 8.0.20 Linux (x86_64) (revision id: fdf0f4c)
240120 15:57:26 [01] Copying undo_001 to /data2/mysql/data/undo_001
240120 15:57:26 [01]        ...done
240120 15:57:26 [01] Copying undo_002 to /data2/mysql/data/undo_002
240120 15:57:26 [01]        ...done
240120 15:57:26 [01] Copying ib_logfile0 to /data2/mysql/data/ib_logfile0
240120 15:57:26 [01]        ...done
240120 15:57:26 [01] Copying ib_logfile1 to /data2/mysql/data/ib_logfile1
240120 15:57:26 [01]        ...done
240120 15:57:26 [01] Copying ibdata1 to /data2/mysql/data/ibdata1
240120 15:57:26 [01]        ...done
xtrabackup: File 'ibdata2' not found (OS errno 2 - No such file or directory)
Operating system error number 2 in a file operation.
The error means the system cannot find the path specified.
[01] error: cannot open file ibdata2
[01] Error: copy_file() failed.

File ‘ibdata2’ not found (OS errno 2 - No such file or directory) 这里我查了说是配置文件的问题

百度解决办法说：实际备份出来就没有 ibdata2.于是在从库中的配置修改一下
innodb data file path=ibdata1:1G:ibdata2:1G:autoextend
改为
innodb data file path=ibdata1:1G:autoextend
但是我重启数据库发现挂了，报错内容是初始化有问题。

---

成功解决方案：

把这一行注释掉，让他用默认的配置innodb data file path

再次进行恢复动作：先删除/mysql/data目录下的数据和/mysql/binlog目录下的数据，再用xtrabackup命令恢复
看到completed ok!代表成功

---

重启数据库报错

查看错误日志

[root@slave ~]# tail -f /data2/mysql/log/mysqld.err 
2024-01-20T10:50:21.326352Z mysqld_safe Starting mysqld daemon with databases from /data2/mysql/data
2024-01-20T10:50:21.558105Z 0 [Warning] [MY-011069] [Server] The syntax '--master-info-file' is deprecated and will be removed in a future release.
2024-01-20T10:50:21.558216Z 0 [System] [MY-010116] [Server] /usr/local/mysql8.0.25/bin/mysqld (mysqld 8.0.25) starting as process 7067
2024-01-20T10:50:21.597694Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-01-20T10:50:22.713684Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-01-20T10:50:22.745539Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2024-01-20T10:50:22.745918Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2024-01-20T10:50:22.746243Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-01-20T10:50:23.258644Z 0 [System] [MY-010910] [Server] /usr/local/mysql8.0.25/bin/mysqld: Shutdown complete (mysqld 8.0.25)  MySQL Community Server - GPL.
2024-01-20T10:50:23.393694Z mysqld_safe mysqld from pid file /data2/mysql/tmp/mysqld.pid ended

Different lower_case_table_names settings for server (‘1’) and data dictionary (‘0’)意思是主库和从库的lower_case_table_names不一样

原因分析

进入主库查看lower_case_table_names变量的值：

mysql> show variables like "%lower_case_table_names%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.07 sec)

从库配置文件：

[root@slave ~]# cat /data2/mysql/conf/my.cnf |grep lower_case_table_names
lower_case_table_names          = 1

成功解决方案：

把从库的lower_case_table_names值改为0，重启成功。