1. MySQL Replication
•
•
MySQL Replication의 master/slave는 1:n관계입니다. master는 갱신쿼리를 바이너리 로그파일로 기록하고, 이 로그파일의 내용이 slave로 전송되어 순차적으로 실행함으로써 복제됩니다. 따라서 MySQL Replication은 준동시성입니다. I/O 스레드가 비동기로 동작하기에 마스터에서 생성한 바이너리 로그가 슬레이브에 수신되기 전에 장애가 날 경우 손실이 발생할 수 있습니다.
•
데이터조작쿼리(INSERT, UPDATE, DELETE)는 마스터로, 데이터조회쿼리(SELECT)는 슬레이브로 받아서 부하를 분산할 수 있습니다.
2. 실습
A. 애플리케이션 설정
spring.datasource.hikari.master.username=root
spring.datasource.hikari.master.password=masterpw
spring.datasource.hikari.master.jdbc-url=jdbc:mysql://[Master DB IP:Port]/subway?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&serverTimezone=UTC&allowPublicKeyRetrieval=true
#spring.datasource.hikari.master.jdbc-url=jdbc:mysql://localhost:13306/subway?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&serverTimezone=UTC&allowPublicKeyRetrieval=true
spring.datasource.hikari.slave.username=root
spring.datasource.hikari.slave.password=slavepw
spring.datasource.hikari.slave.jdbc-url=jdbc:mysql://[Slave DB IP:Port]/subway?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&serverTimezone=UTC&allowPublicKeyRetrieval=true
#spring.datasource.hikari.slave.jdbc-url=jdbc:mysql://localhost:13307/subway?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&serverTimezone=UTC&allowPublicKeyRetrieval=true
YAML
복사
public class ReplicationRoutingDataSource extends AbstractRoutingDataSource {
public static final String DATASOURCE_KEY_MASTER = "master";
public static final String DATASOURCE_KEY_SLAVE = "slave";
@Override
protected Object determineCurrentLookupKey() {
boolean isReadOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
return (isReadOnly)
? DATASOURCE_KEY_SLAVE
: DATASOURCE_KEY_MASTER;
}
}
Java
복사
@Configuration
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = {"com.brainbackdoor.subwaymap"})
class DataBaseConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.hikari.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.hikari.slave")
public DataSource slaveDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean
public DataSource routingDataSource(@Qualifier("masterDataSource") DataSource master,
@Qualifier("slaveDataSource") DataSource slave) {
ReplicationRoutingDataSource routingDataSource = new ReplicationRoutingDataSource();
HashMap<Object, Object> sources = new HashMap<>();
sources.put(DATASOURCE_KEY_MASTER, master);
sources.put(DATASOURCE_KEY_SLAVE, slave);
routingDataSource.setTargetDataSources(sources);
routingDataSource.setDefaultTargetDataSource(master);
return routingDataSource;
}
@Primary
@Bean
public DataSource dataSource(@Qualifier("routingDataSource") DataSource routingDataSource) {
return new LazyConnectionDataSourceProxy(routingDataSource);
}
}
Java
복사
public List<Line> findLines() {
...
@Transactional(readOnly = true)
public List<StationResponse> findAllStations() {
Java
복사
•
findLines() 메서드는 master에서, findAllStations() 메서드는 slave에서 조회합니다. @Transactional(readOnly = true)를 사용할 경우 slave를 활용합니다.
2. Docker로 간단히 구성해보기
master 서버 설정
$ docker run --name mysql-master -p 13306:3306 -v ~/mysql/master:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=masterpw -d mysql
$ docker exec -it mysql-master /bin/bash
$ mysql -u root -p
mysql> CREATE USER 'replication_user'@'%' IDENTIFIED WITH mysql_native_password by 'replication_pw';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: binlog.000002
Position: 683
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
Shell
복사
slave 서버 설정
$ docker run --name mysql-slave -p 13307:3306 -v ~/mysql/slave:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=slavepw -d mysql
$ docker exec -it mysql-slave /bin/bash
$ mysql -u root -p
mysql> SET GLOBAL server_id = 2;
mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.1', MASTER_PORT = 13306, MASTER_USER='replication_user', MASTER_PASSWORD='replication_pw', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=683;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Shell
복사