目录

读写分离集群

概念

实验配置

虚拟机配置

实验任务

安装部署

配置A机器

初始化A中实例

配置A机器的dm( ).ini文件

A机器dm.ini

A机器dmarch.ini

A机器dmmal.ini

A机器dmwatcher.ini

拷贝备份到BC机器

A机器注册服务

配置B机器

初始化B机器中实例

B机器中恢复数据

B机器配置dm.ini dmarch.ini dmmal.ini dmwatcher.ini

B机器dm.ini

B机器dmarch.ini

B机器dmmal.ini

B机器dmwatcher.ini

B机器注册服务

配置C机器

C机器初始化

C机器恢复数据

C机器配置dm.ini dmarch.ini dmmal.ini dmwatcher.ini

C机器dm.ini

C机器dmarch.ini

C机器dmmal.ini

C机器dmwatcher.ini

C机器注册服务

配置监视器

监视器注册服务

启动服务

启动A数据库并修改参数

启动B数据库并修改参数

启动C数据库并修改参数

ABC启动守护进程

前台启动监视器

建立表格测试

A中建立表格插入数据

BC中查询数据

制造主机故障

A机器故障

B机器接管服务

制造B机器故障

C机器接管服务

恢复故障主机

恢复B机器

切换BC主备集群

恢复A机器

切换AB主备集群

读写分离集群

概念

读写分离集群是一种数据库架构设计模式，其核心思想是将数据库的读操作（如 SELECT 查询）与写操作（如 INSERT、UPDATE、DELETE）分别路由到不同的数据库实例上执行，从而提升系统的整体性能、可用性和可扩展性。

在传统的单机数据库架构中，所有的读写请求都由同一个数据库实例处理。当系统负载增加、特别是读请求远多于写请求时，单一节点容易成为性能瓶颈。而通过引入读写分离机制，可以将大量只读请求分发到多个只读副本（备库）上处理，主库则专注于处理事务性写操作，有效减轻主库压力，提高并发处理能力。

实验配置

本次实验基于3台CentOS7虚拟机搭建1主2备读写分离集群。

虚拟机配置

虚拟机物理配置

节点

Ip地址

角色

硬件配置

A

192.168.138.134

主库（GRP1_RWW_01)

1核 CPU / 2GB 内存 / 40GB 磁盘

B

192.168.138.132

备库

（GRP1_RWW_02)

1核 CPU / 2GB 内存 / 30GB 磁盘

C

192.168.138.135

备库（GRP1_RWW_03)

1核 CPU / 2GB 内存 / 30GB 磁盘
虚拟机端口配置

节点

实例端口

MAL端口

MAL守护进程端口

守护进程端口

A

5236

5336

5436

5536

B

5236

5337

5437

5537

C

5336

5338

5438

5538

三台虚拟机的OGUID为均45331，守护组为GRP1。操作系统为CentOS Linux release 7.9 (Core)；达梦镜像版本为dm8_20251016_x86_CentOS7_64.iso；安装目录为/home/dmdba/dmdbms；实例目录为/home/dmdba/dmdbms/data。

实验任务

完成3节点读写分离集群的搭建，要求如下：

     1）完成读写分离集群的部署，并测试建表和插入数据备机是否能及时同步，并可查询到数据。

     2）制造主机故障，测试备机可接管并持续提供服务。

     3）恢复故障主机，将故障主机加入集群。

     4）手动切换主备集群，恢复原主备集群的位置。

安装部署

配置A机器

初始化A中实例

/home/dmdba/dmdbms/bin/dminit \
> PATH=/home/dmdba/dmdbms/data/ \
> INSTANCE_NAME=GRP1_RT_01 \
> PAGE_SIZE=32 \
> EXTENT_SIZE=32 \
> LOG_SIZE=2048 \
> SYSDBA_PWD=Dameng123! \
> SYSAUDITOR_PWD=Dameng123!

启动A机器服务

/home/dmdba/dmdbms/bin/dmserver /home/dmdba/dmdbms/data/DAMENG/dm.ini

连接数据库

/home/dmdba/dmdbms/bin/disql SYSDBA/Dameng123!
ALTER DATABASE MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE ADD ARCHIVELOG 'DEST=/home/dmdba/dmdbms/data/DAMENG/arch, TYPE=LOCAL, FILE_SIZE=1024, SPACE_LIMIT=51200';
ALTER DATABASE OPEN;

