读写分离搭建实操

读写分离搭建实操
1.1停止服务
./DmServiceDMSERVER stop
1.2 A机器：实例、启服务
–初始化实例
[dmdba@bin]# ./dminit PATH=/opt/dmdbms/data/ PAGE_SIZE=16 LOG_SIZE=2048 charset=1

–启动服务
[dmdba@bin]# ./dmserver /opt/dmdbms/data/DAMENG/dm.ini

[dmdba@bin]# exit

1配置A机器
1.1注册服务
①复制数据库和守护进程服务文件到/etc/rc.d/init.d目录
cp /opt/dmdbms/bin/service_template/DmService /etc/rc.d/init.d/DmServiceRWC1_01
cp /opt/dmdbms/bin/service_template/DmWatcherService /etc/rc.d/init.d/DmServiceWatcher
②vi /etc/rc.d/init.d/DmServiceRWC1_01文件
START_MODE=mount
DM_HOME="/opt/dmdbms"
INI_PATH="/opt/dmdbms/data/DAMENG/dm.ini"

③vi /etc/rc.d/init.d/DmServiceWatcher文件
DM_HOME="/opt/dmdbms"
INI_PATH="/opt/dmdbms/data/DAMENG/dmwatcher.ini"

④修改启动服务的等级连接
ln -s /etc/rc.d/init.d/DmServiceRWC1_01 /etc/rc.d/rc0.d/K02DmServiceRWC1_01
ln -s /etc/rc.d/init.d/DmServiceRWC1_01 /etc/rc.d/rc2.d/S98DmServiceRWC1_01
ln -s /etc/rc.d/init.d/DmServiceRWC1_01 /etc/rc.d/rc3.d/S98DmServiceRWC1_01
ln -s /etc/rc.d/init.d/DmServiceRWC1_01 /etc/rc.d/rc5.d/S98DmServiceRWC1_01
ln -s /etc/rc.d/init.d/DmServiceRWC1_01 /etc/rc.d/rc6.d/K02DmServiceRWC1_01
chown dmdba.dmdba /etc/rc.d/init.d/DmServiceRWC1_01
ln -s /etc/rc.d/init.d/DmServiceWatcher /etc/rc.d/rc0.d/K02DmServiceWatcher
ln -s /etc/rc.d/init.d/DmServiceWatcher /etc/rc.d/rc2.d/S98DmServiceWatcher
ln -s /etc/rc.d/init.d/DmServiceWatcher /etc/rc.d/rc3.d/S98DmServiceWatcher
ln -s /etc/rc.d/init.d/DmServiceWatcher /etc/rc.d/rc5.d/S98DmServiceWatcher
ln -s /etc/rc.d/init.d/DmServiceWatcher /etc/rc.d/rc6.d/K02DmServiceWatcher
chown dmdba.dmdba /etc/rc.d/init.d/DmServiceWatcher
chmod 777 /etc/rc.d/init.d/DmServiceRWC1_01
chmod 777 /etc/rc.d/init.d/DmServiceWatcher
备注：删除自启
rm -rf /etc/rc.d/init.d/DmServiceRWC1_01
rm -rf /etc/rc.d/rc0.d/K02DmServiceRWC1_01
rm -rf /etc/rc.d/rc2.d/S98DmServiceRWC1_01
rm -rf /etc/rc.d/rc3.d/S98DmServiceRWC1_01
rm -rf /etc/rc.d/rc5.d/S98DmServiceRWC1_01
rm -rf /etc/rc.d/rc6.d/K02DmServiceRWC1_01
rm -rf /etc/rc.d/init.d/DmServiceWatcher
rm -rf /etc/rc.d/rc0.d/K02DmServiceWatcher
rm -rf /etc/rc.d/rc2.d/S98DmServiceWatcher
rm -rf /etc/rc.d/rc3.d/S98DmServiceWatcher
rm -rf /etc/rc.d/rc5.d/S98DmServiceWatcher
rm -rf /etc/rc.d/rc6.d/K02DmServiceWatcher

1.2配置 dm.ini
INSTANCE_NAME = RWC1_01 #数据库实例名
PORT_NUM = 5236 #数据库实例监听端口
DW_PORT = 5536 #守护环境下，监听守护进程连接端口
DW_ERROR_TIME = 60 #接收守护进程消息超时时间
ALTER_MODE_STATUS = 0 #不允许手工方式修改实例模式/状态
MAL_INI = 1 #打开MAL系统
ARCH_INI = 1 #打开归档配置
HA_INST_CHECK_FLAG = 1 #检测是否多个实例进程同时启动

