MySQL 고급

마스터,마스터 복제와 MMM

본 수업은 Mysql에 대한 지식과 리플리케이션 그리고 가상(사설) IP에 대한 지식을 필요로 합니다

Multi-Master Replication for Mysql 홈페이지

서버 구성

  ip 호스트 서버 ID
모니터링 서버 192.168.0.10 mon -
master 1 192.168.0.11 db1 1
master 2 192.168.0.12 db2 2
slave 1 192.168.0.13 db3 3
slave 2 192.168.0.14 db4 4

가상 IP의 구성

ip 역활(role) 설명
192.168.0.100 쓰기 (writer) 쓰기 쿼리 (update, insert, delete등)가 접근할 IP
192.168.0.101 읽기 (reader) 읽기 쿼리 (select)가 접근할 IP
192.168.0.102 읽기 (reader)
192.168.0.103 읽기 (reader)
192.168.0.104 읽기 (reader)

MYSQL 설정

모든 호스트의 my.cnf 파일을 아래와 같이 수정한다. (/etc/my.cnf, /etc/mysql/my.cnf에 주로 위치한다)

다음 설정 중 server_id 값은 서버마다 유일한 식별값이어야 한다. 중복되면 안된다.

server-id           = 1
log_bin             = /var/log/mysql/mysql-bin.log 
log_bin_index       = /var/log/mysql/mysql-bin.log.index 
relay_log           = /var/log/mysql/mysql-relay-bin 
relay_log_index     = /var/log/mysql/mysql-relay-bin.index 
expire_logs_days    = 10 
max_binlog_size     = 100M 
log_slave_updates   = 1

외부에서 mysql 서버에 접근할 수 있게 접근 제한을 해제한다.

bind-address = 0.0.0.0

auto_increment_increment는 ID값을 auto increment 할 때 증가되는 숫자의 크기를 지정한다. 예를들어 이 값이 2면 2,4,6과 같은 식으로 값이 증가한다.

auto_increment_increment = 2

auto_increment_offset는 auto increment의 최초시작 값을 지정한다. 두대의 마스터가 있다면 하나는 1, 다른 하나는 2를 지정한다. 그래야 상호 리플리케이션 과정에서 고유값이 충돌하지 않는다.

auto_increment_offset = 1

서버를 재시작한다.

/etc/init.d/mysql restart

사용자 생성

각 서버에 3가지 타입의 사용자를 생성해야 한다.

구분 설명 요구권한
모니터링 mmm monitor가 중앙에서 각 서버의 상태를 체크하기 위해서 필요한 계정 replication client
에이전트 유저 서버를 mmm을 통해서 제어하거나, 서버에 문제가 생겼을 때 제어를 수행하는 에이전트의 계정 super, replication client, process
복제 유저 복제를 위한 계정 replication slave

아래의 명령을 mysql 콘솔에 실행한다.

GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.0.%' IDENTIFIED BY 'monitor_password';
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.0.%'   IDENTIFIED BY 'agent_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED BY 'replication_password';
flush privileges;

동기화 작업

db1에 기본 데이터가 있다고 가정하고, db1의 데이터를 dump해서 이를 각 서버에 복원한다.

아래의 명령을 db1의 mysql 콘솔에 실행한다 .다음 명령어는 mysqldump를 실행하기 전에 각 테이블을 읽기전용으로 변경한다. 

(db1) mysql > FLUSH TABLES WITH READ LOCK;

다음 명령을 실행해서 db1의 상태를 확인한다. db1의 파일명(File)과 바이너리 로그 포지션(Position)을 기록해둔다. 나머지 서버들은 dump된 데이터를 기반으로 할 것이기 때문에 db1의 바이너리 로그 상의 포지션을 정확하게 지정해야 동기화에서 오류가 발생하지 않는다.

(db1) mysql> show master status\G

*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 1864
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

mysql 클라이언트를 닫으면 lock가 해제되므로 닫으면 안된다. 쉘을 새로 실행해서 그곳에서 다음 작업을 실행한다.

db1$ mysqldump -u root -p --all-databases > /tmp/database-backup.sql

FLUSH ... LOCK 를 실행한 쉘에서 다음 명령을 실행한다.

(db1) mysql> UNLOCK TABLES;

덤프한 파일을 각 서버로 전송한다.

db1$ scp /tmp/database-backup.sql <user>@192.168.0.12:/tmp
db1$ scp /tmp/database-backup.sql <user>@192.168.0.13:/tmp
db1$ scp /tmp/database-backup.sql <user>@192.168.0.14:/tmp

각 서버에서 백업한 데이터를 복원한다.

