Condition
•
OS : CentOS7
•
Version : 5.7.37
•
Install Path : /home/mysql
•
Data Path : /home/mysql/data
•
방화벽 설정이 되어 있다면 Master ←→ Slave 허용 필수
•
Master ← Slave Replication은 구성되었다고 가정
1. my.cnf (Master :: Primary)
•
필수로 활성화 해야하는 설정
[mysql@devsvr ~]$ vi my.cnf
# 바이너리 로그 사용시 활성화 (기본값 OFF)
log-bin=mysql-bin
# 서버를 식별할 수 있는 id 지정
server-id=2
Bash
복사
2. my.cnf (Slave :: Secondary)
[mysql@devsvr ~]$ vi my.cnf
# 바이너리 로그 사용시 활성화 (기본값 OFF)
log-bin=mysql-bin
# 서버를 식별할 수 있는 id 지정
server-id=3
Bash
복사
3. MySQL 설정 반영
[mysql@devsvr ~]$ ./mysql.server stop
[mysql@devsvr ~]$ ./mysql.server start
or
# 재기동 없이 반영. 단 재기동 시 my.cnf를 로드하는 점 주의
[Master :: Primary]
mysql> SET GLOBAL log_bin = ON;
mysql> SET GLOBAL server-id = 2;
[Slave :: Secondary]
mysql> SET GLOBAL log_bin = ON;
mysql> SET GLOBAL server-id = 3;
Bash
복사
4. Master → Slave replaction 설정
•
Slave :: Secondary
[mysql@devsvr ~]$ ./dbcon
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE USER 'repluser'@'masters ip' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'masters ip';
mysql> FLUSH PRIVILEGES;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000712 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Bash
복사
•
Master :: Primary
[mysql@devsvr ~]$ ./dbcon
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CHANGE MASTER TO MASTER_HOST='Slave ip', MASTER_PORT=3310, MASTER_USER='repluser',
MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000712', MASTER_LOG_POS=154;
mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: Slave ip
Master_User: repluser
Master_Port: 3310
Connect_Retry: 60
Master_Log_File: mysql-bin.000712
Read_Master_Log_Pos: 154
Relay_Log_File: db01-relay-bin.000797
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000712
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: 154
Relay_Log_Space: 786
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_UUID: 3de9e52b-8145-11ec-8206-0800278930f0
Master_Info_File: /home/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Bash
복사
•
여기까지 작업을 완료하였다면, Master도 slave start, Slave도 slave start가 된 상태.
5. 각 APP들의 JDBC 설정 :: Primary(Master), Secondary(Slave)
•
failOverReadOnly=false :: Primary 서버가 다운되었을 때 Secondary 쪽에
data 추가/수정/삭제를 할 수 있도록 하는 옵션. 기본 값은 true
•
secondsBeforeRetryMaster=10 :: 해당 옵션을 사용함으로써, Secondary DB에 데이터를
입력하기 전, Primary DB에 입력 시도하도록 설정.
이거 설정 안하면 Master가 죽었다가 살아난 상황에서도 APP을 재기동하기 전에는
여전히 Secondary에 데이터를 입력하기 때문에 반드시 설정해야함.
vi jdbc.properties
..............
master_jdbc.url=jdbc:mysql://master ip:3310,slave ip:3310/dbname?autoReconnect=true
&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true
&useSSL=false&failOverReadOnly=false&secondsBeforeRetryMaster=10
..............
Bash
복사