DB2HADR 一主多备 环境搭建 centos7搭建db2 hadr 一主多备

DB2HADR 一主多备 环境搭建 centos7搭建db2 hadr 一主多备

操作系统 linux centos7

DB2版本 11.1

主库 192.168.46.72 db2_cluster_node1 orange
主备库192.168.46.73 db2_cluster_node2 banana
从备库 192.168.46.74 db2_cluster_node3 peach

关于如何安装db2请参考我的上一篇文章centos7安装db2 version11.1

配置/etc/hosts，主备均配置

echo "
192.168.46.72 db2_cluster_node1 orange
192.168.46.73 db2_cluster_node2 banana
192.168.46.74 db2_cluster_node3 peach" >> /etc/hosts
1
2
3
4

配置 /etc/services 主备均配置

echo "
db2_cluster_hadr1  60001/tcp
db2_cluster_hadr2  60002/tcp
db2_cluster_hadr3  60003/tcp" >> /etc/services
1
2
3
4

db2网络的配置,可选

默认配置，可不配置

su - db2inst1
db2set DB2COMM=TCPIP
db2set -all
1
2
3

主库配置

创建目录

root用户下执行

mkdir -p /db2/db2imn/data/dogdb
mkdir -p /db2/db2imn/arch
mkdir  /db2/db2imn/data/dogdb/tbs
mkdir -p /home/db2imn/db2_backup
chown db2inst1:db2iadm1  -R /home/db2imn/db2_backup
chown db2inst1:db2iadm1  -R /db2
1
2
3
4
5
6

准备测试表

db2inst1用户下执行

##创建数据库
db2 "create db dogdb on /db2/db2imn/data/dogdb using codeset utf8 territory CN"
db2 connect to dogdb

##创建缓冲区
db2 "create bufferpool TB_BP_8k size 4000 pagesize 8k"
db2 "create bufferpool IDX_BP_8k size 4000 pagesize 8k"

##创建表空间
db2 "create regular tablespace TB_TS pagesize 8k managed by database using(file '/db2/db2imn/data/dogdb/tbs/TB_TS' 10g) bufferpool TB_BP_8k"  
db2 "create regular tablespace IDX_TS pagesize 8k managed by database using(file '/db2/db2imn/data/dogdb/tbs/IDX_TS' 4g) bufferpool IDX_BP_8k"  

##创建表
db2 connect to dogdb

db2 'create table t1(id integer,name varchar(10),sex varchar(10))  IN "TB_TS" INDEX IN "IDX_TS"'
db2 'create table t2(id integer,name varchar(10),sex varchar(10))  IN "TB_TS" INDEX IN "IDX_TS"'

db2 "insert into t1 values(1,'aaaa','bbbbb')"
db2 "insert into t2 values(1,'aaaa','bbbbb')"

db2 'select * from t1'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

配置归档

db2inst1用户下执行

db2 update db cfg for dogdb using logarchmeth1 disk:/db2/db2imn/arch
1

备份数据库

db2inst1用户下执行

db2 force applications all
db2 backup database dogdb to "/home/db2imn/db2_backup"
1
2

重启实例

db2inst1用户下执行

db2stop

db2start
1
2
3

Note：配置归档参数需要重启生效。

手动归档

db2inst1用户下执行

db2 archive log for db dogdb
1

配置HADR同步参数

db2inst1用户下执行

db2 update db cfg for dogdb using HADR_LOCAL_HOST db2_cluster_node1

db2 update db cfg for dogdb using HADR_LOCAL_SVC db2_cluster_hadr1

db2 update db cfg for dogdb using HADR_REMOTE_HOST db2_cluster_node2

db2 update db cfg for dogdb using HADR_REMOTE_SVC db2_cluster_hadr2 

db2 update db cfg for dogdb using HADR_REMOTE_INST db2inst1

db2 update db cfg for dogdb using HADR_SYNCMODE NEARSYNC

db2 update db cfg for dogdb using HADR_TIMEOUT 120

db2 update db cfg for dogdb using LOGINDEXBUILD ON

db2 "update db cfg for dogdb using HADR_TARGET_LIST db2_cluster_node2:db2_cluster_hadr2|db2_cluster_node3:db2_cluster_hadr3"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

备库设置

创建目录,多备库均配置

root用户下执行

mkdir -p /db2/db2imn/data/dogdb
mkdir -p /db2/db2imn/arch
mkdir  /db2/db2imn/data/dogdb/tbs
mkdir -p /home/db2imn/db2_backup
chown db2inst1:db2iadm1  -R /home/db2imn/db2_backup
chown db2inst1:db2iadm1  -R /db2
1
2
3
4
5
6

