MySQL Replication(복제) - 양방향 이중화하기




    지난 포스팅에서는 리플리케이션(Replication)에 대해서 알아보았는데요

    리플리케이션(Replication)에서 Master서버 ->  Slave서버로만 알아보았으며, 이번에는 Slave서버 ->  Master서버도 구현하여 양방향으로 Replication이 가능토록 해보겠습니다





     MySQL Replication 양방향 구성하기




    1. MySQL Replication 단방향 구성



    지난 포스팅에서는 단방향으로 구성하였기때문에 Master 서버에서 Insert/Update/Delete 쿼리를 Slave 서버에서는 Select 쿼리로 진행하였습니다




    2. MySQL Replication 양방향 구성



    이번에는 양방향으로 구성하여 위 그림 처럼 Slave 서버에서도 Insert/Update/Delete 를 사용하여도 Master서버에서는 반영되도록 설정해보도록 하겠습니다









     MySQL Replication 설정하기 - (Slave 서버)


    이번에는 Slave 서버부터 설정하도록 하겠습니다


    양방향 동기화를 하기위해선 Slave 서버에서도 리플리케이션 계정을 생성하여야 되며, mysql 설정파일에서 log-bin을 설정하여야 됩니다




    [Replication 계정 정보]


    IP : 192.168.65.149 - (Slave)


    ID : repli_user2


    PW : test456


    - Master 서버에 데이터를 Slave 서버로 복제하기 위해선 MySQL 계정이 필요합니다

    - MySQL root 계정으로 사용하는것은 보안상 좋지 않기 때문에 복제계정을 생성하는것이 좋습니다




    1. 리플리케이션 계정생성

    mysql> grant replication slave on *.* to 'repli_user2'@'%' identified by 'test456';
    



    2. MySQL 설정 - my.cnf

    vi /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin
    


    Slave서버에서도 log-bin=mysql-bin 추가합니다



    3. MySQL 재시작

    # service mysqld restart
    


    4. Slave 서버 정보 확인

    mysql> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000014 |     4186 |              |                  | 
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    



    File : MySQL 로그파일


    Position : 로그 파일내 읽을 위치


    Binlog_Do_DB : 바이너리(Binary)로그 파일(변경된 이벤트 정보가 쌓이는 파일)


    Binlog_Ignore_DB : 복제 제외 정보





     MySQL Replication 설정하기 - (Master 서버)




    이제 Master 서버를 설정해보도록 하겠습니다



    1. MySQL 설정 - my.cnf

    # vi /etc/my.cnf
     
    [mysqld]
    replicate-do-db='repl_db'
    


    2. MySQL 복원


    1) Master DBMS에서 복제할 데이터베이스를 dump하여 복원합니다




    3. Slave 서버로 연결하기 위한 설정

    mysql> change master to
    master_host='192.168.65.149',
    master_user='repli_user2',
    master_password='test456',
    master_log_file='mysql-bin.000014',
    master_log_pos=4186;
    




    MASTER_HOST : Slave 서버 IP 입력


    MASTER_USER : 리플리케이션 ID


    MASTER_PASSWORD : 리플리케이션 PW


    MASTER_LOG_FILE : Slave STATUS 로그파일명


    MASTER_LOG_POS : Slave STATUS에서 position 값




    4. MySQL 재시작

    # service mysqld restart
    



    리플리케이션(Replication) 양방향을 완료하신후에 Master, Slave 서버에 Slave 상태에서 Slave_IO_Running, Slave_SQL_Running가 YES로 되어 있는지 필히 확인하시기 바랍니다.


    Posted by Server-talk 서버이야기
    • 안녕하세요
      2019.12.18 15:51

      안녕하세요 DB replication 중입니다. Master - Slave 단방향으로는 잘 되는데 서로를 replication하니까 하나만 돌아가고 하나는 안됩니다... 아마 server-id 값의 문제인 것 같은데 이 점은 어떻게 해결하면 좋을까요??ㅜㅜ

      • 방문해주셔서 감사합니다^^
        답변이 늦었네요

        문의해주신 내용만으로는 확인이 힘드네요...

        먼저 선행적으로 아래의 방법으로 진행을 부탁드립니다

        초치-1 - 에러로그 확인
        mysql을 접근하여 show slave status\G; 명령어를 실행후 Last_Error를 확인
        (에러로그가 있다면 댓글로 남겨주시면 됩니다 ^^)

        초치 - 2 슬레이브서버 정보 확인
        mysql을 접근하여 show slave status\G; 명령어를 실행후 Master_Host, Master_User의 정보가 동기화하려고 하는 SLAVE 서버에 정보가 맞는지 확인과 맞지 않는 다면 재적용

        change master to
        master_host='서버IP',
        master_user='아이디',
        master_password='패스워드',
        master_log_file='SLAVE의 MASERT 서버의 STATUS 로그파일명',
        master_log_pos=SLAVE의 MASERT 서버의 position 값;


        초치 - 4 - SLAVE 서버 상태 확인
        mysql을 접근하여 show slave status\G; 명령어를 실행후 Slave_IO_Running, Slave_SQL_Running YES가 되어 있는지 확인


        초치 - 3 - MASTER 서버 SLAVE 중지, 리셋, 시작
        MASTER 서버에 mysql을 접근하여
        SLAVE STOP, SLAVE RESET, SLAVE START 의 명령으를 이용하여 MATER 서버 SLAVE 서버 재시작


        그리고

        MySQL 리플리케이션 양방향을 기술은 데이터가 오적용 되는 사례가 빈번하게 발생하여 적용하실때 장기간의 데이터백업과 지속적인 모니터링 하시기 바랍니다

        감사합니다.

    • bang
      2020.01.03 17:45

      좋은 게시판 올려주셔서 감사합니다.
      그런데 궁금한게 있습니다. mysql 입력창에서 계정생성할때 '@'% 이거 왜 넣는거죠 ? 빼고 그냥 계정만 입력해도 되는건가요 ? mariadb도 동일하게 해도되는가요 ?

      • 방문해주셔서 감사합니다^^

        @, %는 grant 명령어의 문법입니다

        Mysql grant 계정생성 명령어 문법은
        grant 권한 on 데이터베이스.테이블 to ‘ID’ @‘호스트’ identified by ‘비밀번호’
        입니다

        %는 생성한 계정의 접근할수 있는 호스트(접근할수 있는 IP)를 지정하는것이며,
        %를 IP를 지정하지 않는것을 의미합니다

        @는 계정과 호스트의 연결자로 생각하시면 됩니다

        그리고 mariadb 설정은 거의 동일하게 적용하면 되는것으로 알고 있으나,
        mariadb를 직접 운영하고 리플리케이션을 적용한적이 없어서 정확하게 답변 드리기가 어렵네요...

        감사합니다.

    • bang
      2020.01.05 02:09

      mariadb 도 잘됩니다. !!!!! 정말 감사합니다

    • Favicon of https://moon-dev-blog.tistory.com BlogIcon 돌베기
      2020.01.07 14:31 신고

      그림과 함께 설명을 너무 깔끔하게 해주셔서 업무진행하는데 큰도움이 됐습니다. 감사합니다 ㅎㅎ 새해복많이받으세요

    • 만두맨
      2020.02.11 12:12

      우선 도움이 되는 글 올려주셔서 감사합니다.

      단방향 설정 후 양방향을 해야하는 건가요?

      • 방문해 주셔서감사합니다.!

        포스팅대로 진행하시는 것이라면 단방향 진행후 양방향 진행하시면 됩니다

        감사합니다.

    • 치즈
      2020.03.03 18:39

      질문이 있습니다.

      첫 번째
      "1) Master DBMS에서 복제할 데이터베이스를 dump하여 복원합니다" 라는건
      단방향때 slave였던 서버에서 덤프를 떠서
      단방향때 master 였던 서버에 복원 한다는 뜻 인가요??

      두 번 째
      계정생성할 때 단방향해서 했던 계정과 똑같이 만들면 안되나요?

      • 방문해주셔서 감사합니다.!

        1. 첫번째
        Master 서버어 DB를 복원하는 부분입니다


        2. 두번째
        동일하게 생성하셔도 됩니다


        감사합니다.!


    • 2020.03.05 11:32

      좋은 게시물 감사합니다.

      slave 서버가 read 만 할 수 있게 구성하려면 어떻게 해야할까요?

      • 방문해주셔서 감사합니다.!

        slave 서버가 read 만 할수 있게 구성하려면,

        개발적인 부분에서 다소 번거로운 수정이 필요합니다

        SELECT 쿼리의 DB 커넥션 정보를 따로 분리하여 slave로 변경하는 방법을 진행하시는 방법으로 진행하시면 됩니다

        감사합니다.!


    • 2020.04.14 14:35

      비밀댓글입니다


      • 2020.04.21 15:26

        비밀댓글입니다

    • 그루
      2020.04.21 12:56

      안녕하세요
      질문이 있어 글을 남깁니다.

      올리신 글을 보고 따라해서 양방향 이중화를 하였습니다. (OS는 윈도우 대 윈도우 입니다.)
      HeidiSQL에서 양쪽 DB에 데이터를 insert, Update 하면 서로 동기화가 잘 됩니다.
      그런데 HeidiSQL에서 사용하던 동일한 계정으로 응용프로그램에서 Insert, Update 처리를 하면 동기화가 되지 않습니다.

      SHOW SLAVE STATUS;
      SHOW MASTER STATUS; 는 서로 동일한 값으로 세팅되어 있는 걸 확인했습니다.
      Slave_IO_State도 "Waiting for master to send event"로 양쪽 모두 되어 있는 상태입니다.

      혹시 원인을 알 수 있을까요?

      • 방문해주셔서 감사합니다.!

        양쪽 DB에 데이터가 Insert, Update 할경우 서로 동기화가 되고 응용프로그램에서 Insert, Update 하셨는데요

        응용프로그램이 어떤거인지 궁금하네요

        그리고 말씀하신 상황을 본다면 리플리케이션 설정보다는 계정의 권한 부분이 문제일듯 싶습니다

        테스트한계정으로 HeidiSQL에서와 응용프래그램에서 DBMS 계정의 권한이 어떠한 차이가 있는지 확인하셔야 될것으로 보이며,

        윈도우일 경우에는 관련 응용프로그램에서 DBMS의 권한부분이 허용부분이 필요할수 있습니다

        권한부분을 다시한번 확인하시기 바랍니다

        감사합니다.!