db2$ mysql -u root -p < /tmp/database-backup.sql
db3$ mysql -u root -p < /tmp/database-backup.sql
db4$ mysql -u root -p < /tmp/database-backup.sql

db1의 권한을 다른 테이블에도 적용하기 위해서 아래 명령을 실행한다.

(db2) mysql> FLUSH PRIVILEGES;
(db3) mysql> FLUSH PRIVILEGES;
(db4) mysql> FLUSH PRIVILEGES;

debian이나 ubuntu의 경우, msyql를 시작하고 중지하기 위해서 db1의 /etc/mysql/debian.cnf 파일을 db2,3,4에 복사한다.

원래 이 작업은 mysql을 start, stop 할 때 mysql 사용자 계정 중 debian-sys-maint 을 호출해서 DB를 제어하기 위한 것이라고 하는데, 막상 이 값이 같으나 다르나 실제로는 잘 작동하는 것으로 보인다. 원인을 아시는 분은 알려주시면 후사 하겠습니다. ㅎㅎ

이렇게 해서 db1과 db2,3,4의 데이터를 동기화 했다.

복제 설정

db2,3,4의 복제를 설정한다. 아래 명령을 이용해서 db1를 제외한 서버들에게 마스터가 누구인가를 알려준다. 또 db1에서 추출한 dump 이후에 갱신된 데이터에 대한 좌표(파일과 포지션)를 알려줌으로서 dump 데이터 이후부터 슬레이브 구동까지 누락될 데이터가 생기는 것을 방지한다.

각 슬레이브에 아래와 같이 명령해 슬레이브(패시브 마스터 포함)가 마스터 액티브를 바라보게 한다. 여기서 <file>, <position>은 db1에서 내린 show master status의 결과 값을 사용해야 한다.

(db2) mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', 
              master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;
(db3) mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', 
              master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;
(db4) mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', 
              master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;

슬레이브를 구동한다.

(db2) mysql> START SLAVE;
(db3) mysql> START SLAVE;
(db4) mysql> START SLAVE;

각각의 슬레이브에서 show slave status를 실행하고, 슬레이브가 잘 작동하는지 확인한다.  Slave_IO_Running, Slave_SQL_Running의 값이 모두 Yes인지 확인한다. 아니라면 문제가 있는 것이다.

db2) mysql> SHOW SLAVE STATUS\G
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: 192.168.0.11
                Master_User: replication
                Master_Port: 3306 
              Connect_Retry: 60 
…
(db3) mysql> SHOW SLAVE STATUS\G
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: 192.168.0.11
                Master_User: replication
                Master_Port: 3306 
              Connect_Retry: 60 
…
(db4) mysql> SHOW SLAVE STATUS\G
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: 192.168.0.11
                Master_User: replication
                Master_Port: 3306 
              Connect_Retry: 60 
…

db1을 db2의 슬레이브로 설정해서 마스터-마스터 복제구성을 완료해야 한다. 

(db2) mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000001 |       98 |              |                  |
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec)

db1의 마스터로 db2를 바라보게하고, db2와 동기화 시점을 설정한다.

db1) mysql> CHANGE MASTER TO master_host = '192.168.0.12', master_port=3306, master_user='replication',
              master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;

슬레이브 시작

(db1) mysql> START SLAVE;

db1의 슬레이브가 동작하고 있는지를 확인한다.

(db1) mysql> SHOW SLAVE STATUS\G
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: 192.168.0.12
                Master_User: <replication>
                Master_Port: 3306 
              Connect_Retry: 60

MMM 설치

유저 생성(옵션)

useradd --comment "MMM Script owner" --shell /sbin/nologin mmmd

다운로드

http://mysql-mmm.org/downloads

모니터링 호스트

의존성이 있는 모듈을 먼저 설치한다.

mon$ aptitude install liblog-log4perl-perl libmailtools-perl liblog-dispatch-perl libclass-singleton-perl libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl

다운로드한 파일을 설치한다.

mon$ dpkg -i mysql-mmm-common_*.deb mysql-mmm-monitor*.deb

데이터베이스 호스트

ubuntu 설치

db1,2,3,4$ aptitude install liblog-log4perl-perl libmailtools-perl liblog-dispatch-perl iproute libnet-arp-perl libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl
db1,2,3,4$ dpkg -i mysql-mmm-common_*.deb mysql-mmm-agent_*.deb

Redhat 설치

db1,2,3,4$ yum install -y mysql-mmm-agent
db1,2,3,4$ mysql-mmm-agent.noarch 0:2.2.1-1.el5

설치된 의존성 모듈 목록

