Replication

Replication 이란?

리플리케이션(Replication)은 복제를 뜻하며 2대 이상의 DBMS를 나눠서 데이터를 저장하는 방식이며, 사용하기 위한 최소 구성은 Master / Slave 구성해야 한다.

• Master DB
  웹서버로 부터 데이터 등록/수정/삭제 요청시 바이너리로그(Binarylog)를 생성하여 Slave 서버로 전달하게 됩니다 (웹서버로 부터 요청한 데이터 등록/수정/삭제 기능을 하는 DBMS로 많이 사용됩니다)
• Slave DB
  Master DBMS로 부터 전달받은 바이너리로그(Binarylog)를 데이터로 반영하게 된다. (웹서버로 부터 요청을 통해 데이터를 불러오는 DBMS로 많이 사용된다.)

Replication의 목적

• 데이터 백업
  Master 서버에 DBMS의 등록/수정/업데이터가 생기는 즉시 Slave 서버의 변경된 데이터를 전달하게 된다 이러한 과정으로 데이터의 백업을 할수 있으며, 또한 Master 서버의 장애가 생겼을 경우 Slave 서버로 변경하여 사용할수 있다.

• DBMS 부하 분산
  사용자의 폭주로 인해 1대의 DB서버로 감당할수 없을때, MySQL 리플리케이션(Replication)을 이용하여 같은 DB 데이터를 여러대를 만들수 있기에 부하를 분산하수 있다.

 

Replication 주의 사항

1. 호환성을 위해 Replication Mysql 버전을 동일하게 맞추는것이 좋다.

2. 서로 버전이 다른 경우 Slave 서버 버전이 상위 버전이여야 한다.

3. Replication 가동시 Master, Slave 순으로 가동시켜야 한다.

 

Replication 구성 절차

[Master] 서버 접속 후 유저 생성 및 권한 부여

/* 유저 생성 */
create user 'replication_user'@'%' identified by 'dusqhd10djr!!QQ';

/* 권한 생성 */
GRANT REPLICATION SLAVE ON *.* to 'replication_user'@'%';
flush privileges;

 

[Master] database 및 테이블 생성

/* DB 생성 */
create database replica_db;
use replica_db;

/* TABLE 생성 */
create table replication_tb(id INT AUTO_INCREMENT Primary key,name VARCHAR(30));

 

[Master] my.cnf 파일 설정

# 아래 설정 확인 및 추가
log-bin=mysql_bin
server-id=1
binlog-do-db=replica_db
log-slave-updates

 

[Master] mysql restart

service mysqld restart

 

[Master] 덤프 생성

mysqldump -u replication_user -p --single-transaction --master-data=2 --routines --triggers  replica_db > replica_ec2_dump.sql

## 파일 확인
ls -al

 

[Master] binlog와 포지션 확인하기

less replica_ec2_dump.sql


-- MySQL dump 10.13  Distrib 5.7.35, for Linux (x86_64)
--
-- Host: localhost    Database: MIG_TEST_DB
-- ------------------------------------------------------
-- Server version       5.7.35-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

## -------------------------------------------------------------------
#  아래의 파일명과 포지션 복사해놓기		
## -------------------------------------------------------------------
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=1285;

--
-- Current Database: `MIG_TEST_DB`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `MIG_TEST_DB` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `MIG_TEST_DB`;

---

 

[Slave] my.cnf 파일 설정

log-bin=mysql_bin
server-id=2
read_only=1

 

[Slave] restart 하기

service mysqld restart

 

[Slave] Master db 정보 입력

CHANGE MASTER TO MASTER_HOST='3.36.58.83'
     , MASTER_PORT=3306
	 , MASTER_USER='replication_user'
	 , MASTER_PASSWORD='dusqhd10djr!!QQ'
	 , MASTER_LOG_FILE='mysql_bin.000007'
	 , MASTER_LOG_POS=150 ;

 

[Slave] start slave하기

start slave

 

[Slave] 결과 확인

show slave status \G;
*************************** 1. row ***************************

..생략...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


..생략...



# 둘다 YES가 나오면 성공이다.

 

예외 상황

만약 둘중 하나라도 NO가 나온 경우

Last_IO_Error: Fatal error: The SLAVE I/O thread stops because MASTER AND SLAVE have equal MySQL SERVER UUIDs; these UUIDs must be different FOR REPLICATION TO work.

해당 에러는 Slave DB 서버의 auto.cnf 파일에 현재 서버의 UUID가 적혀있는데, 이 파일까지 Master DB 서버에서 함께 가져와서 발생하는 에러다.

다음 명령어 들로 해결해보자

systemctl stop mysqld
rm -rf /var/lib/mysql/auto.cnf
systemctl start mysqld

이후 다시 slave를 start하고 확인해보면 적용이 되어 있을 것이다.

---

Duplicate 이슈

Last_Error: Error 'Duplicate key name 'oauth_users_name_domain'' on query. Default database: 'cyauth'. Query: 'create index oauth_users_name_domain on oauth_users( username , domain , userid )'

이런 경우 slave stop을 하고 아래와 같이 해주면 된다.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

/*
여기선 중복 에러 나는 것이 1개 이므로, 여러 개 일 경우 숫자를 크게
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=100 ;

또는 위에 에러 번호가 1061 이였으므로, my.cnf 파일에 다음과 같이 설정 가능
my.cnf
slave-skip-errors = 1061,1062,1063    등등 스킵하고 싶은 에러번호 나열
*/

 

---

AWS RDS 경우

AWS RDS의 EC2로 진행하는 경우 보안그룹에 각각의 Public ip를 열어 줘야 한다.

less replica_dump.sql

-- MySQL dump 10.13  Distrib 5.7.35, for Linux (x86_64)
--
-- Host: localhost    Database: MIG_TEST_DB
-- ------------------------------------------------------
-- Server version       5.7.35-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

## -------------------------------------------------------------------
#  아래의 파일명과 포지션 복사해놓기		
## -------------------------------------------------------------------
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=1285;

--
-- Current Database: `MIG_TEST_DB`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `MIG_TEST_DB` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `MIG_TEST_DB`;