マルチマスタ

DB

アドバンスド・レプリケーション

https://www.system-exe.co.jp/dbexpert06/
https://www.oracle.com/technetwork/jp/ondemand/database/db-technique/replipractice-520823-ja.pdf


(12cR1)

node1: 192.168.137.124 →マスター定義サイト、DB名:PROD1.example.com
node2: 192.168.137.125 →マスターサイト、DB名:PROD2.example.com

※12cR2でDBMS_REPCAT_ADMINパッケージは削除された

--0.事前準備[node1とnode2]
DBとリスナーを作成し、プライマリキーがあるテーブルtest.tab1を作成


--1.初期化パラメータの設定[node1とnode2]

show parameter GLOBAL_NAMES

ALTER SYSTEM SET GLOBAL_NAMES = TRUE scope=both;


show parameter JOB_QUEUE_PROCESSES
show parameter OPEN_LINKS
show parameter REPLICATION_DEPENDENCY_TRACKING


--2.ネットワーク定義

----node1
vim tnsnames.ora

prod2.example.com =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mmm125)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD2.example.com)
)
)

----node2
vim tnsnames.ora

prod1.example.com =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mmm124)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD1.example.com)
)
)


--3.マスター定義サイトの設定[node1]


CONNECT system/oracle

CREATE USER repadmin IDENTIFIED BY repadmin;

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'repadmin');
END;
/

GRANT COMMENT ANY TABLE TO repadmin;
GRANT LOCK ANY TABLE TO repadmin;
GRANT SELECT ANY DICTIONARY TO repadmin;

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => 'repadmin');
END;
/

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'repadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/

CONNECT repadmin/repadmin
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/1440',
delay_seconds => 0);
END;
/


CONNECT system/oracle
CREATE PUBLIC DATABASE LINK PROD2.example.com
USING 'prod2.example.com';


CONNECT repadmin/repadmin
CREATE DATABASE LINK PROD2.example.com
CONNECT TO repadmin IDENTIFIED BY repadmin;

 

CONNECT repadmin/repadmin
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'PROD2.example.com',
interval => 'SYSDATE + 1/8640',
next_date => SYSDATE);
END;
/


--4.マスターサイトの設定[node2]

CONNECT system/oracle
CREATE USER repadmin IDENTIFIED BY repadmin;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'repadmin');
END;
/
GRANT COMMENT ANY TABLE TO repadmin;
GRANT LOCK ANY TABLE TO repadmin;
GRANT SELECT ANY DICTIONARY TO repadmin;

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => 'repadmin');
END;
/

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'repadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/

CONNECT repadmin/repadmin
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/1440',
delay_seconds => 0);
END;
/

CONNECT system/oracle
CREATE PUBLIC DATABASE LINK PROD1.example.com
USING 'prod1.example.com';

CONNECT repadmin/repadmin
CREATE DATABASE LINK PROD1.example.com
CONNECT TO repadmin IDENTIFIED BY repadmin;


CONNECT repadmin/repadmin
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'PROD1.example.com',
interval => 'SYSDATE + 1/8640',
next_date => SYSDATE);
END;
/


--5.レプリケーショングループの設定[node1]

CONNECT repadmin/repadmin
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'rep_group');
END;
/

 

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'rep_group',
type => 'TABLE',
oname => 'TAB1',
sname => 'TEST');
END;
/


BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'rep_group',
master => 'PROD2.example.com');
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'TEST',
oname => 'TAB1',
type => 'TABLE');
END;
/


--6.レプリケーションの開始[node1]

CONNECT repadmin/repadmin
SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'REP_GROUP';

EXECUTE DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN(gname =>'rep_group');

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'rep_group');
END;
/

--状態確認
--要求の処理中に生成されたすべてのエラー・メッセージは、
--その要求を作成したマスター・サイトのDBA_REPCATLOGビューに転送されます。
--エラーが発生せずに管理要求が正常に完了した場合は、
--この要求に関するすべてのトレースは、DBA_REPCATLOGビューから削除されます。

SELECT * FROM DBA_REPCATLOG WHERE GNAME = 'REP_GROUP';


--レプリケーションの遅延トランザクション・キューに関する統計が含まれます。
select * from V$REPLQUEUE;

 

 