将备份库传输到多个备库，主库操作

db2inst1用户下执行

scp /home/db2imn/db2_backup/DOGDB.0.db2inst1.DBPART000.20231018054643.001 db2inst1@banana:/home/db2imn/db2_backup
scp /home/db2imn/db2_backup/DOGDB.0.db2inst1.DBPART000.20231018054643.001 db2inst1@peach:/home/db2imn/db2_backup
1
2

Db2@1997
1

恢复数据库，多备库均配置

db2inst1用户下执行

db2 restore database dogdb  from "/home/db2imn/db2_backup" 
1

查看备库的状态，多备库执行

db2inst1用户下执行

db2 get db cfg for dogdb | grep Rollforward
db2 rollforward db dogdb query status
1
2

[db2inst1@pear ~]$ db2 get db cfg for dogdb | grep Rollforward
Rollforward pending = DATABASE
[db2inst1@pear ~]$ db2 rollforward db dogdb query status

                          Rollforward Status
1

Input database alias = dogdb
Number of members have returned status = 1

Member ID = 0
Rollforward status = DB pending
Next log file to be read = S0000000.LOG
Log files processed = -
Last committed transaction = 2023-10-13-09.28.49.000000 UTC

Note：开启同步前，备库的状态要为pending

db2 get db cfg for dogdb | grep -i HADR
1

HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR target list (HADR_TARGET_LIST) =
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = AUTOMATIC(0)
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0
HADR SSL certificate label (HADR_SSL_LABEL) =

配置主备参数

db2inst1用户下执行

db2 update db cfg for dogdb using HADR_LOCAL_HOST db2_cluster_node2

db2 update db cfg for dogdb using HADR_LOCAL_SVC  db2_cluster_hadr2

db2 update db cfg for dogdb using HADR_REMOTE_HOST db2_cluster_node1

db2 update db cfg for dogdb using HADR_REMOTE_SVC  db2_cluster_hadr1

db2 update db cfg for dogdb using HADR_REMOTE_INST db2inst1

db2 update db cfg for dogdb using HADR_SYNCMODE ASYNC

db2 update db cfg for dogdb using HADR_TIMEOUT 120

db2 update db cfg for dogdb using LOGINDEXBUILD ON

db2 "update db cfg for dogdb using HADR_TARGET_LIST db2_cluster_node1:db2_cluster_hadr1|db2_cluster_node3:db2_cluster_hadr3"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

配置备库只读,可选操作

如果使备库可读，需要设置db2set的以下两个参数:

DB2_HADR_ROS标明在备库可读，一般不开启。有时候设置读写分离需要读备库的时候才需要设置。

DB2_STANDBY_ISO设置备库隔离级别(UR), 如果用了高于UR的隔离级别将强制转化为UR并且没有警告；当standby接管称为primary之后该参数会失效。

这两个db2set参数是配对使用的。

db2set DB2_HADR_ROS=ON
db2set DB2_STANDBY_ISO=UR
1
2

配置从备参数

db2 update db cfg for dogdb using HADR_LOCAL_HOST db2_cluster_node3

db2 update db cfg for dogdb using HADR_LOCAL_SVC  db2_cluster_hadr3

db2 update db cfg for dogdb using HADR_REMOTE_HOST db2_cluster_node2

db2 update db cfg for dogdb using HADR_REMOTE_SVC  db2_cluster_hadr2

db2 update db cfg for dogdb using HADR_REMOTE_INST db2inst1

db2 update db cfg for dogdb using HADR_SYNCMODE ASYNC

db2 update db cfg for dogdb using HADR_TIMEOUT 120

db2 update db cfg for dogdb using LOGINDEXBUILD ON

db2 "update db cfg for dogdb using HADR_TARGET_LIST db2_cluster_node1:db2_cluster_hadr1|db2_cluster_node2:db2_cluster_hadr2"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

HADR 启动

先启动2个备库

db2inst1用户下执行

db2 start hadr on database dogdb as standby
1

主库启动

db2inst1用户下执行

db2 start hadr on database dogdb as primary
1

查看HADR状态

db2inst1用户下执行

db2pd -db dogdb -hadr 
1

HADR启停顺序

主：
db2 stop hadr on db dogdb

备：
db2 deactivate db dogdb
db2 stop hadr on db dogdb


备：
db2 start hadr on db dogdb as standby

主：
db2 start hadr on db dogdb as primary
1
2
3
4
5
6
7
8
9
10
11
12
13