linux安装配置DB2数据库以及HADR

LH90475

于 2024-03-04 17:41:32 发布

阅读量547

点赞数 16

文章标签：数据库 db

本文链接：https://blog.csdn.net/LH90475/article/details/136456741

版权

【环境准备】
操作系统：centos 7.9

db2：11.1

db2数据库压缩包

链接：https://pan.baidu.com/s/1EQnmpah9HNoC38U2y6aBwg?pwd=xs8e
提取码：xs8e

【安装db2数据库】
一、安装前准备
1.解压压缩包
mkdir -p /home/software

cd /home/software
将压缩包上传至/home/software，解压

tar -zxvf v11.1_linuxx64_expc.tar.gz
2.安装依赖
yum install -y bc binutils* compat-libcap1* compat-libstdc* elfutils-libelf* elfutils-libelf-devel* fontconfig-devel* glibc* glibc-devel* ksh libaio* libaio-devel* libX11* libXau* libXi* libXtst* libXrender* libXrender-devel* libgcc* libstdc++* libstdc++-devel* libxcb* make net-tools nfs-utils smartmontools sysstat unixODBC gcc gcc-c++ python python-configshell python-rtslib python-six targetcli ntp

yum install -y libstdc++.i686

yum install -y pam.i686

yum install pam-1.1.8-23.el7.i686 -y

yum install libstdc++.so.6 -y
3.安装前环境检测
cd /home/software/expc

./db2prereqcheck
二、安装数据库
1.在/home/software/expc目录下安装数据库
cd /home/software/expc

./db2_install
2.创建用户、用户组
groupadd -g 2000 db2iadm1
groupadd -g 2001 db2fadm1
useradd -m -g db2iadm1 -d /home/db2inst1 db2inst1
useradd -m -g db2fadm1 -d /home/db2fenc1 db2fenc1
passwd db2inst1 ##设置db2inst1用户密码
3.添加证书
cd /opt/ibm/db2/V11.1/adm
chmod -R 755 *
./db2licm -a /home/software/expc/db2/license/db2expc_uw.lic
4.开启数据库监听
cd /opt/ibm/db2/V11.1/instance/
chmod -R 775 *
./db2icrt -p 50000 -u db2fenc1 db2inst1
5.切换用户
su -l db2inst1
6.创建样本数据库
db2sampl
7.启动数据库
db2start
三、测试连接数据库
使用DBeaver测试连接

【配置hadr】
前提条件：源备端机器均成功安装db2数据库

一、环境配置（源备端均需配置）
1.配置/etc/hosts
vi /etc/hosts
在末尾添加源备端机器ip以及机器名

xx.xx.78.192 db2pri
xx.xx.78.193 db2std
2.配置/etc/services
vi /etc/services
在末尾添加

db2_hadr01 63001/tcp
db2_hadr02 63002/tcp
3.网络配置
su - db2inst1
db2set DB2COMM=TCPIP
db2set -all
二、主库配置

1.创建目录
[db2inst1@db2pri ~]$ su root
[root@db2pri db2inst1]# mkdir -p /db2/db2inst1/data/testdb
[root@db2pri db2inst1]# mkdir -p /db2/db2inst1/arch
[root@db2pri db2inst1]# mkdir /db2/db2inst1/data/testdb/tbs
[root@db2pri db2inst1]# chown db2inst1:db2iadm1 -R /db2
2.创建测试表
##创建数据库
su - db2inst1

db2 "create db testdb on /db2/db2inst1/data/testdb using codeset utf8 territory CN"
db2 connect to testdb

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

##创建表空间
[db2inst1@db2pri ~]$ db2 "create regular tablespace TB_TS pagesize 8k managed by database using(file '/db2/db2inst1/data/testdb/tbs/TB_TS' 10g) bufferpool TB_BP_8k"
[db2inst1@db2pri ~]$ db2 "create regular tablespace IDX_TS pagesize 8k managed by database using(file '/db2/db2inst1/data/testdb/tbs/IDX_TS' 4g) bufferpool IDX_BP_8k"

##创建表
[db2inst1@db2pri ~]$ db2 connect to testdb
[db2inst1@db2pri ~]$ db2 'create table product(id integer,name varchar(10)) IN "TB_TS" INDEX IN "IDX_TS"'

##新增表数据
[db2inst1@db2pri ~]$ db2 "insert into product values(1,'drm')"
[db2inst1@db2pri ~]$ db2 'select * from product'
ID NAME
----------- ----------
1 drm