创建备份

BACKUP DATABASE BACKUPSET '/home/dmdba/dmdbms/data/DAMENG/bak/BACKUP_FILE';

配置A机器的dm( ).ini文件

A机器dm.ini
SP_SET_PARA_VALUE(2, 'PORT_NUM', 5236);
SP_SET_PARA_VALUE(2, 'DW_INACTIVE_INTERVAL', 60);
SP_SET_PARA_VALUE(2, 'ALTER_MODE_STATUS', 0);
SP_SET_PARA_VALUE(2, 'ENABLE_OFFLINE_TS', 2);
SP_SET_PARA_VALUE(2, 'MAL_INT', 1);
SP_SET_PARA_VALUE(2, 'RLOG_SEND_APPLY_MON', 64);
COMMIT;

A机器dmarch.ini
vim /home/dmdba/dmdbms/data/DAMENG/dmarch.ini
ARCH_WAIT_APPLY = 0

[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /home/dmdba/dmdbms/data/DAMENG/arch
ARCH_FILE_SIZE = 256
ARCH_SPACE_LIMIT = 1024

[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RWW_02

[ARCHIVE_REALTIME2]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RWW_03

A机器dmmal.ini
vim /home/dmdba/dmdbms/data/DAMENG/dmmal.ini
MAL_CHECK_INTERVAL = 10
MAL_CONN_FAIL_INTERVAL = 10
MAL_TEMP_PATH = /home/dmdba/dmdbms/data/DAMENG/malpath
MAL_BUF_SIZE = 512
MAL_SYS_BUF_SIZE = 2048
MAL_COMPRESS_LEVEL = 0

[MAL_INST1]
MAL_INST_NAME = GRP1_RWW_01
MAL_HOST = 192.168.138.134
MAL_PORT = 5336
MAL_INST_HOST = 192.168.138.134
MAL_INST_PORT = 5236
MAL_DW_PORT = 5436
MAL_INST_DW_PORT = 5536

[MAL_INST2]
MAL_INST_NAME = GRP1_RWW_02
MAL_HOST = 192.168.138.132
MAL_PORT = 5337
MAL_INST_HOST = 192.168.138.132
MAL_INST_PORT = 5236
MAL_DW_PORT = 5437
MAL_INST_DW_PORT = 5537

[MAL_INST3]
MAL_INST_NAME = GRP1_RWW_03
MAL_HOST = 192.168.138.135
MAL_PORT = 5338
MAL_INST_HOST = 192.168.138.135
MAL_INST_PORT = 5236
MAL_DW_PORT = 5438
MAL_INST_DW_PORT = 5538

A机器dmwatcher.ini
vim /home/dmdba/dmdbms/data/DAMENG/dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = MANUAL
DW_ERROR_TIME = 20
INST_ERROR_TIME = 20
INST_RECOVER_TIME = 60
INST_OGUID = 45331
INST_INI = /home/dmdba/dmdbms/data/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0

拷贝备份到BC机器

scp -r /home/dmdba/dmdbms/data/DAMENG/bak/BACKUP_FILE
 dmdba@192.168.138.132:/home/dmdba/dmdbms/data/DAMENG/bak/

B中查询

scp -r /home/dmdba/dmdbms/data/DAMENG/bak/BACKUP_FILE 
dmdba@192.168.138.135:/home/dmdba/dmdbms/data/DAMENG/bak/

(这是一开始的图，后面我C机器的动态IP发生了变化，变成了135）

C中查询

A机器注册服务

sudo /home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmserver -p GRP1_RWW_01 -dm_ini /home/dmdba/dmdbms/data/DAMENG/dm.ini -m mount
sudo /home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmwatcher -p Watcher -watcher_ini /home/dmdba/dmdbms/data/DAMENG/dmwatcher.ini

配置B机器

初始化B机器中实例

./dminit PATH=/home/dmdba/dmdbms/data/ INSTANCE_NAME=GRP1_RWW_02 PAGE_SIZE=32 EXTENT_SIZE=32 LOG_SIZE=2048 SYSDBA_PWD=Dameng@123 SYSAUDITOR_PWD=Dameng@123

B机器中恢复数据

[dmdba@localhost bin]$ ./dmrman
dmrman V8
RMAN> restore database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' from backupset '/home/dmdba/dmdbms/data/DAMENG/bak'
RMAN> recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' from backupset '/home/dmdba/dmdbms/data/DAMENG/bak'
RMAN> recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' UPDATE DB_MAGIC

B机器配置dm.ini dmarch.ini dmmal.ini dmwatcher.ini

B机器dm.ini

一个个找到然后修改

vim /home/dmdba/dmdbms/data/DAMENG/dm.ini
INSTANCE_NAME  = GRP1_RWW_02
PORT_NUM = 5236
DW_INACTIVE_INTERVAL = 60
ALTER_MODE_STATUS = 0
ENABLE_OFFLINE_TS  = 2
MAL_INI = 1
ARCH_INI = 1
RLOG_SEND_APPLY_MON = 64

B机器dmarch.ini
vim /home/dmdba/dmdbms/data/DAMENG/dmarch.ini
ARCH_WAIT_APPLY = 0

[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /home/dmdba/dmdbms/data/DAMENG/arch
ARCH_FILE_SIZE = 256
ARCH_SPACE_LIMIT = 1024

[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RWW_01

[ARCHIVE_REALTIME2]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RWW_03

B机器dmmal.ini
vim  /home/dmdba/dmdbms/data/DAMENG/dmmal.ini
MAL_CHECK_INTERVAL = 10
MAL_CONN_FAIL_INTERVAL = 10
MAL_TEMP_PATH = /home/dmdba/dmdbms/data/DAMENG/malpath
MAL_BUF_SIZE = 512
MAL_SYS_BUF_SIZE = 2048
MAL_COMPRESS_LEVEL = 0

[MAL_INST1]
MAL_INST_NAME = GRP1_RWW_01
MAL_HOST = 192.168.138.134
MAL_PORT = 5336
MAL_INST_HOST = 192.168.138.134
MAL_INST_PORT = 5236
MAL_DW_PORT = 5436
MAL_INST_DW_PORT = 5536

[MAL_INST2]
MAL_INST_NAME = GRP1_RWW_02
MAL_HOST = 192.168.138.132
MAL_PORT = 5337
MAL_INST_HOST = 192.168.138.132
MAL_INST_PORT = 5236
MAL_DW_PORT = 5437
MAL_INST_DW_PORT = 5537

[MAL_INST3]
MAL_INST_NAME = GRP1_RWW_03
MAL_HOST = 192.168.138.135
MAL_PORT = 5338
MAL_INST_HOST = 192.168.138.135
MAL_INST_PORT = 5236
MAL_DW_PORT = 5438
MAL_INST_DW_PORT = 5538
B机器dmwatcher.ini
vim /home/dmdba/dmdbms/data/DAMENG/dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = MANUAL
DW_ERROR_TIME = 20
INST_ERROR_TIME = 20
INST_RECOVER_TIME = 60
INST_OGUID = 45331
INST_INI = /home/dmdba/dmdbms/data/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0

B机器注册服务

sudo /home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmserver -p GRP1_RWW_01 -dm_ini /home/dmdba/dmdbms/data/DAMENG/dm.ini -m mount
sudo /home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmwatcher -p Watcher -watcher_ini /home/dmdba/dmdbms/data/DAMENG/dmwatcher.ini

配置C机器

C机器初始化

./dminit PATH=/home/dmdba/dmdbms/data/ INSTANCE_NAME=GRP1_RWW_03 PAGE_SIZE=32 EXTENT_SIZE=32 LOG_SIZE=2048 SYSDBA_PWD=Dameng@123 SYSAUDITOR_PWD=Dameng@123

    C机器恢复数据

    [dmdba@localhost bin]$ ./dmrman
dmrman V8
RMAN> restore database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' from backupset '/home/dmdba/dmdbms/data/DAMENG/bak'
RMAN> recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' from backupset '/home/dmdba/dmdbms/data/DAMENG/bak'
RMAN> recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' UPDATE DB_MAGIC

    C机器配置dm.ini dmarch.ini dmmal.ini dmwatcher.ini

    C机器dm.ini
    vim /home/dmdba/dmdbms/data/DAMENG/dm.ini
    INSTANCE_NAME  = GRP1_RWW_02
PORT_NUM = 5236
DW_INACTIVE_INTERVAL = 60
ALTER_MODE_STATUS = 0
ENABLE_OFFLINE_TS  = 2
MAL_INI = 1
ARCH_INI = 1
RLOG_SEND_APPLY_MON = 64

    C机器dmarch.ini
    vim /home/dmdba/dmdbms/data/DAMENG/dmarch.ini
    ARCH_WAIT_APPLY = 0

[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /home/dmdba/dmdbms/data/DAMENG/arch
ARCH_FILE_SIZE = 256
ARCH_SPACE_LIMIT = 1024

[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RWW_01

[ARCHIVE_REALTIME2]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RWW_02

    C机器dmmal.ini
    vim /home/dmdba/dmdbms/data/DAMENG/dmmal.ini
    MAL_CHECK_INTERVAL = 10
MAL_CONN_FAIL_INTERVAL = 10
MAL_TEMP_PATH = /home/dmdba/dmdbms/data/DAMENG/malpath
MAL_BUF_SIZE = 512
MAL_SYS_BUF_SIZE = 2048
MAL_COMPRESS_LEVEL = 0

[MAL_INST1]
MAL_INST_NAME = GRP1_RWW_01
MAL_HOST = 192.168.138.134
MAL_PORT = 5336
MAL_INST_HOST = 192.168.138.134
MAL_INST_PORT = 5236
MAL_DW_PORT = 5436
MAL_INST_DW_PORT = 5536

[MAL_INST2]
MAL_INST_NAME = GRP1_RWW_02
MAL_HOST = 192.168.138.132
MAL_PORT = 5337
MAL_INST_HOST = 192.168.138.132
MAL_INST_PORT = 5236
MAL_DW_PORT = 5437
MAL_INST_DW_PORT = 5537

[MAL_INST3]
MAL_INST_NAME = GRP1_RWW_03
MAL_HOST = 192.168.138.135
MAL_PORT = 5338
MAL_INST_HOST = 192.168.138.135
MAL_INST_PORT = 5236
MAL_DW_PORT = 5438
MAL_INST_DW_PORT = 5538

    C机器dmwatcher.ini
    vim /home/dmdba/dmdbms/data/DAMENG/dmwatcher.ini
    [GRP1]
DW_TYPE = GLOBAL
DW_MODE = MANUAL
DW_ERROR_TIME = 20
INST_ERROR_TIME = 20
INST_RECOVER_TIME = 60
INST_OGUID = 45331
INST_INI = /home/dmdba/dmdbms/data/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0

    C机器注册服务

    sudo /home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmserver -p GRP1_RWW_03 -dm_ini /home/dmdba/dmdbms/data/DAMENG/dm.ini -m mount
sudo /home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmwatcher -p Watcher -watcher_ini /home/dmdba/dmdbms/data/DAMENG/dmwatcher.ini

    配置监视器

    vim /home/dmdba/dmdbms/bin/dmmonitor.ini
    MON_DW_CONFIRM = 0
MON_LOG_PATH = /home/dmdba/dmdbms/log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 512
MON_LOG_SPACE_LIMIT = 2048

[GRP1]
MON_INST_OGUID = 45331
MON_DW_IP = 192.168.138.134:5436
MON_DW_IP = 192.168.138.132:5437
MON_DW_IP = 192.168.138.135:5438

    监视器注册服务

    sudo /home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmmonitor -p Monitor -monitor_ini /home/dmdba/dmdbms/bin/dmmonitor.ini

    启动服务

    启动A数据库并修改参数

    systemctl start DmServiceGRP1_RWW_01
disql SYSDBA/Dameng@123@192.168.138.134:5236
SQL>SP_SET_OGUID(45331);
SQL>ALTER DATABASE PRIMARY;

    启动B数据库并修改参数

    /home/dmdba/dmdbms/bin/DmServiceGRP1_RWW_02 start
cd /home/dmdba/dmdbms/bin
disql "SYSDBA/Dameng@123@192.168.138.132:5236"
    SP_SET_OGUID(45331);
ALTER DATABASE STANDBY;
exit

    启动C数据库并修改参数

    /home/dmdba/dmdbms/bin/DmServiceGRP1_RWW_03 start
cd /home/dmdba/dmdbms/bin
disql "SYSDBA/Dameng@123@192.168.138.135:5236"
    SP_SET_OGUID(45331);
ALTER DATABASE STANDBY;
exit

    ABC启动守护进程

    ABC都执行

    /home/dmdba/dmdbms/bin/DmWatcherServiceWatcher start

    前台启动监视器

    /home/dmdba/dmdbms/bin/dmmonitor /home/dmdba/dmdbms/bin/dmmonitor.ini

    成功，ABC三台机器皆为OPEN状态，并且A为primary，BC为standby。

    建立表格测试

    A中建立表格插入数据

    CREATE TABLE TEST_1 (id INT, name VARCHAR(50));
INSERT INTO TEST_1 (id, name) VALUES(1, 'Zhangsan');
INSERT INTO TEST_1 (id, name) VALUES(2, 'Lisi');
COMMIT;
SELECT * FROM TEST_1;

    BC中查询数据

    SELECT * FROM TEST_1;

    查询成功，可以及时同步，数据同步正常。

    制造主机故障

    A机器故障

    停止A机器的守护进程和服务

    DmWatcherServiceWatcher stop
DmServiceGRP1_RWW_01 stop

    监视器中查询，此时已经查询不到A，主库已经故障

    B机器接管服务

    此时更改监视器配置

    MON_DW_CONFIRM = 1
MON_LOG_PATH = /home/dmdba/dmdbms/log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 512
MON_LOG_SPACE_LIMIT = 2048

[GRP1]
MON_INST_OGUID = 45331
MON_DW_IP = 192.168.138.134:5436
MON_DW_IP = 192.168.138.132:5437
MON_DW_IP = 192.168.138.135:5438

    ABC中dmwatcher.ini更改配置

    [GRP1]
DW_TYPE = GLOBAL
DW_MODE = AUTO
DW_ERROR_TIME = 20
INST_ERROR_TIME = 20
INST_RECOVER_TIME = 60
INST_OGUID = 45331
INST_INI = /home/dmdba/dmdbms/data/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0

    此时监视器会自动进行服务转换接管

    此时A故障，B为主机，C为备机

    制造B机器故障

    停止B机器的守护进程和服务

    DmWatcherServiceWatcher stop
DmServiceGRP1_RWW_02 stop

    C机器接管服务

    C机器此时会自动接管服务

    此时只剩C一个主库正在工作，AB均已故障

    在C机器中插入数据

    SELECT * FROM TEST_1;
INSERT INTO TEST_1 (id, name) VALUES(3, 'Wangwu');
COMMIT;
SELECT * FROM TEST_1;

    可以查询到，说明C机器是正常工作的。

    恢复故障主机

    恢复B机器

    启动B机器的守护进程和服务

    DmWatcherServiceWatcher start
DmServiceGRP1_RWW_02 start

    此时在监视器中查看BC机器状态，B为standby，C为primary。

    在B中查看数据，正常，可以查询到

    切换BC主备集群

    登录监视器，查看可以切换为主机的备机

    choose switchover GRP1

    切换B机器为主机

    switchover GRP1_GRP1_RWW_02

    此时B机器为primary，C机器为standby，主备切换成功。

    在B机器中插入数据

    INSERT INTO TEST_1 (id, name) VALUES(4, 'Zhaoxiaoliu');
COMMIT;
SELECT * FROM TEST_1;

    恢复A机器

    启动A机器的服务和守护进程

    DmWatcherServiceWatcher start
DmServiceGRP1_RWW_01 start

    A已加入集群，并且状态为standby

    在A中查询数据，查询结果正常。

    切换AB主备集群

    监视器中查询可切换的备机，有A和C。

    choose switchover GRP1

    登录监视器，切换A为主机

    switchover GRP1_GRP1_RWW_01

    此时A为primary，B和C为standby

    https://eco.dameng.com

    # 数据库 # 服务器 # 运维
    Logo
    https://edu.csdn.net/learn/39067/627173?utm_source=2019755004

    汇聚全球AI编程工具，助力开发者即刻编程。

    更多推荐

    cover

    四天蒸发近2000亿，宇树科技股价近乎“腰斩”；苹果举办库克告别派对：9月1日正式卸任CEO；Anthropic两款新Claude模型曝光 | 极客头条

    avatar AI编程社区
    cover

    Claude Code反超GitHub Copilot登顶第一、90%程序员已用上Agent，最新AI编码调查报告来了

    avatar AI编程社区
    cover

    如何用好 Claude Code 写后端代码

    avatar AI编程社区