LinuxDB2HADR双机搭建-mysql教程

搞了好几天总算是把HADR弄好啦，下面分享下 系统环境： OS:SUSE 11sp1-64bit DB: db29.7.0.5 DB2server1:192.168.5.151 db2in

搞了好几天总算是把HADR弄好啦，，下面分享下

系统环境：

OS:SUSE 11sp1-64bit

DB: db29.7.0.5

DB2server1:192.168.5.151 db2inst1

DB2server2:192.168.5.152 db2inst2

步骤：

DB2server1上操作：

db2inst1@DB2server1:~> db2 create database oga;

db2inst1@DB2server1:~> db2 get dbm cfg | grep SVC

db2inst1@DB2server1:~> db2set db2comm=tcpip

db2inst1@DB2server1:~> db2 update dbm cfg usingSVCENAME 50001

db2inst1@DB2server1:~> db2 update db cfg for ogausing logretain on

db2inst1@DB2server1:~> db2 update db cfg for ogausing trackmod on;

db2inst1@DB2server1:~> db2 update db cfg for ogausing logindexbuild on;

db2inst2@DB2server1:~> db2 backup db oga

db2inst1@DB2server1:~> db2 "create tablecert(OrgID int not null,EntId int not null,certnum char(20) not null primarykey,issuedate date)"

db2inst1@DB2server1:~> db2 "alter table certdata capture changes"

db2inst1@DB2server1:~> db2 "create tableorg(OrgId int not null primary key, OrgName char(20)) date capturechanges"

db2inst1@DB2server1:~> db2 "insert into orgvalues(1, 'org1')"

db2inst1@DB2server1:~> db2 "insert into orgvalues(2, 'org2')"

db2inst1@DB2server1:~> db2 "insert into orgvalues(3, 'org3')"

db2inst1@DB2server1:~> db2 "insert into certvalues(1, 2, 'cert1','2009-12-5')"

db2inst1@DB2server1:~> db2 "insert into certvalues(2, 2, 'cert2','2010-3-5')"

db2inst1@DB2server1:~> db2 "insert into certvalues(3, 2, 'cert3', current date)"

db2inst1@DB2server1:~> db2 update db cfg for ogausing hadr_local_svc 44455

DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.

db2inst1@DB2server1:~> db2 update db cfg for ogausing hadr_remote_host 192.168.5.152

DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.

db2inst1@DB2server1:~> db2 update db cfg for ogausing hadr_local_host 192.168.5.151

DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.

db2inst1@DB2server1:~> db2 update db cfg for ogausing hadr_remote_svc 33344

DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.

db2inst1@DB2server1:~> db2 update db cfg for ogausing hadr_remote_inst db2inst2

DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.

db2inst1@DB2server1:~> db2 update db cfg for ogausing hadr_syncmode nearsync

DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.

db2 UPDATE ALTERNATE SERVER FOR DATABASE SAMPLE USINGHOSTNAME 192.168.5.151 PORT 50001

db2inst1@DB2server1:~> db2 get db cfg for oga | grep-i hadr

DB2server2上的操作

db2inst1@DB2server2:~> db2 create database oga;

db2inst1@DB2server2:~> db2 get dbm cfg | grep SVC

db2inst1@DB2server2:~> db2set db2comm=tcpip

db2inst1@DB2server2:~> db2 update dbm cfg usingSVCENAME 50001

db2inst1@DB2server2:~> db2 update db cfg for ogausing logretain on

db2inst1@DB2server2:~> db2 update db cfg for ogausing trackmod on;

db2inst1@DB2server2:~> db2 update db cfg for ogausing logindexbuild on;

db2inst1@DB2server1:/opt/bak> db2 backup db oga to/opt/bak

db2inst1@DB2server2:~> db2 "create tablecert(OrgID int not null,EntId int not null,certnum char(20) not null primarykey,issuedate date)"

db2inst1@DB2server2:~> db2 "alter table certdata capture changes"

db2inst1@DB2server2:~> db2 "create tableorg(OrgId int not null primary key, OrgName char(20)) date capturechanges"

db2inst2@DB2server2:~> db2 update db cfg for ogausing hadr_local_host 192.168.5.152

DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.

db2inst2@DB2server2:~> db2 update db cfg for ogausing hadr_local_svc 44455

DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.

db2inst2@DB2server2:~> db2 update db cfg for ogausing hadr_remote_host 192.168.5.151

DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.

db2inst2@DB2server2:~> db2 update db cfg for ogausing hadr_remote_svc 33344

DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.

db2inst2@DB2server2:~> db2 update db cfg for ogausing hadr_syncmode nearsync

DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.

db2inst2@DB2server2:~> db2 update db cfg for ogausing HADR_REMOTE_INST db2inst1

DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.

db2 UPDATE ALTERNATE SERVER FOR DATABASE SAMPLE USINGHOSTNAME 192.168.5.152 PORT 50001

db2inst1@DB2server2:~> db2 get db cfg for oga | grep-i hadr

db2inst2@DB2server2:/opt/bak> cd /opt/bak/

重定向恢复

db2 restore db oga on /data_inst2/db2inst2/oga/ dbpathon /data_inst2/db2inst2/ into oga

