mysql数据库无法备份的问题

背景

环境采用的是mysql的数据库版本为8.0.30，这个版本使用xtrabackup进行备份的时候，有个redo log异常的问题，新功能在增加减少列之后，再使用工具备份，工具就会判断异常，然后就不进行备份

我们备份一般是使用备节点进行备份，那么可以在出现了列相关的变动以后，在备节点上面操作库优化，然后再进行备份即可

注意操作修剪表之前停止备份，修剪完之后，开启备份

实践过程

备份命令

xtrabackup --defaults-file=/etc/my.cnf --backup   --user=root --password --host=localhost --port=3306   --use-memory=1GB --rsync   --target-dir=/share/bk/backup

开启备份

[root@lab102 sync]# xtrabackup --defaults-file=/etc/my.cnf --backup  --user=root --password --host=localhost --port=3306  --use-memory=1GB --rsync  --target-dir=/share/bk/backup
2025-06-03T15:50:32.877726+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/share/mysql/data --server-id=2 --log_bin=master-bin --innodb_file_per_table=1 --innodb_buffer_pool_size=6G
2025-06-03T15:50:32.878140+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --socket=/share/mysql/data/mysqld.sock --backup=1 --user=root --password --host=localhost --port=3306 --use-memory=1GB --rsync=1 --target-dir=/share/bk/backup
Enter password:
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
250603 15:50:36  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=localhost;port=3306;mysql_socket=/share/mysql/data/mysqld.sock' as 'root'  (using password: YES).
250603 15:50:36  version_check Connected to MySQL server
250603 15:50:36  version_check Executing a version check against the server...
250603 15:50:36  version_check Done.
2025-06-03T15:50:36.654582+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /share/mysql/data/mysqld.sock
2025-06-03T15:50:36.666462+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.30
2025-06-03T15:50:36.673599+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2025-06-03T15:50:36.677808+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns
2025-06-03T15:50:36.677882+08:00 0 [ERROR] [MY-011825] [Xtrabackup] This feature is not stable and will cause backup corruption.
2025-06-03T15:50:36.677905+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Please check https://docs.percona.com/percona-xtrabackup/8.0/em/instant.html for more details.
2025-06-03T15:50:36.677923+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Tables found:
2025-06-03T15:50:36.677938+08:00 0 [ERROR] [MY-011825] [Xtrabackup] sync/aaaaa
2025-06-03T15:50:36.677954+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on all listed tables to fix this issue.

可以看到异常的日志

查看列版本

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME       | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|     1098 | sync/aaaaa |   33 |     10 |     7 | Dynamic    |             0 | Single     |            0 |                  1 |
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.00 sec)

执行处理表

mysql> OPTIMIZE TABLE sync.aaaaa;
+------------+----------+----------+-------------------------------------------------------------------+
| Table      | Op       | Msg_type | Msg_text                                                          |
+------------+----------+----------+-------------------------------------------------------------------+
| sync.aaaaa | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| sync.aaaaa | optimize | status   | OK                                                                |
+------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.06 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;
Empty set (0.00 sec)

处理完成后

[root@lab102 sync]# xtrabackup --defaults-file=/etc/my.cnf --backup  --user=root --password --host=localhost --port=3306   --use-memory=1GB --rsync  --target-dir=/share/bk/backup

再次备份即可

备注

根据官网的提示，是可以修改列合并的算法，但是这个会影响性能，尽量不要去动主库，在备库上面操作就不影响正常的情况