バージョン違いの論理レプリケーション

• MySQL

移行元: MySQL 5.6 mmm050
移行先: MySQL 5.7 mmm051

【1】confファイルの設定[移行元での作業]

vim /etc/my.cnf

[mysqld]
・
・
・
log_bin
server_id=101
binlog_format=STATEMENT
max_binlog_size=100M
expire_logs_days=30
sync_binlog=1
innodb_flush_log_at_trx_commit=1
report_host=mmm050
log_slave_updates

systemctl restart mysqld.service

【2】Slaveアカウント作成[移行元での作業]

create user 'repl'@'192.168.137.51' identified by 'Aaa!1234';
grant replication slave on *.* to 'repl'@'192.168.137.51';

create user 'repl'@'192.168.137.50' identified by 'Aaa!1234';
grant replication slave on *.* to 'repl'@'192.168.137.50';

flush privileges;

【3】dump作成[移行元での作業]

drop database test;
create database test;
use test;

drop table tab1;
create table tab1(col1 int);
alter table tab1 add constraint tab1p primary key(col1);
insert into tab1 values(1);
select * from tab1;

mysqldump --quick --master-data=2 --flush-logs --single-transaction -u root -p --all-databases > all.dump

scp all.dump 192.168.137.51:~

【4】confファイルの設定[移行先での作業]

vim /etc/my.cnf

[mysqld]
・
・
・
log_bin
server_id=102
binlog_format=STATEMENT
max_binlog_size=100M
expire_logs_days=30
sync_binlog=1
innodb_flush_log_at_trx_commit=1
report_host=mmm051
log_slave_updates

systemctl restart mysqld.service

【5】dump読込[移行先での作業]

source ~/all.dump;

【6】レプリケーション設定[移行先での作業]

\! head -100 all.dump | grep CHANGE

change master to
master_host='192.168.137.50',
master_user='repl',
master_password='Aaa!1234',
master_log_file='mysqld-bin.000002',
master_log_pos=120;

start slave;

show slave status\G

【7】動作確認

--レプリケーションホスト確認[移行元での作業]
show slave hosts\G

--移行先でカラム追加後、レプリケーションが継続を確認

insert into tab1 values(100);
select * from tab1;

alter table tab1 add col2 varchar(100);

insert into tab1(col1) values(103);
→OK

insert into tab1 values(105);
→Error 'Column count doesn't match value count at row 1'

select * from tab1;

※
バイナリロギング形式がSTATEMENTの場合、対象カラムを明示しないSQLはエラーとなる
バイナリロギング形式がROWの場合、対象カラムを明示しなくても動作する

• Oracle

移行元(node1): Oracle 12cR1 mmm052
移行先(node2): Oracle 12cR2 mmm053

手順
【1】 12cR1 - 12cR1 のフィジカルスタンバイ
【2】 12cR1 - 12cR1 のロジカルスタンバイ
【3】 12cR1 - 12cR2 のロジカルスタンバイ
【4】 動作確認

----

【1】 12cR1 - 12cR1 のフィジカルスタンバイ

--前提
node1：インストールとDB作成を実施
node2：インストールを実施

--1.強制ロギングの有効化[node1で実施]
select force_logging FROM v$database;
ALTER DATABASE FORCE LOGGING;

--2.スタンバイREDOログ作成[node1で実施]
SELECT group#,type,member FROM v$logfile;
SELECT group#,bytes/1048576 "SIZE(MB)" FROM v$log;
SELECT group#,bytes/1048576 "SIZE(MB)" FROM v$standby_log;

ALTER DATABASE ADD standby logfile
group 4 ('/u01/app/oracle/oradata/orcl/stredo01.log') SIZE 100M,
group 5 ('/u01/app/oracle/oradata/orcl/stredo02.log') SIZE 100M,
group 6 ('/u01/app/oracle/oradata/orcl/stredo03.log') SIZE 100M,
group 7 ('/u01/app/oracle/oradata/orcl/stredo04.log') SIZE 100M;

--3.初期化パラメータの設定[node1で実施]
mkdir -p /u01/app/oracle/oradata/orcl/arc

*.db_domain='example.com'
*.memory_target=800M
DB_NAME=orcl
DB_UNIQUE_NAME=orcl
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,stb)'
CONTROL_FILES='/u01/app/oracle/oradata/orcl/control01.ctl', '/u01/app/oracle/oradata/orcl/control02.ctl'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/orcl/arc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
LOG_ARCHIVE_DEST_2='SERVICE=stb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stb'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=stb
DB_FILE_NAME_CONVERT='/orcl/','/orcl/'
LOG_FILE_NAME_CONVERT='/orcl/','/orcl/'
STANDBY_FILE_MANAGEMENT=AUTO

