DB replication

22/01/27

 

-  동작원리

http://cloudrain21.com/mysql-replication 참고

데이터베이스에서 데이터를 복제하는 방식은 크게 동기, 비동기 방식이 있다.

동기 방식은 Master 데이터 변경이 일어나면 slave까지 동시에 적용되는 것을 보장

비동기 방식은 Master 데이터 변경이 일어나면 slave 에는 시차를 두고 동기화 되는 방식이다.

비동기 방식은 slave를 이용해 바로 서비스를 이어 가기엔 데이터 접합성 문제가 있을 수 있다.

 

Mysql은 기본적으로 비동기 복제 방식을 사용한다.

 

Master - Slave 

Master가 데이터 변경에 대한 이력을 binary log에 기록한다. bin-log를 읽어 slave 쪽으로 데이터를 전송, slave에서 데이터를 수신하여 relay-log에 기록, Relay log를 읽어서 해당 데이터를 적용한다.

트랜젝션이 있으니  복제가 한번에 일어나는 것이 아니다. relay-log를 생성해서 작업을 해야하는 기록을 남기는 것이다. 

 

Master와 Slave는 트랜젝션을 비교하지 않는다. (비동기화 방식)

Active와 standby는 트랜젝션을 비교한다. 트랜젝션을 처리후 standby로 넘기고 실행해서 서로 완료하면 저장한다.

 

DB 마다 HA를 위한 솔루션이 다르다.

 

-SQL sever(mssql) HA(high availablity) 솔루션

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=kimsajang&logNo=221249340057 참고

 

- Failover Clustering

윈도우 서버 장애조치 클러스터 기반

인스턴스(서버) 수준  장애조치

별도의 공유 스토리지(SAN) 구축 필요, SAN 장애시 복구 불가

 

- Database Mirroring

구성에 서버 3개가 필요하다. Monitoring(DB 상태 확인), DB1(Active), DB2(Standby)

DB 수준 고 가용성 제공

하나의 미러 서버만 구성 가능(1:1)

미러 중인 데이터베이스에서 작업 불가

2012버전부터 alwaysON으로 지원

 

- Replication

DB 복제

사용자 분산 효과가 주 목적이나 피어-투-피어 복제로 구성할 경우 고가용성 보장 한다.( 장애조치 기능이 거의 없음)

 

- Log Shipping

1:M의 관계로 보조 서버 확장이 가능하다.

자동 장애조치 기능 없고 실시간 동기화 불가능

 

- Alwayson

구성에 서버 3개필요 - AD(Active Directory, 사용자를 나눠서 관리한다), DB1,DB2

장애조치 클러스터링 + 미러링 장점을 결합

데이터 베이스 + 인스턴스 수준 장애조치

4대의 보조 서버 구성, 보조 서버에서 작업 가능 (사용자 분산 효과)

공유 스토리지가 필요하지 않다 (구축 비용 절감)

자동화된 HA - DB 한개가 죽으면 부수적인 설정 없이 standby DB를 자동으로 올려준다.

 

 

 

DB replication

보통 Active, stand by 방식이다. Master, slave 방식도 있다. Master, master 방식도 있지만 위험해서 많이 사용하는 방식은 아니다.

 

 

 

- SQL 명령어의 종류

DML(Data Manipulation Language) : 데이터 조작 언어, 데이터 변경에 사용됨, SELECT, INSERT, UPDATE, DELETE

DDL(Data Definition Language) : 데이터 정의 언어, 데이터베이스 객체(DB,TBL,함수..)를 변경하는데 사용됨, CREATE, DROP(삭제), ALTER

DCL(Data Control Language) : 데이터 제안 언어, 사용자에게 권한 부여시 주로 사용된다.

 

 

- DB replication

 

두개의 서버에서 db 설치 및 가동한다.

yum install -y mariadb-server mariadb
systemctl start mariadb && systemctl enable mariadb

 

<Master>

vi /etc/my.cnf 로 mySQL 설정 파일의 설정을 변경해준다.

[mysqld]
..
log-bin=mysql-bin
server-id=1

을 추가 해준다. ( 위치 중요하다)

systemctl restart mariadb 로 재가동해준다.

 

 

mysql -u root -p 로 DB에 들어간다.

CREATE DATABASE sqlDB;
USE sqlDB;
CREATE TABLE userTBL
( 
userID CHAR(8) NOT NULL,
name NVARCHAR(10)  NOT NULL,
birthYear INT NOT NULL,
addr NCHAR(2) NOT NULL,
mobile1 CHAR(3),
mobile2 CHAR(8),
height SMALLINT,
mDATE DATE
);

