GBase数据库——常用命令

2018/3/7 9:55:02 人评论 次浏览 分类:操作技巧

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<'/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] <variablename> = 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)

 

相关知识

  • 我必须得告诉大家的MySQL优化原理

    说起MySQL的查询优化,相信大家收藏了一堆奇淫技巧:不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧?是否理解其背后的工作原理?在实际场景下性能真有提升吗?我想未必。因而理解这些优化建议背后的原理就尤为…

    2017/7/19 8:56:00
  • 优化SQL查询:如何写出高性能SQL语句

    1、 首先要搞明白什么叫执行计划?执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,如果该表进行了归…

    2017/7/19 8:56:00
  • memcached与redis实现的对比

    memcached和redis,作为近些年最常用的缓存服务器,相信大家对它们再熟悉不过了。前两年还在学校时,我曾经读过它们的主要源码,如今写篇笔记从个人角度简单对比一下它们的实现方式,权当做复习,有理解错误之处,欢迎指正。文中使用的架构类的图片大多来自于网络,有部分图…

    2017/7/19 8:56:00
  • SQL Server Management Studio 2016 最让人兴奋的10大新功能

    介绍这篇文章列出了SQL Server Management Studio 2016已经取得的一些令人兴奋的改进。我相信,大多数新功能可以帮助我们提升工作速度,更方便地使用SSMS。1.颜色主题随着SSMS 2016的到来,现在你可以选择自己喜欢的颜色主题,类似于Visual Studio中的相似功能。你可以在两种…

    2017/7/19 8:56:00

共有访客发表了评论 网友评论

验证码: 看不清楚?