티스토리 뷰

카테고리 없음

6. [MySQL]my.conf 환경 설정하기

미니대왕님 2020. 4. 16. 00:07

1. [MySQL] 설치(CentOS)

2. [MySQL] 기본 명령어 익히기

3. [MySQL] 외부에서 접근하는방법

4. [MySQL]워크벤치 설치하기

5. [MySQL]리플리케이션 방법

6. [MySQL]my.conf 환경 설정하기

7. [MySQL] MySQL 리플리케이션 UUID 관련 에러

8. [MySQL] mysql 데이터샘플 밀어 넣기

9. [MySQL]Workbench & DA# 모델링 그리기

MySQL Master/Slave 환경구축 (기본편)

업무상 MySQL을 사용하여 Master/Slave 를 조사하게 되었다.
하지만 단순히 Master/Slave 를 구축하는게 아니라 JPA를 사용하여 Master/Slave를 구축하는게 최종 미션이다.

먼저 MySQL을 사용한 Master/Slave에 대해서 알아본다.
당연한 말이겠지만 Master와 Slave를 구성할 mysqld 가 최소 2개 이상 필요하다.

하지만 mysqld_multi 라는 유용한 툴이 있는데 이것을 이용하면 서버가 1대라도 포트를 나누어서 2개의 mysqld를 설정할 수 있다.

그럼 먼저 my.cnf 부터 알아보자..

[mysqld_multi]

mysqld = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

user = root

password = password

[mysql]

prompt = '\u@\h mysql>'

host = localhost.localdomain

[mysqld]

default-character-set = utf8

[mysqld1]

server-id = 1

port = 3306

datadir = /var/lib/mysql

socket = /var/lib/mysql/mysql.sock

pid-file = /var/run/mysqld/mysqld.pid

[mysqld2]

server-id = 2

port = 3307

datadir = /var/lib/mysql2

socket = /var/lib/mysql2/mysql.sock

pid-file = /var/lib/mysql2/mysqld.pid

위에서 중요한 설정은 mysqld1 과 mysqld2 인데, mysqld1 이 Master 이고 mysqld2 가 Slave 로 이용할 예정이다.

당연히 datadir 이나 socket 그리고 pid-file 등에서 설정한 디렉토리가 없다면 생성을 해준다.
퍼미션도 mysql 로 설정하는것을 잊지말도록한다.

다음으로는 새로 생성한 mysqld 의 datadir 에 초기데이터를 넣어준다.

mysql_install_db –datadir=/var/lib/mysql2 –user=mysql

설정은 여기까지 이다..
이렇게 하면 mysqld 인스턴스를 2개 3306포트와 3307포트에 리스닝할 수 있다.
(물론 아직 Master/Slave 설정은 안했지만…)

mysqld 시작하기

mysqld_multi start 1-2

mysqld 정지

mysqld_multi stop 1-2

상태를 보고 싶을때는 아래와 같다.