Galera Cluster
http://galeracluster.com/products/
https://www.dreamvps.com/tutorials/install-mysql-galera-cluster-centos-7/
https://www.s-style.co.jp/blog/2018/06/1906/
https://blog.jetserver.net/how-to-install-configure-a-galera-cluster-with-mysql-on-centos-7


node1: 192.168.137.121
node2: 192.168.137.122
node3: 192.168.137.123


CentOS7
(5.7)


※serviceコマンドを使用する

 

--------------------------------------------
--1.インストール[全ノードで実行]


cat << EOF > /etc/yum.repos.d/galera.repo
[galera]
name = Galera
baseurl = http://releases.galeracluster.com/galera-3/centos/7/x86_64/
gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1
[mysql-wsrep]
name = MySQL-wsrep
baseurl = http://releases.galeracluster.com/mysql-wsrep-5.7.21-25.14/centos/7/x86_64/
gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1
EOF

yum -y install galera-3 mysql-wsrep-5.7 rsync lsof policycoreutils-python firewalld

 


--2.パスワード設定[全ノードで実行]
service mysqld start

grep 'password' /var/log/mysqld.log


/usr/bin/mysql_secure_installation

DATABASE_PASS='Aaa!1234'

 

echo '[client]
password='$DATABASE_PASS'
user=root' > /root/.my.cnf

mysql -u root -e 'show databases;'

service mysqld stop

 