1 record(s) selected.
3.配置归档
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using logarchmeth1 disk:/db2/db2inst1/arch
4.备份数据库
[db2inst1@db2pri ~]$ mkdir -p /home/db2inst1/db2_backup
[db2inst1@db2pri ~]$ chown db2inst1:db2iadm1 -R /home/db2inst1/db2_backup
[db2inst1@db2pri ~]$ db2 force applications all
[db2inst1@db2pri ~]$ db2 backup database testdb to "/home/db2inst1/db2_backup"
5.重启实例
[db2inst1@db2pri ~]$ db2stop
[db2inst1@db2pri ~]$ db2start
6.手动归档
[db2inst1@db2pri ~]$ db2 archive log for db testdb
7.配置备库只读
[db2inst1@db2pri ~]$ db2set DB2_HADR_ROS=ON
[db2inst1@db2pri ~]$ db2set DB2_STANDBY_ISO=UR
8.配置hadr同步参数
##HADR_LOCAL_* 为当前机器参数 HADR_REMOT_* 为对方的参数（主或者备） HADR_REMOTE_INST 为示例名

[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_LOCAL_HOST db2pri
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_LOCAL_SVC db2_hadr01
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_REMOTE_HOST db2std
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_REMOTE_SVC db2_hadr02
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_REMOTE_INST db2inst1
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_SYNCMODE NEARSYNC
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_TIMEOUT 120
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using LOGINDEXBUILD ON
9.拷贝备份集
[root@db2pri ~]# scp /home/db2inst1/db2_backup/TESTDB.0.db2inst1.DBPART000.20231129105054.001 db2inst1@db2std:/home/db2inst1/db2_backup
三、备库配置
1.备库/home/db2inst1/db2_backup目录下查看
[root@db2std db2_backup]# ll
总用量 196688
-rw-------. 1 db2inst1 db2iadm1 201408512 11月 29 11:29 TESTDB.0.db2inst1.DBPART000.20231129105054.001
2.创建目录
[root@db2std db2_backup]# mkdir -p /db2/db2inst1/data/testdb
[root@db2std db2_backup]# mkdir -p /db2/db2inst1/arch
[root@db2std db2_backup]# mkdir /db2/db2inst1/data/testdb/tbs
[root@db2std db2_backup]# chown db2inst1:db2iadm1 -R /db2
3.恢复数据库
[db2inst1@db2std ~]$ db2 restore database testdb from "/home/db2inst1/db2_backup"
4.配置备库只读
[db2inst1@db2std ~]$ db2set DB2_HADR_ROS=ON
[db2inst1@db2std ~]$ db2set DB2_STANDBY_ISO=UR
5.查看备库状态
[db2inst1@db2std ~]$ db2 get db cfg for testdb | grep Rollforward
Rollforward pending = DATABASE
[db2inst1@db2std ~]$ db2 rollforward db testdb query status

Rollforward Status

Input database alias = testdb
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-11-29-02.50.59.000000 UTC
6.配置HADR同步参数
[db2inst1@db2std ~]$ db2 get db cfg for testdb | grep -i HADR
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@db2std ~]$ db2 update db cfg for testdb using HADR_LOCAL_HOST db2std
[db2inst1@db2std ~]$ db2 update db cfg for testdb using HADR_LOCAL_SVC db2_hadr02
[db2inst1@db2std ~]$ db2 update db cfg for testdb using HADR_REMOTE_HOST db2pri
[db2inst1@db2std ~]$ db2 update db cfg for testdb using HADR_REMOTE_SVC db2_hadr01
[db2inst1@db2std ~]$ db2 update db cfg for testdb using HADR_REMOTE_INST db2inst1
[db2inst1@db2std ~]$ db2 update db cfg for testdb using HADR_SYNCMODE NEARSYNC
[db2inst1@db2std ~]$ db2 update db cfg for testdb using HADR_TIMEOUT 120
[db2inst1@db2std ~]$ db2 update db cfg for testdb using LOGINDEXBUILD ON

四、启动hadr
1.先启动备库
[db2inst1@db2std ~]$ db2 start hadr on database testdb as standby
2.再启动主库
[db2inst1@db2pri ~]$ db2 start hadr on database testdb as primary
3.查看hadr状态
##主库
[db2inst1@db2pri ~]$ db2pd -db testdb -hadr

Database Member 0 -- Database TESTDB -- Active -- Up 0 days 00:00:36 -- Date 2023-11-29-13.52.29.823298

HADR_ROLE = PRIMARY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = NEARSYNC
STANDBY_ID = 1
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS = TCP_PROTOCOL
PRIMARY_MEMBER_HOST = db2pri
PRIMARY_INSTANCE = db2inst1
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = db2std
STANDBY_INSTANCE = db2inst1
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
HADR_CONNECT_STATUS_TIME = 2023-11-29 13:51:56.301931 (1701237116)
HEARTBEAT_INTERVAL(seconds) = 30
HEARTBEAT_MISSED = 0
HEARTBEAT_EXPECTED = 1
HADR_TIMEOUT(seconds) = 120
TIME_SINCE_LAST_RECV(seconds) = 3
PEER_WAIT_LIMIT(seconds) = 0
LOG_HADR_WAIT_CUR(seconds) = 0.000
LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000000
LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.000
LOG_HADR_WAIT_COUNT = 0
SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 87040
SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 369280
PRIMARY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 61531297
STANDBY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 61531297
HADR_LOG_GAP(bytes) = 0
STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 61531297
STANDBY_RECV_REPLAY_GAP(bytes) = 0
PRIMARY_LOG_TIME = 2023-11-29 10:50:59.000000 (1701226259)
STANDBY_LOG_TIME = 2023-11-29 10:50:59.000000 (1701226259)
STANDBY_REPLAY_LOG_TIME = 2023-11-29 10:50:59.000000 (1701226259)
STANDBY_RECV_BUF_SIZE(pages) = 4300
STANDBY_RECV_BUF_PERCENT = 0
STANDBY_SPOOL_LIMIT(pages) = 25600
STANDBY_SPOOL_PERCENT = 0

