[root@vm10-110-65-170 ~]# mysqld --version
/usr/local/mysql/bin/mysqld Ver 8.0.31 for Linux on x86_64 (Source distribution) version影响范围 8.0.29~8.0.31
mysql> create database test;
mysql> use test;
mysql> create table jianglichun(id int primary key,name varchar(255));
mysql> select * from information_schema.innodb_tables;
+----------+------------------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+------------------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+--------------------+
| 1025 | mysql/db | 161 | 25 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1026 | mysql/user | 161 | 54 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1027 | mysql/default_roles | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1028 | mysql/role_edges | 161 | 8 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1029 | mysql/global_grants | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1030 | mysql/password_history | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1031 | mysql/func | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1032 | mysql/plugin | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1059 | mysql/help_topic | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1060 | mysql/help_category | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1062 | mysql/help_relation | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1036 | mysql/servers | 161 | 12 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1037 | mysql/tables_priv | 161 | 11 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1038 | mysql/columns_priv | 161 | 10 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1061 | mysql/help_keyword | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1040 | mysql/time_zone_name | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1041 | mysql/time_zone | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1042 | mysql/time_zone_transition | 161 | 6 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1043 | mysql/time_zone_transition_type | 161 | 8 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1044 | mysql/time_zone_leap_second | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1045 | mysql/procs_priv | 161 | 11 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1046 | mysql/component | 161 | 6 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1047 | mysql/slave_relay_log_info | 161 | 18 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1048 | mysql/slave_master_info | 161 | 36 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1049 | mysql/slave_worker_info | 161 | 16 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1050 | mysql/gtid_executed | 161 | 6 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1051 | mysql/replication_asynchronous_connection_failover | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1052 | mysql/replication_asynchronous_connection_failover_managed | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1053 | mysql/replication_group_member_actions | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1054 | mysql/replication_group_configuration_version | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1055 | mysql/server_cost | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1056 | mysql/engine_cost | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1057 | mysql/proxies_priv | 161 | 10 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1058 | mysql/ndb_binlog_index | 161 | 15 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1063 | sys/sys_config | 33 | 7 | 1 | Dynamic | 0 | Single | 0 | 0 |
| 1064 | mysql/rds_heartbeat | 33 | 5 | 2 | Dynamic | 0 | Single | 0 | 0 |
| 1065 | test/jianglichun | 33 | 6 | 3 | Dynamic | 0 | Single | 0 | 1 |
+----------+------------------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+--------------------+
TOTAL_ROW_VERSIONS
The number of row versions for the table. The initial value is 0. The value is incremented by ALTER TABLE ... ALGORITHM=INSTANT operations that add or remove columns. When a table with instantly added or dropped columns is rebuilt due to a table-rebuilding ALTER TABLE or OPTIMIZE TABLE operation, the value is reset to 0. For more information, see Column Operations.
最后一列的total_row_version是行的版本记录,每次对表的ALGORITHM=INSTANT DDL操作会使value 自增,每次使用instant方式自增后应该执行ALTER TABLE or OPTIMIZE TABLE ,否则会导致备份失败
备份失败日志:
2023-06-20T10:14:11.206180+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns
2023-06-20T10:14:11.206208+08:00 0 [ERROR] [MY-011825] [Xtrabackup] This feature is not stable and will cause backup corruption.
2023-06-20T10:14:11.206232+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Please check https://docs.percona.com/percona-xtrabackup/8.0/em/instant.html for more details.
2023-06-20T10:14:11.206247+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Tables found:
2023-06-20T10:14:11.206262+08:00 0 [ERROR] [MY-011825] [Xtrabackup] test/jianglichun
2023-06-20T10:14:11.206276+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on all listed tables to fix this issue.
执行optimize
mysql> optimize table jianglichun;
+------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+----------+----------+-------------------------------------------------------------------+
| test.jianglichun | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.jianglichun | optimize | status | OK |
+------------------+----------+----------+-------------------------------------------------------------------+
查看total_row_versions变化
mysql> select * from information_schema.innodb_tables;
+----------+------------------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+------------------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+--------------------+
| 1025 | mysql/db | 161 | 25 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1026 | mysql/user | 161 | 54 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1027 | mysql/default_roles | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1028 | mysql/role_edges | 161 | 8 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1029 | mysql/global_grants | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1030 | mysql/password_history | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1031 | mysql/func | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1032 | mysql/plugin | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1059 | mysql/help_topic | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1060 | mysql/help_category | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1062 | mysql/help_relation | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1036 | mysql/servers | 161 | 12 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1037 | mysql/tables_priv | 161 | 11 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1038 | mysql/columns_priv | 161 | 10 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1061 | mysql/help_keyword | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1040 | mysql/time_zone_name | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1041 | mysql/time_zone | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1042 | mysql/time_zone_transition | 161 | 6 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1043 | mysql/time_zone_transition_type | 161 | 8 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1044 | mysql/time_zone_leap_second | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1045 | mysql/procs_priv | 161 | 11 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1046 | mysql/component | 161 | 6 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1047 | mysql/slave_relay_log_info | 161 | 18 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1048 | mysql/slave_master_info | 161 | 36 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1049 | mysql/slave_worker_info | 161 | 16 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1050 | mysql/gtid_executed | 161 | 6 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1051 | mysql/replication_asynchronous_connection_failover | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1052 | mysql/replication_asynchronous_connection_failover_managed | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1053 | mysql/replication_group_member_actions | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1054 | mysql/replication_group_configuration_version | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1055 | mysql/server_cost | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1056 | mysql/engine_cost | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1057 | mysql/proxies_priv | 161 | 10 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1058 | mysql/ndb_binlog_index | 161 | 15 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1063 | sys/sys_config | 33 | 7 | 1 | Dynamic | 0 | Single | 0 | 0 |
| 1064 | mysql/rds_heartbeat | 33 | 5 | 2 | Dynamic | 0 | Single | 0 | 0 |
| 1067 | test/jianglichun | 33 | 6 | 5 | Dynamic | 0 | Single | 0 | 0 |
+----------+------------------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+--------------------+
控制台发起备份,备份成功
2023-06-20T11:46:51.735056+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Compressing and streaming file <STDOUT>
2023-06-20T11:46:51.735288+08:00 0 [Note] [MY-011825] [Xtrabackup] Compressing and streaming <STDOUT>
2023-06-20T11:46:51.735334+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Compressing and streaming file <STDOUT>
2023-06-20T11:46:52.735620+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (22699217) to (22717796) was copied.
2023-06-20T11:46:52.952734+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
使用inplace方式ddl
mysql> alter table jianglichun add column k2 int after k1,ALGORITHM=inplace;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc jianglichun;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(255) | YES | | NULL | |
| k1 | int | YES | | NULL | |
| k2 | int | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from information_schema.innodb_tables;
+----------+------------------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+------------------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+--------------------+
| 1025 | mysql/db | 161 | 25 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1026 | mysql/user | 161 | 54 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1027 | mysql/default_roles | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1028 | mysql/role_edges | 161 | 8 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1029 | mysql/global_grants | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1030 | mysql/password_history | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1031 | mysql/func | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1032 | mysql/plugin | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1059 | mysql/help_topic | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1060 | mysql/help_category | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1062 | mysql/help_relation | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1036 | mysql/servers | 161 | 12 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1037 | mysql/tables_priv | 161 | 11 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1038 | mysql/columns_priv | 161 | 10 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1061 | mysql/help_keyword | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1040 | mysql/time_zone_name | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1041 | mysql/time_zone | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1042 | mysql/time_zone_transition | 161 | 6 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1043 | mysql/time_zone_transition_type | 161 | 8 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1044 | mysql/time_zone_leap_second | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1045 | mysql/procs_priv | 161 | 11 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1046 | mysql/component | 161 | 6 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1047 | mysql/slave_relay_log_info | 161 | 18 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1048 | mysql/slave_master_info | 161 | 36 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1049 | mysql/slave_worker_info | 161 | 16 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1050 | mysql/gtid_executed | 161 | 6 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1051 | mysql/replication_asynchronous_connection_failover | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1052 | mysql/replication_asynchronous_connection_failover_managed | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1053 | mysql/replication_group_member_actions | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1054 | mysql/replication_group_configuration_version | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1055 | mysql/server_cost | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1056 | mysql/engine_cost | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1057 | mysql/proxies_priv | 161 | 10 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1058 | mysql/ndb_binlog_index | 161 | 15 | 4294967294 | Dynamic | 0 | General | 0 | 0 |
| 1063 | sys/sys_config | 33 | 7 | 1 | Dynamic | 0 | Single | 0 | 0 |
| 1064 | mysql/rds_heartbeat | 33 | 5 | 2 | Dynamic | 0 | Single | 0 | 0 |
| 1068 | test/jianglichun | 33 | 7 | 6 | Dynamic | 0 | Single | 0 | 0 |
+----------+------------------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+--------------------+
控制台发起备份成功
mysql8.0版本使用instant ddl方式增加列、xbk备份失败记录
最新推荐文章于 2024-04-22 11:03:14 发布