복제구성
MASTER와 SLAVE의 노드가 모두 동일한 상태에서 복제 구성이 가능. 혹은 초기 MASTER의 FULL BACKUP 본과 그 이후의 LSN을 알 수 있다면 BINARY LOG를 통해 복제 구성이 가능하다.
ASYNC 복제 구성
일반적으로 ASYNC 방식의 SLAVE 구성시 MASTER와 가본적인 설정은 비슷하고 server-id만 다르게 구성하면 된다. 아래는 간단히 ASYNC 방식으로 구성한 예제이다.
SLAVE쪽 my.cnf 구성 시 아래 내용을 변경/추가한다.
..
[mysqld]
server-id=2 #슬레이브 인스턴스 번호 변경
read_only #슬레이브 read_only 설정 추가
..
MASTER 구성
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%' identified by 'repl';
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
// SLAVE쪽에서 복제해야할 시작점을 확인한다. xtrabackup, mysqldump 수행후 복구했다면 해당 위치를 기록한다.
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 | 372 | | |
+------------------+----------+--------------+------------------+
SLAVE 구성
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%' identified by 'repl';
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> change master to
master_host='192.168.0.2', //마스터 IP주소 또는 호스트명
master_user='repl',
master_password='repl',
master_port=3301,
master_log_file='mysql-bin.000007', //기록해 놨던 마스터의 마지막 binlog 파일명
master_log_pos=372'; //기록해 놨던 마스터의 마지막 binlog LSN
Query OK, 0 rows affected (0.02 sec)
//리플리케이션 구동
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.02 sec)
//리플리케이션 상태 확인
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.2
Master_User: repl
Master_Port: 3310
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 2084
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 2230
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes // I/O쓰레드가 마스터에서 binlog를 relaylog로 받아온다
Slave_SQL_Running: Yes // SQL쓰레드가 relaylog를 DB에 반영한다.
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2084
Relay_Log_Space: 48057666
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 //마스터 슬레이브간의 리플리케이션 time gap(초)
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
SEMI-SYNC 복제 구성
ASYNC 방식의 경우 MASTER 장애시 슬레이브로 FAIL-OVER되는 과정에서 동기화가 되지 못한 트랜잭션이 있다면 해당 트랜잭션은 유실될 우려가 있다. SEMI-SYNC 방식은 이 점을 보완한 것이며 COMMIT 후 OK메시지를 받았다는 것은 적어도 SLAVE의 RELAY-LOG에 반영되었다는 것을 의미한다.
구성방법은 플러그인 설치 -> 파라미터 ON하고 기동하면 된다.
MASTER, SLAVE
# 플러그인 설치
MariaDB [(none)]>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
MariaDB [(none)]>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SEMI-SYNC 설정 및 재기동
..
[mysqld]
rpl_semi_sync_master_enabled=1 #semi-sync 설정 시 적용, 주석 해제
rpl_semi_sync_slave_enabled=1 #semi-sync 설정 시 적용, 주석 해제
..
마스터노드 에러로그 확인
[mysql@testdb ~]# tail -100f /MYSQL_LOG/db1/error/mysql.err
… 중략
161030 15:50:26 [Note] Semi-sync replication initialized for transactions.
161030 15:50:26 [Note] Semi-sync replication enabled on the master.
161030 15:50:26 [Note] Server socket created on IP: '0.0.0.0'.
… 중략
multi-source replication
0) 양쪽DB DATABASE,TABLE 정보 동기후(똑같이 해놓고)
1) plugin install show global variables like '%semi%'; show global status like '%semi%';
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
show global variables like '%semi%'; show global status like '%semi%';
2) my.cnf rpl_semi_sync_master_enabled=1 rpl_semi_sync_slave_enabled=1
server-id= gtid-domain-id=
3) db재기동
4) master node : show master status; 로 binlog명과 pos 확인
5) slave 에서
CHANGE MASTER TO master_host="192.168.100.139", master_port=3306, master_user="root", MASTER_PASSWORD='root01', MASTER_CONNECT_RETRY=10, MASTER_LOG_FILE='mariadb-bin.000075', MASTER_LOG_POS=59160352;
start slave;
show slave status\G 로 확인
6) slave 에서 gtid모드로 변경 stop slave;
CHANGE MASTER TO master_use_gtid=slave_pos;
start slave;
show slave status\G 로 확인
7) 확인 use test; trx 수행후 show slave status\G 로 정상확인
=================================================================
mater node 에서
SELECT BINLOG_GTID_POS("mariadb-bin.000039", 572); 확인후
slave에서
SET GLOBAL gtid_slave_pos = "700-70-3";
CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4, MASTER_CONNECT_RETRY=10;
CHANGE MASTER TO master_host="192.168.100.147", master_port=3306, master_user="root", MASTER_PASSWORD='root01', MASTER_CONNECT_RETRY=10, MASTER_LOG_FILE='mariadb-bin.000039', MASTER_LOG_POS=572;
CHANGE MASTER 'com' TO master_use_gtid=slave_pos;
START SLAVE;
;
CHANGE MASTER TO master_host="192.168.100.142", master_port=3306, master_user="root", MASTER_PASSWORD='root01', MASTER_CONNECT_RETRY=10, MASTER_LOG_FILE='mariadb-bin.000041', MASTER_LOG_POS=33109883;
multi-source replicaton setting
set @@default_master_connection='com'; stop slave 'com'; CHANGE MASTER 'com' TO master_host="192.168.100.147", master_port=3306, master_user="root", MASTER_PASSWORD='root01', MASTER_CONNECT_RETRY=10, MASTER_LOG_FILE='mariadb-bin.000043', MASTER_LOG_POS=48571069; start slave 'com'; stop slave 'com'; CHANGE MASTER 'com' TO master_use_gtid=slave_pos; start slave 'com';
set @@default_master_connection='shard1'; stop slave 'shard1'; CHANGE MASTER TO master_host="192.168.100.146", master_port=3306, master_user="root", MASTER_PASSWORD='root01', MASTER_CONNECT_RETRY=10, MASTER_LOG_FILE='mariadb-bin.000046', MASTER_LOG_POS=175811; start slave 'shard1'; stop slave 'shard1'; CHANGE MASTER 'shard1' TO master_use_gtid=slave_pos; start slave 'shard1';
CHANGE MASTER 'shard1' TO master_host="192.168.100.146", master_port=3306, master_user="root", MASTER_PASSWORD='root01', MASTER_CONNECT_RETRY=10, MASTER_LOG_FILE='mariadb-bin.000040', MASTER_LOG_POS=1541;
참고 : multli source : https://mariadb.com/kb/en/mariadb/multi-source-replication/ global trx id : https://mariadb.com/kb/en/mariadb/global-transaction-id/#setting-up-from-backup
-- increment 설정 | auto_increment_increment | 1 | | auto_increment_offset | 1 |
auto_increment_increment = 서버수 offset= 1...2.. 서버수
CREATE TABLE animals (
id mediumint(9) NOT NULL AUTO_INCREMENT,
name char(30) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;
insert into animals values (null,'abc'); insert into animals values (null,'abc'); insert into animals values (null,'abc'); insert into animals values (null,'abc'); insert into animals values (null,'abc'); insert into animals values (null,'abc');
insert into animals values (null,'111'); insert into animals values (null,'111'); insert into animals values (null,'222'); insert into animals values (null,'222'); insert into animals values (null,'333'); insert into animals values (null,'333');
- tshard1, tshard2, tshard3의 my.cnf에 각각 아래와 같이 설정 auto_increment_increment=3 auto_increment_offset=1
auto_increment_increment=3 auto_increment_offset=2
auto_increment_increment=3 auto_increment_offset=3
- 트랜잭션 발생후 데이터 비교
auto_increment_offset + N × auto_increment_increment
CHANGE MASTER 'com' TO master_host="192.168.100.147", master_port=3306, master_user="root", MASTER_PASSWORD='root01', MASTER_CONNECT_RETRY=10, MASTER_LOG_FILE='mariadb-bin.000040', MASTER_LOG_POS=344;
-- 공통쪽 테이블 일부 mpay DB로 옮김(07/22) alter table mclip_member_app rename to mpay.mclip_member_app ; alter table mclip_member rename to mpay.mclip_member ; alter table mclip_member_transaction rename to mpay.mclip_member_transaction ; alter table mclip_member_history rename to mpay.mclip_member_history ; alter table mclip_cardinfo rename to mpay.mclip_cardinfo ; alter table mclip_cardserial_temp rename to mpay.mclip_cardserial_temp ; alter table mclip_cardinfo_history rename to mpay.mclip_cardinfo_history ; alter table mclip_app_encryptionkey rename to mpay.mclip_app_encryptionkey ; alter table mclip_terms_agree_check rename to mpay.mclip_terms_agree_check ; alter table mclip_public_key_history rename to mpay.mclip_public_key_history ; alter table mclip_pg_link_data_history rename to mpay.mclip_pg_link_data_history; alter table mclip_ars_info rename to mpay.mclip_ars_info ;
-- fucntion 확인 show function status\G
-- APP용 user 생성 인증DB에서는 2대 : grant all on auth. to 'mpay_app'@'%' identified by 'ap123!'; 공통DB (2대): grant all on com. to 'mpay_app'@'%' identified by 'ap123!'; 샤드 전체(6대) : grant all on mpay. to 'mpay_app'@'%' identified by 'ap123!'; grant select on com. to 'mpay_app'@'%' identified by 'ap123!';
pacemaker에 percona plug-in으로 .
create database auth /!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin /;
alter table mclip_mkey rename to auth.mclip_mkey ; alter table mclip_nr_log rename to auth.mclip_nr_log ; alter table mclip_sc_log_201209 rename to auth.mclip_sc_log_201209 ; alter table mclip_sc_log_201210 rename to auth.mclip_sc_log_201210 ; alter table mclip_sc_log_201211 rename to auth.mclip_sc_log_201211 ; alter table mclip_sc_log_201212 rename to auth.mclip_sc_log_201212 ; alter table mclip_sc_log_201301 rename to auth.mclip_sc_log_201301 ; alter table mclip_sc_log_201302 rename to auth.mclip_sc_log_201302 ; alter table mclip_sc_log_201303 rename to auth.mclip_sc_log_201303 ; alter table mclip_sc_log_201304 rename to auth.mclip_sc_log_201304 ; alter table mclip_sc_log_201305 rename to auth.mclip_sc_log_201305 ; alter table mclip_sc_log_201306 rename to auth.mclip_sc_log_201306 ; alter table mclip_sc_log_201307 rename to auth.mclip_sc_log_201307 ; alter table mclip_sc_log_201308 rename to auth.mclip_sc_log_201308 ; alter table mclip_sc_log_201309 rename to auth.mclip_sc_log_201309 ; alter table mclip_sc_log_201310 rename to auth.mclip_sc_log_201310 ; alter table mclip_sc_log_201311 rename to auth.mclip_sc_log_201311 ; alter table mclip_sc_log_201312 rename to auth.mclip_sc_log_201312 ; alter table mclip_sc_log_201401 rename to auth.mclip_sc_log_201401 ; alter table mclip_sc_log_201402 rename to auth.mclip_sc_log_201402 ; alter table mclip_sc_log_201403 rename to auth.mclip_sc_log_201403 ; alter table mclip_sc_log_201404 rename to auth.mclip_sc_log_201404 ; alter table mclip_sc_log_201405 rename to auth.mclip_sc_log_201405 ; alter table mclip_sc_log_201406 rename to auth.mclip_sc_log_201406 ; alter table mclip_sc_log_201407 rename to auth.mclip_sc_log_201407 ; alter table mclip_sc_log_201408 rename to auth.mclip_sc_log_201408 ; alter table mclip_sc_log_201409 rename to auth.mclip_sc_log_201409 ; alter table mclip_sc_log_201410 rename to auth.mclip_sc_log_201410 ; alter table mclip_sc_log_201411 rename to auth.mclip_sc_log_201411 ; alter table mclip_sc_log_201412 rename to auth.mclip_sc_log_201412 ; alter table mclip_sc_log_201501 rename to auth.mclip_sc_log_201501 ; alter table mclip_sc_log_201502 rename to auth.mclip_sc_log_201502 ; alter table mclip_sc_log_201503 rename to auth.mclip_sc_log_201503 ; alter table mclip_sc_log_201504 rename to auth.mclip_sc_log_201504 ; alter table mclip_sc_log_201505 rename to auth.mclip_sc_log_201505 ; alter table mclip_sc_member rename to auth.mclip_sc_member ; alter table mclip_sc_tran rename to auth.mclip_sc_tran ; alter table mclip_smstemp rename to auth.mclip_smstemp ;
nohup bin/mysqldump -uroot --socket=data/mysqld.sock -proot01 mpay > mpay_0825.sql &
nohup bin/mysqldump -uroot --socket=data/mysqld.sock -proot01 -A > mpay_0825.sql &
CHANGE MASTER 'com' TO master_host="192.168.100.143", master_port=3306, master_user="root", MASTER_PASSWORD='root01', MASTER_CONNECT_RETRY=10, MASTER_LOG_FILE='mariadb-bin.000075', MASTER_LOG_POS=198637297; start slave ; stop slave ; CHANGE MASTER TO master_use_gtid=slave_pos; start slave ;
CHANGE MASTER 'com' TO master_host="192.168.100.147", master_port=3306, master_user="root", MASTER_PASSWORD='root01', MASTER_CONNECT_RETRY=10, MASTER_LOG_FILE='mariadb-bin.000043', MASTER_LOG_POS=74665367; 48571069