针对GBase 8a 版本
1数据库操作与维护
1.1数据库启停
[root@OMMB-66-V10-001 ~]# service gcware stopStopping GCMonit success!Signaling GCRECOVER (gcrecover) to terminate: [ OK ]Waiting for gcrecover services to unload:.....[ OK ]Signaling GCSYNC (gc_sync_server) to terminate: [ OK ]Waiting for gc_sync_server services to unload:.[ OK ]Signaling GCLUSTERD to terminate: [ OK ]Waiting for gclusterd services to unload:.........[ OK ]Signaling GBASED to terminate: [ OK ]Waiting for gbased services to unload:^[[A.........[ OK ]Signaling GCWARE (gcware) to terminate: [ OK ]Waiting for gcware services to unload:.[ OK ][root@OMMB-66-V10-001 ~]# service gcware startStarting GCWARE (gcwexec): [ OK ]Starting GCMonit success!Starting GBASED : [ OK ]Starting GCLUSTERD : [ OK ]Starting GCSYNC : [ OK ]Starting GCRECOVER : [ OK ][root@OMMB-66-V10-001 ~]# service gcware restart
1.2集群状态查询
[root@zdc3 zdcapp3]# gcadminCLUSTER STATE: ACTIVECLUSTER MODE: NORMAL===============================================================| GBASE COORDINATOR CLUSTER INFORMATION |===============================================================| NodeName | IpAddress |gcware |gcluster |DataState |---------------------------------------------------------------| coordinator1 | 192.168.250.250 | OPEN | OPEN | 0 |---------------------------------------------------------------| coordinator2 | 192.168.250.251 | OPEN | OPEN | 0 |---------------------------------------------------------------| coordinator3 | 192.168.250.252 | OPEN | OPEN | 0 |---------------------------------------------------------------===========================================================| GBASE DATA CLUSTER INFORMATION |===========================================================|NodeName | IpAddress |gnode |syncserver |DataState |-----------------------------------------------------------| node1 | 192.168.250.250 | OPEN | OPEN | 0 |-----------------------------------------------------------| node2 | 192.168.250.251 | OPEN | OPEN | 0 |-----------------------------------------------------------| node3 | 192.168.250.252 | OPEN | OPEN | 0 |-----------------------------------------------------------
1.3进程状态查询
[root@zdc2 gbase]# service gcware statuscorosync (pid 3157) is running...gclusterd (pid 3966) is running...gcrecover (pid 4194) is running...gbased (pid 3271) is running...gc_sync_server (pid 3920) is running...
1.4GBase进程名
GBase协调节点/Coordinator node:gcluster进程名 : gclusterdgcware进程名 : corosync自动回复进程名 : gcrecoverGBase数据节点/Data nodegnode进程名 : gbasedsyncserver进程名 : gcsync监控工具 : gcmonit / gcmmonit
2.数据库访问、信息查询
2.1命令行方式访问数据库
root、gbase用户本地访问 [root@zdc2 installGBase_v14.1]# gccliGBase client 8.6.2.20-R1.84277. Copyright (c) 2004-2017, GBase. All Rights Reserved.gbase>非本地访问[gbase@zdc2 ~]$ gccli -ugbase -pgbase20110531 -P5258 -h10.92.250.252GBase client 8.6.2.20-R1.84277. Copyright (c) 2004-2017, GBase. All Rights Reserved.gbase>如果需要直接访问gnode,查询gnode相关内容,请使用gncli命令直接执行sqlgccli -ugbase -ppassword -P5258 -h10.92.250.252 -vvv<'/home/test.sql'
2.2查询数据库节点,副本情况
gbase> show nodes;+------------+-----------------+-------+--------------+----------------+--------+-----------+| Id | ip | name | primary part | duplicate part | status | datastate |+------------+-----------------+-------+--------------+----------------+--------+-----------+| 4210731200 | 192.168.250.250 | node1 | n1 | n3 | online | 0 || 4227508416 | 192.168.250.251 | node2 | n2 | n1 | online | 0 || 4244285632 | 192.168.250.252 | node3 | n3 | n2 | online | 0 |+------------+-----------------+-------+--------------+----------------+--------+-----------+3 rows in set (Elapsed: 00:00:00.01)
2.3数据库版本查询
gbase> select @@version;+-------------------+| @@version |+-------------------+| 8.6.2.20-R1.84277 |+-------------------+1 row in set (Elapsed: 00:00:00.01)
2.4数据库参数查询与设置
gbase> show variables like '%heap%';+---------------------------+-----------+| Variable_name | Value |+---------------------------+-----------+| _gbase_enable_system_heap | OFF || gbase_heap_data | 536870912 || gbase_heap_large | 268435456 || gbase_heap_temp | 268435456 || max_heap_table_size | 16777216 |+---------------------------+-----------+5 rows in set (Elapsed: 00:00:00.00)gbase> set global gcluster_max_conn_in_pool=301; 全局参数Query OK, 0 rows affected (Elapsed: 00:00:00.08)SET [GLOBAL | SESSION]= value丼例:开启本节点的trace日志,默认是设置session 变量。SET gbase_sql_trace =on;
2.5加载数据文件
load data infile 'ftp://gbase:gbase@disp_server//opt/1.txt,ftp://gbase:gbase@disp_server//opt/2.txt' into table test.tablename data_format 3 fields terminated by '#' table_fields 'FIELD1,FIELD2,FIELD3' datetime format '%Y-%m-%d %H:%i:%S'文件名中的#要用%23代替。
2.6常用的show语句
show databases;--查看系统中数据库名的语句(根据权限显示)show tables;--列出给定数据库的所有非临时表desc test.t;--查看test库t表的列信息show index from test.t;--查看test库t表的索引信息show create table test.t;--查看test的建库语句showcreateprocedurep_demo;--查看创建存储过程的语句显示警告或错误信息:SHOW WARNINGS; --显示由最后一个语句产生的错误,警告和注意信息。SHOW ERRORS; --显示由最后一个语句产生的错误信息。显示集群中节点信息:SHOW NODES; --如果有SUPER 权限,可以看到所有节点信息。SHOW LOCAL NODE; --显示集群中客户端正在访问节点的信息。
3.数据库故障定位
3.1查看错误日志
[root@zdc2 gbase]# gcadmin showddlevent 查看DDL语句错误日志Event count:0[root@zdc2 gbase]# gcadmin showdmlevent 查看DML语句错误日志Event count:0[root@zdc2 gbase]# gcadmin showdmlstorageevent 查看表数据损坏日志Event count:0
3.2查看数据库锁情况
[root@zdc2 gbase]# gcadmin showlock +===============================================+ | GCLUSTER LOCK | +===============================================+ +---------+-----+-------+-----------+------+----+ |Lock name|owner|content|create time|orphan|type| +---------+-----+-------+-----------+------+----+ Total : 0Lock name | owner | content | create time | orphan | type库名.表名 |节点IP |锁的备注 | 上锁时间 |是否孤儿锁|锁类型(S/E)gbase> show processlist; 查看当前正在执行的gbase进程,如果有上锁的语句的话会打出语句+-------+-----------------+-----------------------+---------+---------+-------+-----------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+-------+-----------------+-----------------------+---------+---------+-------+-----------------------------+------------------+| 1 | event_scheduler | localhost | NULL | Daemon | 35728 | Waiting for next activation | NULL || 46247 | root | 192.168.250.250:58468 | dap_etl | Sleep | 1 | | NULL || 47119 | root | 192.168.250.250:9318 | dap_etl | Sleep | 27 | | NULL || 47196 | root | 127.0.0.1:6824 | NULL | Query | 0 | NULL | show processlist |+-------+-----------------+-----------------------+---------+---------+-------+-----------------------------+------------------+4 rows in set (Elapsed: 00:00:00.00)