[리눅스 CentOS] MariaDB 이중화 : Master - Slave 설정 방법

 

DB 이중화 Master - Slave 설정

 

 

db 이중화를 위해서는 2개의 db서버가 필요합니다.

2개의 서버에 ip를 잡아주고 각각 Mariadb를 설치하여 준비해 줍니다.

 

1. MASTER 서버 [192.168.0.147]

2. SLAVE 서버 [192.168.0.148]

 

 

 

1. Master

1) 기존 서버 사용

master서버는 기존 사용하던 서버를 사용하겠습니다.

 

2) master 설정

vi /etc/my.cnf.d/mariadb-server.cnf

[mariadb] <- 이거는 추가하는거 아님
log-bin
server_id=1
log-basename=master1
binlog-format=mixed

 

3) master 확인

mysql
show master status;

 

 

4) 사용자 추가 및 권한 설정

CREATE USER 'slave_user'@'%' IDENTIFIED BY 'qwer1234';
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%';

 

 

5) DB백업

mysql dump -u root [DB 이름] > web.sql
scp web.sql [SLAVE 서버의 IP]:/root/web.sql

 

 

 

---

 

 

2. SLAVE

 

1) DB 서버 프로그램 설치

yum install -y mariadb-server

slave로 사용할 서버에 mariadb를 설치해 줍니다.

 

2) DB 서버 프로그램 실행

systemctl start mariadb

 

 

3) 방화벽 끄기

setenforce 0
systemctl stop firewalld

 

 

4) DB생성

mysql

CREATE DATABASE [DB 이름];
exit

 

5) DB복구

mysql -u root [DB 이름] < web.sql

 

 

6) DB 서버 설정

vi /etc/my.cnf.d/mariadb-server.cnf

[mariadb] <- 이거는 추가하는거 아님
server_id=2

systemctl restart mariadb

 

7) Master 지정

Master

 

Slave

mysql

CHANGE MASTER TO
MASTER_HOST='[Master 서버 IP]',
MASTER_USER='slave_user',
MASTER_PASSWORD='qwer1234',
MASTER_PORT=3306,
MASTER_LOG_FILE='[마스터에서 show master status 했을 때 File 이름]',
MASTER_LOG_POS=[마스터에서 show master status 했을 때 position 번호],
MASTER_CONNECT_RETRY=10;

START SLAVE;

 

 

8) Slave 확인

SHOW SLAVE STATUS\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

 

 

 

---

 

3. JAVA 소스코드

 

application.yml

spring:
  jwt:
    secret: 12345678901234567890123456789012
  datasource:
    hikari:
      primary:
        jdbc-url: jdbc:mariadb://192.168.0.147:3306/web
        username: jyj
        password: qwer1234
        driver-class-name: org.mariadb.jdbc.Driver
      secondary:
        jdbc-url: jdbc:mariadb://192.168.0.148:3306/web
        username: jyj
        password: qwer1234
        driver-class-name: org.mariadb.jdbc.Driver
  jpa:
    database-platform: org.hibernate.dialect.MariaDBDialect
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        format_sql: true


logging:
  level:
    org.hibernate.SQL: debug

 

 

DynamicRoutingDataSource.java

package com.example.day13.config;

import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import static org.springframework.transaction.support.TransactionSynchronizationManager.isCurrentTransactionReadOnly;

@Slf4j
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        String dataSourceName = isCurrentTransactionReadOnly() ? "SECONDARY" : "PRIMARY";
        log.info(">>>>>> current data source : {}", dataSourceName);
        return dataSourceName;
    }
}

 

 

DataSourceConfig 파일

package com.example.day13.config;

import com.zaxxer.hikari.HikariDataSource;
import jakarta.persistence.EntityManagerFactory;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;


@Configuration
@Slf4j
public class DataSourceConfig {

    @ConfigurationProperties(prefix = "spring.datasource.hikari.primary")
    @Bean
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @ConfigurationProperties(prefix = "spring.datasource.hikari.secondary")
    @Bean
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @DependsOn({"primaryDataSource", "secondaryDataSource"})
    @Bean
    public DataSource routingDataSource(
            @Qualifier("primaryDataSource") DataSource primary,
            @Qualifier("secondaryDataSource") DataSource secondary) {
        DynamicRoutingDataSource routingDataSource = new DynamicRoutingDataSource();

        Map<Object, Object> dataSourceMap = new HashMap<>();

        dataSourceMap.put("PRIMARY", primary);
        dataSourceMap.put("SECONDARY", secondary);

        routingDataSource.setTargetDataSources(dataSourceMap);
        routingDataSource.setDefaultTargetDataSource(primary);

        return routingDataSource;
    }

    @DependsOn({"routingDataSource"})
    @Primary
    @Bean
    public DataSource dataSource(DataSource routingDataSource) {
        log.info(">>>>>>>>>>>>>>>???");
        return new LazyConnectionDataSourceProxy(routingDataSource);
    }

    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory){
        JpaTransactionManager jpaTransactionManager = new JpaTransactionManager();
        jpaTransactionManager.setEntityManagerFactory(entityManagerFactory);
        return jpaTransactionManager;
    }
}

 

 

 

 

 

---

 

 

 

Test 화면

 

 

Postmen으로 테스트 데이터 입력

 

 

 

동일한 데이터가 양쪽 DB에 들어간걸 확인 가능

 

 

---

 

yum 실행 안될 때 설치

sudo sed -i -e "s|mirrorlist=|#mirrorlist=|g" /etc/yum.repos.d/CentOS-*
sudo sed -i -e "s|#baseurl=http://mirror.centos.org|baseurl=http://vault.centos.org|g" /etc/yum.repos.d/CentOS-*