MySQL Replication 이해(1) – 개념

Overview

오늘은 조금더 제너럴한 주제를 가지고 정리를 할까합니다.
바로 MySQL Replication 입니다. MySQL Community에서 유일하게 HA 또는 분산 구성을 할 수 있는 유일한 기능입니다. 물론 “MySQL+DRBD 구성”와 같이 HA를 구성하는 방법도 있습니다만, MySQL 제품이 아니므로 스킵~!

먼저 Replication에 대해 간략하게 말씀 드리겠습니다.

MySQL Replication이란?

MySQL Replication이란 말 그대로 복제입니다. 영어 사전에 나온 듯한 DNA는 아니지만 데이터를 “물리적으로 다른 서버의 저장 공간” 안에 동일한 데이터를 복사하는 기술이죠.

다음 그림은 MySQL Replication을 가장 간단하게 나타낸 그림입니다.  데이터 변경을 마스터 장비에서만 수행하기 때문에 마스터 장애 시에는 전체 노드에 데이터 쓰기 작업이 불가능한 한계가 있습니다.

MySQL Replicaton Master Slave
MySQL Replicaton Master Slave

아래 그림은 MySQL Replication과 Oracle RAC스토리지 구조를 가장 간단하게 묘사한 그림입니다.

MySQL Replication과 Oracle RAC 비교
MySQL Replication과 Oracle RAC 비교

MySQL복제라는 말과 같이, 디스크를 독립적으로 분리하여 데이터를 유지합니다.

이에 반해 오라클은 RAC 구성 시에는 공유 스토리지(SAN,iSCSI) 장비를 중간에 두고 DB를 이중화 합니다. 엄격하게 다시 말하자면, “MySQL Replication은 데이터를 이중화”하는 것이고, “Oracle RAC는 DB를 이중화하는 개념”입니다.

MySQL은 오직 단일 마스터에서만 데이터 변경 작업을 수행할 수 있고, Oracle은 하나의 스토리지를 중간에 두고 여러 노드에서 데이터 변경 작업이 일어날 수 있습니다.  그렇기 때문에 MySQL에서는 쓰기 부하 분산은 불가능하지만, 읽기 부하 분산은 가능합니다. 그리고 특정 노드 디스크 장애가 전체 데이터 유실로 이어지지 않습니다. 데이터는 “복제”되니까요. Oracle은 어느정도의 읽기/쓰기 부하 분산은 가능하지만 공유 스토리지를 쓰는 만큼 스토리지 장애에는 상당히 취약합니다. 어디까지나, 일반 구성 시 비교를 한 것임을 알아주세요^^

마스터, 슬레이브 간 Data 복제 방법

MySQL Replication은 로그 기반으로 비동기적으로 데이터를 복제합니다. 마스터에서는 데이터 변경 작업이 수행되면 Binary Log라는 곳에 이력을 기록을 하는데, Statement, Row 그리고 Mixed 등 세 가지 방식이 있습니다.

Statement-based Type
MySQL 3.23 이후로 도입된 방식
실행된 SQL을 그대로 Binary Log에 기록
Binary Log 사이즈는 작으나, SQL 에 따라 결과가 달라질 수 있음
(Time Function, UUID, User Defined Function)
Row-based Type
MySQL 5.1부터 도입된 방식
변경된 행을 BASE64로 Encoding하여 Binary Log에 기록
특정 SQL이 변경하는 행 수가 많은 경우 Binary Log 사이즈가 비약적으로 커질 수 있음
Mixed Type (Statement + Row)
기본적으로 Statement-Based Type으로 기록되나, 경우에 따라 Row-base Type으로 Binary Log에 기록

그렇다면 복제는 어떤 방식으로 이뤄질까요? 아래 그림으로 설명 드리겠습니다.

