Mysql Slave (replication)

Mysql Replication 구축

작성일 2013.10.04

추가 수정일 2014.04.14

우선 서버 설치.. 이미 과거 포스팅 여러 차례이므로 접어 둔다. 

과거 Posting Install Mysql (source version) 

Tip: 선작업 - Master 디비의 dump 파일 / bin파일명, master Position 값이 필요하다.

Tip: mysql  유저 & 그룹확인

$ cat /etc/passwd | grep mysql

mysql:x:900:900::/home/mysql:/usr/bin/sudosh

$ cat /etc/group | grep mysql

mysql:x:900:

tar -zxvf mysql-5.5.8.tar.gz

cd mysql-5.5.8

groupadd mysql

useradd -g mysql mysql

Tip: cmake 가 없는 경우 설치 (ex. yum list installed | grep cmake )

+ 필요한 라이브러리 다 ~ 체크하자!

$ yum install cmake.x86_64

yum -y install zlib curl

yum -y install gcc g++ cpp gcc-c++

yum -y install openssl openssl-devel

yum -y install libtermcap-devel ncurses-devel libc-client-devel bzip2-devel

yum -y install bison

cmake . -DCMAKE_INSTALL_PREFIX=/mysql -DMYSQL_DATADIR=/data -DDEFAULT_CHARSET=utf8 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DWITH_SSL=bundled  -DWITH_FEDERATED_STORAGE_ENGINE=1

-DWITH_INNOBASE_STORAGE_ENGINE : InnoDB 스토리지 엔진을 사용

-DCMAKE_INSTALL_PREFIX : mysql 설치 위치

-DMYSQL_UNIX_ADDR : unix socket 파일 위치

-DDEFAULT_CHARSET : 기본 캐릭터 지정

-DDEFAULT_COLLATION : 캐릭터 셋의 collation을 지정

-DWITH_EXTRA_CHARSETS : 추가로 지원할 언어를 지정 (기본값 all)

-DMYSQL_USER : mysql 서비스 유저

-DMYSQL_DATADIR : DB 파일 위치설정

-DMYSQL_TCP_PORT : 서비스 포트 설정

-DSYSCONFDIR : mysql 설정파일 위치설정

make -j 4 ; make install

Tip: make -j 4 :프로세스 4개사용해서 make 

mkdir /data/ismdata/

mkdir /data/idbdata/

cp /mysql/support-files/my-medium.cnf /etc/my.cnf

Tip:  기존 돌고있는 slave server my.cnf 를 복사함. (data 경로, size 확인하기)

/mysql/scripts/mysql_install_db --user=mysql --basedir=/mysql --datadir=/data/ismdata/

chown -R mysql.mysql /mysql

chown -R mysql.mysql /data/ismdata/

chown -R mysql.mysql /data/idbdata/

! INSTALL 가이드랑 살짝 다르다. (퍼미션부분, 사내 정책 반영)

chmod 640 /etc/my.cnf

chmod 750 /mysql/support-files/mysql.server

chmod 750 /mysql/bin/mysqld_safe

시작 /mysql/bin/mysqld_safe &

종료 /mysql/bin/mysqladmin shutdown

+ 추가추가

루트 비번을 걸자!

$ /mysql/bin/mysql -u root -p

mysql> use mysql;

mysql> update user set password=password('비번') where user='root';

mysql> flush privileges;

---

----------------------------------------------------------------------------------------------

#

# The MySQL database server configuration file.

#

[client]

port            = 3306

socket          = /tmp/mysqld.sock

[mysqld_safe]

socket          = /tmp/mysqld.sock

nice            = 0

# Increase the amount of open files allowed per process. Warning: Make

# sure you have set the global system limit high enough! The high value

# is required for a large number of opened tables

#open-files-limit = 8192

[mysqld]

user            = mysql

socket          = /tmp/mysqld.sock

port            = 3306

basedir         = /mysql

datadir         = /data/ismdata

tmpdir          = /tmp 

slow_query_log  = 1

slow_query_log_file  = /data/ismdata/slow.log

#language       = /usr/share/mysql/korean

character-set-server=utf8 

init_connect='set names utf8'

skip-character-set-client-handshake=1

#skip-external-locking

skip-log-warnings

skip-name-resolve

event_scheduler=1   # 마스터는 1, 슬레이브에서 필요없으면 주석처리하기

#transaction_isolation = READ-COMMITTED

long_query_time         = 1

max_connections         = 512

max_connect_errors      = 1000

wait_timeout            = 86400

connect_timeout         = 5

# fine tuning

max_allowed_packet      = 16M

table_cache             = 256

thread_stack            = 192K

thread_cache_size       = 8

thread_concurrency      = 8

#5.0 higher

join_buffer_size        = 2M

max_heap_table_size     = 64M

tmp_table_size = 64M

ft_min_word_len = 4

key_buffer_size         = 128M

sort_buffer_size        = 2M

read_buffer_size        = 2M

read_rnd_buffer_size = 8M

bulk_insert_buffer_size = 16M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 1G

myisam_repair_threads = 1

myisam_recover=BACKUP

innodb_data_home_dir = /data/idbdata

innodb_log_group_home_dir = /data/idbdata/

innodb_data_file_path = ibdata1:500M;ibdata2:50M:autoextend