##备库
[db2inst1@db2std ~]$ db2pd -db testdb -hadr

Database Member 0 -- Database TESTDB -- Active Standby -- Up 0 days 00:20:53 -- Date 2023-11-29-13.58.31.780621

HADR_ROLE = STANDBY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = NEARSYNC
STANDBY_ID = 0
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS = TCP_PROTOCOL
PRIMARY_MEMBER_HOST = db2pri
PRIMARY_INSTANCE = db2inst1
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = db2std
STANDBY_INSTANCE = db2inst1
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
HADR_CONNECT_STATUS_TIME = 2023-11-29 13:54:20.279629 (1701237260)
HEARTBEAT_INTERVAL(seconds) = 30
HEARTBEAT_MISSED = 1
HEARTBEAT_EXPECTED = 12
HADR_TIMEOUT(seconds) = 120
TIME_SINCE_LAST_RECV(seconds) = 8
PEER_WAIT_LIMIT(seconds) = 0
LOG_HADR_WAIT_CUR(seconds) = 0.000
LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000354
LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.001
LOG_HADR_WAIT_COUNT = 3
SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 16384
SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 87380
PRIMARY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 61532050
STANDBY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 61532050
HADR_LOG_GAP(bytes) = 0
STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 61532050
STANDBY_RECV_REPLAY_GAP(bytes) = 0
PRIMARY_LOG_TIME = 2023-11-29 13:57:48.000000 (1701237468)
STANDBY_LOG_TIME = 2023-11-29 13:57:48.000000 (1701237468)
STANDBY_REPLAY_LOG_TIME = 2023-11-29 13:57:48.000000 (1701237468)
STANDBY_RECV_BUF_SIZE(pages) = 4300
STANDBY_RECV_BUF_PERCENT = 0
STANDBY_SPOOL_LIMIT(pages) = 25600
STANDBY_SPOOL_PERCENT = 0
STANDBY_ERROR_TIME = NULL
PEER_WINDOW(seconds) = 0
READS_ON_STANDBY_ENABLED = Y
STANDBY_REPLAY_ONLY_WINDOW_ACTIVE = N

4.测试
##主库新增数据
[db2inst1@db2pri ~]$ db2 connect to testdb

Database Connection Information

Database server = DB2/LINUXX8664 11.1.4.4
SQL authorization ID = DB2INST1
Local database alias = TESTDB

[db2inst1@db2pri ~]$ db2 'select * from product'

ID NAME
----------- ----------
1 drm

1 record(s) selected.

[db2inst1@db2pri ~]$ db2 "insert into product values(2,'up')"
DB20000I The SQL command completed successfully.
[db2inst1@db2pri ~]$ db2 'select * from product'

ID NAME
----------- ----------
1 drm
2 up

2 record(s) selected.

##备库查看
[db2inst1@db2std ~]$ db2 connect to testdb

Database Connection Information

Database server = DB2/LINUXX8664 11.1.4.4
SQL authorization ID = DB2INST1
Local database alias = TESTDB

[db2inst1@db2std ~]$ db2 'select * from product'

ID NAME
----------- ----------
1 drm
2 up

2 record(s) selected.

注：HADR启停顺序

##停止

主：
db2 stop hadr on db testdb

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

##启动

备：
db2 start hadr on db testdb as standby

主：
db2 start hadr on db testdb as primary

五、切换
1.主备切换（备库执行）
db2 takeover hadr on database testdb
2.故障切换（备库执行）
db2 takeover hadr on database testdb by force

LH90475

关注

16
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
linux安装配置DB2数据库以及HADR

#HADR_LOCAL_* 为当前机器参数 HADR_REMOT_* 为对方的参数（主或者备） HADR_REMOTE_INST 为示例名。链接：https://pan.baidu.com/s/1EQnmpah9HNoC38U2y6aBwg?1.备库/home/db2inst1/db2_backup目录下查看。1.在/home/software/expc目录下安装数据库。2.配置/etc/services。一、环境配置（源备端均需配置）1.配置/etc/hosts。使用DBeaver测试连接。
复制链接

扫一扫