libart_lgpl.x86_64 0:2.3.17-4                                                 
mysql-mmm.noarch 0:2.2.1-1.el5                                                
perl-Algorithm-Diff.noarch 0:1.1902-2.el5                                     
perl-DBD-mysql.x86_64 0:4.008-1.rf                                            
perl-DateManip.noarch 0:5.44-1.2.1                                            
perl-IPC-Shareable.noarch 0:0.60-3.el5                                        
perl-Log-Dispatch.noarch 0:2.20-1.el5                                         
perl-Log-Dispatch-FileRotate.noarch 0:1.16-1.el5                              
perl-Log-Log4perl.noarch 0:1.13-2.el5                                         
perl-MIME-Lite.noarch 0:3.01-5.el5                                            
perl-Mail-Sender.noarch 0:0.8.13-2.el5.1                                      
perl-Mail-Sendmail.noarch 0:0.79-9.el5.1                                      
perl-MailTools.noarch 0:1.77-1.el5                                            
perl-Net-ARP.x86_64 0:1.0.6-2.1.el5                                           
perl-Params-Validate.x86_64 0:0.88-3.el5                                      
perl-Proc-Daemon.noarch 0:0.03-1.el5                                          
perl-TimeDate.noarch 1:1.16-5.el5                                             
perl-XML-DOM.noarch 0:1.44-2.el5                                              
perl-XML-Parser.x86_64 0:2.34-6.1.2.2.1                                       
perl-XML-RegExp.noarch 0:0.03-2.el5                                           
rrdtool.x86_64 0:1.2.27-3.el5                                                 
rrdtool-perl.x86_64 0:1.2.27-3.el5

MMM 설정

아래의 설정 파일을 모든 호스트(모니터링 호스트 포함)의 /etc/mysql-mmm/mmm_common.conf 파일에 적용한다.

active_master_role          writer


<host default>
    cluster_interface       eth0

    pid_path                /var/run/mmmd_agent.pid
    bin_path                /usr/lib/mysql-mmm/

    replication_user        replication
    replication_password    replication_password

    agent_user              mmm_agent
    agent_password          agent_password
</host>

<host db1>
    ip                      192.168.0.11
    mode                    master
    peer                    db2
</host>

<host db2>
    ip                      192.168.0.12
    mode                    master
    peer                    db1
</host>

<host db3>
    ip                      192.168.0.13
    mode                    slave
</host>

<host db4>
    ip                      192.168.0.14
    mode                    slave
</host>


<role writer>
    hosts                   db1, db2
    ips                     192.168.0.100
    mode                    exclusive
</role>

<role reader>
    hosts                   db1, db2, db3, db4
    ips                     192.168.0.101, 192.168.0.102, 192.168.0.103, 192.168.0.104
    mode                    balanced
</role>

모니터링 호스트를 제외한 데이터베이스 호스트의 /etc/mysql-mmm/mmm_agent.conf 파일을 수정한다. 'db1'은 각 서버의 서버명에 맞게 변경해야 한다.

include mmm_common.conf
this db1

모니터링 호스트에 /etc/mysql-mmm/mmm_mon.conf 파일을 아래와 같이 수정한다. ping_ips 중 192.168.0.1은 네트워크 스위치를 의미한다.

include mmm_common.conf

<monitor>
    ip                      127.0.0.1
    pid_path                /var/run/mmmd_mon.pid
    bin_path                /usr/lib/mysql-mmm/
    status_path             /var/lib/misc/mmmd_mon.status
    ping_ips                192.168.0.1, 192.168.0.11, 192.168.0.12, 192.168.0.13, 192.168.0.14
</monitor>

<host default>
    monitor_user            mmm_monitor
    monitor_password        monitor_password
</host>

debug 0

MMM 구동하기

Debian/ubuntu

데이터베이스 호스트의 /etc/default/mysql-mmm-agent 파일을 아래와 같이 수정한다.

ENABLED = 1

Red Hat

자동실행을 위해서 아래와 같이 해준다.

chkconfig mysql-mmm-agent on

시동한다.

/etc/init.d/mysql-mmm-agent start

모니터링 시작

모니터링 호스트의 /etc/default/mysql-mmm-monitor 파일을 아래와 같이 수정한다.

ENABLED=1

그리고 시작한다.

/etc/init.d/mysql-mmm-monitor start

참시 기다렸다가 아래와 같이 실행하면 유사하게 출력되야 정상이다.

mon$ mmm_control show
  db1(192.168.0.11) master/AWAITING_RECOVERY. Roles: 
  db2(192.168.0.12) master/AWAITING_RECOVERY. Roles: 
  db3(192.168.0.13) slave/AWAITING_RECOVERY. Roles: 
  db4(192.168.0.14) slave/AWAITING_RECOVERY. Roles:

