StarRocks 排查单副本表

StarRocks 排查单副本表

方式1 查询元数据，检查分区级的副本数

# 方式一 查询元数据，检查分区级的副本数
with v_tmp1 as (selectTABLE_ID, PARTITION_ID, tablet_id, count(*) as cntfrominformation_schema.be_tabletsgroup byTABLE_ID, PARTITION_ID, tablet_idhavingcount(*) = 1
) 
select /*+ set_var(query_timeout = 20) */distinct t1.TABLE_SCHEMA, t1.TABLE_NAME
from information_schema.tables_config t1 
join v_tmp1 t2 on t1.TABLE_ID = t2.TABLE_ID
;

方式2 SHOW PARTITIONS命令查看 ReplicationNum

# 方式二 遍历所有库表SHOW PARTITIONS命令查看 ReplicationNum
SHOW  PARTITIONS FROM [db_name.]table_name WHERE ReplicationNum = 1

修改副本数命令

# 修改副本数命令
# 修改表基本副本数
ALTER TABLE example_db.my_table SET ("default.replication_num" = "2");
# 单分区副本数
ALTER TABLE example_db.my_table SET ("replication_num" = "2");
# 历史分区副本数
ALTER TABLE example_db.my_table MODIFY PARTITION (*) SET("replication_num"="2");

遍历所有库表执行SHOW PARTITIONS命令

import pymysql
import logging
logging.basicConfig(level=logging.INFO,format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',filemode='a',filename='./sr.log')ip = '10.xx'
port = 9030
user = 'xx'
passwd = 'xx'
database = 'information_schema'class MysqlUtils(object):def __init__(self, ip, port, user, passwd, database):self.ip = ipself.port = portself.user = userself.passwd = passwdself.database = databaseself.conn = Noneself.cur = Nonetry:self.conn = pymysql.connect(host=self.ip, user=self.user, passwd=self.passwd, port=self.port,charset='utf8', database=self.database)except Exception as e:logging.error("连接失败：{}".format(e))if self.conn:self.cur = self.conn.cursor()def close(self):if self.cur:self.cur.close()if self.conn:self.conn.close()def insert_data(self, sql, data):if self.cur:# cur = conn.cursor(pymysql.cursors.DictCursor)try:self.cur.executemany(sql, data)except pymysql.Error as e:logging.error("executemany执行失败：{}".format(e))self.conn.rollback()self.conn.close()self.conn.commit()def select_data(self,sql):if self.cur:self.cur.execute(sql)res = self.cur.fetchall()return resdef start():obj = MysqlUtils(ip, port, user, passwd, database)sql1 = "show databases;"dbs = obj.select_data(sql1)  # (('_statistics_',), ('ads_biz',))dbs = [d[0] for d in dbs if d[0] not in ('_statistics_','information_schema')]resu = list()for db in dbs:sql2 = "show tables from {}".format(db)tables = obj.select_data(sql2)tables = [t[0] for t in tables]# print(tables)for table in tables:# sql3 = "show create table `{}`.`{}`".format(db,table)# sql3 = """ ALTER TABLE `{}`.`{}` MODIFY PARTITION (*) SET("replication_num"="2"); """ .format(db,table)sql3 = "SHOW  PARTITIONS FROM `{}`.`{}` WHERE ReplicationNum = 1".format(db,table)logging.info(sql3)try:res = obj.select_data(sql3)  except Exception as e:logging.error(e)if res:resu.append(res)obj.close()