--3.my.cnfの設定
----node1
cp /etc/my.cnf /etc/my.cnf.bak
vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
binlog_format=ROW
bind-address=0.0.0.0
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=122M
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_cluster_name="galera_cluster1"
wsrep_cluster_address="gcomm://"
wsrep_sst_method=rsync
server_id=121
wsrep_node_address="192.168.137.121"
wsrep_node_name="mmm121"
[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

 


----node2
cp /etc/my.cnf /etc/my.cnf.bak
vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
binlog_format=ROW
bind-address=0.0.0.0
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=122M
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_cluster_name="galera_cluster1"
wsrep_cluster_address="gcomm://mmm121,mmm122,mmm123"
wsrep_sst_method=rsync
server_id=122
wsrep_node_address="192.168.137.122"
wsrep_node_name="mmm122"
[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


----node3
cp /etc/my.cnf /etc/my.cnf.bak
vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
binlog_format=ROW
bind-address=0.0.0.0
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=122M
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_cluster_name="galera_cluster1"
wsrep_cluster_address="gcomm://mmm121,mmm122,mmm123"
wsrep_sst_method=rsync
server_id=123
wsrep_node_address="192.168.137.123"
wsrep_node_name="mmm123"
[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

--4.起動
----node1

service mysqld start


mysql -u root -e "show status like 'wsrep%'";
mysql -u root -e "show status like 'wsrep_cluster_size'";


----node2
service mysqld start

mysql -u root -e "show status like 'wsrep%'";
mysql -u root -e "show status like 'wsrep_cluster_size'";

----node3
service mysqld start

mysql -u root -e "show status like 'wsrep%'";
mysql -u root -e "show status like 'wsrep_cluster_size'";


--5.動作確認

----node1,node2,node3

lsof -i:4567
lsof -i:3306


----node1

mysql -u root -e 'create database test1;'

----node1,node2,node3

mysql -u root -e 'show databases;'

----node2

mysql -u root -e 'create database test2;'

----node1,node2,node3

mysql -u root -e 'show databases;'

----node3

mysql -u root -e 'create database test3;'

----node1,node2,node3

mysql -u root -e 'show databases;'

 

 

 

BDR

https://qiita.com/t-fujiwara/items/7dbdd4b5481cd4041fa4


CentOS7
node1: 192.168.137.125
node2: 192.168.137.126

 

 

--1.インストール準備[node1とnode2]

groupadd -g 301 postgres
useradd -d /home/postgres -m -u 301 -g postgres postgres
id postgres


cd /usr/local/src

wget https://yum.postgresql.org/9.4/redhat/rhel-7.6-x86_64/pgdg-centos94-9.4-3.noarch.rpm

rpm -ihv pgdg-centos94-9.4-3.noarch.rpm
yum -y check-update
yum -y groupinstall "Development Tools"
yum -y install yum-utils openjade docbook-dtds docbook-style-dsssl docbook-style-xsl
yum-builddep -y postgresql94

 


--2.PostgreSQL9.4をインストール[node1とnode2]

git clone -b bdr-pg/REL9_4_STABLE git://git.postgresql.org/git/2ndquadrant_bdr.git postgresql-bdr
mkdir /usr/local/pgsql
chown -R postgres:postgres /usr/local/pgsql
chown -R postgres:postgres /usr/local/src/postgresql-bdr

su - postgres
cd /usr/local/src/postgresql-bdr
./configure --prefix=/usr/local/pgsql --enable-debug --with-openssl
make -j4 -s install-world
exit

 

--3.BDRをインストール[node1とnode2]


id
cd /usr/local/src
git clone -b bdr-plugin/REL0_9_STABLE git://git.postgresql.org/git/2ndquadrant_bdr.git bdr-plugin
chown -R postgres:postgres /usr/local/src/bdr-plugin

su - postgres
cd /usr/local/src/bdr-plugin
PATH=/usr/local/pgsql/bin:"$PATH" ./configure
make -j4 -s all
make -s install
ll /usr/local/pgsql/bin/ | grep bdr

 

--4.PostgreSQL設定[node1とnode2]


PATH=/usr/local/pgsql/bin:"$PATH"
mkdir /usr/local/pgsql/bdr
initdb -D /usr/local/pgsql/bdr/data -A trust -U postgres

 

cd /usr/local/pgsql/bdr
cat >> ./data/postgresql.conf <<EOF
listen_addresses = '*'
shared_preload_libraries = 'bdr'
wal_level = 'logical'
track_commit_timestamp = on
max_connections = 100
max_wal_senders = 10
max_replication_slots = 10
max_worker_processes = 10
log_error_verbosity = verbose
log_min_messages = debug1
log_line_prefix = 'd=%d p=%p a=%a%q '
bdr.default_apply_delay=2000 # milliseconds
bdr.log_conflicts_to_table=on
EOF


cat >> ./data/pg_hba.conf <<EOF
host all all 192.168.137.0/24 trust
local replication postgres trust
host replication postgres 192.168.137.0/24 trust
host replication postgres ::1/128 trust
EOF

 


--5.PostgreSQL起動・DB作成[node1とnode2]


pg_ctl -l /usr/local/pgsql/bdr/data/postgres.log -D /usr/local/pgsql/bdr/data -w start
createdb -U postgres bdrdemo

 

 

--6.CREATE_EXTENSION実行[node1]


psql -U postgres bdrdemo
CREATE EXTENSION btree_gist;
CREATE EXTENSION bdr;
SELECT bdr.bdr_group_create(local_node_name := 'node1',node_external_dsn := 'host=192.168.137.125 port=5432 dbname=bdrdemo');

SELECT bdr.bdr_node_join_wait_for_ready();
\q

--7.CREATE_EXTENSION実行[node2]


psql -U postgres bdrdemo
CREATE EXTENSION btree_gist;
CREATE EXTENSION bdr;
SELECT bdr.bdr_group_join(local_node_name := 'node2',node_external_dsn := 'host=192.168.137.126 port=5432 dbname=bdrdemo', join_using_dsn := 'host=192.168.137.125 port=5432 dbname=bdrdemo');

SELECT bdr.bdr_node_join_wait_for_ready();
\q


--8.BDR状態確認[node1とnode2]


psql -U postgres bdrdemo
select * from bdr.bdr_nodes;

 

--9.PostgreSQL BDR動作確認


----node1

CREATE TABLE bdr_test (c1 INT, c2 text, PRIMARY KEY (c1));
INSERT INTO bdr_test VALUES (1, 'test1');
INSERT INTO bdr_test VALUES (2, 'test2');
SELECT * FROM bdr_test;


----node2

SELECT * FROM bdr_test;

DELETE FROM bdr_test WHERE c1 = 2;
SELECT * FROM bdr_test;

 

----node1

SELECT * FROM bdr_test;

 

Peer-to-Peer Transactional Replication
で似たようなことはできる模様