innodb_fast_shutdown = 1

innodb_buffer_pool_size = 128M

innodb_additional_mem_pool_size = 2M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 0

innodb_lock_wait_timeout = 10

innodb_thread_concurrency = 4

innodb_file_io_threads = 4

innodb_log_file_size = 128M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

# * Query Cache Configuration

query_cache_limit       = 2M

query_cache_size        = 16M

query_cache_type        = 1

#log                     = /data/ismdata/mysql-query.log

#log-bin                 = /mysql/var/mysql-bin.log

# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!

expire_logs_days        = 15

binlog_cache_size       = 1M

max_binlog_size         = 10M

# 리플이 없는경우 주석처리 (마스터인경우 주석 해제)

#log-bin

#log-slave-update

#binlog-ignore-db=mysql

#binlog-ignore-db=test

#binlog-ignore-db=information_schema

#binlog-ignore-db=performance_schema

log_bin_trust_function_creators=1

server-id=20

[mysqldump]

# Do not buffer the whole result set in memory before writing it to

# file. Required for dumping very large tables

quick

#quote-names

#max_allowed_packet      = 16M

[mysql]

#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]

key_buffer = 256M

sort_buffer_size = 256M

read_buffer = 8M

write_buffer = 8M

[myisamchk]

key_buffer = 256M

sort_buffer_size = 256M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

----------------------------------------------------------------------------------------------

Master 

1. 리플 계정 생성 

mysql> INSERT INTO `user` (`Host`, `User`, `Password`, ~ 생략) VALUES('슬레이브 IP','in_repl','비밀번호', ~ N 아니면 0);

mysql> FLUSH PRIVILEGES;

Tip. bin log 를 Slave DB 계정이 접속해서 가져가는 방식이다.

꼼수로 이런 방법도 있지요~ 링퀴

Slave 

1. Database 생성

mysql> CREATE DATABASE `BB` DEFAULT CHARACTER SET utf8;

2. dump 부어넣기

]# /mysql/bin/mysql -uroot -p BB < BB.sql 

3. 리플 설정

CHANGE MASTER TO

       MASTER_HOST='마스터 IP',

       MASTER_USER='in_repl',

       MASTER_PORT=3306,

       MASTER_PASSWORD='계정비밀번호',

       MASTER_LOG_FILE='db01-bin.000005(덤프시 파일명)',

       MASTER_LOG_POS=2148737(덤프시 마스터 포지션);

4. 슬레이브 START

mysql> slave start;

5. 슬레이브 상태 확인

I/O 스레드 : 마스터와 관련된 트래픽

SQL 스레드 : 슬레이브 데이터 동기화

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State:                                // 정상시 : Waiting for master to send event

Master_Host: 마스터IP

Master_User: in_repl

Master_Port: 3306

Master_Log_File: db01-bin.000007        // 마스터의 이진 로그파일

Read_Master_Log_Pos: 4274059 // 마스터의 포지션, 슬레이브 I/O 에러 발생시 비어있음

Relay_Log_File: db02-relay-bin.000005   // 중계 로그 파일, 슬레이브에 존재, 마스터 이진로그에서 전송된 정보

Relay_Log_Pos: 471705                        // 중계로그파일 현재 위치

Relay_Master_Log_File: db01-bin.000007

Slave_IO_Running: No                         // 정상시 Slave_IO_Running: Yes

Slave_SQL_Running: No                      // 정상시 Slave_SQL_Running: Yes

Last_Errno: 1539

Last_Error: Error 'Unknown event 'event_day'' on query. Default database: 'BB'. Query: 'drop event `BB`.`event_day`'

Skip_Counter: 0

Exec_Master_Log_Pos: 471552 // Slave bin 반영중인 포지션

Seconds_Behind_Master: NULL           // 비정상

 // 슬레이브 최근의 이벤트와 Relay_Log_File 에 기록된 마스터 이진로그의 시간차이를 초단위로 나타냄.

// 정상시 Seconds_Behind_Master: 0
// 마스터와 연결안된경우 : NULL

Last_SQL_Errno: 1539

Last_SQL_Error: Error 'Unknown event 'event_day'' on query. Default database: 'BB'. Query: 'drop event `BB`.`event_day`'

6. 슬레이브 에러 처리

슬레이브에서 사용하지 않는 이벤트임.

mysql> stop slave;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

mysql> start slave;

7. 이벤트 Drop 

시원하게 삭제해주고..

환경에서 원천 봉쇄!!!

mysql> SET GLOBAL event_scheduler = OFF;

mysql> SHOW VARIABLES LIKE '%event_scheduler%';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| event_scheduler | OFF   |

+-----------------+-------+

1 row in set (0.00 sec)

shell> vi /etc/my.cnf

#event_scheduler=1      #주석처리하기

8. 최종 안심하기

Master 에서 DB생성하고 Slave 에서 확인한다. (물론 삭제도 확인)

CREATE DATABASE a1;

DROP DATABASE a1;

9. 재부팅시 디비 자동 실행

$ vi /etc/rc.local

 

#!/bin/sh

#

# This script will be executed *after* all the other init scripts.

# You can put your own initialization stuff in here if you don't

# want to do the full Sys V style init stuff.

 

touch /var/lock/subsys/local

/bin/sh /mysql/bin/mysqld_safe &