1.3配置 dmarch.ini
[ARCHIVE_TIMELY1]
ARCH_TYPE = TIMELY #即时归档类型
ARCH_DEST = RWC1_01B #即时归档目标实例名
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL #本地归档类型
ARCH_DEST = /opt/dmdbms/data/DAMENG/arch/ #本地归档文件存放路径
ARCH_FILE_SIZE = 2048 #单位MB，本地单个归档文件最大值
ARCH_SPACE_LIMIT = 10240 #单位MB，0表示无限制，范围1024~4294967294MB

1.4配置 dmmal.ini
MAL_CHECK_INTERVAL = 30 #MAL链路检测时间间隔
MAL_CONN_FAIL_INTERVAL = 60 #判定MAL链路断开的时间
MAL_TEMP_PATH = /opt/dmdbms/data/malpath/ #临时文件目录
MAL_BUF_SIZE = 512 #单个MAL缓存大小限制，单位MB
MAL_SYS_BUF_SIZE = 2048 #MAL系统总内存大小限制，单位MB
MAL_COMPRESS_LEVEL = 0 #MAL消息压缩等级，0表示不压缩

[MAL_INST1]
MAL_INST_NAME = RWC1_01 #实例名，和 dm.ini中的INSTANCE_NAME一致
MAL_HOST = 192.168.1.12 #MAL系统监听TCP连接的IP地址
MAL_PORT = 5336 #MAL系统监听TCP连接的端口
MAL_INST_HOST = 192.168.1.12 #实例的对外服务IP地址
MAL_INST_PORT = 5236 #实例的对外服务端口，和dm.ini中的PORT_NUM一致
MAL_DW_PORT = 5436 #实例对应的守护进程监听TCP连接的端口
[MAL_INST2]
MAL_INST_NAME = RWC1_01B
MAL_HOST = 192.168.1.13
MAL_PORT = 5336
MAL_INST_HOST = 192.168.1.13
MAL_INST_PORT = 5236
MAL_DW_PORT = 5436

1.5配置 dmwatcher.ini
[GRWC1_01]
DW_TYPE = GLOBAL #全局守护类型
DW_MODE = MANUAL #故障手动切换模式
DW_ERROR_TIME = 30 #远程守护进程故障认定时间
INST_ERROR_TIME = 30 #本地实例故障认定时间
INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间
INST_OGUID = 45331 #守护系统唯一OGUID值
INST_INI = /opt/dmdbms/data/DAMENG/dm.ini #dm.ini配置文件路径
INST_AUTO_RESTART = 1 #打开实例的自动启动功能
INST_STARTUP_CMD = /opt/dmdbms/bin/dmserver #命令行方式启动

1.6配置 dmwatcher.ctl
[dmdba@bin]# ./dmctlcvt t2dwctl /opt/dmdbms/data/DAMENG/dmwatcher.ini /opt/dmdbms/dmwatcher/
[dmdba@bin]# cp /opt/dmdbms/dmwatcher/GRWC1_01/dmwatcher.ctl /opt/dmdbms/data/DAMENG/

1.7拷贝实例
–拷贝实例到B机器
[dmdba@bin]# scp -r /opt/dmdbms/data/DAMENG dmdba@192.168.1.13:/opt/dmdbms/data/

2配置B机器
停服务

2.1注册服务
①复制数据库和守护进程服务文件到/etc/rc.d/init.d目录
cp /opt/dmdbms/bin/service_template/DmService /etc/rc.d/init.d/DmServiceRWC1_01B
cp /opt/dmdbms/bin/service_template/DmWatcherService /etc/rc.d/init.d/DmServiceWatcher
②vi /etc/rc.d/init.d/DmServiceRWC1_01B文件
START_MODE=mount
DM_HOME="/opt/dmdbms"
INI_PATH="/opt/dmdbms/data/DAMENG/dm.ini"

③vi /etc/rc.d/init.d/DmServiceWatcher文件
DM_HOME="/opt/dmdbms"
INI_PATH="/opt/dmdbms/data/DAMENG/dmwatcher.ini"