[root@localhost mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

자.. 그럼 Master/Slave 설정을 해보자.

우선 my.cnf 를 아래와 같이 몇개 설정을 추가한다.

[mysqld_multi]

mysqld = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

user = root

password = password

[mysql]

prompt = '\u@\h mysql>'

host = localhost.localdomain

[mysqld]

default-character-set = utf8

[mysqld1]

server-id = 1

port = 3306

datadir = /var/lib/mysql

socket = /var/lib/mysql/mysql.sock

pid-file = /var/run/mysqld/mysqld.pid

sync_binlog=1 # バイナリログの同期間隔らしい。

log-bin=mysql-bin

innodb_flush_log_at_trx_commit=1

log-bin-index=mysql-bin

relay-log=mysqld-relay-bin

relay-log-index=relay-bin

[mysqld2]

server-id = 2

port = 3307

datadir = /var/lib/mysql2

socket = /var/lib/mysql2/mysql.sock

pid-file = /var/lib/mysql2/mysqld.pid

read_only # 読み込み専用 (rootユーザ除外)

log-bin=mysql-bin # バイナリログを有効にする。

log-slave-updates # スレーブの SQL スレッドで実行された更新を、スレーブのバイナリログに記録する

log-bin-index=mysql-bin

relay-log=mysqld-relay-bin

relay-log-index=relay-bin

master-host=localhost

master-port=3306

master-user=user

master-password=password

위와 같이 mysqld1 과 mysqld2 에 몇개의 설정을 추가한다.

다음으로 현재 사용중인 Master 에서 필요한 작업을 실시한다.

1. Replication용 유저생성

root유져로는 read-only 가 통하지 않으므로 전용 유져를 만든다.

CREATE USER user@localhost IDENTIFIED BY ‘password';

다음으로 생성한 유져에 권한을 할당한다.

GRANT REPLICATION SLAVE ON *.* TO user@localhost IDENTIFIED BY ‘password';

2. 데이터 동기화

Slave에는 현재 Master와 동기되지 않았으므로 덤프를 하던 어떻게 하던간에 필요한 데이터파일들을 전부 복사해올 필요가있다.
일단, 더이상 Master노드에 변경이 일어나지 않도록 Lock을 건다.

mysql> FLUSH TABLES WITH READ LOCK;

다음으로 현재 Master 바이너리로그의 상태를 확인한다.

mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000003 | 110 | | |
+——————+———-+————–+——————+
1 row in set (0.10 sec)

File과 Position을 메모해둔다. Slave에서 설정이 필요하다.
Master노드의 mysql을 빠져나간후에 좀 무식한 방법이지만 데이터파일을 통째로 복사한다.

cp -aR /var/lib/mysql/* /var/lib/mysql2/

이제 Master와 Slave의 데이터는 완전히 같은 상태일것이다.
Master노드의 Lock을 해제한다.

mysql> UNLOCK TABLES;

여기까지 했으면 mysqld_multi 를 정지후에 다시 시작하여 설정을 반영한다.

이제 Slave 노드에 접속해본다.

mysql -u root -P 3307 -p

-h 옵션으로 OS의 hostname 에 나오는 값을 정확히 입력하지 않으면 Slave에 접속되지 않는다. (에러는 발생안하고 Master 노드에 접속이 됨)
나의 경우는 그런 경우를 방지하고자 my.cnf 에 host 항목을 설정했다.
혹시 Slave 노드에 접속이 안되는 경우는 host 항목을 꼭 확인한다.

mysql> CHANGE MASTER TO
MASTER_HOST=’localhost’,
MASTER_USER=’user’,
MASTER_PASSWORD=’password’,
MASTER_LOG_FILE=’mysql-bin.000003′,
MASTER_LOG_POS=110;

이걸 실행하면 Master의 datadir 에 master.info 파일이 생성될 것이다.

참고로 master.info 파일은 SLAVE 가 가져야 하는 파일이다.
절대 Master쪽 디렉토리에 넣지 않도록 한다.
만약 Master에 이 파일이 있다면 아래와 같이 Master쪽 로그에 출력이 될것이다.

121203 23:39:23 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error_code: 1593

여기까지 문제 없으면 Slave 를 시작한다.

mysql> start slave;

Slave의 상태를 확인할때는 아래와 같이 한다.
Master에서는 아래 명령어를 실행해도 아무것도 안나타날것이므로 Slave에서 실행하여 확인한다.
그런데 show master status 는 Slave에서 실행해도 나타난다. -.-

mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 378
Relay_Log_File: mysqld-relay-bin.000017
Relay_Log_Pos: 515
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 378
Relay_Log_Space: 515
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.01 sec)

중요한건
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
와 같이 2개 항목이 Yes로 표시되면 괜찮은것이다.

이게 뭐냐면 아래의 명령어를 실행해본다.

mysql>show processlist;
+—-+————-+—————–+——+———+——+———————————————————————–+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+—————–+——+———+——+———————————————————————–+——————+
| 1 | system user | | NULL | Connect | 2094 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 1863 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 6 | root | localhost:60835 | NULL | Query | 0 | NULL | show processlist |
+—-+————-+—————–+——+———+——+———————————————————————–+——————+
3 rows in set (0.00 sec)

Id가 1번과 2번을 확인하면알 수 있듯이 Master노드와 접속을 하고 있는 스레드이다.

이것으로 모든 설정이 완료되었다.

Master노드에서 데이터베이스를 생성해보고 그 결과가 mysqld2 에서 설정한 datadir 에 반영이 된다면 제대로 Replication 이 되는것이다.
단, 주의점은 Slave노드에서 변경(DDL 또는 DML중의 update/insert/delete)을 한다면 Master노드에 반영이 되지 않는다.
이는 MySQL Cluster와는 달리 Master/Slave구성은 Master에서 Slave의 단일방향 동기화만 지원하기 때문이다.

따라서 Slave에서 잘못된 조작을 할 경우 데이터가 어긋나는 경우가 있으니 Slave는 반드시 read_only 로써만 이용한다.

또 한가지 팁을 주자면 Slave노드가 정지되어 있다가 한참 지난후에 시작해도 시작후에 데이터가 동기화 된다.
내부적으로는 바이너리 로그를 전송하여 반영하는듯하다.

Slave가 꼭 read_only 인것을 확인하자.
Slave에서 Insert 를 해보고 아래와 같은 에러가 발생한다면 OK.

user@localhost.localdomain mysql>insert into test values (7);
ERROR 1290 (HY000): The MySQL server is running with the –read-only option so it cannot execute this statement