1. 필요
- 서비스 안정성(고가용성)을 위해 DB 복제 필요.
2. 가정
- CentOS, Mysql 기반임.
- Master DB Server Private IP는 192.168.0.1임
- Slave DB Server Private IP는 192.168.0.2임
3. 방법
ㅁ Master DB Server 설정
[my.cnf 설정]
- [root@Master /]# vi /etc/my.cnf
log-bin = mysql-bin
server-id = 11
binlog_do_db = 복제할 DB명
binlog_do_db = 추가로 복제할 DB명
#복제할 DB명가 더 있다면 binlog_do_db = 복제할 DB명만 추가"
- [root@Master /]# service mysqld restart
- [root@Master /]# mysql -u root -p
use mysql;
grant replication slave, REPLICATION CLIENT on *.* TO 'myrepl'@'192.168.0.%' identified by '사용할 password 입력';
flush privileges;
start master;
show master status;
ㅁ Slave DB Server 설정
[my.cnf 설정]
- [root@Slave /]# vi /etc/my.cnf
log-bin = mysql-bin
server-id = 12
binlog_do_db = 복제할 DB명
binlog_do_db = 추가로 복제할 DB명
#복제할 DB명가 더 있다면 binlog_do_db = 복제할 DB명만 추가"
- [root@Slave /]# service mysqld restart
- [root@Slave /]# mysql -u root -p
use mysql;
grant replication slave, REPLICATION CLIENT on *.* TO 'myrepl'@'192.168.0.%' identified by '사용할 password 입력';
flush privileges;
change master to
master_host='192.168.0.1',
master_user='myrepl',
master_password='사용할 password 입력',
master_log_file='mysql-bin.000001',
master_log_pos=1;
start slave;
show slave status \G
4. 문제 발생시
STOP SLAVE;
set global sql_slave_skip_counter = 1;
START SLAVE;
show slave status \G
끝.