④修改启动服务的等级连接
ln -s /etc/rc.d/init.d/DmServiceRWC1_01B /etc/rc.d/rc0.d/K02DmServiceRWC1_01B
ln -s /etc/rc.d/init.d/DmServiceRWC1_01B /etc/rc.d/rc2.d/S98DmServiceRWC1_01B
ln -s /etc/rc.d/init.d/DmServiceRWC1_01B /etc/rc.d/rc3.d/S98DmServiceRWC1_01B
ln -s /etc/rc.d/init.d/DmServiceRWC1_01B /etc/rc.d/rc5.d/S98DmServiceRWC1_01B
ln -s /etc/rc.d/init.d/DmServiceRWC1_01B /etc/rc.d/rc6.d/K02DmServiceRWC1_01B
chown dmdba.dmdba /etc/rc.d/init.d/DmServiceRWC1_01B
ln -s /etc/rc.d/init.d/DmServiceWatcher /etc/rc.d/rc0.d/K02DmServiceWatcher
ln -s /etc/rc.d/init.d/DmServiceWatcher /etc/rc.d/rc2.d/S98DmServiceWatcher
ln -s /etc/rc.d/init.d/DmServiceWatcher /etc/rc.d/rc3.d/S98DmServiceWatcher
ln -s /etc/rc.d/init.d/DmServiceWatcher /etc/rc.d/rc5.d/S98DmServiceWatcher
ln -s /etc/rc.d/init.d/DmServiceWatcher /etc/rc.d/rc6.d/K02DmServiceWatcher
chown dmdba.dmdba /etc/rc.d/init.d/DmServiceWatcher
chmod 777 /etc/rc.d/init.d/DmServiceRWC1_01B
chmod 777 /etc/rc.d/init.d/DmServiceWatcher
备注：删除自启
rm -rf /etc/rc.d/init.d/DmServiceRWC1_01B
rm -rf /etc/rc.d/rc0.d/K02DmServiceRWC1_01B
rm -rf /etc/rc.d/rc2.d/S98DmServiceRWC1_01B
rm -rf /etc/rc.d/rc3.d/S98DmServiceRWC1_01B
rm -rf /etc/rc.d/rc5.d/S98DmServiceRWC1_01B
rm -rf /etc/rc.d/rc6.d/K02DmServiceRWC1_01B
rm -rf /etc/rc.d/init.d/DmServiceWatcher
rm -rf /etc/rc.d/rc0.d/K02DmServiceWatcher
rm -rf /etc/rc.d/rc2.d/S98DmServiceWatcher
rm -rf /etc/rc.d/rc3.d/S98DmServiceWatcher
rm -rf /etc/rc.d/rc5.d/S98DmServiceWatcher
rm -rf /etc/rc.d/rc6.d/K02DmServiceWatcher

2.2配置 dm.ini
INSTANCE_NAME = RWC1_01B #数据库实例名

2.3配置 dmarch.ini
[ARCHIVE_TIMELY1]
ARCH_TYPE = TIMELY #即时归档类型
ARCH_DEST = RWC1_01 #即时归档目标实例名
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL #本地归档类型
ARCH_DEST = /opt/dmdbms/data/DAMENG/arch/ #本地归档文件存放路径
ARCH_FILE_SIZE = 2048 #单位MB，本地单个归档文件最大值
ARCH_SPACE_LIMIT = 10240 #单位MB，0表示无限制，范围1024~4294967294MB

2.4相同配置项
与A机器RWC1_01的dmmal.ini、dmwatcher.ini、dmwatcher.ctl相同

3配置监视器
①在确认监视器机器上 注册确认监视器自启服务。
②在各节点数据库的bin目录中 存放非/确认监视器配置文件。
3.1注册服务
①复制DmServiceMonitor文件到/etc/rc.d/init.d目录
cp /opt/dmdbms/bin/service_template/DmMonitorServive /etc/rc.d/init.d/DmServiceMonitor
②vi /etc/rc.d/init.d/DmServiceMonitor文件
DM_HOME="/opt/dmdbms"
INI_PATH="/opt/dmdbms/bin/dmmonitor.ini"

③修改启动服务的等级连接
ln -s /etc/rc.d/init.d/DmServiceMonitor /etc/rc.d/rc0.d/K02DmServiceMonitor
ln -s /etc/rc.d/init.d/DmServiceMonitor /etc/rc.d/rc2.d/S98DmServiceMonitor
ln -s /etc/rc.d/init.d/DmServiceMonitor /etc/rc.d/rc3.d/S98DmServiceMonitor
ln -s /etc/rc.d/init.d/DmServiceMonitor /etc/rc.d/rc5.d/S98DmServiceMonitor
ln -s /etc/rc.d/init.d/DmServiceMonitor /etc/rc.d/rc6.d/K02DmServiceMonitor
chown dmdba.dmdba /etc/rc.d/init.d/DmServiceMonitor
chmod 777 /etc/rc.d/init.d/DmServiceMonitor
备注：删除自启
rm -rf /etc/rc.d/init.d/DmServiceMonitor
rm -rf /etc/rc.d/rc0.d/K02DmServiceMonitor
rm -rf /etc/rc.d/rc2.d/S98DmServiceMonitor
rm -rf /etc/rc.d/rc3.d/S98DmServiceMonitor
rm -rf /etc/rc.d/rc5.d/S98DmServiceMonitor
rm -rf /etc/rc.d/rc6.d/K02DmServiceMonitor

