YashanDB数据库主备高可用架构实践( 四 ) _YashanDB

2）连接实例查看状态
$ ./bin/yasboot sql -d sys@192.168.112.121:1688YashanDB SQL Personal Edition Release 23.1.1.100 x86_64please input password: xxxxConnected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit LinuxSQL> SELECT STATUS FROM V$INSTANCE;SELECT STATUS FROM V$INSTANCE;STATUS------------- OPEN1 row fetched.SQL> SELECT database_name FROM v$database;SELECT database_name FROM v$database;DATABASE_NAME---------------------------------------------------------------- yashandb1 row fetched.2.2.6 创建用户及授权1）创建用户并授权
SQL>create user yasuser01 identified by "YashanDB01";create user yasuser01 identified by "YashanDB01";Succeed.SQL> grant DBA to yasuser01;grant DBA to yasuser01;2）切换用户登录
#./bin/yasboot sql -d yasuser01@192.168.112.121:1688Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit LinuxSQL> SELECT database_name FROM v$database;SELECT database_name FROM v$database;DATABASE_NAME---------------------------------------------------------------- yashandb2.3 数据库访问操作2.3.1 数据库表操作1）创建表空间
SQL> create tablespace ts01;create tablespace ts01;#删除表空间SQL> drop tablespace ts01;删除表空间默认会保留文件，如果再创建相同的表空间名称时，会提示文件已经存在，但是在备节点观察时该文件已经删除了。
YAS-02044 file '/usr/local/yashandb/yasdb_data/db-1-1/dbfiles/TS010' already exists在删除表空间时指定删除文件datafiles
SQL> drop tablespace ts02 including contents and datafiles;drop tablespace ts02 including contents and datafiles;2）创建表和索引
##1、创建表tb01 ，表空间为TS01create table if not exists tb01(id int NOT NULL,c1 intNOT NULL DEFAULT '0',c2 char(120) NOT NULL DEFAULT '',c3 char(60) NOT NULL DEFAULT '')TABLESPACE TS01;##查看创建的表SQL> SELECT TABLE_NAME,TABLE_TYPE FROM USER_TABLES;TABLE_NAMETABLE_TYPE ---------------------------------------------------------------- ---------- TB01HEAP##2、创建索引SQL> create unique index uniq_ix01 on tb01(id);SQL> create index ix_c1 on tb01(c1);##查看索引SQL> SELECT * FROM USER_INDEXES;INDEX_NAMEINDEX_TYPETABLE_OWNERTABLE_NAMETABLE_TYPEUNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAMEINI_TRANSMAX_TRANSPCT_FREE LOGGINGBLEVELLEAF_BLOCKSDISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY STATUSNUM_ROWSSAMPLE_SIZE LAST_ANALYZEDPARTITIONED TEMPORARY GENERATED VISIBILITYDATABASE_MAINTAINED CONSTRAINT_INDEX ---------------------------------------------------------------- ----------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- --------------------------------------------- ---------- ----------- ------------- ---------------------------------------------------------------- ------------ ------------ ------------ ------- ------------ --------------------- --------------------- ----------------------- ----------------------- --------- --------------------- --------------------- -------------------------------- ----------- --------- --------- ------------- ------------------- ---------------- IX_C1NORMALYASUSER01TB01TABLENDISABLED0 USERS22558 YVALIDNNNVISIBLENNUNIQ_IX01NORMALYASUSER01TB01TABLEYDISABLED0 USERS22558 YVALIDNNNVISIBLENN2 rows fetched.2.3.2 数据导入导出1）从mysql数据库中导出表数据为csv格式
mysql> SELECT * INTO OUTFILE '/tmp/sbtest.sbtest1.csv'-> FIELDS TERMINATED BY ','-> ENCLOSED BY '"'-> LINES TERMINATED BY 'n'-> FROM sbtest1 where id<10000;Query OK, 9999 rows affected (0.09 sec)2）使用yasldr工具将csv格式数据导入到表里
#./yasldr yasuser01/xxxx batch_size=4032 control_text="'LOAD DATA OPTIONS(DEGREE_OF_PARALLELISM=2) INFILE '/tmp/sbtest.sbtest1.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' AppEND INTO TABLE tb01(id,c1,c2,c3) '"YashanDB Loader Personal Edition Release 23.1.1.100 x86_64 0e623bd9999 rows successfully loaded.Check /tmp/sbtest.sbtest1.log for more info.[YASLDR] execute succeeded导入的命令项有多个：