그런데 각 호스트의 상태가 AWATING_RECOVERY 이다. 명시적으로 호스트를 모니터에 등록해줘야 한다. 아래는 문제가 발생했을 때 자세한 정보를 얻기 위한 로깅 장면이다.

mon$ tail /var/log/mysql-mmm/mmm_mon.warn
…
2009/10/28 23:15:28  WARN Detected new host 'db1': Setting its initial state to 'AWAITING_RECOVERY'. Use 'mmm_control set_online db1' to switch it online.
2009/10/28 23:15:28  WARN Detected new host 'db2': Setting its initial state to 'AWAITING_RECOVERY'. Use 'mmm_control set_online db2' to switch it online.
2009/10/28 23:15:28  WARN Detected new host 'db3': Setting its initial state to 'AWAITING_RECOVERY'. Use 'mmm_control set_online db3' to switch it online.
2009/10/28 23:15:28  WARN Detected new host 'db4': Setting its initial state to 'AWAITING_RECOVERY'. Use 'mmm_control set_online db4' to switch it online.

아래와 같이 하면 모니터링에 명시적으로 각호스트가 등록되고 이제부터 각각의 호스트를 제어할 수 있다.

mon$ mmm_control set_online db1
OK: State of 'db1' changed to ONLINE. Now you can wait some time and check its new roles!
mon$ mmm_control set_online db2
OK: State of 'db2' changed to ONLINE. Now you can wait some time and check its new roles!
mon$ mmm_control set_online db3
OK: State of 'db3' changed to ONLINE. Now you can wait some time and check its new roles!
mon$ mmm_control set_online db4
OK: State of 'db4' changed to ONLINE. Now you can wait some time and check its new roles!

원문 : http://mysql-mmm.org/mmm2:guide

댓글