--4.アーカイブの有効化[node1で実施]

shutdown immediate;
startup mount
alter database archivelog;
alter database open;
archive log list

--5.プライマリ・データベース・データファイルのバックアップ・コピーの作成[node1で実施]

shutdown immediate;

cd /u01/app/oracle/oradata
tar cvzf orcl.tgz orcl

--6.スタンバイ・データベース用の制御ファイルの作成[node1で実施]

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stb.ctl';

--7.スタンバイ・データベース用のパラメータ・ファイルの作成[node1で実施]

CREATE PFILE='/tmp/initstb.ora' FROM SPFILE;

vim initstb.ora

*.CONTROL_FILES='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.db_domain='example.com'
*.DB_FILE_NAME_CONVERT='/orcl/','/orcl/'
*.DB_NAME='orcl'
*.DB_UNIQUE_NAME='stb'
*.FAL_SERVER='orcl'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,stb)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/orcl/arc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stb'
*.LOG_ARCHIVE_DEST_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_FILE_NAME_CONVERT='/orcl/','/orcl/'
*.memory_target=800M
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'

--8.プライマリ・システムからスタンバイ・システムへのファイルのコピー[node1で実施]

scp /u01/app/oracle/oradata/orcl.tgz mmm053:/tmp
scp /tmp/stb.ctl mmm053:/tmp
scp /tmp/initstb.ora mmm053:/tmp

--9.スタンバイ・データベースをサポートする環境の設定

--パスワードファイル[node1で作成し、node2へコピーする]

cd $ORACLE_HOME/dbs

orapwd file=orapworcl password=oracle

--12cR2以降の場合
--orapwd file=orapworcl password=oracle format=12

scp orapworcl mmm053:/tmp

--node2で実施
mv /tmp/orapworcl $ORACLE_HOME/dbs/orapwstb

--リスナー

[node1で実施]
vim listener.ora

LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=mmm052)(PORT=1521))
)

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orcl.example.com)
(SID_NAME=orcl)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
)
)

lsnrctl start listener

[node2で実施]
vim listener.ora

LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=mmm053)(PORT=1521))
)

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=stb.example.com)
(SID_NAME=stb)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
)
)

lsnrctl start listener

--Oracle Netネット・サービス
[node1とnode2で実施]

vim tnsnames.ora

orcl=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=mmm052)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=orcl.example.com)))

stb=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=mmm053)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=stb.example.com)))

--10.フィジカル・スタンバイ・データベースの起動[node2で実施]

8.でコピーしたファイルをスタンバイに展開する。制御ファイルはスタンバイ制御ファイルで置き換える

cd /tmp

mv orcl.tgz /u01/app/oracle/oradata/
cd /u01/app/oracle/oradata/
tar xvzf orcl.tgz
cd orcl

mv /tmp/stb.ctl .
rm -i control01.ctl control02.ctl
mv stb.ctl control01.ctl
cp -a control01.ctl control02.ctl

cd $ORACLE_HOME/dbs
mv /tmp/initstb.ora .

create spfile from pfile;

STARTUP MOUNT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SELECT open_mode FROM V$DATABASE;

【2】 12cR1 - 12cR1 のロジカルスタンバイ

前提：フィジカルスタンバイ/最大パフォーマンス構築済。リスナー起動済

SELECT PROTECTION_MODE FROM V$DATABASE;

--1.ロジカル・スタンバイ・データベースの作成要件(node1)

SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
WHERE (OWNER, TABLE_NAME) NOT IN
(SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
AND BAD_COLUMN = 'Y';

--2.フィジカル・スタンバイ・データベースでのREDO Applyの停止(node2)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

--3.ロールの推移のためのプライマリ・データベースの準備(node1)
mkdir -p /u01/app/oracle/oradata/orcl/arc2

alter system set log_archive_dest_1 ='LOCATION=/u01/app/oracle/oradata/orcl/arc VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both;

alter system set log_archive_dest_3 ='LOCATION=/u01/app/oracle/oradata/orcl/arc2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=orcl' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;

--4.REDOデータでのディクショナリの構築(node1)
EXECUTE DBMS_LOGSTDBY.BUILD;

--5.ロジカル・スタンバイ・データベースへの変換(node2)

ALTER DATABASE RECOVER TO LOGICAL STANDBY stb;

--6.ロジカル・スタンバイ・データベース用の初期化パラメータの調整(node2)

SHUTDOWN;
STARTUP MOUNT;

mkdir -p /u01/app/oracle/oradata/orcl/arc2

alter system set log_archive_dest_1 ='LOCATION=/u01/app/oracle/oradata/orcl/arc VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stb' scope=both;

alter system set log_archive_dest_3 ='LOCATION=/u01/app/oracle/oradata/orcl/arc2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stb' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;

--7.ロジカル・スタンバイ・データベースのオープン(node2)
ALTER DATABASE OPEN RESETLOGS;

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

--8.ロジカル・スタンバイ・データベースが正しく実行されているかどうかの確認(node2)

SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;

【3】 12cR1 - 12cR2 のロジカルスタンバイ

--node2で実施

--12cR2のソフトウェアインストール

--アップグレード前情報ツールの実行
※Oracleが起動した状態で実行

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=stb
export PATH=.:$ORACLE_HOME/bin:$PATH

$ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT

--アップグレードのためのOracle Databaseのバックアップ

rman target /

RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT '/home/oracle/%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE FORMAT '/home/oracle/control-%U';
}

--アップグレード実行のための設定変更

vim /etc/oratab
stb:/u01/app/oracle/product/12.1.0/dbhome_1:N:

メモリサイズを2G→4G

alter system set compatible = '12.1.0' scope=spfile;
shutdown immediate;
startup;
show parameter compatible

alter system set memory_target = 0;

--アップグレード実行
cd /u01/app/oracle/product/12.2.0/dbhome_1/bin
./dbua

cd
vim .bash_profile
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
. .bash_profile

--アップグレード後の状態ツールを使用したアップグレードの確認

@/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlu122s.sql

--tnsnames.oraの設定

cd $ORACLE_HOME/network/admin
ls -ltr
cp /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora .
ls -ltr

--listener.oraの設定

vim listener.ora

LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=mmm053)(PORT=1521))
)

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=stb.example.com)
(SID_NAME=stb)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
)
)

lsnrctl stop listener
lsnrctl start listener
lsnrctl status listener

【4】 動作確認

12cR2にアップグレードしたスタンバイ側(node2)でSQL Applyを再開し、レプリケートされていることを確認する

--node2での作業
shutdown immediate;
startup

ALTER DATABASE STOP LOGICAL STANDBY APPLY;
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;

--node1での作業
drop table tab1 purge;
create table tab1(col1 int primary key);
insert into test.tab1 values(12181);
commit;
select * from test.tab1;

--node2での作業
select * from test.tab1;

※ローリングアップグレードの場合、引き続き下記を実施する

-- 1.スイッチオーバー(プライマリをnode2へ移動)

※通常と異なり、1フェーズ準備なしスイッチオーバーを使用する

--node1
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

--node2
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

--node1,node2
select name, db_unique_name, open_mode, database_role from v$database;

-- 2.node1のアップグレード

-- 3.12cR2にアップグレードしたスタンバイ側(node1)でSQL Applyを再開

※データベース・リンクを作成し、NEW PRIMARY句を使用する必要がある
--node1での作業

create database link dblink1 connect to test identified by test using 'stb';
select * from dba_db_links;
select * from v$instance@dblink1;

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE NEW PRIMARY dblink1;

SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;

--node2での作業
insert into test.tab1 values(12);
commit;
select * from test.tab1;

--node1での作業
shutdown immediate;
startup

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;

select * from test.tab1;

-- 4.スイッチオーバー(プライマリをnode1へ移動)
※2フェーズ準備済スイッチオーバー

--node2
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;

--node1
ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;

--node2
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

--node1
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

--node2
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;

--node1,node2
select name, db_unique_name, open_mode, database_role from v$database;

-- 5.動作確認

--node1での作業
insert into test.tab1 values(999);
commit;
select * from test.tab1;

--node2での作業
select * from test.tab1;

• PostgreSQL

pglogical

https://blog.dbi-services.com/postgresql-logical-replication-with-pglogical/
https://techracho.bpsinc.jp/hachi8833/2017_12_14/48991

移行元: PostgreSQL9.4 mmm050
移行先: PostgreSQL11 mmm065

【1】pglogicalインストール

--移行元での作業

yum install postgresql94-server postgresql94-contrib
curl https://access.2ndquadrant.com/api/repository/dl/default/release/9.4/rpm | bash
yum install postgresql94-pglogical

--移行先での作業
yum install postgresql11-server postgresql11-contrib
curl https://access.2ndquadrant.com/api/repository/dl/default/release/11/rpm | bash
yum install postgresql11-pglogical

