GBase 8a 数据库DBA在日常运维巡检的命令汇总

1、查看集群是否正常

查看集群数据不一致情况,后面可选的数字，是显示event数量，默认16个

gcadmin showdmlevent [number]  
gcadmin showddlevent [number]  
gcadmin showdmlstorageevent [number]

重启集群服务

V8 在操作系统root下: service gcware start/stop/restart
V9 在dba用户下，一般是gbase: gcluster_services all start/stop/restart

2、导出数据

rmt:select * from ac08 where btime between ‘2020-01-01’ and ‘2020-02-01’ into outfile ‘/opt/ac08.txt’ fields terminated by ‘,’ ENCLOSED BY ‘”‘ null_value ‘\N’

3、加载数据

load data infile ‘ftp://gbase:gbase@192.168.0.100//opt/ac08.txt’ into table ac08 NULL_VALUE ‘\N’ fields terminated by ‘,’ ENCLOSED BY ‘”‘ DATETIME FORMAT ‘%Y-%m-%d %H:%i:%s’  

带毫秒的日期

load data infile ‘ftp://gbase:gbase@192.168.0.100//opt/ac08.txt’ into table ac08 NULL_VALUE ‘\N’ fields terminated by ‘,’ ENCLOSED BY ‘”‘ DATETIME FORMAT ‘%Y-%m-%d %H:%i:%s.%f’

4、查看当前加载进度

select db_name,tb_name,IP,state,start_time,progress,total_size,loaded_size,loaded_records,skipped_records from information_schema.load_status order by db_name,tb_name;

5、查看历史加载结果

select task_id,db_name,tb_name,user,HOST_IP,start_time,end_time,elapsed_time,total_size,loaded_records,skipped_records from information_schema.load_result where start_time>’2020-07-26′ order by start_time;

6、查看表、存储过程、函数

show tables;  
show tables like ‘%XXX%’ ;  
show function status;  
show procedure status;

7、查看表、函数、存储过程结构

show create table mytablename;  
show create procedure myProc;  
show create function myFunc;

8、查看变量

show variables like ‘%XXXX%’;  
查看状态  
show status like ‘%meminfo%’;

9、查看某个表占用的空间

select * from information_schema.cluster_tables a where table_schema=’testdb’ and table_name=’t1′;  

查看每个分片的空间

select * from information_schema.CLUSTER_TABLE_SEGMENTS a where table_schema=’testdb’ and table_name=’t1′;

10、集群命令行，用于执行SQL命令

su – gbase
交互模式 gccli -uUsername -pPassword -hIP
简单执行 gccli -uUsername -pPassword -hIP -e”show processlist”
批量文件 gccli -uUsername -pPassword -hIP < allSQL.txt

11、查看集群运行的SQL

11.1 指定节点

简单 show processlist
完整 show full processlist
详细 show detail processlist

11.2 所有调度节点

select COORDINATOR_NAME, ID, user, host, command, start_time, time, state, substring(info,0,100) info from information_schema.COORDINATORS_TASK_INFORMATION where command=‘query’ and time >=0 order by time desc limit 10;

11.3 所有数据节点

select NODE_NAME, ID, user, host, command, start_time, time, state, substring(info,0,100) info from information_schema.GNODES_TASK_INFORMATION where command=‘query’ and info is not null and info not like ‘%information_schema.processlist%’ order by time desc limit 10;

13、各种日志位置

服务 进程名字 默认日志位置
gcware corosync
服务启停日志，只有启动和停止信息
V8版本 安装目录/gcluster/log/gcluster/corosync_system.log
V9版本 安装目录/gcluster/log/gcluster/gcware_system.log
服务详细日志，详细的启动停止信息
V8版本 /var/log/corosync.log
V9版本 安装目录/gcware/log/gcware.log
gcadmin命令执行日志
V8版本 /var/log/gcware/gcware_XXXX.log
V9版本 安装目录/gcware/liblog/gcware.log
gcluster gclusterd
安装目录/gcluster/log/gcluster/system.log 启停日志
安装目录/gcluster/log/gcluster/express.log 引擎日志
安装目录/gcluster/log/gcluster/gclusterd.log 通用日志，记录可执行的SQL命令
安装目录/gcluster/log/gcluster/gclusterd-audit.log 审计日志，记录所有命令执行结果
gnode gbased 安装目录/gnode/log/gbase/system.log启停日志
安装目录/gnode/log/gbase/express.log引擎日志
syncserver gc_sync_server
安装目录/gnode/log/gbase/syncserver.log 服务日志
安装目录/gnode/log/gbase/ syncclient_XXXX.log 客户端日志
gcrecover
安装目录/gclusger/log/gcluster/ gc_recover.log 调度日志
安装目录/gclusger/log/gcluster/gcrecover_taskrecord.log 同步记录
安装目录/gclusger/log/gcluster/gcrecover_system.log 启停日志
gcmonit 安装目录/gclusger/log/gcluster/gcmonit.log 进程监控
gcmmonit 安装目录/gclusger/log/gcluster/gcmmonit.log 进程监控的监控

