清理影子表

1 影子表的由来

ONES 版本升级可能会做数据库表的切换，切换操作是先创建临时表、在临时表中执行变更，再做临时表和正式表的更名，故数据库会存在升级前的备份临时表，俗称影子表。 在下一次升级前可清理影子表，减少磁盘空间消耗和性能消耗。

2 清理操作

登录mysql数据库

ones-ai-k8s.sh

# 获取 mysqlRootPassword
kubectl exec -n ones-installer -it $(kubectl -n ones-installer get po  | grep installer-api | awk {'print $1'}) -c installer-api -- bash -c "cd /data/ones/ones-ai-k8s && make get_value KEY=mysqlRootPassword"

kubectl -n ones exec -it mysql-cluster-mysql-0  bash

mysql -uroot -p 
# 输入密码

执行清理命令

use project
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_schema = 'project' AND table_name LIKE '_m_2%';
# 根据查询结果，执行DROP语句

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_schema = 'project' AND table_name LIKE '_o_2%';
# 根据查询结果，执行DROP语句

use wiki
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_schema = 'wiki' AND table_name LIKE '_m_2%';
# 根据查询结果，执行DROP语句
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_schema = 'wiki' AND table_name LIKE '_o_2%';
# 根据查询结果，执行DROP语句