CockroachDB介绍及安装使用(2)

说明：启动时要求各节点时间同步。
3、 启动第3个节点

[root@dcs-03 ~]# cockroach start --insecure --host=dcs-03 --background --http-port CockroachDB node starting at 2017-03-02 18:07:59.945175342 +0800 CST build: CCL 9648163 @ 2017/02/13 19:27:00 (go1.7.5) admin: 03:8081 sql: postgresql://root@dcs-03:26257?sslmode=disable logs: /opt/cockroach-data/logs store[0]: path=/opt/cockroach-data status: initialized new node, joined pre-existing cluster clusterID: 255b11fe-d801-4b06-9efc-db7baa3fccbf nodeID: 3

4、 sql命令行

[root@dcs-01 ~]# cockroach sql --url=postgresql://root@dcs-01:26257?sslmode=disab # Welcome to the cockroach SQL interface. # All statements must be terminated by a semicolon. # To exit: CTRL + D. root@dcs-01:26257/> 2.3 使用CRDB 2.3.1 基本操作 root@dcs-01:26257/> create database bank; CREATE DATABASE root@dcs-01:26257/> set database = bank; SET root@dcs-01:26257/bank> create table accounts (id INT PRIMARY KEY, balance DECIMAL); CREATE TABLE root@dcs-01:26257/bank> INSERT INTO accounts VALUES (1234, 10000); INSERT 1 root@dcs-01:26257/bank> SELECT * FROM accounts; +------+---------+ | id | balance | +------+---------+ | 1234 | 10000 | +------+---------+ (1 row) 2.3.2 web监控

1、 登录:8081 进入web界面，查看集群运行状态

2、 查看当前database信息

3、 查看权限信息

2.3.3 停止服务 [root@ dcs-01 ~]# cockroach quit --host=dcs-01 [root@ dcs-01 ~]# cockroach quit --host=dcs-02 [root@ dcs-01 ~]# cockroach quit --host=dcs-03 2.4 CRDB语法 2.4.1 外键

只是语法稍有区别（字段后缀和语句后缀均可，无任何差异，见下面例子）； “on delete cascade”语法crdb不支持，删除即可。
外键的第一种写法（字段后缀写法）：

DROP TABLE IF EXISTS xxx.IDN_OAUTH1A_REQUEST_TOKEN; CREATE TABLE xxx.IDN_OAUTH1A_REQUEST_TOKEN ( REQUEST_TOKEN VARCHAR(512), REQUEST_TOKEN_SECRET VARCHAR(512), CONSUMER_KEY_ID INTEGER REFERENCES xxx.IDN_OAUTH_CONSUMER_APPS(ID), CALLBACK_URL VARCHAR(1024), SCOPE VARCHAR(2048), AUTHORIZED VARCHAR(128), OAUTH_VERIFIER VARCHAR(512), AUTHZ_USER VARCHAR(512), TENANT_ID INTEGER DEFAULT -1, PRIMARY KEY (REQUEST_TOKEN) );

外键的第二种写法（语句后缀写法）：

DROP TABLE IF EXISTS xxx.IDN_OAUTH2_ACCESS_TOKEN; CREATE TABLE xxx.IDN_OAUTH2_ACCESS_TOKEN ( TOKEN_ID VARCHAR (255), ACCESS_TOKEN VARCHAR(255), REFRESH_TOKEN VARCHAR(255), CONSUMER_KEY_ID INTEGER, AUTHZ_USER VARCHAR (100), TENANT_ID INTEGER, USER_DOMAIN VARCHAR(50), USER_TYPE VARCHAR (25), GRANT_TYPE VARCHAR (50), TIME_CREATED TIMESTAMP, REFRESH_TOKEN_TIME_CREATED TIMESTAMP, VALIDITY_PERIOD BIGINT, REFRESH_TOKEN_VALIDITY_PERIOD BIGINT, TOKEN_SCOPE_HASH VARCHAR(32), TOKEN_STATE VARCHAR(25) DEFAULT 'ACTIVE', TOKEN_STATE_ID VARCHAR (128) DEFAULT 'NONE', SUBJECT_IDENTIFIER VARCHAR(255), PRIMARY KEY (TOKEN_ID), FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES xxx.IDN_OAUTH_CONSUMER_APPS(ID), CONSTRAINT CON_APP_KEY UNIQUE (CONSUMER_KEY_ID,AUTHZ_USER,TOKEN_ID,USER_DOMAIN,USER_TYPE,TOKEN_SCOPE_HASH, TOKEN_STATE,TOKEN_STATE_ID) ); 2.4.2 序列