gbase导入sql文件_GBase数据库——常用命令

1数据库操作与维护

1.1数据库启停

[root@OMMB-66-V10-001 ~]# service gcware stop

Stopping 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 start

Starting 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]# gcadmin

CLUSTER STATE: ACTIVE

CLUSTER 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 status

corosync (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进程名 : gclusterd

gcware进程名 : corosync

自动回复进程名 : gcrecover

GBase数据节点/Data node

gnode进程名 : gbased

syncserver进程名 : gcsync

监控工具 ： gcmonit / gcmmonit

2.数据库访问、信息查询

2.1命令行方式访问数据库

root、gbase用户本地访问

[root@zdc2 installGBase_v14.1]# gccli

GBase 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.252

GBase client 8.6.2.20-R1.84277. Copyright (c) 2004-2017, GBase. All Rights Reserved.

gbase>

如果需要直接访问gnode，查询gnode相关内容，请使用gncli命令

直接执行sql

gccli -ugbase -ppassword -P5258 -h10.92.250.252 -vvv

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 : 0

Lock 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)