1、查看集群是否正常
查看集群數(shù)據(jù)不一致情況,后面可選的數(shù)字,是顯示event數(shù)量,默認(rèn)16個
gcadmin showdmlevent [number]
gcadmin showddlevent [number]
gcadmin showdmlstorageevent [number]
重啟集群服務(wù)
V8 在操作系統(tǒng)root下: service gcware start/stop/restart
V9 在dba用戶下,一般是gbase: gcluster_services all start/stop/restart
2、導(dǎo)出數(shù)據(jù)
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、加載數(shù)據(jù)
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、查看當(dāng)前加載進(jìn)度
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、查看歷史加載結(jié)果
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、查看表、存儲過程、函數(shù)
show tables;
show tables like ‘%XXX%’ ;
show function status;
show procedure status;
7、查看表、函數(shù)、存儲過程結(jié)構(gòu)
show create table mytablename;
show create procedure myProc;
show create function myFunc;
8、查看變量
show variables like ‘%XXXX%’;
查看狀態(tài)
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、集群命令行,用于執(zhí)行SQL命令
su – gbase
交互模式 gccli -uUsername -pPassword -hIP
簡單執(zhí)行 gccli -uUsername -pPassword -hIP -e”show processlist”
批量文件 gccli -uUsername -pPassword -hIP < allSQL.txt
11、查看集群運行的SQL
11.1 指定節(jié)點
簡單 show processlist
完整 show full processlist
詳細(xì) show detail processlist
11.2 所有調(diào)度節(jié)點
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 所有數(shù)據(jù)節(jié)點
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、各種日志位置
服務(wù) 進(jìn)程名字 默認(rèn)日志位置
gcware corosync
服務(wù)啟停日志,只有啟動和停止信息
V8版本 安裝目錄/gcluster/log/gcluster/corosync_system.log
V9版本 安裝目錄/gcluster/log/gcluster/gcware_system.log
服務(wù)詳細(xì)日志,詳細(xì)的啟動停止信息
V8版本 /var/log/corosync.log
V9版本 安裝目錄/gcware/log/gcware.log
gcadmin命令執(zhí)行日志
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 通用日志,記錄可執(zhí)行的SQL命令
安裝目錄/gcluster/log/gcluster/gclusterd-audit.log 審計日志,記錄所有命令執(zhí)行結(jié)果
gnode gbased 安裝目錄/gnode/log/gbase/system.log啟停日志
安裝目錄/gnode/log/gbase/express.log引擎日志
syncserver gc_sync_server
安裝目錄/gnode/log/gbase/syncserver.log 服務(wù)日志
安裝目錄/gnode/log/gbase/ syncclient_XXXX.log 客戶端日志
gcrecover
安裝目錄/gclusger/log/gcluster/ gc_recover.log 調(diào)度日志
安裝目錄/gclusger/log/gcluster/gcrecover_taskrecord.log 同步記錄
安裝目錄/gclusger/log/gcluster/gcrecover_system.log 啟停日志
gcmonit 安裝目錄/gclusger/log/gcluster/gcmonit.log 進(jìn)程監(jiān)控
gcmmonit 安裝目錄/gclusger/log/gcluster/gcmmonit.log 進(jìn)程監(jiān)控的監(jiān)控
14、sp_table_segment_count 表在各個數(shù)據(jù)節(jié)點分片的數(shù)據(jù)行數(shù)
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 動態(tài)執(zhí)行SQL
動態(tài)生成SQL的字符串,然后執(zhí)行。注意長度最大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 檢查版本(部分功能,只能在特定版本才實現(xiàn)。)
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任務(wù)
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任務(wù)
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 表在各數(shù)據(jù)節(jié)點的分片占用的磁盤空間
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');