댓글 본문
  1. wasdq123
    vip 설정은 어떻게 해야 하나요?
    답글수정삭제
  2. 라키
    좋은 수업, 좋은 목소리 너무너무 감사드립니다.

    현재 ssh 환경에서 설정을 진행하고 있는데요.

    ssh 환경 하에서도 mmm 사용이 가능한지요? 정확하게는 액티브 마스터와 그 외 서버(패시브, 슬래이브1,2)는 서로 도메인 주소가 달라서 ssh tunnel을 통해(정확하게는 autossh 프로그램 이용) 통신해야 하는 상황입니다. 그전까지는 온갖 삽질끝에 무사 해결했는데, /etc/mysql-mmm/mmm_common.conf 파일 설정 부분에서 딱 막혔습니다.

    혹시 가능하지 않다면 mmm 유사 프로그램이 있을런지요.

    감사합니다~!
    답글
  3. 이재웅
    Centos 7 사용하면서 epel repo 를 7용으로 사용할 경우 dependencies 오류 해결법입니다.

    # mysql-mmm이 yum 으로 설치가 안되므로 소스 받아서 설치합니다.
    # (rpm이 epel repo 5까지만 있는데 설치하면 dependencies 오류 발생)
    sudo yum install cpan
    wget http://mysql-mmm.org.......gz
    tar -xvzf mysql-mmm-2.2.1.tar.gz
    cd mysql-mmm-2.2.1
    sudo make install

    # per-Proc-Daemon 설치
    sudo yum install perl-Proc-Daemon --nogpgcheck

    # Agent나 Monitor 실행시 Log4perl, perl-Algorithm-Diff 이 없어서 오류 발생
    # 아래와 같이 설치 합니다.
    wget http://search.cpan.org.......gz
    tar -xvf Log-Log4perl-1.46.tar.gz
    cd Log-Log4perl-1.46
    perl Makefile.PL
    sudo make;sudo make install
    sudo yum install perl-Algorithm-Diff


    # monitor 의 경우 Class-Singleton 설치가 필요합니다.
    # http://search.cpan.org.......pm
    wget http://search.cpan.org.......gz
    tar -xvzf Class-Singleton-1.03.tar.gz; cd Class-Singleton-1.03; perl Makefile.PL
    sudo make;sudo make install

    # mmm_control checks all 했을때 아래와 같이 Agent 에 오류가 발생하면 아래 링크 참고하시기 바랍니다.
    # http://cpansearch.perl.org......tml
    install_driver(mysql) failed: Can't load
    '/usr/lib/perl5/site_perl/i586-linux/auto/DBD/mysql/mysql.so'
    for module DBD::mysql: File not found at
    /usr/lib/perl5/i586-linux/5.00404/DynaLoader.pm line 166
    답글수정삭제
  4. 삽질은죄악이다
    wget http://dl.fedoraproject.org......rpm
    rpm -Uvh epel-release-6-8.noarch.rpm 
    yum -y install mysql-mmm* 하면 찾음

    밑에같은 에러나면
    Starting MMM Agent Daemon: Configuration file /etc/mysql-mmm/mmm_agent_navy2.conf is world readable! at /usr/share/perl5/MMM/Common/Config.pm line 132
    [FAILED]

    chmod 640 /etc/mysql-mmm/*
    출처: <http://navyaijm.blog.51cto.com/4647068/1232662>

    Proc/Daemon -- > 난다김님 감사합니다
    구축끝! 잘보고 가요
    답글수정삭제
  5. 굴러댕겨
    Proc/Daemon에 대한 오류가 똑같이 발생하는데 perl-proc-daemon을 어떻게 설치하는지요?
    대화보기
      답글수정삭제
    • 난다김
      여기서 받으세요

      https://aur.archlinux.org......on/
      대화보기
        답글수정삭제
      • REX
        MMM을 설치하고 있는데 잘 안되서 질문 드립니다.
        1. yum install -y mysql-mmm-agent 실행시 No package mysql-mmm-agent available 메세지 뜬다.
        - yum으로 다른 패키지는 설치가 되는데 왜 mmm만 안될까요?

        2. yum으로 설치가 안되어 홈피에서 tar를 받아서 셋팅시 아래와 같은 메세지가 뜬다
        agent machine과 monitor machine에서 같은 에러가 발생한다.
        도와주세요~~~
        - Database hosts
        [root@rexCentOS6 mysql-mmm]# /etc/init.d/mysql-mmm-agent start
        Daemon bin: '/usr/sbin/mmm_agentd'
        Daemon pid: '/var/run/mmm_agentd.pid'
        Starting MMM Agent daemon... Can't locate Proc/Daemon.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/sbin/mmm_agentd line 7.
        BEGIN failed--compilation aborted at /usr/sbin/mmm_agentd line 7.
        failed

        - Monitoring host
        [root@rexcom05 util]# /etc/init.d/mysql-mmm-monitor start
        Daemon bin: '/usr/sbin/mmm_mond'
        Daemon pid: '/var/run/mmm_mond.pid'
        Starting MMM Monitor daemon: Can't locate Proc/Daemon.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/sbin/mmm_mond line 11.
        BEGIN failed--compilation aborted at /usr/sbin/mmm_mond line 11.
        failed
        답글수정삭제
      • 김대리
        MMM구성할일이 생겨서 참고하면서 보고 있습니다^^ 정말 도움이 많이 됩니다^^
        구성을 보니 웹서버에서 DB에 접속을 할때
        등록할때는 write용 vip로 접속하여 커넥션를 하고 조회할때는 각각의 read용 vip로 접속을 해야하는건가요!?? 그럼 웹서버(PHP)에서 커넥션 하는 부분을 쓰기용, 읽기용 따 로 만들어야 하는지 도통 햇갈리네요.
        답글
      • Starmomo
        FLUSH TABLES WITH READ LOCK; 명령을 하면 입력이 안 되는군요. ^^이고잉님, 답변 고맙습니다. 생활코딩이 날로 번창하길 바랍니다. 힘~!
        대화보기
          답글
        • egoing
          ROOT 계정으로 접속하시고요. 아래와 같이 해보세요.mysql -uroot -p;create database test;create table t(id init);# 아래는 잘 들어갑니다.insert into t values(1); #락을 겁니다.FLUSH TABLES WITH READ LOCK; 아래와 같이 입력하면 에러가 납니다. mysql> insert into t values(2);ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lockUNLOCK TABLES;
          결론은 입력이 안됩니다. 읽기라도 제공하려는 의도의 명령이라고 보시면 되고요. 좋은 질문이었습니다. (사실 저도 방금 해봤다능...)
          대화보기
            답글
          • Starmomo
            [질문] 수업의 동기화 작업에서 db1의 mysqldump를 실행할 때에 db변동을 막기 위한 FLUSH TABLES WITH READ LOCK; 명령을 해제하는 아래 명령을 (복제구성을 완료하여)복제가 작동하기 전에 실행하면 그 사이에 변경(추가)된 db도 동기화 되는지요?
            FLUSH ... LOCK 를 실행한 쉘에서 다음 명령을 실행한다.(db1) mysql> UNLOCK TABLES;
            * Position: 1864 이후~복제 시작점까지의 DB를 동기화하기 못한다면, 복제설정 후 슬레이브를 구동한 다음에 해야 할 것 같은 생각이 들어서요.
            답글