溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
讀寫分離集群
環境說明
下列機器事先都安裝了DM軟件，安裝路徑為/dm7,執行程序保存在/dm7/bin目錄中，數據存放路徑為/dm7/data
機器名 IP地址 初始狀態 操作系統 18c1 10.13.13.171(對外) 主庫 JY1 redhat 6.7 10.13.13.171(mal對內) 18c2 10.13.13.172(對外) 10.13.13.171(mal對內) 備庫 JY2 redhat 6.7 dmks 10.13.13.187 確認監視器 redhat 6.7 實例名 port_num dw_port mal_host mal_port mal_dw_port jy1 5236 5239 10.13.13.171 5237 5238 jy2 5236 5239 10.13.13.172 5237 5238
數據準備
在主庫機器上初始化數據庫到目錄/dm7/data:
[dmdba@18c1 bin]$ ./dminit path=/dm7/data db_name=jy instance_name=jy1 port_num=5236 page_size=8 charset=0 initdb V7.1.6.46-Build(2018.02.08-89107)ENT db version: 0x7000a file dm.key not found, use default license! License will expire in 14 day(s) on 2020-06-17 log file path: /dm7/data/jy/jy01.log log file path: /dm7/data/jy/jy02.log write to dir [/dm7/data/jy]. create dm database success. 2020-06-03 16:01:44
注冊服務用于啟動數據庫
[root@18c1 root]# ./dm_service_installer.sh -i /dm7/data/jy/dm.ini -p jy1 -t dmserver ln -s '/usr/lib/systemd/system/DmServicejy1.service' '/etc/systemd/system/multi-user.target.wants/DmServicejy1.service' Finished to create the service (DmServicejy1)
正常啟動數據庫并正常關閉
[root@18c1 root]# service DmServicejy1 start Redirecting to /bin/systemctl start DmServicejy1.service [root@18c1 root]# ps -ef | grep dmserver dmdba 29989 1 13 16:04 ? 00:00:04 /dm7/bin/dmserver /dm7/data/jy/dm.ini -noconsole root 30292 3890 0 16:05 pts/1 00:00:00 grep --color=auto dmserver [dmdba@18c1 bin]$ ./disql SYSDBA/SYSDBA Server[LOCALHOST:5236]:mode is normal, state is open login used time: 8.010(ms) disql V7.1.6.46-Build(2018.02.08-89107)ENT Connected to: DM 7.1.6.46 SQL> exit [root@18c1 root]# service DmServicejy1 stop Redirecting to /bin/systemctl stop DmServicejy1.service
一.配置操作
主庫
配置dm.ini文件，配置以下參數
[dmdba@18c1 jy]$ vi dmmal.ini INSTANCE_NAME = JY1 PORT_NUM = 5236 DW_PORT = 5239 DW_ERROR_TIME = 60 ALTER_MODE_STATUS = 0 ENABLE_OFFLINE_TS = 2 MAL_INI = 1 ARCH_INI = 1 HA_INST_CHECK_FLAG = 1 RLOG_SEND_APPLY_MON = 64
配置dmmal.ini文件
配置MAL系統，各主備庫的dmmal.ini配置必須完全一致，MAL_HOST使用內部網絡IP，MAL_PORT與dm.ini中PORT_NUM使用不同的端口值，MAL_DW_PORT是各實例對應的守護進程之間，以及守護進程和監視器之間的通信端口，配置如下：
[dmdba@18c1 jy]$ vi dmmal.ini MAL_CHECK_INTERVAL = 5 MAL_CONN_FAIL_INTERVAL = 5 [MAL_INST1] MAL_INST_NAME = JY1 MAL_HOST = 10.13.13.171 MAL_PORT = 5237 MAL_INST_HOST = 10.13.13.171 MAL_INST_PORT = 5236 MAL_DW_PORT = 5238 [MAL_INST2] MAL_INST_NAME = JY2 MAL_HOST = 10.13.13.172 MAL_PORT = 5237 MAL_INST_HOST = 10.13.13.172 MAL_INST_PORT = 5236 MAL_DW_PORT = 5238
配置dmarch.ini
修改dmarch.ini，配置本地歸檔和實時歸檔。除了本地歸檔外，其他歸檔配置項中的ARCH_DEST表示實例是Primary模式時，需要同步歸檔數據的目標實例名。當前實例DM1是主庫，需要向DM2（實時備庫）同步數據，因此實時歸檔的ARCH_DEST配置為DM2。
[dmdba@18c1 jy]$ vi dmarch.ini [ARCHIVE_TIMELY] ARCH_TYPE = TIMELY ARCH_DEST = JY2 [ARCHIVE_LOCAL1] ARCH_TYPE = LOCAL ARCH_DEST = /dm7/data/jy/arch ARCH_FILE_SIZE = 128 ARCH_SPACE_LIMIT = 0
配置dmwatcher.ini
修改dmwatcher.ini配置守護進程，配置為全局守護類型，使用自動切換模式。
[dmdba@18c1 jy]$ vi dmwatcher.ini [GRP1] DW_TYPE = GLOBAL DW_MODE = AUTO DW_ERROR_TIME = 10 INST_RECOVER_TIME = 60 INST_ERROR_TIME = 10 INST_OGUID = 453332 INST_INI = /dm7/data/jy/dm.ini INST_AUTO_RESTART = 1 INST_STARTUP_CMD = /dm7/bin/dmserver RLOG_SEND_THRESHOLD = 0 RLOG_APPLY_THRESHOLD = 0
配置dmwatcher.ctl
同一個守護進程組，必須使用同一份dmwatcher.ctl。因此，只需要使用dmctlcvt工具生成一份dmwatcher.ctl文件，然后分別拷貝到各個數據庫目錄下即可。在配置完成dmwatcher.ini后，使用dmctlcvt工具生成dmwatcher.ctl：(特別注意，DEST目錄為jy的上一級目錄，否則不生成控制文件)
[dmdba@18c1 bin]$ ./dmctlcvt TYPE=3 SRC=/dm7/data/jy/dmwatcher.ini DEST=/dm7/data DMCTLCVT V7.1.6.46-Build(2018.02.08-89107)ENT convert txt to ctl success!
會在/dm7/data目錄中生成一個GRP1目錄，在GRP1目錄中生成了dmwatcher.ctl控制文件
[dmdba@18c1 data]$ ls -lrt total 4 drwxr-xr-x 6 dmdba dinstall 4096 Jun 3 16:23 jy drwxr-xr-x 2 dmdba dinstall 26 Jun 3 16:23 GRP1 [dmdba@18c1 data]$ cd GRP1/ [dmdba@18c1 GRP1]$ ls -lrt total 4 -rw-r--r-- 1 dmdba dinstall 512 Jun 3 16:23 dmwatcher.ctl [dmdba@18c1 GRP1]$ cp dmwatcher.ctl /dm7/data/jy/
拷貝生成的dmwatcher.ctl文件到數據文件目錄/dm7/data/jy。
將主庫相關文件傳輸到備機:
[dmdba@18c1 dm7]$ scp -r data/ dmdba@10.13.13.172:/dm7/ The authenticity of host '10.13.13.172 (10.13.13.172)' can't be established. ECDSA key fingerprint is 7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '10.13.13.172' (ECDSA) to the list of known hosts. dmdba@10.13.13.172's password: dminit20200603160057.log 100% 727 0.7KB/s 00:00 sqllog.ini 100% 479 0.5KB/s 00:00 dm.ctl 100% 5120 5.0KB/s 00:00 jy01.log 100% 256MB 128.0MB/s 00:02 jy02.log 100% 256MB 85.3MB/s 00:03 dm_20200603160143_364345.ctl 100% 5120 5.0KB/s 00:00 dm_20200603160450_367099.ctl 100% 5120 5.0KB/s 00:00 SYSTEM.DBF 100% 21MB 21.0MB/s 00:00 dm_service.prikey 100% 633 0.6KB/s 00:00 MAIN.DBF 100% 128MB 128.0MB/s 00:01 ROLL.DBF 100% 128MB 128.0MB/s 00:01 dminst.sys 100% 220 0.2KB/s 00:00 TEMP.DBF 100% 10MB 10.0MB/s 00:00 rep_conflict.log 100% 12 0.0KB/s 00:00 dm.ini 100% 40KB 39.8KB/s 00:00 dmmal.ini 100% 558 0.5KB/s 00:00 dmarch.ini 100% 340 0.3KB/s 00:00 dmwatcher.ini 100% 665 0.7KB/s 00:00 dmwatcher.ctl 100% 512 0.5KB/s 00:00 dmwatcher.ctl 100% 512 0.5KB/s 00:00 [dmdba@18c1 dm7]$
備機修改相關配置
修改dm.ini
INSTANCE_NAME = JY2
修改dmarch.ini
ARCH_DEST = JY1
dmwatcher.ini,dmwatcher.ctl,dmmal.ini與主庫一致不用修改 二:啟動到mount狀態設置oguid 主庫
[dmdba@18c1 bin]$ ./dmserver /dm7/data/jy/dm.ini mount file dm.key not found, use default license! version info: develop Use normal os_malloc instead of HugeTLB Use normal os_malloc instead of HugeTLB DM Database Server x64 V7.1.6.46-Build(2018.02.08-89107)ENT startup... License will expire in 14 day(s) on 2020-06-17 ckpt lsn: 32981 SYSTEM IS READY. [dmdba@18c1 bin]$ ./disql SYSDBA/SYSDBA Server[LOCALHOST:5236]:mode is normal, state is mount login used time: 5.995(ms) disql V7.1.6.46-Build(2018.02.08-89107)ENT Connected to: DM 7.1.6.46 SQL> sp_set_oguid(453332); DMSQL executed successfully used time: 68.576(ms). Execute id is 1.
備庫
[dmdba@18c2 bin]$ ./dmserver /dm7/data/jy/dm.ini mount file dm.key not found, use default license! version info: develop Use normal os_malloc instead of HugeTLB Use normal os_malloc instead of HugeTLB DM Database Server x64 V7.1.6.46-Build(2018.02.08-89107)ENT startup... License will expire in 14 day(s) on 2020-06-17 ckpt lsn: 32981 SYSTEM IS READY. [dmdba@18c2 bin]$ ./disql SYSDBA/SYSDBA Server[LOCALHOST:5236]:mode is normal, state is mount login used time: 6.344(ms) disql V7.1.6.46-Build(2018.02.08-89107)ENT Connected to: DM 7.1.6.46 SQL> sp_set_oguid(453332); DMSQL executed successfully used time: 32.329(ms). Execute id is 1.
注冊服務用于啟動數據庫
[root@18c2 root]# ./dm_service_installer.sh -i /dm7/data/jy/dm.ini -p jy2 -t dmserver ln -s '/usr/lib/systemd/system/DmServicejy2.service' '/etc/systemd/system/multi-user.target.wants/DmServicejy2.service' Finished to create the service (DmServicejy2)
三:打開數據庫
主庫以primary打開
SQL> alter database primary; executed successfully used time: 43.384(ms). Execute id is 0.
備庫以standby 打開
SQL> alter database standby; executed successfully used time: 100.645(ms). Execute id is 0.
四:啟動守護進程
啟動各個主備庫上的守護進程：
主庫
[dmdba@18c1 bin]$ ./dmwatcher /dm7/data/jy/dmwatcher.ini DMWATCHER[2.1] V7.1.6.46-Build(2018.02.08-89107)ENT DMWATCHER[2.1] IS READY show 2020-06-03 16:43:10 --------------------------------------------------------------------------- GROUP_NAME TYPE MODE OGUID MPP_FLAG AUTO_RESTART LOCAL_DW_STATUS GRP1 GLOBAL AUTO 453332 FALSE TRUE OPEN INST_OK NAME SVR_MODE SYS_STATUS OPEN_CNT RTYPE N_TASK TASK_MEM_USED FLSN CLSN SLSN SSLSN OK JY1 PRIMARY OPEN 2 TIMELY 0 0 34412 34412 34412 34412 ---------------------------------------------------------------------------
備庫
[dmdba@18c2 bin]$ ./dmwatcher /dm7/data/jy/dmwatcher.ini DMWATCHER[2.1] V7.1.6.46-Build(2018.02.08-89107)ENT DMWATCHER[2.1] IS READY show 2020-06-03 16:43:05 --------------------------------------------------------------------------- GROUP_NAME TYPE MODE OGUID MPP_FLAG AUTO_RESTART LOCAL_DW_STATUS GRP1 GLOBAL AUTO 453332 FALSE TRUE OPEN INST_OK NAME SVR_MODE SYS_STATUS OPEN_CNT RTYPE N_TASK TASK_MEM_USED FLSN CLSN SLSN SSLSN OK JY2 STANDBY OPEN 1 TIMELY 0 0 32981 32981 32981 32981 ---------------------------------------------------------------------------
五:查看file_lsn與cur_lsn主備庫是否一致
主庫
SQL> select file_LSN, cur_LSN from v$rlog; LINEID FILE_LSN CUR_LSN ---------- -------------------- -------------------- 1 34412 34412 used time: 1.203(ms). Execute id is 6.
備庫
SQL> select file_LSN, cur_LSN from v$rlog; LINEID FILE_LSN CUR_LSN ---------- -------------------- -------------------- 1 34412 34412 used time: 1.228(ms). Execute id is 3.
測試數據同步
主庫：
SQL> create table t1(id int); executed successfully used time: 23.402(ms). Execute id is 4. SQL> insert into t1 values(1); affect rows 1 used time: 1.303(ms). Execute id is 5. SQL> commit; executed successfully used time: 4.034(ms). Execute id is 6. SQL> select file_LSN, cur_LSN from v$rlog; LINEID FILE_LSN CUR_LSN ---------- -------------------- -------------------- 1 34443 34443 used time: 0.555(ms). Execute id is 7.
備庫:
SQL> select file_LSN, cur_LSN from v$rlog; LINEID FILE_LSN CUR_LSN ---------- -------------------- -------------------- 1 34443 34443 used time: 0.325(ms). Execute id is 2. SQL> select * from t1; LINEID ID ---------- ----------- 1 1 used time: 1.373(ms). Execute id is 3.
六:配置監視器(基本要求，安裝dm7的軟件)
由于主庫和實時備庫的守護進程配置為自動切換模式，因此這里選擇配置確認監視器。和普通監視器相比，確認監視器除了相同的命令支持外，在主庫發生故障時，能夠自動通知實時備庫接管為新的主庫，具有自動故障處理的功能。修改dmmonitor.ini配置確認監視器，其中MON_DW_IP中的IP和PORT和dmmal.ini中的MAL_HOST和MAL_DW_PORT配置項保持一致。
[dmdba@ora19c data]$ vi dmmonitor.ini [dmdba@dmks dmdbms]$ vi dmmonitor.ini MON_DW_CONFIRM = 1 MON_LOG_PATH = /dm_home/dmdbms/log MON_LOG_INTERVAL = 60 MON_LOG_FILE_SIZE = 32 MON_LOG_SPACE_LIMIT = 0 [GRP1] MON_INST_OGUID = 453332 MON_DW_IP = 10.13.13.171:5238 MON_DW_IP = 10.13.13.172:5238
啟動監視器：
[dmdba@dmks bin]$ ./dmmonitor /dm_home/dmdbms/dmmonitor.ini [monitor] 2020-06-03 10:54:59: DMMONITOR[2.1] V7.1.6.46-Build(2018.02.08-89107)ENT [monitor] 2020-06-03 10:54:59: DMMONITOR[2.1] IS READY. [monitor] 2020-06-03 10:54:59: Received message from(JY1) WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN SSLSN SLSN 2020-06-03 16:47:46 OPEN OK JY1 OPEN PRIMARY VALID 2 34443 34443 34443 34443 [monitor] 2020-06-03 10:54:59: Received message from(JY2) WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN SSLSN SLSN 2020-06-03 16:47:47 OPEN OK JY2 OPEN STANDBY VALID 2 34443 34443 34443 34443
在JDBC連接串中增加了兩個連接屬性：
rwSeparate 是否使用讀寫分離系統，默認0；取值（0不使用，1使用）。
rwPercent 分發到主庫的事務占主備庫總事務的百分比，有效值0~100，默認值25。
下面使用jdbc來測試讀寫分離
package cs;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class testrw
{
// 驅動
public static String driver = "dm.jdbc.driver.DmDriver";
// 連接URL
public static String url = "jdbc:dm://10.13.13.171:5236?rwSeparate=1&rwPercent=10";
// 數據庫用戶
public static String username = "SYSDBA";
// 數據庫密碼
public static String password = "SYSDBA";
/**
* @param driver
* @param url
* @param username
* @param password
* @return
*/
public static Connection createConnection(String driver, String url,
String username, String password) {
Connection connection = null;
try {
// 加載JDBC驅動程序
Class.forName(driver);
connection = DriverManager.getConnection(url, username, password);
} catch (Exception ex) {
ex.printStackTrace();
System.err.println("Load JDBC Driver Error : " + ex.getMessage());
}
return connection;
}
/**
* @param args
*/
public static void main(String[] args){
Connection connection = createConnection(driver,url,username,password);
System.out.println(connection);
try
{ PreparedStatement ps1=connection.prepareStatement("select * from t2;");
ResultSet rs = ps1.executeQuery();
Statement ps=connection.createStatement();
ps.addBatch("insert into t2 values(2)");
ps.executeBatch();
String name = "";
while (rs.next())
{
name = rs.getString("ID");
System.out.println("ID is:"+name);
}
rs.close();
ps.close();
}
catch (Exception ex)
{
ex.printStackTrace();
System.err.println("Run SQL Error : " + ex.getMessage());
}
}
}
備庫上執行的是查詢語句執行時間是2020-06-10 22:18:14.000000
SQL> select * from v$sessions; LINEID SESS_ID SESS_SEQ SQL_TEXT STATE N_STMT N_USED_STMT SEQ_NO CURR_SCH USER_NAME TRX_ID CREATE_TIME CLNT_TYPE TIME_ZONE CHK_CONS CHK_IDENT RDONLY INS_NULL COMPILE_FLAG AUTO_CMT DDL_AUTOCMT RS_FOR_QRY CHK_NET ISO_LEVEL CLNT_HOST APPNAME CLNT_IP OSNAME CONN_TYPE VPOOLADDR RUN_STATUS MSG_STATUS LAST_RECV_TIME LAST_SEND_TIME DCP_FLAG THRD_ID CONNECTED PORT_TYPE SRC_SITE MAL_ID CONCURRENT_FLAG ---------- -------------------- ----------- ------------------------- ------ ----------- ----------- ----------- -------- --------- -------------------- --------------------------- --------- --------- -------- --------- ------ -------- ------------ -------- ----------- ---------- ------- ----------- --------------- ------- --------------------- ---------------------- ------------ -------------------- ---------- ---------- --------------------------- --------------------------- -------- ----------- ----------- ----------- ----------- -------------------- --------------- 1 139663411057416 3 select * from v$sessions; ACTIVE 64 1 16 SYSDBA SYSDBA 140737488355329 2020-06-03 16:43:58.000000 SQL3 +08:00 N N N Y N N Y N N 1 18c2 disql ::1 Linux HOMOGENEOUS 139663410989816 RUNNING RECIEVE 2020-06-10 22:18:39.000000 2020-06-10 22:16:07.000000 N 4982 1 0 65535 NULL 0 2 139663412173640 11 select * from t2; IDLE 64 2 5 SYSDBA SYSDBA 0 2020-06-10 22:18:14.000000 JDBC +08:00 N N N Y N Y Y N N 1 WIN-ROUOJ6ERFO3 ::ffff:10.13.13.242 Windows Server 2008 R2 HOMOGENEOUS 139663412106040 IDLE SEND 2020-06-10 22:18:21.000000 2020-06-10 22:18:21.000000 N 12355 1 0 65535 NULL 0 used time: 0.833(ms). Execute id is 25.
主庫上執行的是插入語句執行時間是2020-06-10 22:18:13.000000
SQL> select * from v$sessions; LINEID SESS_ID SESS_SEQ SQL_TEXT STATE N_STMT N_USED_STMT SEQ_NO CURR_SCH USER_NAME TRX_ID CREATE_TIME CLNT_TYPE TIME_ZONE CHK_CONS CHK_IDENT RDONLY INS_NULL COMPILE_FLAG AUTO_CMT DDL_AUTOCMT RS_FOR_QRY CHK_NET ISO_LEVEL CLNT_HOST APPNAME CLNT_IP OSNAME CONN_TYPE VPOOLADDR RUN_STATUS MSG_STATUS LAST_RECV_TIME LAST_SEND_TIME DCP_FLAG THRD_ID CONNECTED PORT_TYPE SRC_SITE MAL_ID CONCURRENT_FLAG ---------- -------------------- ----------- ------------------------- ------ ----------- ----------- ----------- -------- --------- -------------------- --------------------------- --------- --------- -------- --------- ------ -------- ------------ -------- ----------- ---------- ------- ----------- --------------- ------- --------------------- ---------------------- ------------ -------------------- ---------- ---------- --------------------------- --------------------------- -------- ----------- ----------- ----------- ----------- -------------------- --------------- 1 139880043352296 1 select * from v$sessions; ACTIVE 64 1 42 SYSDBA SYSDBA 1124 2020-06-03 16:41:00.000000 SQL3 +08:00 N N N Y N N Y N N 1 18c1 disql ::1 Linux HOMOGENEOUS 139878427790072 RUNNING RECIEVE 2020-06-10 22:18:50.000000 2020-06-10 22:15:59.000000 N 17205 1 0 65535 NULL 0 2 139878562075400 12 insert into t2 values(2) IDLE 64 2 4 SYSDBA SYSDBA 0 2020-06-10 22:18:13.000000 JDBC +08:00 N N N Y N Y Y N N 1 WIN-ROUOJ6ERFO3 ::ffff:10.13.13.242 Windows Server 2008 R2 HOMOGENEOUS 139878562007800 IDLE SEND 2020-06-10 22:18:26.000000 2020-06-10 22:18:26.000000 N 21802 1 0 65535 NULL 0 used time: 1.602(ms). Execute id is 41.
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
