db2主备部署hadr(单个数据库)
主库:192.168.1.135 host135
从库:192.168.1.134 host134
说明:
a.主库已经运行并有数据库DB_HXL
可以使用如下命令查看:
db2 list database directory
b.主数据库已经处于归档模式,做了主备后,备库也会是归档模式,归档路径与主库配置的是一样的,这就需要备库提前有相应的目录.
db2 get db cfg for db_hxl |grep -i LOGARCHMETH1
具体配置请参考如下链接:
https://www.cnblogs.com/hxlasky/p/17986410
c.从库已经创建了实例,但是不建库
实例名与主库保持一致,并启动实例
[db2inst1@host134 ~]$ ss -nlp|grep 50000
1.主库配置HADR同步参数
db2inst1用户下执行
db2 update db cfg for db_hxl using logretain on ##去掉
db2 update db cfg for db_hxl using trackmod on ##好像需要重启动
db2 update db cfg for db_hxl using logindexbuild on
db2 update db cfg for db_hxl using indexrec restart
db2 update db cfg for db_hxl using HADR_LOCAL_HOST 192.168.1.135 ##好像需要重启动
db2 update db cfg for db_hxl using HADR_LOCAL_SVC 60006 (当启动HADR后，这个端口就会打开，负责复制的通信，端口可自定义)
db2 update db cfg for db_hxl using HADR_REMOTE_HOST 192.168.1.134
db2 update db cfg for db_hxl using HADR_REMOTE_SVC 60006
db2 update db cfg for db_hxl using HADR_REMOTE_INST db2inst1
db2 update db cfg for db_hxl using HADR_TIMEOUT 120
db2 update db cfg for db_hxl using HADR_PEER_WINDOW 10
db2 update db cfg for db_hxl using HADR_SYNCMODE sync
尝试重启数据库
db2 restart db db_hxl
[db2inst1@host135 ~]$ db2 restart db db_hxl
DB20000I The RESTART DATABASE command completed successfully.
查看配置:
db2 get db cfg for db_hxl|grep HADR_LOCAL_HOST
db2 get db cfg for db_hxl|grep HADR_LOCAL_SVC
2.备份数据库
在主库的db2inst1用户下执行
db2 force applications all
db2 backup database db_hxl to /home/db2inst1/backup
[db2inst1@host135 backup]$ db2 backup database db_hxl to /home/db2inst1/backup
SQL1433N The application is already connected to "DB_HXL01" while the command
issued requires a connection to "DB_HXL" for successful execution.
解决办法:
退出后重新登录执行
3.备份文件拷贝到备库
拷贝到目的库相应的目录
scp DB_HXL.0.db2inst1.DBPART000.20240131164653.001 db2inst1@192.168.1.134:/home/db2inst1/backup/
4.备库创建数据库库并恢复
在备库上执行
db2 create db db_hxl using codeset utf-8 territory CN
db2 restore db db_hxl from /home/db2inst1/backup
[db2inst1@host134 backup]$ db2 restore db db_hxl from /home/db2inst1/backup
SQL2523W Warning! Restoring to an existing database that is different from
the database on the backup image, but have matching names. The target database
will be overwritten by the backup version. The Roll-forward recovery logs
associated with the target database will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
这个时候好像链接不上备库的
db2 => connect to db_hxl
SQL1117N A connection to or activation of database "DB_HXL" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
5.开启备库可读
主备库都执行,切换后原来的主库变成备库,可提供读服务.
db2set DB2_HADR_ROS=ON
db2set DB2_STANDBY_ISO=UR
[db2inst1@host134 backup]$ db2set -all
[i] DB2_STANDBY_ISO=UR
[i] DB2_HADR_ROS=ON
[i] DB2COMM=TCPIP
[g] DB2SYSTEM=host134
6.修改备库参数
配置备库参数
db2 update db cfg for db_hxl using logretain on ##取消
db2 update db cfg for db_hxl using trackmod on
db2 update db cfg for db_hxl using logindexbuild on
db2 update db cfg for db_hxl using indexrec restart
db2 update db cfg for db_hxl using HADR_LOCAL_HOST 192.168.1.134
db2 update db cfg for db_hxl using HADR_LOCAL_SVC 60006
db2 update db cfg for db_hxl using HADR_REMOTE_HOST 192.168.1.135
db2 update db cfg for db_hxl using HADR_REMOTE_SVC 60006
db2 update db cfg for db_hxl using HADR_REMOTE_INST db2inst1
db2 update db cfg for db_hxl using HADR_TIMEOUT 120
db2 update db cfg for db_hxl using HADR_PEER_WINDOW 10
db2 update db cfg for db_hxl using HADR_SYNCMODE sync
7.启动备库，必须先启动备库，再启动主库
db2stop
db2start
若实例已经启动的情况下,不需要重启动
db2 "deactivate database db_hxl"
db2 start hadr on db db_hxl as standby
8.启动主库
db2 "deactivate database db_hxl"
db2 start hadr on db db_hxl as primary
9.监控主备数据库状态:
在主备库上执行
db2pd -hadr -db db_hxl
找到如下项目,红色部分,说明主从同步正常
[db2inst1@host135 backup]$ db2pd -hadr -db db_hxl
Database Member 0 -- Database DB_HXL -- Active -- Up 0 days 00:16:47 -- Date 2024-01-31-17.26.34.151110
HADR_ROLE = PRIMARY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = SYNC
STANDBY_ID = 1
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS = TCP_PROTOCOL
PRIMARY_MEMBER_HOST = 192.168.1.135
PRIMARY_INSTANCE = db2inst1
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = 192.168.1.134
STANDBY_INSTANCE = db2inst1
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
从库执行
[db2inst1@host134 ~]$ db2pd -hadr -db db_hxl
Database Member 0 -- Database DB_HXL -- Active Standby -- Up 0 days 00:17:51 -- Date 2024-01-31-17.27.23.616711
HADR_ROLE = STANDBY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = SYNC
STANDBY_ID = 0
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS = TCP_PROTOCOL
PRIMARY_MEMBER_HOST = 192.168.1.135
PRIMARY_INSTANCE = db2inst1
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = 192.168.1.134
STANDBY_INSTANCE = db2inst1
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
10.登录从库
[db2inst1@host134 backup]$ db2
db2 => connect to db_hxl
db2 => select * from tb_test;
ID NAME USER_AGES
-------------------- -------------------- -----------
1 hxl 20
2 hxl01 30
可以查到刚才恢复的数据
11.数据验证
主库建表,并写入数据
[db2inst1@host135 bin]$ db2
db2 => connect to db_hxl
db2 => create table tb_test01(id bigint not null generated by default as identity (start with 1,increment by 1),name varchar(20),user_ages int)
db2 => insert into tb_test01(name,user_ages) values('hxl',20);
DB20000I The SQL command completed successfully.
db2 => insert into tb_test01(name,user_ages) values('hxl01',30);
DB20000I The SQL command completed successfully.
11.登录备库查看
db2 => select * from tb_test01;
ID NAME USER_AGES
-------------------- -------------------- -----------
1 hxl 20
2 hxl01 30
2 record(s) selected.
可以看到主从同步完成
尝试在从库上写入数据会报如下错误:
db2 => insert into tb_test01(name,user_ages) values('hxl01',30);
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1773N The statement or command failed because it requires functionality
that is not supported on a read-enabled HADR standby database. Reason code = "5".
12.主从库的正常停止和启动
#########停HADR,先停主在停从
主:
db2 deactivate database db_hxl
db2 stop hadr on database db_hxl
备:
db2 deactivate database db_hxl
db2 stop hadr on database db_hxl
########起HADR,先启从再启主
备:
db2 deactivate database db_hxl
db2 start hadr on database db_hxl as standby
主:
db2 deactivate database db_hxl
db2 start hadr on database db_hxl as primary
13.主从切换
主备库切换
备库接管主库,下面命令只能在备库执行,我这里是在192.168.1.134机器上执行.
正常接管,相当于Oracle的standby的switchover,接管之后,主变备,备变主,重新在原主库(也就是现在的备库执行此命令),hadr重新回到原来状态.
[db2inst1@host134 ~]$ db2 takeover hadr on database db_hxl
DB20000I The TAKEOVER HADR ON DATABASE command completed successfully.
这个时候查看主从库的状态
目前的主库(原从库),192.168.1.134
[db2inst1@host134 ~]$ db2pd -hadr -db db_hxl
Database Member 0 -- Database DB_HXL -- Active -- Up 0 days 16:52:55 -- Date 2024-02-01-10.28.46.650953
HADR_ROLE = PRIMARY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = SYNC
STANDBY_ID = 1
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS = TCP_PROTOCOL
PRIMARY_MEMBER_HOST = 192.168.1.134
PRIMARY_INSTANCE = db2inst1
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = 192.168.1.135
STANDBY_INSTANCE = db2inst1
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
目前的从库(原主库),192.168.1.135
[db2inst1@host135 ~]$ db2pd -hadr -db db_hxl
Database Member 0 -- Database DB_HXL -- Active Standby -- Up 0 days 16:53:32 -- Date 2024-02-01-10.29.35.314130
HADR_ROLE = STANDBY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = SYNC
STANDBY_ID = 0
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS = TCP_PROTOCOL
PRIMARY_MEMBER_HOST = 192.168.1.134
PRIMARY_INSTANCE = db2inst1
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = 192.168.1.135
STANDBY_INSTANCE = db2inst1
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
强制切换
db2 takeover hadr on db db_hxl by force
一定要在primary为down的状态下执行.
14.强制关机
