YashanDB數(shù)據(jù)庫主備高可用架構(gòu)實(shí)踐
作者：大唐小少
在架構(gòu)上主備集群的可用性通過yasom檢測的，如果這臺服務(wù)器宕機(jī)后主備切換是沒有自動實(shí)現(xiàn)的，在高可用切換這一塊有優(yōu)化的空間。像OpenGauss在后續(xù)版本增加CM實(shí)現(xiàn)切換功能，也可以參考GoldenDB管理節(jié)點(diǎn)對數(shù)據(jù)節(jié)點(diǎn)的高可用管理。
YashanDB數(shù)據(jù)庫是全面自主設(shè)計(jì)研發(fā)，支持集中式、分布式和共享存儲部署架構(gòu)以及混合負(fù)載場景的超融合數(shù)據(jù)庫。本文簡要介紹了YashanDB的特性和部署架構(gòu)，并部署1主1備的測試環(huán)境，驗(yàn)證數(shù)據(jù)庫訪問操作、主備切換高可用過程，以了解。
1、YashanDB基本介紹
崖山數(shù)據(jù)庫系統(tǒng)YashanDB是一種新型的數(shù)據(jù)庫管理系統(tǒng)，由深圳計(jì)算科學(xué)研究院自主設(shè)計(jì)研發(fā)。它在經(jīng)典數(shù)據(jù)庫理論的基礎(chǔ)上，融入了原創(chuàng)的有界計(jì)算理論、近似計(jì)算理論、并行可擴(kuò)展理論和跨模融合計(jì)算理論。YashanDB在部署模式上支持單機(jī)/主備、共享集群、分布式等多種部署方式，覆蓋OLTP/HTAP/OLAP交易和分析混合負(fù)載場景，支持大規(guī)模并發(fā)事務(wù)處理和混合事務(wù)-分析處理、在線實(shí)時(shí)分析、海量數(shù)據(jù)查詢加速以及異構(gòu)數(shù)據(jù)融合處理場景。
1.1 YashanDB數(shù)據(jù)庫核心特性
YashanDB數(shù)據(jù)庫是全面自研的國產(chǎn)數(shù)據(jù)庫，滿足實(shí)時(shí)性和高并發(fā)下的性能和穩(wěn)定性要求，同時(shí)具備主流的信創(chuàng)改造生態(tài)，符合應(yīng)用遷移的標(biāo)準(zhǔn)。主要有以下特性：
- 全面兼容主流的Oracle數(shù)據(jù)庫和MySQL數(shù)據(jù)庫的語法、語義，應(yīng)用無需大量改造，可以完成低成本的平滑遷移；
- 兼容適配主流的信創(chuàng)軟硬件生態(tài)，包括芯片、操作系統(tǒng)和中間件等，并且內(nèi)核代碼自主可控，實(shí)現(xiàn)自主知識產(chǎn)權(quán)的核心競爭力；
- 基于NUMA架構(gòu)設(shè)計(jì)并行計(jì)算框架，實(shí)現(xiàn)高性能低成本的性能要求；
- 提供金融級的高可用容災(zāi)能力，實(shí)現(xiàn)多種部署模式和多中心多活的高可用部署架構(gòu)；
- 多種存儲引擎架構(gòu)支持混合負(fù)載和實(shí)時(shí)分析等融合數(shù)據(jù)處理
圖片
1.2 YashanDB數(shù)據(jù)庫部署架構(gòu)
YashanDB在部署架構(gòu)上分為單實(shí)例的主備部署、分布式部署以及共享集群的部署模式，部署架構(gòu)如下圖所示：
- 單實(shí)例主備部署：1主多備的主備部署模式，主備實(shí)例部署在不同的主機(jī)上采用本地存儲，主備節(jié)點(diǎn)之間通過日志同步的方式進(jìn)行數(shù)據(jù)同步。主節(jié)點(diǎn)故障時(shí)自動切換到備節(jié)點(diǎn)，保證服務(wù)的高可用。
- 分布式部署：基于Shared-nothing架構(gòu)，由多個(gè)實(shí)例節(jié)點(diǎn)組成（包括MN節(jié)點(diǎn)、CN節(jié)點(diǎn)和DN節(jié)點(diǎn)），這些節(jié)點(diǎn)部署在不同的機(jī)器上
- 共享集群部署：基于聚合內(nèi)存的技術(shù)實(shí)現(xiàn)集群數(shù)據(jù)庫各實(shí)例之間協(xié)同數(shù)據(jù)頁的讀寫訪問以及各種非數(shù)據(jù)類的并發(fā)控制。共享集群在部署上是多實(shí)例多活的架構(gòu)，多個(gè)數(shù)據(jù)庫實(shí)例并發(fā)的讀寫同一份共享數(shù)據(jù)。在管理組件上多了YCS和YFS，分別對集群數(shù)據(jù)的高可用和集群的文件系統(tǒng)進(jìn)行管理。
圖片
三種部署模式分別適用于不同的場景，各自特性如下：
- 單實(shí)例主備架構(gòu)：集中式數(shù)據(jù)庫處理類型，基于高性能的服務(wù)器和存儲滿足高并發(fā)的聯(lián)機(jī)類業(yè)務(wù)處理，同時(shí)1主多備的架構(gòu)保證了系統(tǒng)的高可用。不過受限于單臺服務(wù)器的處理性能和存儲限制，適用于非大并發(fā)大數(shù)據(jù)量存儲的應(yīng)用。
- 分布式架構(gòu)：原生的分布式數(shù)據(jù)庫架構(gòu)，支持在線交易和實(shí)時(shí)數(shù)據(jù)分析混合交易，適用于海量數(shù)據(jù)分析以及數(shù)據(jù)倉庫類應(yīng)用
- 共享集群架構(gòu)：對標(biāo)Oracle RAC的部署架構(gòu)，滿足核心業(yè)務(wù)場景的高性能、高可靠以及擴(kuò)展性要求。適用于核心業(yè)務(wù)場景的國產(chǎn)化替換。
圖片
1.3 YashanDB數(shù)據(jù)庫內(nèi)核架構(gòu)
圖片
YashanDB數(shù)據(jù)庫的內(nèi)存架構(gòu)如圖，主要包括以下部分：
- 私有內(nèi)存區(qū)域：包括SQL執(zhí)行區(qū)以及SQL算子使用的虛擬內(nèi)存，這部分內(nèi)存區(qū)域在各個(gè)線程運(yùn)行時(shí)分配使用，釋放后才能被其它線程使用
- 共享內(nèi)存區(qū)域：包括SQL緩存、日志緩存等內(nèi)存共享池和全局緩存、全局鎖和全局隊(duì)列等全局共享資源池、數(shù)據(jù)緩存區(qū)域以及有界加速緩存
- 工作線程：處理客戶端連接請求的線程，可以為會話專有模式或共享模式
- 后臺線程：寫日志、寫數(shù)據(jù)和checkpoint等數(shù)據(jù)庫后臺的工作線程。
對于分布式架構(gòu)，還有MDS元數(shù)據(jù)管理、CMS分布式集群管理和GTS分布式全局時(shí)間管理等線程；對于共享存儲架構(gòu)，還有共享集群間的同步消息處理、GRC全局資源訪問控制、GLS全局鎖服務(wù)和GCS全局緩存服務(wù)等專有線程。
2、YashanDB主備部署實(shí)踐
2.1 環(huán)境準(zhǔn)備
2.1.1 主備集群服務(wù)器信息
1）YashanDB數(shù)據(jù)庫1主1備部署服務(wù)器信息如下：
IP | 操作系統(tǒng) | CPU | 內(nèi)存 | YashanDB版本 | 角色 |
192.168.112.121 | Centos_7.4_x86_64 | 1核 | 2G | 23.1.1.100 | 主節(jié)點(diǎn) |
192.168.112.122 | Centos_7.4_x86_64 | 1核 | 2G | 23.1.1.100 | 備節(jié)點(diǎn) |
2）安裝默認(rèn)端口
圖片
3）關(guān)閉防火墻
##關(guān)閉防火墻
# systemctl stop firewalld
##關(guān)閉開機(jī)自啟
# systemctl disable firewalld
##檢查防火墻狀態(tài)
# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)4）開啟SSH服務(wù)
##檢查ssh服務(wù)
# systemctl start sshd.service
##檢查ssh服務(wù)
# systemctl status sshd.service
● sshd.service - OpenSSH server daemon
Loaded: loaded (/usr/lib/systemd/system/sshd.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2023-11-23 09:13:12 CST; 1h 38min ago
Docs: man:sshd(8)
man:sshd_config(5)2.1.2 創(chuàng)建用戶yashan并加入sudoer中
##1、創(chuàng)建用戶
# useradd -d /home/yashan -m yashan
# passwd yashan
##2、配置sudoers
# chmod +w /etc/sudoers
#在/etc/sudoers添加內(nèi)容
yashan ALL=(ALL) NOPASSWD:ALL
# chmod -w /etc/sudoers
##3、查看用戶
# id yashan
uid=1002(yashan) gid=1002(yashan) groups=1002(yashan)2.1.3 安裝目錄規(guī)劃
所有安裝YashanDB的實(shí)例節(jié)點(diǎn)上必須規(guī)劃的兩個(gè)目錄：
- HOME目錄：YashanDB的產(chǎn)品目錄，包含YashanDB所提供的命令、數(shù)據(jù)庫運(yùn)行所需的庫及各關(guān)鍵組件。該目錄由yashan用戶執(zhí)行安裝部署時(shí)輸入的install-path參數(shù)根據(jù)一定規(guī)則生成并創(chuàng)建。
- DATA目錄：YashanDB的數(shù)據(jù)目錄，包含數(shù)據(jù)庫的各類系統(tǒng)數(shù)據(jù)文件、日志文件和配置文件，用戶數(shù)據(jù)也缺省存儲在該目錄下。但對于共享集群，所有的數(shù)據(jù)文件和redo文件均需保存在共享存儲上，DATA目錄將只用于存儲實(shí)例運(yùn)行相關(guān)的配置文件、日志文件等數(shù)據(jù)。該目錄由yashan用戶執(zhí)行安裝部署時(shí)輸入的data-path參數(shù)根據(jù)一定規(guī)則生成并創(chuàng)建。
2.2 主備節(jié)點(diǎn)安裝
2.2.1 安裝包和配置文件
1）解壓安裝包
# tar -xzvf yashandb-personal-23.1.1.100-linux-x86_64.tar.gz -C /usr/local/yashandb/install2）在主備節(jié)點(diǎn)創(chuàng)建安裝目錄
#mkdir /usr/local/yashandb3）執(zhí)行yasboot package命令生成配置文件
#cd /usr/local/yashandb/install
#./bin/yasboot package se gen --cluster yashandb -u yashan -p xxxx --ip 192.168.112.121,192.168.112.122 --port 22 --install-path /usr/local/yashandb/yasdb_home --data-path /usr/local/yashandb/yasdb_data --begin-port 1688 --node 2
192.168.112.121
ip:192.168.112.121 cpu cores is less than 2
ip:192.168.112.121 memroy is less than 4096MB
192.168.112.122
ip:192.168.112.122 cpu cores is less than 2
ip:192.168.112.122 memroy is less than 4096MB
hostid | group | node_type | node_name | listen_addr | replication_addr | data_path
-------------------------------------------------------------------------------------------------------------------------
host0001 | dbg1 | db | 1-1 | 192.168.112.121:1688 | 192.168.112.121:1689 | /usr/local/yashandb/yasdb_data
----------+-------+-----------+-----------+----------------------+----------------------+--------------------------------
host0002 | dbg1 | db | 1-2 | 192.168.112.122:1688 | 192.168.112.122:1689 | /usr/local/yashandb/yasdb_data
----------+-------+-----------+-----------+----------------------+----------------------+--------------------------------
Generate config success- --cluster指定為要部署的數(shù)據(jù)庫集群名稱，該名稱也將作為集群中所有節(jié)點(diǎn)上初始創(chuàng)建數(shù)據(jù)庫的名稱（database name）
- -u和-p，指定創(chuàng)建的用戶yashan和密碼
- --port指定SSH服務(wù)端口，一般為22
- --install-path指定數(shù)據(jù)庫安裝路徑，指定為/usr/local/yashandb/yasdb_home
- --data-path指定數(shù)據(jù)存放目錄，指定為/usr/local/yashandb/yasdb_data
- --begin-port指定數(shù)據(jù)庫監(jiān)聽端口，默認(rèn)1688
- --node指定主備節(jié)點(diǎn)總數(shù)量，1主1備設(shè)置為2
4）上一步驟執(zhí)行完畢后，當(dāng)前目錄下將生成yashandb.toml和hosts.toml兩個(gè)配置文件，yashandb.toml為數(shù)據(jù)庫集群的配置文件，hosts.toml為主機(jī)的配置文件。根據(jù)需要調(diào)整配置項(xiàng)，比如RUN_LOG_FILE_PATH和SLOW_LOG_FILE_PATH。
cluster = "yashandb"
create_simple_schema = false
uuid = "655eb2496767a825e8dda1d6c4da4d4e"
yas_type = "SE"
[[group]]
group_type = "db"
name = "dbg1"
[group.config]
CHARACTER_SET = "utf8"
ISARCHIVELOG = true
REDO_FILE_NUM = 4
REDO_FILE_SIZE = "128M"
[group.create_sql]
[[group.node]]
data_path = "/usr/local/yashandb/yasdb_data"
hostid = "host0001"
role = 1
[group.node.config]
LISTEN_ADDR = "192.168.112.121:1688"
REPLICATION_ADDR = "192.168.112.121:1689"
RUN_LOG_FILE_PATH = "/usr/local/yashandb/yasdb_home/yashandb/log/yashandb/db-1-1/run"
RUN_LOG_LEVEL = "INFO"
SLOW_LOG_FILE_PATH = "/usr/local/yashandb/yasdb_home/yashandb/log/yashandb/db-1-1/slow"
[[group.node]]
data_path = "/usr/local/yashandb/yasdb_data"
hostid = "host0002"
role = 2
[group.node.config]
LISTEN_ADDR = "192.168.112.122:1688"
REPLICATION_ADDR = "192.168.112.122:1689"
RUN_LOG_FILE_PATH = "/usr/local/yashandb/yasdb_home/yashandb/log/yashandb/db-1-2/run"
RUN_LOG_LEVEL = "INFO"
SLOW_LOG_FILE_PATH = "/usr/local/yashandb/yasdb_home/yashandb/log/yashandb/db-1-2/slow"2.2.2 執(zhí)行安裝
使用以下命令執(zhí)行安裝
#./bin/yasboot package install -t hosts.toml -i /usr/local/src/yashandb-personal-23.1.1.100-linux-x86_64.tar.gz -f
checking install package...
install version: yashandb 23.1.1.100
host0001 100% [====================================================================] 6s
host0002 100% [====================================================================] 6s
update host to yasom...注：指定參數(shù)-f不強(qiáng)制校驗(yàn)環(huán)境是否滿足部署條件，否則會提示：
192.168.112.121
ip:192.168.112.121 cpu cores is less than 2
ip:192.168.112.121 memroy is less than 4096MB2.2.3 數(shù)據(jù)庫部署
1）執(zhí)行部署命令
$ ./bin/yasboot cluster deploy -t yashandb.toml
type | uuid | name | hostid | index | status | return_code | progress | cost
------------------------------------------------------------------------------------------------------------
task | 3190567c61c193bc | DeployYasdbCluster | - | yashandb | SUCCESS | 0 | 100 | 59
------+------------------+--------------------+--------+----------+---------+-------------+----------+------
task completed, status: SUCCESS2）生效環(huán)境變量
#部署命令成功執(zhí)行后將會在$YASDB_HOME目錄下的conf文件夾中生成<<集群名稱>>.bashrc環(huán)境變量文件
$ cd /usr/local/yashandb/yasdb_home/yashandb/23.1.1.100/conf
# 如~/.bashrc中已存在YashanDB相關(guān)的環(huán)境變量，將其清除
$ cat yashandb.bashrc >> ~/.bashrc
$ source ~/.bashrc3）開機(jī)后啟動命令
yashoot process yasom start -c yashandb
yasboot process yasagent start -c yashandb
yasboot cluster start -c yashandb2.2.4 修改sys用戶口令
通過yasboot工具設(shè)置集群內(nèi)所有節(jié)點(diǎn)sys用戶的密碼
#./bin/yasboot cluster password set -n xxxx -c yashandb
type | uuid | name | hostid | index | status | return_code | progress | cost
----------------------------------------------------------------------------------------------------------
task | ce83dacc3c2e4246 | YasdbPasswordSet | - | yashandb | SUCCESS | 0 | 100 | 2
------+------------------+------------------+--------+----------+---------+-------------+----------+------
task completed, status: SUCCESS2.2.5 驗(yàn)證安裝
1）查看數(shù)據(jù)庫狀態(tài)
$ ./bin/yasboot cluster status -c yashandb -d
hostid | node_type | nodeid | pid | instance_status | database_status | database_role | listen_address | data_path
----------------------------------------------------------------------------------------------------------------------------------------------------------
host0001 | db | 1-1:1 | 16119 | open | normal | primary | 192.168.112.121:1688 | /usr/local/yashandb/yasdb_data/db-1-1
----------+-----------+--------+-------+-----------------+-----------------+---------------+----------------------+---------------------------------------
host0002 | db | 1-2:2 | 15363 | open | normal | standby | 192.168.112.122:1688 | /usr/local/yashandb/yasdb_data/db-1-2
----------+-----------+--------+-------+-----------------+-----------------+---------------+----------------------+---------------------------------------2）連接實(shí)例查看狀態(tài)
$ ./bin/yasboot sql -d sys@192.168.112.121:1688
YashanDB SQL Personal Edition Release 23.1.1.100 x86_64
please input password: xxxx
Connected to:
YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux
SQL> SELECT STATUS FROM V$INSTANCE;
SELECT STATUS FROM V$INSTANCE;
STATUS
-------------
OPEN
1 row fetched.
SQL> SELECT database_name FROM v$database;
SELECT database_name FROM v$database;
DATABASE_NAME
----------------------------------------------------------------
yashandb
1 row fetched.2.2.6 創(chuàng)建用戶及授權(quán)
1）創(chuàng)建用戶并授權(quán)
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:1688
Connected to:
YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux
SQL> SELECT database_name FROM v$database;
SELECT database_name FROM v$database;
DATABASE_NAME
----------------------------------------------------------------
yashandb2.3 數(shù)據(jù)庫訪問操作
2.3.1 數(shù)據(jù)庫表操作
1）創(chuàng)建表空間
SQL> create tablespace ts01;
create tablespace ts01;
#刪除表空間
SQL> drop tablespace ts01;刪除表空間默認(rèn)會保留文件，如果再創(chuàng)建相同的表空間名稱時(shí)，會提示文件已經(jīng)存在，但是在備節(jié)點(diǎn)觀察時(shí)該文件已經(jīng)刪除了。
YAS-02044 file '/usr/local/yashandb/yasdb_data/db-1-1/dbfiles/TS010' already exists在刪除表空間時(shí)指定刪除文件datafiles
SQL> drop tablespace ts02 including contents and datafiles;
drop tablespace ts02 including contents and datafiles;2）創(chuàng)建表和索引
##1、創(chuàng)建表tb01，表空間為TS01
create table if not exists tb01(
id int NOT NULL,
c1 int NOT NULL DEFAULT '0',
c2 char(120) NOT NULL DEFAULT '',
c3 char(60) NOT NULL DEFAULT '')
TABLESPACE TS01;
##查看創(chuàng)建的表
SQL> SELECT TABLE_NAME,TABLE_TYPE FROM USER_TABLES;
TABLE_NAME TABLE_TYPE
---------------------------------------------------------------- ----------
TB01 HEAP
##2、創(chuàng)建索引
SQL> create unique index uniq_ix01 on tb01(id);
SQL> create index ix_c1 on tb01(c1);
##查看索引
SQL> SELECT * FROM USER_INDEXES;
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED PARTITIONED TEMPORARY GENERATED VISIBILITY DATABASE_MAINTAINED CONSTRAINT_INDEX
---------------------------------------------------------------- ----------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- --------------------------------------------- ---------- ----------- ------------- ---------------------------------------------------------------- ------------ ------------ ------------ ------- ------------ --------------------- --------------------- ----------------------- ----------------------- --------- --------------------- --------------------- -------------------------------- ----------- --------- --------- ------------- ------------------- ----------------
IX_C1 NORMAL YASUSER01 TB01 TABLE N DISABLED 0 USERS 2 255 8 Y VALID N N N VISIBLE N N
UNIQ_IX01 NORMAL YASUSER01 TB01 TABLE Y DISABLED 0 USERS 2 255 8 Y VALID N N N VISIBLE N N
2 rows fetched.2.3.2 數(shù)據(jù)導(dǎo)入導(dǎo)出
1）從mysql數(shù)據(jù)庫中導(dǎo)出表數(shù)據(jù)為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格式數(shù)據(jù)導(dǎo)入到表里
#./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 0e623bd
9999 rows successfully loaded.
Check /tmp/sbtest.sbtest1.log for more info.
[YASLDR] execute succeeded導(dǎo)入的命令項(xiàng)有多個(gè)：
- BATCH_SIZE：每批次的CSV數(shù)據(jù)行數(shù)，默認(rèn)4032，范圍[1,65535]。
- MODE：導(dǎo)入方式，包括BASIC方式和BATCH方式，默認(rèn)BATCH方式。
圖片
登錄到表中查看數(shù)據(jù)情況
SQL> select count(1) from tb01 ;
COUNT(1)
---------------------
9999
1 row fetched.3）使用exp和imp導(dǎo)出和導(dǎo)入到新表
#1、使用exp命令將yasuser01用戶下所有數(shù)據(jù)導(dǎo)出至export. yasuser01.data文件中
[yashan@tango-DB01 ~]$ exp yasuser01/YashanDB01 FILE=export.yasuser01.dat OWNER=yasuser01
YashanDB Export Personal Edition Release 23.1.1.100 x86_64 0e623bd
export terminated successfully
[yashan@tango-DB01 ~]$ ll
total 1892
-rw-r----- 1 yashan yashan 1936736 Nov 23 19:54 export.yasuser01.dat
#2、使用imp命令將導(dǎo)出數(shù)據(jù)導(dǎo)入到y(tǒng)asuser02中
[yashan@tango-DB01 ~]$ imp yasuser02/YashanDB02 FILE=export.yasuser01.dat FROMUSER=yasuser01 TOUSER=yasuser02
YashanDB Import Personal Edition Release 23.1.1.100 x86_64 0e623bd
import terminated successfully
##查看表數(shù)據(jù)情況
SQL> SELECT table_name FROM USER_TABLES;
TABLE_NAME
----------------------------------------------------------------
TB01
1 row fetched.
SQL> select count(1) from tb01;
COUNT(1)
---------------------
9999
1 row fetched.- FROMUSER模式用于導(dǎo)入指定用戶下的對象及數(shù)據(jù)，包括用戶下所有元數(shù)據(jù)、表數(shù)據(jù)以及用戶權(quán)限等
- TOUSER命令用于更換導(dǎo)入的用戶，將導(dǎo)入對象的OWNER更換成TOUSER指定的用戶
3、系統(tǒng)高可用驗(yàn)證
3.1主備切換驗(yàn)證
3.1.1 主節(jié)點(diǎn)故障
在1主1備的部署架構(gòu)下，不同于1主多備的Raft協(xié)議自動選舉，主節(jié)點(diǎn)故障通過yasom仲裁，默認(rèn)是不開啟的。
1）使用命令開啟election
[yashan@tango-DB01 ~]$ yasboot
election enable on
-c yashandb
result: Succeed查看仲裁選舉的參數(shù)配置
[yashan@tango-DB01 ~]$ yasboot election config show -c yashandb
Protection Mode: MAXIMUM PROTECTION
Members:
[1-1:1] - Primary database
[1-2:2] - Physical standby database
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Apply Rate: 11.85 MByte/s
Properties:
FailoverThreshold = 9
FailoverAutoReinstate = false
ZeroDataLossMode = true
Automatic Failover: Enabled in Zero Data Loss Mode默認(rèn)是最大保護(hù)模式，表示redo log同步到備節(jié)點(diǎn)后，事務(wù)才提交。
- FailoverThreshold：備機(jī)心跳超時(shí)時(shí)間，到達(dá)該時(shí)間后，yasom將執(zhí)行failover切換流程
- FailoverAutoReinstate：是否啟用自動腦裂修復(fù)，啟用后如果備機(jī)發(fā)送腦裂處于Need REPAIR狀態(tài)，yasom將嘗試自動修復(fù)
- ZeroDataLossMode：是否啟用零丟失模式，啟用后主備設(shè)置為最大保護(hù)模式，當(dāng)主庫宕機(jī)后備機(jī)會自動failover；當(dāng)備庫異常時(shí)主庫降級為最大可用模式，并禁止自動failover，直到備庫恢復(fù)同步。
另外通過以下命令修改配置
$ yasboot election enable off -c yashandb
$ yasboot election config set -k FailoverThreshold -v 2 -c yashandb
$ yasboot election enable on -c yashandb
$ yasboot election config show -c yashandb2）檢查主備集群的狀態(tài)
[yashan@tango-DB01 ~]$ yasboot cluster status -c yashandb -d
hostid | node_type | nodeid | pid | instance_status | database_status | database_role | listen_address | data_path
----------------------------------------------------------------------------------------------------------------------------------------------------------
host0001 | db | 1-1:1 | 16119 | open | normal | primary | 192.168.112.121:1688 | /usr/local/yashandb/yasdb_data/db-1-1
----------+-----------+--------+-------+-----------------+-----------------+---------------+----------------------+---------------------------------------
host0002 | db | 1-2:2 | 15363 | open | normal | standby | 192.168.112.122:1688 | /usr/local/yashandb/yasdb_data/db-1-2
----------+-----------+--------+-------+-----------------+-----------------+---------------+----------------------+---------------------------------------3）模擬主節(jié)點(diǎn)故障，kill主節(jié)點(diǎn)進(jìn)程
[yashan@tango-DB01 ~]$ ps -fu $USER
UID PID PPID C STIME TTY TIME CMD
yashan 1874 1 0 11:14 ? 00:00:10 /usr/local/yashandb/yasdb_home/yashandb/23.1.1.100/bin/yasom -c yashandb -l 192.168.112.121:1675 -d
yashan 1952 1 0 11:16 ? 00:01:17 /usr/local/yashandb/yasdb_home/yashandb/23.1.1.100/bin/yasagent -c yashandb -l 192.168.112.121:1676 -d
yashan 95091 1 7 16:23 ? 00:01:47 /usr/local/yashandb/yasdb_home/yashandb/23.1.1.100/bin/yasdb open -D /usr/local/yashandb/yasdb_data/db-1-1
[yashan@tango-DB01 ~]$ kill -9 95091查看yasom.log日志，能看到failover切換信息
2023-11-25 16:47:38 DEBUG [yasom] replication.go:188 process replication event: failover
2023-11-25 16:47:38 INFO [yasom] task.go:24 call yasagent api success: NodeAPI.ExecSQL, get result: {"connection":"DISCONNECTED","status":"NORMAL","time_since_last_msg":
"2"}
2023-11-25 16:47:38 ERROR [yasom] task.go:21 call yasagent api failed: NodeAPI.ExecSQL, err: YAS-00402 failed to connect socket, errno 111, error message "Connection refu
sed"
2023-11-25 16:47:38 INFO [yasom] task.go:24 call yasagent api success: NodeAPI.ExecSQL, get result: {"database_role":"STANDBY","status":"NORMAL","switchover_status":"NOT
ALLOWED"}
2023-11-25 16:47:38 INFO [yasom] replication.go:231 execute failover sql command on node 1-2:2, next reset id is 1
2023-11-25 16:47:40 INFO [yasom] task.go:24 call yasagent api success: NodeAPI.ExecSQL, get result: {}
2023-11-25 16:47:40 INFO
[yasom] replication.go:246 failover succeeds4）查看主備狀態(tài)，備節(jié)點(diǎn)已經(jīng)升級為主節(jié)點(diǎn)
[yashan@tango-DB02 ~]$ yasboot cluster status -c yashandb -d
hostid | node_type | nodeid | pid | instance_status | database_status | database_role | listen_address | data_path
---------------------------------------------------------------------------------------------------------------------------------------------------------
host0001 | db | 1-1:1 | off | - | - | - | 192.168.112.121:1688 | /usr/local/yashandb/yasdb_data/db-1-1
----------+-----------+--------+------+-----------------+-----------------+---------------+----------------------+---------------------------------------
host0002 | db | 1-2:2 | 4410 | open | normal | primary | 192.168.112.122:1688 | /usr/local/yashandb/yasdb_data/db-1-2
----------+-----------+--------+------+-----------------+-----------------+---------------+----------------------+---------------------------------------重啟原來的主節(jié)點(diǎn)后，主備關(guān)系正常
[yashan@tango-DB02 ~]$ yasboot cluster status -c yashandb -d
hostid | node_type | nodeid | pid | instance_status | database_status | database_role | listen_address | data_path
-----------------------------------------------------------------------------------------------------------------------------------------------------------
host0001 | db | 1-1:1 | 102613 | open | normal | standby | 192.168.112.121:1688 | /usr/local/yashandb/yasdb_data/db-1-1
----------+-----------+--------+--------+-----------------+-----------------+---------------+----------------------+---------------------------------------
host0002 | db | 1-2:2 | 4410 | open | normal | primary | 192.168.112.122:1688 | /usr/local/yashandb/yasdb_data/db-1-2
----------+-----------+--------+--------+-----------------+-----------------+---------------+----------------------+---------------------------------------3.1.2 手動主備切換
1）登錄備機(jī)，檢查主備機(jī)同步狀態(tài)，確認(rèn)主備機(jī)同步無異常
SQL> SELECT database_id,database_name,log_mode,open_mode,database_role FROM V$DATABASE;
DATABASE_ID DATABASE_NAME LOG_MODE OPEN_MODE DATABASE_ROLE
--------------------- ---------------------------------------------------------------- ----------------- ----------------- -----------------
2888093870 yashandb ARCHIVELOG READ_ONLY STANDBY2）確認(rèn)備機(jī)同步狀態(tài)正常
SQL> SELECT connection, status, peer_addr, transport_lag, apply_lag FROM V$REPLICATION_STATUS;
CONNECTION STATUS PEER_ADDR TRANSPORT_LAG APPLY_LAG
----------------- ----------------- ---------------------------------------------------------------- ------------- ------------
CONNECTED NORMAL 192.168.112.121:1689 0 03）在備機(jī)執(zhí)行switchover切換操作，此時(shí)主機(jī)的所有事務(wù)中斷，且switchover執(zhí)行過程中，客戶端無法連接主機(jī)。
SQL>
alter database switchover;切換完成后，查看主備機(jī)的角色是否發(fā)生變化
[yashan@tango-DB01 ~]$ yasboot cluster status -c yashandb -d
hostid | node_type | nodeid | pid | instance_status | database_status | database_role | listen_address | data_path
----------------------------------------------------------------------------------------------------------------------------------------------------------
host0001 | db | 1-1:1 | 39926 | open | normal | standby | 192.168.112.121:1688 | /usr/local/yashandb/yasdb_data/db-1-1
----------+-----------+--------+-------+-----------------+-----------------+---------------+----------------------+---------------------------------------
host0002 | db | 1-2:2 | 38096 | open | normal | primary | 192.168.112.122:1688 | /usr/local/yashandb/yasdb_data/db-1-2
----------+-----------+--------+-------+-----------------+-----------------+---------------+----------------------+---------------------------------------4）也可以使用yaboot命令執(zhí)行主備切換
[yashan@tango-DB01 ~]$
yasboot node switchover -c yashandb -n 1-1
|key |value
|-----------------------------------------------+------
|clusterName |yashandb
|the group type of input node is |db
|the group name of input node is |dbg1
|the role of input node is |standby
|the primary node name of input node's group is |1-2
Switchover need primary node and selected node all are open and normal
Are you sure you want to switchover node 1-1 to primary: [yashandb][yes/no]: yes
type | uuid | name | hostid | index | status | return_code | progress | cost
-------------------------------------------------------------------------------------------------------------
task | 839275bfbfccf242 | SwitchoverYasdbCluster | - | dbg1 | SUCCESS | 0 | 100 | 2
------+------------------+------------------------+--------+-------+---------+-------------+----------+------
task completed, status: SUCCESS其中參數(shù)-n為要升主的節(jié)點(diǎn)ID（例如1-1，可以通過cluster status命令查看，不需要冒號及后面的數(shù)字）。檢查主備節(jié)點(diǎn)狀態(tài)，確認(rèn)已經(jīng)發(fā)生了切換。
3.2 表的閃回操作
YashanDB提供的閃回功能，當(dāng)用戶誤操作等原因?qū)е卤淼臄?shù)據(jù)被刪除，使用閃回功能可以查看過去的數(shù)據(jù)狀態(tài)，并在時(shí)間上倒回指定時(shí)間點(diǎn)的數(shù)據(jù)，無需從備份中恢復(fù)數(shù)據(jù)。注：該功能只對行存表（HEAP表）有效
3.2.1 delete操作閃回
#1、刪除表tb01數(shù)據(jù)
SQL> delete from tb01 where id<100;
99 rows affected.
SQL> select count(1) from tb01 ;
COUNT(1)
---------------------
9900
1 row fetched.
#2、開啟row movement
SQL> ALTER TABLE tb01 ENABLE ROW MOVEMENT;
Succeed.
#3、表數(shù)據(jù)閃回（通過時(shí)間戳閃回）
SQL> FLASHBACK TABLE tb01 TO TIMESTAMP TO_TIMESTAMP('2023/11/23 20:18:00','yyyy/mm/dd hh24:mi:ss');
FLASHBACK TABLE tb01 TO TIMESTAMP TO_TIMESTAMP('2023/11/23 20:18:00','yyyy/mm/dd hh24:mi:ss');
Succeed.
#4、查看數(shù)據(jù)返回正常
SQL> select count(1) from tb01 ;
COUNT(1)
---------------------
9999
1 row fetched.3.2.2 drop表操作閃回
#1、開啟了回收站ALTER SYSTEM SET RECYCLEBIN_ENABLED=ON;
#2、刪除表
SQL> drop table tb01 ;
#3、查詢回收站是否存在TB01表
SQL> SELECT object_name FROM DBA_RECYCLEBIN WHERE original_name = 'TB01';
OBJECT_NAME
----------------------------------------------------------------
BIN$2260
#4、表結(jié)構(gòu)及表數(shù)據(jù)閃回
SQL> FLASHBACK TABLE "BIN$2260" TO BEFORE DROP;
#5、表和索引已恢復(fù)
SQL> SELECT table_name FROM USER_TABLES;
TABLE_NAME
----------------------------------------------------------------
TB01
SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'TB01';
INDEX_NAME
----------------------------------------------------------------
UNIQ_IX01
IX_C13.2.3 truncate表閃回
#1、開啟了回收站ALTER SYSTEM SET RECYCLEBIN_ENABLED=ON;
#2、TRUNCATE表操作
SQL> truncate table tb01;
#3、查詢回收站是否存在TB01表
SQL> SELECT object_name FROM DBA_RECYCLEBIN WHERE original_name = 'TB01';
OBJECT_NAME
----------------------------------------------------------------
TB01
#4、表結(jié)構(gòu)及表數(shù)據(jù)閃回
SQL> FLASHBACK TABLE "TB01" TO BEFORE TRUNCATE;
#5、表數(shù)據(jù)已恢復(fù)
SQL> select count(1) from tb01;
COUNT(1)
---------------------
99994、總結(jié)
本文簡要介紹了YashanDB的特性以及部署架構(gòu)，并測試部署了1主1備的環(huán)境進(jìn)行功能性驗(yàn)證?？偨Y(jié)下來有以下幾點(diǎn)：
- 文檔還是挺全的，根據(jù)文檔一步步能夠搭建好環(huán)境使用。受限于個(gè)人體驗(yàn)版，只能部署單實(shí)例的主備環(huán)境，沒法體驗(yàn)分布式以及共享集群的架構(gòu)
- 作為一個(gè)新數(shù)據(jù)庫，對用戶來說有個(gè)學(xué)習(xí)和適應(yīng)的過程，一些命令和SQL語句和MySQL或Oracle等還是有些差異，可能和Oracle更接近。
- 生態(tài)的建設(shè)上還在慢慢完善，比如對Python或Go等語言的支持，主流的開發(fā)框架的支持等。還有和主流的信創(chuàng)操作系統(tǒng)的適配性和穩(wěn)定性，需要進(jìn)一步測試驗(yàn)證。尤其是在一眾國產(chǎn)數(shù)據(jù)庫產(chǎn)品中建立領(lǐng)先優(yōu)勢，還是有一定的挑戰(zhàn)性的。
- 在架構(gòu)上主備集群的可用性通過yasom檢測的，如果這臺服務(wù)器宕機(jī)后主備切換是沒有自動實(shí)現(xiàn)的，在高可用切換這一塊有優(yōu)化的空間。像OpenGauss在后續(xù)版本增加CM實(shí)現(xiàn)切換功能，也可以參考GoldenDB管理節(jié)點(diǎn)對數(shù)據(jù)節(jié)點(diǎn)的高可用管理。
- 在運(yùn)維功能實(shí)現(xiàn)上，數(shù)據(jù)庫運(yùn)維管理平臺，對服務(wù)器的納管和一鍵部署、監(jiān)控指標(biāo)和客戶的現(xiàn)有流程對接、CMDB元數(shù)據(jù)模型和告警接入等，還是有很多客戶化落地的細(xì)節(jié)。
責(zé)任編輯：武曉燕
來源：
牧羊人的方向