MySQL에서 데이터 복제 방법
MySQL에서 데이터 복제 방법
  1. Master에서 데이터 변경이 일어나면 자신의 데이터베이스에 반영합니다.
  2. Master에서 변경된 이력을 Binary Log에 기록 후 관련 이벤트를 날립니다.
  3. Slave IO_THREAD에서 Master 이벤트를 감지하고,
    Master Binary Log 자신의 Relay Log라는 곳에 기록을 합니다.
  4. Slave SQL_THREAD는 Relay Log를 읽고 자신의 데이터베이스에 기록을 합니다. (4,5단계)

기억해야할 사항은 마스터에서는 여러 세션에서 데이터 변경 처리가 가능하지만, 슬레이브에서는 오직 하나 SQL Thread에서만 데이터 변경 처리가 가능한 점입니다. 그렇기 때문에 마스터에 데이터 변경 트래픽이 과도하게 몰리게 되면 마스터/슬레이브 간 데이터 동기화 시간이 크게 벌어질 수도 있습니다.

마치며..

리플리케이션을 잘 활용하면, 부하분산 뿐만 아니라 고가용성 그리고 버전 테스트 등 여러 분야에 멋지게 사용할 수 있습니다. 차근차근 이러한 내용을 정리해서 포스팅하도록 하겠습니다.
일단, 다음 번에는 실제 리플리케이션 구성 방법에 대해서 먼저 진행할께요^^
좋은 하루 되세요~!

리눅스에 MySQL 설치하기(CentOS 5.6)

MySQL DBMS 를 설치할 때 제가 적용하는 내용을 공유합니다.
root 계정으로 설치 준비를 하고, mysql 계정으로 DB를 구동합니다.
일단 하단 내용들은 root계정으로 수행을 합니다. 

OS 계정 추가

다음과 같이 dba 그룹을 추가하고 그 밑에 mysql 계정을 추가합니다.

groupadd -g 600 dba
useradd -g 600 -u 605 mysql
passwd mysql

Linux 설정 변경

세션 Limit 를 설정합니다.

vi /etc/security/limits.conf
##하단 내용 추가
mysql            soft    nproc  8192
mysql            hard    nproc  16384
mysql            soft    nofile 8192
mysql            hard    nofile 65536

OS에서 limits.conf 파일을 읽어들이도록 설정합니다. 없으면 생성합니다.

vi /etc/pam.d/login
## 하단 내용 추가
session    required     pam_limits.so

/etc/profile 에 다음 내용을 추가하여 login 시 적용되도록 합니다.

