MySQL Replication 적용하기(데이터 동기화 및 백업) MySQL/MariaDB

Master - Salve1 - Slave2로 운영되는 시스템 설정이다.
Master는 UPDATE, SELECT를 담당하고, Slave는 SELECT를 담당한다.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++ Replication 적용하기 (Mysql 5.x) ++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* 사용자 권한 생성
[ Master DB 사용자 ]
mysql> GRANT file ON *.* TO repl1@"%" IDENTIFIED BY 'repl1';
mysql> GRANT REPLICATION SLAVE  ON *.* TO repl1@"%" IDENTIFIED BY 'repl1';   => slave서버 모두에서 repl1계정 하나로만 접속해도 무관하다. 
mysql> GRANT file ON *.* TO repl2@"%" IDENTIFIED BY 'repl2';
mysql> GRANT REPLICATION SLAVE  ON *.* TO repl2@"%" IDENTIFIED BY 'repl2';
mysql> flush privileges;


[ Slave1,2 DB 사용자 ]
mysql> GRANT file ON *.* TO repl2@"%" IDENTIFIED BY 'repl2';
mysql> GRANT REPLICATION SLAVE  ON *.* TO repl2@"%" IDENTIFIED BY 'repl2';
mysql> flush privileges;


############# MASTER (/etc/my.cnf) #############
[client]
port            = 3306
socket          = /tmp/mysql.sock


[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 500M
table_open_cache = 1280
sort_buffer_size = 12M
read_buffer_size = 12M
read_rnd_buffer_size = 24M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
max_connection = 1024
max_connect_errors = 999999
tmp_table_size = 100M
wait-timeout = 86400
interactive_timeout = 86400
# character set
default-character-set=utf8
init_connect=set collation_connection=utf8_general_ci
init_connect=set names utf8
character-set-server=utf8
collation-server=utf8_general_ci


log-bin=mysql-bin
server-id       = 1


[mysqldump]
quick
max_allowed_packet = 32M
default-character-set=utf8


[mysql]
no-auto-rehash
default-character-set=utf8


[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 4M
write_buffer = 4M


[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 4M
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout


############# SLAVE1  (/etc/my.cnf) #############
[client]
port            = 3306
socket          = /tmp/mysql.sock


[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 500M
table_open_cache = 1280
sort_buffer_size = 12M
read_buffer_size = 12M
read_rnd_buffer_size = 24M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
max_connection = 1024
max_connect_errors = 999999
slave-skip-errors = all
tmp_table_size = 100M
wait-timeout = 86400
interactive_timeout = 86400
# character set
default-character-set=utf8
init_connect=set collation_connection=utf8_general_ci
init_connect=set names utf8
character-set-server=utf8
collation-server=utf8_general_ci


server-id       = 2

master-host     = master.domain.com
master-user     = repl1
master-password = repl1
master-port     = 3306


[mysqldump]
quick
max_allowed_packet = 32M
default-character-set=utf8


[mysql]
no-auto-rehash
default-character-set=utf8


[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 4M
write_buffer = 4M


[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 4M
write_buffer = 4M


[mysqlhotcopy]
interactive-timeout


############# SLAVE2  (/etc/my.cnf) #############
[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 500M
table_open_cache = 1280
sort_buffer_size = 12M
read_buffer_size = 12M
read_rnd_buffer_size = 24M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
max_connection = 1024
max_connect_errors = 999999
slave-skip-errors = all
tmp_table_size = 100M
wait-timeout = 86400
interactive_timeout = 86400
# character set
default-character-set=utf8
init_connect=set collation_connection=utf8_general_ci
init_connect=set names utf8
character-set-server=utf8
collation-server=utf8_general_ci


server-id       = 3

master-host     = master.domain.com
master-user     = repl2
master-password = repl2
master-port     = 3306


[mysqldump]
quick
max_allowed_packet = 32M
default-character-set=utf8


[mysql]
no-auto-rehash
default-character-set=utf8


[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 4M
write_buffer = 4M


[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 4M
write_buffer = 4M


[mysqlhotcopy]
interactive-timeout


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* 데이터 동기화(Master, Slave모두)
[ 방법 1 ]
~/mysql/data/에서 아래 로그 파일 삭제 후 재 기동
mysql-bin*
host-relay-bin*
master.info
relay-log.info


[ 방법 2(Salve)]
mysql> LOAD DATA FROM MASTER (MyISAM)


[ 방법 3 ]
Master MySQL data 디렉토리를 압축 후 Slave에 옮긴 후 실행(Master DB중지 후 작업)


* 기타
SLAVE실행시에 --skip-dbd --low-priority-updates --delay-key-write-for-all-tables 옵션을 주면 좋다.
SQL명령을 통한 PRELICATION 실행 및 중지
STOP [SLAVE | MASTER]
RESET [SLAVE | MASTER]
START [SLAVE | MASTER]
SHOW  [MASTER | SLAVE] STATUS; => 상태확인


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++Mysql Replication에서 특정한 Position으로 건너뛰고 싶을때+++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

replication서비스를 진행하다가 보면
replication이 아래와 같은 이유등으로 오류가 날 경우가 있습니다.
이때의 데이터가 필요없는 부분이라면 그냥 건너뛰게 하면 되는데요
건너뛰게 하는 방법은 아래와 같습니다.

slave서버에서 멈춘 위치 확인(Exec_master_log_pos: 1033756136)
mysql> show slave status\G

master서버에서 멈춘 위치근처에 어떤 쿼리들이 있는지 확인
mysql> show binlog events in 'MASTERSERVER-bin.012' from 1033756136 limit 3\G

오류가 발생한 쿼리는 필요없으므로 다음 쿼리로 이동
mysql> slave stop;
mysql> change master to master_log_file='MASTERSERVER-bin.012', master_log_pos=1033756434;
mysql> slave start;
mysql> show slave status\G


덧글

  • 장땡 2013/06/12 11:20 # 답글

    CHANGE MASTER TO MASTER_HOST='maildb01.korea.com', MASTER_USER='repl',
    MASTER_PASSWORD='ehdrlghk!', MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=0;

    이런 형식으로도 가능하며
    반드시 에러 로그에 포함되는 Position 값보다 작은 값을 적는다. (MASTER_LOG_POS=0)
  • 장땡 2013/08/19 17:11 # 삭제 답글

    MySQL 5.6.13을 설치하는데 Slave서버에서 Master설정하는 옵션이 없습니다. 젠장
    그래서 Slave 동기화를 죽이고 아래와 같이 동기화를 시켜야합니다
    .
    CHANGE MASTER TO MASTER_HOST='127.0.0.
    ', MASTER_USER='user', MASTER_PORT=3306, MASTER_PASSWORD='passwd', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1;

    그리고 Slave 동기화를 시작해야하죠.

    또있어요. 동기화를 실행하면 아래와 같은 에러가 발생하는데
    mysql/data/auto.cnf파일을 없애고 MySQL을 재시작하세요.

    [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIror_code: 1593

    버전이 올라가면서 몇 몇 가지 바뀐부분때문에 노가를 했네요. ㅠㅠ
댓글 입력 영역