StarRocks 排查单副本表 - ToB企服应用市场:ToB评测及商务社交产业平台

# 方式一查询元数据，检查分区级的副本数
with v_tmp1 as (
select
TABLE_ID, PARTITION_ID, tablet_id, count(*) as cnt
from
information_schema.be_tablets
group by
TABLE_ID, PARTITION_ID, tablet_id
having
count(*) = 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
;

复制代码

# 方式二遍历所有库表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");

复制代码

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 = ip
self.port = port
self.user = user
self.passwd = passwd
self.database = database
self.conn = None
self.cur = None
try:
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 res
def 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()

复制代码