vi /etc/profile
##
if [ $USER = "mysql" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi

MySQL 데이터 저장 디렉토리를 생성합니다.

mkdir -p /data/mysql/mysql-data
mkdir -p /data/mysql/mysql-tmp
mkdir -p /data/mysql/mysql-iblog
mkdir -p /data/mysql/mysql-binlog

MySQL 설치 파일 다운로드

하단 실행 시 x86_64 가 있으면 64비트이고, i686 이 있으면 32비트입니다.

## OS 버전 확인 ##
uname -a
Linux ..중략.. EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

이제 MySQL Download 의 “Linux – Generic” 탭에서 자신의 OS에 맞는 MySQL Server 받으세요. 현재 Release되는 주 버전은 MySQL 5.5.x이나, 여기서는 MySQL 5.1.57 64비트 버전으로 설명드리겠습니다.

굴욕적이지만, 한국보다는 일본 mirror서버에서 받는 것이 빠르다는..-_-;;

cd /usr/local/
## 설치 파일 다운로드
wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.19-linux2.6-x86_64.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

MySQL 기본 설정

시스템에 따라 데이터 파일과 같은 일부 변수 값이 달라질 수 있으니, 자신의 시스템에 맞게 수정해서 사용하세요.

vi /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
# generic configuration options
port = 3306
socket = /tmp/mysql.sock

back_log = 100
max_connections = 500
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
join_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
bulk_insert_buffer_size = 16M
thread_cache_size = 128
thread_concurrency = 16
query_cache_type = 0
default_storage_engine = innodb
thread_stack = 192K
lower_case_table_names = 1
max_heap_table_size = 128M
tmp_table_size = 128M
local_infile = 0
max_prepared_stmt_count = 256K
event_scheduler = ON
log_bin_trust_function_creators = 1
secure_auth = 1
skip_external_locking
skip_symbolic_links
#skip_name_resolve

## config server and data path
basedir = /usr/local/mysql
datadir = /data/mysql/mysql-data
tmpdir = /data/mysql/mysql-tmp
log_bin = /data/mysql/mysql-binlog/mysql-bin
relay_log = /data/mysql/mysql-binlog/mysql-relay
innodb_data_home_dir = /data/mysql/mysql-data
innodb_log_group_home_dir = /data/mysql/mysql-iblog

## config character set
##utf8
character_set_client_handshake = FALSE
character_set_server = utf8
collation_server = utf8_general_ci
init_connect = "SET collation_connection = utf8_general_ci"
init_connect = "SET NAMES utf8"

## bin log
binlog_format = row
binlog_cache_size = 4M

## Replication related settings
server_id = 1
expire_logs_days = 7
slave_net_timeout = 60
log_slave_updates
#read_only

## MyISAM Specific options
key_buffer_size = 32M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 16M
myisam_repair_threads = 1
myisam_recover = FORCE,BACKUP

# *** INNODB Specific options ***
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G

innodb_data_file_path = ibdata1:1G:autoextend
innodb_file_per_table = 1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 120
innodb_support_xa = 0
innodb_file_io_threads = 8

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no_auto_rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive_timeout

[mysqld_safe]
open_files_limit = 8192

MySQL Server 설치

## 압축 해제
cd /usr/local
tar xzvf mysql-5.5.19-linux2.6-x86_64.tar.gz
## 관리를 위한 심볼릭 링크 생성
ln -s mysql-5.5.19-linux2.6-x86_64 mysql
## 설치 파일 권한 변경
chown -R mysql.dba /usr/local/mysql*
## 시작 스크립트 복사
cp mysql/support-files/mysql.server /etc/init.d/mysqld
## 관련 파일 권한 설정
chown mysql.dba /data/mysql/*
chown mysql.dba /etc/my.cnf
chown mysql.dba /usr/local/mysql*

여기서부터는 이제 mysql 계정으로 실행을 합니다.
관리를 위해서 몇몇 alias를 설정하는 부분입니다.^^

su - mysql
cat >> ~/.bash_profile
## 하단 내용 입력
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin:.
export ADMIN_PWD="ROOT 패스워드"

alias ll="ls -al --color=auto"
alias mydba="mysql -uroot -p$ADMIN_PWD"
alias mymaster="mysql -uroot -p$ADMIN_PWD -e'show master status;'"
alias myslave="mysql -uroot -p$ADMIN_PWD -e'show slave status\G'"
alias mh="cd $MYSQL_HOME"
alias md="cd /data/mysql/mysql-data"
alias mt="cd /data/mysql/mysql-tmp"
alias mb="cd /data/mysql/mysql-binlog"
alias mi="cd /data/mysql/mysql-data"
alias dp="cd /data/mysql/mysql-data"

## 환경 변수 적용
. ~/.bash_profile

MySQL Server 구동

cd /usr/local/mysql
## 기본 데이터베이스 설치
./scripts/mysql_install_db
## MySQL 데몬 Startup
/etc/init.d/mysqld start

MySQL 보안 설정

처음 DB를 올리면 보안 면에서 취약한 부분이 있습니다.
기본적인 보안 정책을 적용하도록 합니다.
mysql root  계정 패스워드만 설정하고 나머지는 Enter만 쭉 치면 됩니다.

cd /usr/local/mysql
./bin/mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Change the root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
- Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
... Failed! Not critical, keep moving...
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

이제 MySQL DB 설치가 다 끝났습니다. 참 쉽죠잉~!^^

반드시 알아야할 MySQL 특징 세 가지

Overview

MySQL 요구가 전보다 급증하고 있습니다. 이제 친숙해서 사용하는 간단한 소용량 DBMS 이 아닌, 많은 대형 업체에서도 사용되고 있기 때문에 많은 이슈가 되고 있습니다. 트위터, 페이스북, 구글, 야후 뿐만 아니라 최근들어 SNS 열풍으로 국내에도 MySQL 관련하여 엄청난 붐이 시작되려는 찰나인 듯 하네요.

대형 인터넷 서비스

KTH에 입사를 한 당시에 주력 DB는 Oracle이었습니다. 그러나 라이선스 비용 문제로 주력 DB 선정을 위한 저울질이 시작되었고, 그중 일반 개발자에게도 친숙한 MySQL을 선택했습니다.

MySQL 세가지 특성?

MySQL 3.X 버전으로 광고 시스템을 만든 적이 있습니다. 꽤나 오래된 얘기..
지금 생각하면 당시 광고 시스템에서 DB에 날리는 쿼리는 간단하기는 했지만, 상당한 트래픽을 무난히 견디는 것을 보고 감탄을 금치 않았습니다. 와~! 이거 물건인데? 제 첫 사용 소감이었습니다.하지만 “과연 MySQL에 대한 중요한 특성을 잘 알고 있었을까?” 라는 생각이 들었습니다.그때부터였다. MySQL이라는 녀석과 진지한 악수를 한번 해보고 싶다는 생각을 한 것이.. 그래서 여기저기 해외 사례도 기웃거리고, 서적도 진지하게 읽기 시작했죠. 읽으면서 틈틈이 벤치마킹도 수행해보고 나름의 지식 베이스를 늘려갔습니다.
  • 단일 코어에서 Nested Loop Join 처리
  • 다양한 스토리지 엔진
  • 데이터 복제(Replication) 기능

1) 단일 코어에서 Nested Loop Join 처리

MySQL에서는 모든 SQL 처리를 단일 코어에서 Nested Loop Join 방식으로만 데이터를 처리합니다. 병렬 처리라는 것은 없습니다. 물론 일부 3rd스토리지 엔진을 플러그인으로 설치를 하면 병렬 처리가 가능하다고는 하지만, 기본적인 스토리지 엔진에는 단일 코어 수행합니다. 그렇기 때문에 MySQL 입장에서는 CPU코어 개수를 늘리는 Scale-Out보다는 오히려 단위 처리량이 좋은 CPU로 Scale-Up을 하는 것이 훨씬 유리합니다.
MySQL Single Core Processing
MySQL Single Core Processing
게다가 모든 데이터 처리를 Nested Loop Join(이하 NL join)으로 처리합니다. NL join이란 선행 테이블(A)의 조건 검색 결과 값 하나하나를 엑세스 하면서 연결할 테이블(B)에 대입하여 조인하는 방식입니다. 프로그램적으로 풀자면 2 중 While문과 유사하다고 볼 수 있습니다. 처리할 데이터가 적으면 수행 속도가 빠르지만, A테이블 또는 B테이블 중 하나라도 연산을 해야할 데이터가 많아지만 쿼리 효율이 기하급수적으로 떨어집니다.
Nested Loop Join in MySQL
출처 : http://dev.mysql.com/doc/refman/5.5/en/nested-loop-joins.html
Outer Join이든, Sub Query든, Inner Join이든 모두 NL-Join으로만 처리합니다. DW 또는 데이터 분석 도구보다는 단순 처리를 위한 OLTP 프로세싱에 적합한 DBMS입니다.

2) 다양한 스토리지 엔진

MySQL은 다양한 스토리지 엔진을 지원합니다. MyISAM, InnoDB, Archive, Memory, NDB, Federated 등 기본 엔진 뿐만 아니라 3rd Party 스토리지 엔진도 간단하게 플러그인 형식으로 설치를 할 수 있습니다.
제가 자주 사용하는 스토리지 엔진 세 가지(MyISAM, InnoDB, Archive) 세 가지에 관한 간단한 비교 표입니다.
MySQL Storage Engines
MySQL Storage Engines 비교
  • MyISAM Storage Engine
    인덱스만 메모리에 올려서 테이블 잠금으로 데이터를 처리하는 스토리지 엔진으로 단순 백그라운드에서 로그 수집에 적합합니다. 동시 다발적으로 데이터 변경 작업을 수행하는 로직에는 절대 적합하지 않습니다. 특히 인덱스가 걸려있는 상태에서 대용량(500만 건 이상) 데이터 처리 시 단순 입력 상태에서도 테이블 잠금이 빈번하게 발생할 수 있다는 점을 명심하시기 바랍니다.
  • Archive Storage Engine
    원시 로그 수집에 최적인 스토리지 엔진입니다. 트랜잭션, 인덱스 모두 지원하지 않지만, 테이블에서 데이터 처리를 행 단위 잠금으로 수행하기 때문에 동시 다발적으로 데이터 입력 상황에도 상당히 좋은 퍼포먼스를 제공합니다. 게다가 메모리에서 데이터 압축을 수행하면서 실제적으로 디스크에 기록하기 때문에, InnoDB 대비 꽤 좋은 디스크 용량 효율이 있습니다. 게다가 파티셔닝을 지원하기 때문에 추후 로그 정리 시에도 상당히 간단하죠.
  • InnoDB Storage Engine
    현재 Oracle에서 가장 밀고 있는 스토리지 엔진으로 In-Memory 특성을 가지고 있습니다. 메모리에 인덱스/데이터 모두 올려서 데이터를 처리하기 때문에 데이터 접근 속도가 상당히 빠릅니다. 메모리가 많이 허용되면 엄청난 퍼포먼스를 발휘하는 엔진이죠. 게다가 트랜잭션을 제공하고, 동시 데이터 처리 시에도 행 단위 잠금으로 처리하기 때문에 실제적으로 InnoDB Storage Engine은 OLTP 성 대용량 처리에 가장 적합한 스토리지 엔진이라고 볼 수 있습니다.

위 세가지 엔진을 능숙하게 사용할 수만 있다면 서버 효율성을 상당 수준 이끌어 낼 수 있다고 생각하는데요. 그래서 간단하게 선정 기준을 다음과 같이 만들어 보았습니다.(지극히 개인적인 생각입니다.)

스토리지 엔진 선정 기준
스토리지 엔진 선정 기준

3) 데이터 복제(Replication) 기능

MySQL은 물리적으로 독립적인 디스크 영역에 데이터를 복제(Replication)하여 데이터를 이중화할 수 있습니다.  하단 그림은 가장 대표적인 MySQL Replication Master-Slave구조를 나타낸 그림입니다. 마스터에서 데이터 변경이 작업이 일어나면, 해당 내역이 자동으로 슬레이브로 비동기적으로 전송되어 실제 마스터의 데이터 복사본을 유지하는 것을 의미합니다.
MySQL Replication 서버 구성
MySQL Replication 서버 구성
특정 데이터 디스크 Fail시에도 다른 물리적으로 독립적인 디스크에 데이터가 존재하기 때문에, DB 버그가 아니라면 유실은 거의 없습니다. 게다가 디스크 읽기 분산이 가능하기 때문에 읽기 트래픽이 상당히 큰 서비스에 큰 효율을 갖습니다. 대신 데이터 반영은 오직 하나의 노드, 마스터에서만 가능하기 때문에 쓰기 관련된 부하 분산이 불가능합니다. 그리고 마스터 자체 장애는 쓰기 관련 전체 장애로 발생한다는 점도 꼭 기억하세요.

마치며..

MySQL DBMS 사용 시 반드시 알아야하는 세가지 특성!! 꼭 잊지마세요.^^
차근차근 각 특성들을 상세하게 정리해서 공유드리도록 하겠습니다.
읽어주셔서 감사합니다.