sql运维手册
liguoqiang edited this page Jan 3, 2023
·
2 revisions
| 类型 | SQL | 功能 | 参数说明 |
|---|---|---|---|
| 基本信息 | show meta; | 查询Baikal MetaServer地址 | |
| show socket; | 查询当前socket连接状态 | ||
| handle create_namespace Namespace; | 创建namespace | ||
| table相关 | show cost switch; | 查询所有开启了代价开关的table | |
| show all_tables binlog/ttl/fulltext/cstore/learner | 查看所有开启binlog/ttl的表 | ||
| show schema_conf Conf | 查询schame conf | @ Conf ：[ need_merge, storage_compute_separate, select_index_by_cost, pk_prefix_balance, in_fast_import ] | |
| handle schema_conf TableName Key Value(true/false) | 修改某个表的schema conf | @ Key ：[ need_merge, storage_compute_separate, select_index_by_cost, pk_prefix_balance] | |
| handle schema_conf {table_name} backup_table BT_LEARNER | 强制将某个表的读请求降级到learner | 先use库，红色部分为对应的表名 | |
| handle schema_conf {table_name} backup_table BT_NONE | 删除表的learner降级策略 | 先use库，红色部分为对应的表名 | |
| show schema_conf backup_table | 查看索引表的降级策略 | ||
| handle schema_conf {table_name} add_forcelearner {sign} | 强制某个sql降级到learner | 先use库，无法确认sql签名请联系baikaldb值班同学 | |
| handle schema_conf {table_name} delete_forcelearner {sign} | 取消该sql的强制降级 | 同上 | |
| select * from information_schema.SIGN_FORCELEARNER; | 查看已经降级的SQL | ||
| handle schema_conf {table_name} add_blacklist {sign} | 屏蔽某个SQL | 先use库 | |
| handle schema_conf {table_name} delete_blacklist {sign} | 取消该sql的屏蔽 | 先use库 | |
| select * from information_schema.SIGN_BLACKLIST; | 查看已经屏蔽的SQL | ||
| handle schema_conf {table_name} add_forceindex sign:indec_name; | 以sql sign指定索引 | 先use库 | |
| handle schema_conf {table_name} delete_forceindex sign:indec_name; | 删除以sql sign指定的索引 | 先use库 | |
| select * from information_schema.SIGN_FORCEINDEX; | 查看forceindex的sql | ||
| handle table_resource_tag TableName NewResourceTag | 修改表的resource tag | ||
| handle main_logical_room TableName MainLogicalRoom | 修改表的主机房 | ||
| handle link_binlog DBName TableName binlog表DBName binlog表TableName [link_field_name] handle unlink_binlog DBName TableName binlog表DBName binlog表TableName | 修改表的binlog配置 | ||
| handle update_dists '{"table_name": "table_name","database": "database","namespace_name": "namespace","replica_num": 3,"main_logical_room" : "nj","dists": [ {"logical_room": "nj","count" : 3}]}' | 修改表的副本分布 | ||
| handle split_lines TableName SplitLines | 修改table splitLine | ||
| handle ttl_duration TableName ttl_duration | 修改table ttl_duration | ||
| binlog表全部信息展示 | show binlogs_info detail db.table_name; | 展示binlog表的全部region信息 | |
| binlog分区展示 | show binlogs_info db.table_name [partition_id]; | 展示binlog的分区信息 | |
| instance / resource tag | handle drop_instance InstanceAddress | drop一个instance | |
| handle instance_param ResourceTagOrAddress Key Value IsMetaParam(true) | 修改instance的InstanceParam配置(value=delete：删除配置） | resourcetag设置__baikaldb ，可以改同一个meta下所有baikaldb的参数 | |
| handle migrate_instance InstanceAddress | 迁移一个instance | ||
| handle instance_status InstanceAddress status(NORMAL/SLOW/MIGRATE) | 设置一个instance的状态 | ||
| handle load_balance ResourceTag open/close handle migrate ResourceTag open/close handle network_balance ResourceTag open/close | 开启/关闭某个resource tag的负载均衡故障迁移 网段load balance | ||
| show switch resourceTag | 查看resource tag开关是否开启（load_balance, migrate, 网段load balance），resourceTag可选 | ||
| show network_segment resourceTag | 查看一个resourceTag的网段划分 | ||
| handle instance_param resourceTag use_token_bucket 0/1 | 令牌桶开关 | ||
| region | show region TableID/TableName RegionID | 查询region信息 | |
| show abnormal regions ResourceTag [unhealthy/learner]; | 查询所有非normal状态的region信息learner表示查learner集群信息 | ||
| handle meta_drop_region RegionID RegionID... | 通知MetaServer drop region | ||
| handle abnormal regions Action ResourceTag | 处理非正常状态region | @ Action：[ remove_illegal_peer, remove_error_peer, set_peer, init_peer ] | |
| handle split_region RegionID [SplitKey] | 分裂region | ||
| user & privilege | handle add_user '{"username" : "username","password" : "password","namespace_name" : "namespace","privilege_database" : [{"database" : "database_name","database_rw" : 2 // 1: read, 2: write}],"bns":["preonline", "offline"],"ip":["127.0.0.1", "127.0.0.2"]}' | 创建用户 | |
| handle add_user '{"username" : "username","password" : "password","namespace_name" : "namespace","privilege_database" : [{"database" : "database_name","database_rw" : 2 // 1: read, 2: write}],"bns":["preonline", "offline"],"ip":["127.0.0.1", "127.0.0.2"],"resource_tag" : "e0"}' | 创建用户，并指定learner resource_tag | ||
| handle add_privilege DBName [ READ ] | 添加权限 | ||
| handle rm_privilege DBName TableName | 删除权限 | ||
| handle add_privilege resource_tag Tag | 修改当前user的learner resource_tag | ||
| handle rm_privilege resource_tag Tag | 删除当前user的learner resource_tag | ||
| handle add_privilege ddl_permission true/false | 修改当前user的ddl权限对当前用户授权后需要重连生效 | 使用如下命令设置baikaldb是否校验ddl权限，如果为false则不校验，所有user下的ddl权限不生效。e0设置为false，其他默认truehandle instance_param __baikaldb need_verify_ddl_permission true/false | |
| show user UserName | 查询用户信息 | ||
| show privilege UserName | 查询用户权限 | ||
| show namespace; | 查询用户所在的namespace | ||
| index & ddl | show global_ddlwork TableName | 查询某个表全局ddlwork信息 | |
| show ddlwork TableName [ region ] | 查询某个表ddlwork信息 | ||
| handle delete_ddl TableName [ global ] | 删除ddlwork | ||
| handle suspend_ddl TableName | 暂停ddlwork | ||
| handle restart_ddl TableName | 重新启动ddlwork | ||
| show virtual index; | 查询虚拟索引 | ||
| show disable indexs; | 查看所有出于disable状态的index | ||
| store | show store region StoreAddress RegionID; | 查询store上的一个region信息 | |
| show store_txn StoreAddress regionID; | 查询txn | ||
| handle store_rm_region StoreAddress regionID (no_delay) (force) | 删除某个store上的region | ||
| handle store_add_peer TableID RegionID PeerAddress | add_peer | ||
| handle store_rm_peer TableID RegionID PeerAddress force | rm_peer | ||
| handle store_set_peer TableID RegionID PeerAddress (force) | set_peer，强制设置成一个peer | ||
| handle store_trans_leader TableID RegionID NewLeaderAddress | transfer leader | ||
| handle store_compact_region StoreAddress TYPE [reginID] | compact region | @Type: [ data, meta, raft_log ] | |
| 和mysql表现保持一致 | show create table TableName;show collation;show databases;show table status;show tables;show warnings;show processlist;show full tables;show full columns from TableNameshow variables | 略 | |
| 紧急上线开关 | handle instance_param ResourceTag Key Value IsMetaParam(true) | handle instance_param global_baikalStore_clusters emergency_launched_switch 1 true | 1表示打开 0表示关闭ResourceTag为global_baikalStore_clusters，key为emergency_launched_switch，value为0或者1 IsMetaParam为true |
| 线上导数据切表操作 | show create table OldTableName;CREATE TABLE NewTableName{OldTableSchema};insert into NewTableName select * from OldTableName;alter table OldTableName Rename OldTableName+"_del";alter table NewTableName Rename OldTableName; | 将旧表数据导入新表，并切表，旧表数据在100w行以内有效。 |