JAVA MySQL Connector replication 을 통해 쓰기/읽기 쿼리 분산

얼마 전 MySQL 쓰기/읽기 DB가 분산 구성된 경우 커넥션 엔드포인트를 별도로 구성하는 방법 외에 사용할 수 있는 방법이 있는지 궁금증이 생겨 찾아보던 중,
MySQL Connector 에서 제공하는 기능인 Replication 커넥션을 통한 쓰기/읽기 쿼리 분산 방법이 있어 공유하려고 합니다.

1. 간단 요약

먼저 MySQL Connector Replication 커넥션을 통해 쓰기/읽기 쿼리를 분산하는 방법은 conn.setReadOnly(); 메소드를 통해 쿼리를 마스터/슬레이브 중 어떤 DB로 보낼지 설정하는 것이기 때문에 소스 상의 수정이 필요하며, 쿼리의 성격에 따라 일일이 지정해줘야하는 점에서 쓰기/읽기 커넥션 엔드포인트를 별도로 지정하는 것과 크게 다르진 않습니다. 다만 다른 점이 있다면 Replication 커넥션의 경우 쓰기/읽기 DB와 동시에 커넥션을 맺은 뒤, conn.setReadOnly(); 메소드의 값을 통해 쿼리를 보낼 DB를 결정하는 것 정도입니다.

한줄 요약 -> 소스 수정 필요, 자동 쿼리 라우팅 불가

2. 설치

MySQL Connector Replication 커넥션은 기존 MySQL Connector for JAVA 라이브러리에 포함되어 있어 별도 추가 설치 없이 사용 가능합니다.

https://dev.mysql.com/downloads/connector/j/

3. MySQL Connector Replication 커넥션 적용 전

먼저 쓰기/읽기 DB가 분산되어 있지 않은 경우 1개의 DB 엔드포인트로 커넥션이 설정된 소스가 있습니다. 여기서 커넥션을 맺을 DB 엔드포인트에 대한 내용은 String jdbcDriver = "jdbc:mysql://mysql.wp:3306/premisan_test?useUnicode=true&characterEncoding=utf8" 라인에 설정되어 있습니다.

... (중략)
    <tbody>
    <%
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try{
            String jdbcDriver = "jdbc:mysql://mysql.wp:3306/premisan_test?useUnicode=true&characterEncoding=utf8";
            String dbUser = "premisan";
            String dbPwd = "my-password";

            conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPwd);

            pstmt = conn.prepareStatement("SELECT * FROM Test order by pk DESC limit 10;");

            rs = pstmt.executeQuery();

            while(rs.next()){
    %>
        <tr>
            <td><%= rs.getString("pk") %></td>
            <td><%= rs.getString("col01") %></td>
            <td><%= rs.getString("regDate") %></td>
        </tr>
    <%
            }
        }catch(SQLException se){
            se.printStackTrace();
        }finally{
            if(rs != null) rs.close();
            if(pstmt != null) pstmt.close();
            if(conn != null) conn.close();
        }
    %>
    </tbody>
... (중략)

원본 소스를 통해 웹 접속 시 아래와 같이 select 결과를 보여주며,

DB 제너럴 로그 확인 시 아래와 같이 로그가 확인됩니다.

4. MySQL Connector Replication 커넥션 적용 후

MySQL Connector Replication 커넥션에 대한 사용법은 아래 주소에서 가이드를 제공하고 있습니다.

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-source-replica-replication-connection.html

아래와 같이 기존 "jdbc:mysql://"으로 시작하는 커넥션 엔드포인트 설정을 "jdbc:mysql:replication://"으로 수정한 뒤, 연결할 소스(마스터) DB 및 리플리카(슬레이브) DB 다수에 대한 엔드포인트를 병렬로 입력합니다.

jdbc:mysql:replication://[source host][:port],[replica host 1][:port][,[replica host 2][:port]]...[/[database]] »
[?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

위 사용법에 따라 아래와 같이 소스를 수정하였습니다. 제 테스트 환경에서 마스터 DB의 엔드포인트는 mysql.wp이며, 슬레이브 DB의 엔드포인트는 mysql_rep.wp 입니다.

# 기존
String jdbcDriver = "jdbc:mysql://mysql.wp:3306/premisan_test?useUnicode=true&characterEncoding=utf8";

# 수정
String jdbcDriver = "jdbc:mysql:replication://mysql.wp:3306,mysql_rep.wp:3306/premisan_test?useUnicode=true&characterEncoding=utf8";

쓰기/읽기 DB 중 쿼리를 어느 DB로 날릴지는 conn.setReadOnly(); 메소드에 따라 결정되기 때문에 커넥션 연결 구문 밑에 해당 메소드 구문을 추가합니다.

# 기존
conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPwd);

# 마스터 DB로 쿼리를 보내는 경우(readonly -> false)
conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPwd);
conn.setReadOnly(false);

# 슬레이브 DB로 쿼리를 보내는 경우(readonly -> true)
conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPwd);
conn.setReadOnly(true);

먼저 conn.setReadOnly(false); 값으로 설정한 뒤, 웹페이지에 접속하여 마스터 DB로 쿼리하는지 확인합니다.

아래 이미지에서 위가 마스터 DB, 아래가 슬레이브 DB입니다. 마스터 DB로 SELECT 쿼리가 발생하는 것을 확인할 수 있습니다. MySQL Connector Replication 커넥션은 jdbc 구문에 입력한 모든 DB에 동시에 커넥션을 맺기 때문에 슬레이브 DB에는 쿼리가 실행되진 않았지만 커넥션은 발생한 것을 확인할 수 있습니다.

이제 반대로 conn.setReadOnly(true); 값으로 설정한 뒤, 웹페이지에 접속하여 슬레이브 DB로 쿼리하는지 확인합니다.

마찬가지로 위가 마스터 DB, 아래가 슬레이브 DB입니다. 슬레이브 DB로 SELECT 쿼리가 발생하는 것을 확인할 수 있습니다.

5. 결론

결론적으로 MySQL Connector Replication 커넥션을 이용하여 쿼리를 쓰기/읽기 DB로 분산하여 소스 구성이 가능합니다.

쿼리마다 소스의 수정(conn.setReadOnly() 값 지정)을 통해 어떤 DB로 쿼리할지 지정해줘야 하는 점(자동 쿼리 라우팅 불가), 커넥션을 맺을 때마다 모든 DB에 커넥션이 발생하는 점(쿼리를 날리지 않을 DB에도 커넥션 발생하여 커넥션 낭비), DB 엔드포인트 추가 시 소스 수정이 필요한 점(jdbc:mysql:replication:// 라인) 등의 단점이 있으나, 별도의 추가 드라이버 설치가 필요 없고, 별도의 DB proxy 없이 소스 수정만으로 설정이 가능하다는 점은 장점인 것 같습니다.