INSERT INTO userTBL VALUES ('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO userTBL VALUES ('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO userTBL VALUES ('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO userTBL VALUES ('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO userTBL VALUES ('SSK', '성시경', 1979, '서울', NULL, NULL, 186, '2013-12-12');
INSERT INTO userTBL VALUES ('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO userTBL VALUES ('YJS', '윤종신', 1969, '경남', NULL, NULL, 170, '2005-5-5');
INSERT INTO userTBL VALUES ('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO userTBL VALUES ('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO userTBL VALUES ('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');

db 만들고 테이블 만들어준다.

GRANT ALL PRIVILEGES ON *.* TO 'gasida'@'%' IDENTIFIED BY 'qwe123'; 
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'qwe123'; 

사용자를 생성해준다. gasida는 원격으로 DB에 접속하는 계정, repl_user는 DB복제 할때 마스터와 슬레이브를 연결 하는 계정이다.

flush privileges; 변경사항을 저장 해준다.

 

 

flush tables with read lock;

 

show master status; 를 통해 file과 position의 정보를 저장해 놓는다.

여기에 나오는 log file에 대한 정보를 가지고 slave가 master를 바라보며 정보를 가져오기 때문이다.

 mysql-bin.000001   245

 

쉘로나와서 백업 파일을 생성해준뒤 slave에 보내준다.

mysqldump -uroot -p sqlDB > /tmp/backup_sqlDB.sql
scp /tmp/backup_sqlDB.sql root@172.27.0.241:/tmp/backup_sqlDB.sql   -> slave ip 넣는다.'

 

 

 

 

 

 

<Slave>

 

vi /etc/my.cnf 설정을 변경해준다.

[mysqld]
..
log-bin=mysql-bin
server-id=2
read_only=1
replicate-do-db='sqlDB'

log-bin=mysql-bin             Binlog의 파일명으로 마스터와 같아야한다.
server-id=2                      마스터와 다른 값을 1 ~ 2^32 까지의 값을 넣어준다.
read_only=1                     읽기만 가능하게 하는 것이다.
replicate-do-db='sqlDB'      넣어준  데이터 베이스 복제한다.

만 추가해준다.

 

systemctl restart mariadb 해준다.

 

 

mysql -u root -p

CREATE DATABASE sqlDB ; 로 데이터베이스 하나 만들어준다.

 

mysql -u root -p sqlDB < /tmp/backup_sqlDB.sql 백업 받아온 sql파일을 복제해준다.

 

 

 

Slave에서 Master의 Replication을 위한 Master연결 정보를 입력한다.

mysql -u root -p 로 들어간 뒤에 슬래이브 쪽에서 마스터 설정을 해준다. 

change master to
master_host="마스터IP",
master_user="repl_user",
master_password="qwe123",
master_log_file="mysql-bin.000001",
master_log_pos=245;

- master_host="마스터IP",       Master 서버의 host 혹은 IP주소
- master_user="repl_user",       Master에서 replication을 위해 만든 계정
- master_password="qwe123",      replication 계정의 패스워드
- master_log_file="mysql-bin.000001",        Master에서 'show master status' 명령어를 통해 따로 적어두었던 log file - name
- master_log_pos=245;                  log file position

 

START SLAVE; 를 통해서 슬레이브로 동작을 시작하게 한다.

 

 

 

<확인>

slave 측에서 

 

show slave status\G

Slave_IO_Running: Yes  
Slave_SQL_Running: Yes 여야한다. 이면 정상 동작 하는 것이다. 만약 Slave_IO_Running: Connecting 이면 양쪽 통신이 안되는 것이니 방화벽 같은 것을 의심해보자! 아님 한쪽 서버가 중단 된 것이다.

 

 

 

 

마스터에서 sqlDB 들어가서

USE sqlDB;

테이블 락풀고

unlock tables;

INSERT INTO userTBL VALUES ('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');

INSERT 구문 바꿔준다.

 

이 두 구문이 마스터 슬레이브 모두 들어있는지 확인한다. select * from sqlDB.userTBL

 

master 측에서 

INSERT INTO userTBL VALUES ('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5'); 를 사용해 sqlDB에 추가해주면 slave 측에 추가가 된다.

 

 

slave에 insert 구문을 사용시 오류가 난다.

Auto increment(AI) : 자동으로 증가하는 값을 가지는 컬럼이다.

Primary key :