3.2配置 dmmonitor.ini
MON_DW_CONFIRM = 1 #0为非确认，1为确认
MON_LOG_PATH = …/log #监视器日志文件存放路径
MON_LOG_INTERVAL = 60 #每隔 60s 定时记录系统信息到日志文件
MON_LOG_FILE_SIZE = 512 #单位MB，范围1~2048MB
MON_LOG_SPACE_LIMIT = 2048 #日志文件总占用2048MB

[GRWC1_01]
MON_INST_OGUID = 45331 #组GRWC1_01的唯一OGUID 值
MON_DW_IP = 192.168.1.12:5436 #IP对应MAL_HOST，PORT对应MAL_DW_PORT
MON_DW_IP = 192.168.1.13:5436

3.3监视器使用
命令 含义
list 列出所有实例对应守护进程的配置信息
show 显示所有实例组的信息
tip 查看系统当前运行状态
login 登录监视器
logout 退出登录
choose switchover <组名> 显示可切换为Primary实例的列表
switchover <组名>.<实例名> 使用指定组的指定实例，切换为Primary实例
choose takeover <组名> 显示可接管故障Primary实例的列表
takeover <组名>.<实例名> 使用指定组的指定实例，接管故障Primary实例
choose takeover force <组名> 显示可强制接管故障Primary实例的列表
takeover force <组名>.<实例名> 使用指定组的指定实例，强制接管故障Primary实例
主机故障后，登录备机执行SELECT SF_DW_CHECK_TAKEOVER();返回1代表可以接管，0表示不能接管。

4启动服务及查看信息
4.1启动数据库并修改参数
A机器
[dmdba@bin]# /etc/rc.d/init.d/DmServiceRWC1_01 start

SQL> SP_SET_OGUID(45331);
SQL> ALTER DATABASE PRIMARY;

B机器
[dmdba@bin]# /etc/rc.d/init.d/DmServiceRWC1_01B start

SQL> SP_SET_OGUID(45331);
SQL> ALTER DATABASE STANDBY;

4.2查询主备库信息是否一致
查看归档是否打开、归档信息、MAL信息、OGUID信息
SQL> SELECT ARCH_MODE FROM V$DATABASE;SQL>SELECTARC{H}_{N}AME,ARC{H}_{T}YPE,ARC{H}_{D}ESTFROMV$DM_ARCH_INI;
SQL> SELECT * FROM V$D{M}_{M}A{L}_{I}NI;SQL>SELECTOGUIDFROMV$INSTANCE;

4.3启动守护进程
A机器：[dmdba@bin]# /etc/rc.d/init.d/DmServiceWatcher start

B机器：[dmdba@bin]# /etc/rc.d/init.d/DmServiceWatcher start

4.4启动监视器
[dmdba@bin]# /etc/rc.d/init.d/DmServiceMonitor start
或./dmmonitor /opt/dmdbms/bin/dmmonitor.ini

4.5启停集群
启动：A机器守护进程→B机器守护进程
A机器：[dmdba@bin]# /etc/rc.d/init.d/DmServiceWatcher start
B机器：[dmdba@bin]# /etc/rc.d/init.d/DmServiceWatcher start
停止：A机器守护进程→B机器守护进程→A机器RWC1_01主库→B机器RWC1_01B备库
A机器：[dmdba@bin]# /etc/rc.d/init.d/DmServiceWatcher stop
B机器：[dmdba@bin]# /etc/rc.d/init.d/DmServiceWatcher stop
A机器：[dmdba@bin]# /etc/rc.d/init.d/DmServiceRWC1_01 stop
B机器：[dmdba@bin]# /etc/rc.d/init.d/DmServiceRWC1_01B stop

5配置dm_svc.conf文件
5.1dm_svc.conf配置内容
RWC1=(192.168.1.12:5236,192.168.1.13:5236)
TIME_ZONE=(480)
LANGUAGE=(cn)
LOGIN_ENCRYPT=(0)
RW_SEPARATE=(1)
RW_PERCENT=(0)
SWITCH_TIME=(6000)
SWITCH_INTERVAL=(500)

5.2应用连接
①文件存放：
Linux：将dm_svc.conf文件放在应用服务器/etc目录下。
Windows32位：将dm_svc.conf文件放在应用服务器system32目录下。
Windows64位：将dm_svc.conf文件放在应用服务器system32和syswow64目录下。
②配置应用连接串：
dm.jdbc.driver.DmDriver
jdbc:dm://RWC1?rwSeparate=1&rwPercent=0
xml文件中&是特殊字符，需要实体化&字符使用：jdbc:dm://RWC1?rwSeparate=1&rwPercent=0
rwSeparate：是否使用读写分离系统
rwpercent：分发多少读的事务到主库上执行，值越大，主库压力越大(0-100)。