db2 rollforward db oga stop ——这个不需要执行，否则在启动备库的时候会提示SQL1767N Start HADR cannot complete. Reason code ="1".

将表恢复到了db2inst1下面，保证db2inst2可以看到这些表

db2inst2@DB2server2:~> db2 connect to sample userdb2inst1 using db2inst

db2inst2@DB2server2:/opt/bak> db2 connect to sample

b2inst1@DB2server1:/opt/bak> db2 grant dbadm ondatabase to db2inst2

DB20000I The SQLcommand completed successfully.

db2inst1@DB2server1:/opt/bak> db2 grant secadm ondatabase to db2inst2

DB20000I The SQLcommand completed successfully.

db2inst2@DB2server2:~> db2 "select * from db2inst1.cert"

ORGID ENTID CERTNUM ISSUEDATE

----------- ----------- -------------------- ----------

1 2 cert1 12/05/2009

2 2 cert2 03/05/2010

3 2 cert3 03/23/2012

启动standby

db2inst2@DB2server2：~>db2 deactivate database sample

SQL1496W Deactivate database is successful, but the database was not

activated.是断开关闭数据库

db2inst2@DB2server2:~> db2 start hadr on db oga asstandby

SQL1032N Nostart database manager command was issued.

注意：此时standby不可以连接数据库，否则会造成主库不一致的。

db2inst2@DB2server2:~> db2 get snapshot for db onoga | grep Role

Role = Standby

db2inst2@DB2server2:~>

启动主机

db2inst1@DB2server1:/opt/bak> db2 activate db oga

DB20000I TheACTIVATE DATABASE command completed successfully.

db2inst2@DB2server1:~> db2 start hadr on db oga as primary

db2inst1@DB2server1:~> db2 get snapshot for db onsample | grep Role

Role = Primary

验证两台机的状态：

db2inst1@DB2server1:~> db2 get snapshot for db onoga | grep state

Commit statements attempted = 16

Rollback statements attempted = 0

Dynamic statements attempted = 479

Static statements attempted = 30

Failed statement operatiOns= 0

Select SQL statements executed = 152

Xquery statements executed = 0

Update/Insert/Delete statements executed = 9

DDL statements executed = 0

停止

db2inst2@DB2server2:~> db2 deactivate database oga

DB20000I TheDEACTIVATE DATABASE command completed successfully.

db2inst2@DB2server2:~> db2 stop hadr on database oga

DB20000I TheSTOP HADR ON DATABASE command completed successfully.

db2inst2@DB2server1:~> db2 stop hadr on database oga

DB20000I TheSTOP HADR ON DATABASE command completed successfully.

测试：

db2inst1@DB2server1:~> db2 "insert into orgvalues (5,'org5')"

DB20000I The SQLcommand completed successfully.

备库查看

db2inst2@DB2server2:/opt/bak> db2 get snapshot fordb on oga | grep -i file

Database files closed = Not Collected

File number of first active log = Not applicable

File number of last active log = Not applicable

File number of current active log = 12

File number of log being archived = Not applicable

Rollforward log file being processed = 7

Primary logposition(file, page, LSN) = S0000012.LOG, 76, 0000000005374584

Standby logposition(file, page, LSN) = S0000012.LOG, 76, 0000000005374584

接管主库

原来的主库可以停掉也可以不停

db2inst2@DB2server2:/opt/bak> db2 takeover hadr ondatabase oga user db2inst1 using db2inst1

DB20000I TheTAKEOVER HADR ON DATABASE command completed successfully.

db2inst2@DB2server2:/opt/bak> db2 connect to oga userdb2inst1 using db2inst1

db2inst2@DB2server2:/opt/bak> db2 "select *from org"

ORGID ORGNAME

----------- --------------------

1org1

2org2

3org3

4 org4

5org5

5 record(s)selected.

查看原来主机的状态

db2inst1@DB2server1:~> db2 get snapshot for db onoga | more

Database Snapshot

Database name = OGA

Database path =/data_inst1/db2inst1/db2inst1/NODE0000/SQL00001/

Input database alias = OGA

Database status = Standby

Catalog database partition number = 0

Catalog network node name =

Operating system running at database server= LINUXAMD64

Location of the database = Local

First database connect timestamp = 2012-03-28 15:21:16.354049

Last reset timestamp =

Last backup timestamp = 2012-03-2715:20:54.000000

Snapshot timestamp = 2012-03-2816:26:47.497005

Number of automatic storage paths = 1

原来备库的状态

db2inst2@DB2server2:/opt/bak> db2 get snapshot fordb on oga | more

Database Snapshot

Database name = OGA

Database path =/data_inst2/db2inst2/db2inst2/NODE0000/SQL00001/

Input database alias = OGA

Database status = Active

Catalog database partition number = 0

Catalog network node name =

Operating system running at database server= LINUXAMD64

Location of the database = Local

First database connect timestamp = 03/28/2012 15:20:41.342208

Last reset timestamp =

Last backup timestamp =

Snapshot timestamp = 03/28/201216:27:38.538201

Number of automatic storage paths = 1