14、sp_table_segment_count 表在各个数据节点分片的数据行数

drop procedure if exists sp_table_segment_count;  
delimiter //  
create procedure sp_table_segment_count(dbname varchar,tbname varchar)  
main:begin  
select check_version('9.5.3') into @rtn from dual;  
if !@rtn then  
select 'this function need 9.5.3 +';  
leave main;  
end if;  
 
call executeSQL('set gcluster_segment_id_replace=1');  
 
set @sql=concat('select segment_id,count(*) from '  
,dbname  
,'.'  
,tbname  
,' group by segment_id');  
call executeSQL(@sql);  
end  
//  
delimiter ;  
 
call sp_table_segment_count('testdb','t1');  

15、executeSQL 动态执行SQL

动态生成SQL的字符串，然后执行。注意长度最大10K。

delimiter //  
create procedure executeSQL(  
S_SQL_TMP VARCHAR(21000)  
)  
BEGIN  
set @executeSQL_sql = S_SQL_TMP;  
PREPARE executeSQL_s1 FROM @executeSQL_sql ;  
EXECUTE executeSQL_s1;  
DEALLOCATE PREPARE executeSQL_s1;  
END //  
delimiter ;  

16、check_version 检查版本（部分功能，只能在特定版本才实现。）

drop function if exists check_version;  
delimiter //  
create function check_version(ver varchar) returns boolean  
begin  
select instr(version(),ver) into @rtn;  
return @rtn;  
end //  
delimiter ;  

17、sp_cluster_processlist 集群层正在运行的SQL任务

drop procedure if exists sp_cluster_processlist;  
delimiter //  
create procedure sp_cluster_processlist(showSize int,topN int)  
begin  
set @sql=concat('select COORDINATOR_NAME, ID, user, host, command, start_time, time, state, substring(info,0,'  
,showSize  
,') info from information_schema.COORDINATORS_TASK_INFORMATION where command=\'query\' and info is not null and info not like '%information_schema.processlist%' order by time desc limit '  
,topN);  
call executeSQL(@sql);  
end  
//  
delimiter ;  

call sp_cluster_processlist(100,10);  

18、sp_node_processlist 计算层正在运行的SQL任务

drop procedure if exists sp_node_processlist;  
delimiter //  
create procedure sp_node_processlist(showSize int,topN int)  
begin  
set @sql=concat('select NODE_NAME, ID, user, host, command, start_time, time, state,substring(info,0,'  
,showSize  
,') info from information_schema.GNODES_TASK_INFORMATION where command='query' and info is not null and info not like '%information_schema.processlist%' order by time desc limit '  
,topN);  
call executeSQL(@sql);  
end  
//  
delimiter ;  
 
call sp_node_processlist(100,10);  

19、sp_table_size 表占用的磁盘空间大小汇总

drop procedure if exists sp_table_size;  
delimiter //  
create procedure sp_table_size(dbname varchar,tbname varchar)  
begin  
set @sql=concat('select * from information_schema.cluster_tables a where table_schema=''  
,dbname  
,'' and table_name=''  
,tbname  
,''');  
call executeSQL(@sql);  
end  
//  
delimiter ;  
call sp_table_size('testdb','t1');  

20、sp_table_segment_size 表在各数据节点的分片占用的磁盘空间

drop procedure if exists sp_table_segment_size;  
delimiter //  
create procedure sp_table_segment_size(dbname varchar,tbname varchar)  
begin  
set @sql=concat('select SUFFIX,HOST,TABLE_DATA_SIZE,TABLE_STORAGE_SIZE,DATA_PERCENT from information_schema.CLUSTER_TABLE_SEGMENTS a where table_schema=''  
,dbname  
,'' and table_name=''  
,tbname  
,''');  
call executeSQL(@sql);  
end  
//  
delimiter ;  
call sp_table_segment_size('testdb','t1');