【2】pglogicalのためのDB設定[両ノードでの作業]

vim $PGDATA/postgresql.conf

listen_addresses = '*'
wal_level = logical
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pglogical'

vim $PGDATA/pg_hba.conf

※先頭に追加

host all all 192.168.137.0/24 trust
host replication all 192.168.137.0/24 trust

DB再起動
sudo systemctl restart postgresql-9.4
sudo systemctl restart postgresql-11

ユーザ作成
psql

drop user appuser;
create user appuser with password 'appuser';
alter user appuser superuser;

DB作成
drop database mydb;
create database mydb;

【3】テストテーブル作成[移行元での作業]

psql -h 192.168.137.50 -U appuser -d mydb

drop table tab1 cascade;

create table tab1(col1 int);

alter table tab1 add constraint tab1p primary key(col1);

insert into tab1 values(1);
select * from tab1;

【4】定義ダンプ取得[移行元での作業]

pg_dump -Fc -s -h 192.168.137.50 -p 5432 -U appuser mydb > mydb-schema.dump
scp mydb-schema.dump 192.168.137.65:/var/lib/pgsql

【5】プロバイダ設定[移行元での作業]

psql -h 192.168.137.50 -p 5432 -U appuser -d mydb

--拡張作成
create extension pglogical_origin;
create extension pglogical;

--プロバイダ作成
select pglogical.create_node(node_name := 'provider', dsn := 'host=192.168.137.50 port=5432 dbname=mydb');

--ノードの確認
select * from pglogical.node_interface;

--レプリケート対象の追加
select pglogical.replication_set_add_all_tables('default', ARRAY['public']);

【6】サブスクライバ設定[移行先での作業]

--定義ダンプのリストア

cd
pg_restore -s -h 192.168.137.65 -p 5432 -U appuser -d mydb mydb-schema.dump

psql -h 192.168.137.65 -U appuser -d mydb

--拡張作成
create extension pglogical;

--サブスクライバ作成
select pglogical.create_node(node_name := 'subscriber', dsn := 'host=192.168.137.65 port=5432 dbname=mydb');

--ノードの確認
select * from pglogical.node_interface;

--サブスクリプション作成
select pglogical.create_subscription(subscription_name := 'subscription', provider_dsn := 'host=192.168.137.50 port=5432 dbname=mydb');

--サブスクリプション確認
select subscription_name, status FROM pglogical.show_subscription_status();

【7】動作確認[移行元での作業]

psql -h 192.168.137.50 -p 5432 -U appuser -d mydb

insert into tab1 values(100);
insert into tab1 values(101);
insert into tab1 values(102);
update tab1 set col1=201 where col1 = 101;
delete from tab1 where col1 = 102;

select * from tab1;

【8】動作確認[移行先での作業]

psql -h 192.168.137.65 -U appuser -d mydb

select * from tab1;

--サブスクリプションテーブルの確認
select * from pglogical.show_subscription_table('subscription','tab1');

【9】クリーンアップ

--サブスクリプションの削除[移行先での作業]
psql -h 192.168.137.50 -p 5432 -U appuser -d mydb

select * from pglogical.drop_subscription('subscription',true);

--サブスクライバの削除[移行先での作業]
psql -h 192.168.137.65 -U appuser -d mydb

select * from pglogical.drop_node('subscriber',true);
select * from pglogical.node_interface;

--プロバイダの削除[移行元での作業]

select * from pglogical.drop_node('provider',true);
select * from pglogical.node_interface;

• SQL Server

参考サイト : https://www.ipentec.com/document/sql-server-setup-transaction-replication

移行元(node1): SQL Server 2014 ディストリビューター、パブリッシャー
移行先(node2): SQL Server 2016 サブスクライバー

レプリケーショントポロジー：トランザクションレプリケーション

前提：
1. パブリッシャーとサブスクライバーでSQL Server エージェントの自動起動設定およびサービス起動済み
2. パブリッシャーとサブスクライバーでSQL Server エージェントの起動アカウントにAdministrator設定済み
3. パブリッシャーとサブスクライバーでhostsの設定済

1.ディストリビューターの設定
[レプリケーション]ノードを選択し[ディストリビューションの構成]をクリック

2.パブリケーションの設定
[レプリケーション]ノード内の[ローカル パブリケーション]ノードを選択します。[新しいパブリケーション]をクリック

3.サブスクリプションの設定
[レプリケーション]ノードの[ローカルサブスクリプション]ノードをクリックして選択