mmm

MMM (Mysql Multi Master) 

 설치

### monitor 서버 설치  (monitoring server)

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

yum install -y mysql-mmm-*

필요한 설정파일

/etc/mysql-mmm

mmm_common.conf

active_master_role      writer

<host default>

    cluster_interface       eth0 (서버의 이더넷 카드)

    pid_path                /var/run/mysql-mmm/mmm_agentd.pid

    bin_path                /usr/libexec/mysql-mmm/

    replication_user        replicant

    replication_password    slave

    agent_user              mmm_agent

    agent_password          RepAgent

</host>

<host db1>

    ip      192.168.100.49

    mode    master

    peer    db2

</host>

<host db2>

    ip      192.168.100.50

    mode    master

    peer    db1

</host>

#<host db3>

#    ip      192.168.100.51

#    mode    slave

#</host>

<role writer>

    hosts   db1, db2

    ips     192.168.100.250

    mode    exclusive

</role>

<role reader>

    hosts   db1, db2

    ips     192.168.100.251, 192.168.100.252

    mode    balanced

</role>

mmm_mon.conf

<monitor>

    ip                  127.0.0.1

    pid_path            /var/run/mysql-mmm/mmm_mond.pid

    bin_path            /usr/libexec/mysql-mmm

    status_path         /var/lib/mysql-mmm/mmm_mond.status

    ping_ips            192.168.100.50

    auto_set_online     60

    # The kill_host_bin does not exist by default, though the monitor will

    # throw a warning about it missing.  See the section 5.10 "Kill Host 

    # Functionality" in the PDF documentation.

    #

    # kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host

    #

</monitor>

<host default>

    monitor_user        mmm_monitor

    monitor_password    RepMonitor

</host>

debug 0

/etc/init.d/mysql-mmm-monitor  (실행 스크립트)

# Cluster name (it can be empty for default cases)

CLUSTER=''

### DB서버에 설치해야되는 것들

### mmm scripts 설치

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

yum install -y mysql-mmm-agent

서버에 mmm_common.conf 파일을 다 동일한 내용으로 넣어줘야한다.

master 서버 설정에 

bind-address = 0.0.0.0

auto_increment_increment = 2

auto_increment_offset = 2

이 부분을 master 1번서버는 홀수, 2번서버는 짝수로 넣어줘야 db 입력되는 순서에 문제가 생기지 않는다.

log-bin                         = /data/mysql/binlog/mysql-bin

log-bin-index                   = /data/mysql/binlog/mysql-bin.index

relay-log                       = /data/mysql/binlog/mysql-relay-bin

relay-log-index                 = /data/mysql/binlog/mysql-relay-bin.index

log-slave_updates               = 1

그 이외에 위의 binlog 부분이랑 relaylog 부분을 넣어준다.

설치한 서버에 mysql 권한을 주어야 함

GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'10.0.0.%' IDENTIFIED BY 'RepMonitor';

ㄴ mmm_mon.conf 파일에 입력되어있는 설정값

GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'10.0.0.%'   IDENTIFIED BY 'RepAgent';

ㄴ mmm_common.conf 파일에 입력되어있는 설정값

flush privileges;

/etc/mysql-mmm/mmm_agent.conf

include mmm_common.conf

# The 'this' variable refers to this server.  Proper operation requires 

# that 'this' server (db1 by default), as well as all other servers, have the 

# proper IP addresses set in mmm_common.conf.

this servername

위의 내용들은 설정파일들과, 설정파일에서 수정해야 되는 부분들을 작성해둔 내용이다.

mmm 설치의 대략적인 내용은

1. mmm_monitor 설치 -> monitor에서 2개의 설정파일과 실행스크립트 복사 및 수정작업

============================================================================

root@mmm-wf-01:/etc/mysql-mmm> ll

-rw-r----- 1 root root  786 Sep 16  2013 mmm_common.conf.org

-rw-r----- 1 root root  623 May  9 15:21 mmm_common_9702.conf

-rw-r----- 1 root root  786 Sep 11  2015 mmm_common_9703.conf

-rw-r----- 1 root root  915 Jan  7 16:31 mmm_common_9704.conf

-rw-r----- 1 root root  792 Aug  5  2015 mmm_common_9705.conf

-rw-r----- 1 root root  661 Oct 27  2015 mmm_common_9706.conf

-rw-r----- 1 root root  827 Sep 16  2013 mmm_mon.conf.org

-rw-r----- 1 root root  723 May  9 15:26 mmm_mon_9702.conf

-rw-r----- 1 root root  755 Jan  7 17:11 mmm_mon_9703.conf

-rw-r----- 1 root root  491 Jan  7 16:43 mmm_mon_9704.conf

-rw-r----- 1 root root  765 Jan  7 17:08 mmm_mon_9705.conf

-rw-r----- 1 root root  730 Oct 27  2015 mmm_mon_9706.conf

root@mmm-wf-01:/etc/mysql-mmm> netstat -lpn

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   

tcp        0      0 10.0.0.33:9702            0.0.0.0:*                   LISTEN      23535/mmm_mond-9702

tcp        0      0 10.0.0.33:9703            0.0.0.0:*                   LISTEN      23412/mmm_mond-9703

tcp        0      0 127.0.0.1:199               0.0.0.0:*                   LISTEN      1563/snmpd          

tcp        0      0 10.0.0.33:9704            0.0.0.0:*                   LISTEN      26959/mmm_mond-9704

tcp        0      0 10.0.0.33:9705            0.0.0.0:*                   LISTEN      23299/mmm_mond-9705

tcp        0      0 10.0.0.33:9706            0.0.0.0:*                   LISTEN      17554/mmm_mond-9706 

tcp        0      0 0.0.0.0:4949                0.0.0.0:*                   LISTEN      2878/perl           

tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      1577/sshd           

tcp        0      0 0.0.0.0:5666                0.0.0.0:*                   LISTEN      3842/nrpe           

tcp        0      0 :::22                       :::*                        LISTEN      1577/sshd           

tcp        0      0 :::5666                     :::*                        LISTEN      3842/nrpe           

udp        0      0 0.0.0.0:161                 0.0.0.0:*                               1563/snmpd          

Active UNIX domain sockets (only servers)

Proto RefCnt Flags       Type       State         I-Node PID/Program name    Path

unix  2      [ ACC ]     STREAM     LISTENING     8018   1/init              @/com/ubuntu/upstart

root@mmm-wf-01:/etc/mysql-mmm> ll /etc/init.d/mysql-mmm-*

-rwxr-xr-x 1 root root 1687 Sep 16  2013 /etc/init.d/mysql-mmm-agent

-rwxr-xr-x 1 root root 1907 Sep 16  2013 /etc/init.d/mysql-mmm-monitor

-rwxr-xr-x 1 root root 1910 May  9 15:23 /etc/init.d/mysql-mmm-monitor_9702

-rwxr-xr-x 1 root root 1913 Oct 26  2015 /etc/init.d/mysql-mmm-monitor_9703

-rwxr-xr-x 1 root root 1912 Jan  7 16:34 /etc/init.d/mysql-mmm-monitor_9704

-rwxr-xr-x 1 root root 1916 Jan  7 17:07 /etc/init.d/mysql-mmm-monitor_9705

-rwxr-xr-x 1 root root 1912 Oct 27  2015 /etc/init.d/mysql-mmm-monitor_9706

root@mmm-wf-01:/etc/mysql-mmm>

root@mmm-wf-01:/etc/mysql-mmm> cat /etc/init.d/mysql-mmm-monitor_9702

.

.

.

# Cluster name (it can be empty for default cases)

CLUSTER='9702'

.

.

.

root@mmm-wf-01:/etc/mysql-mmm> cat mmm_common_9702.conf

active_master_role      writer

<host default>

    cluster_interface       eth0

    pid_path                /var/run/mysql-mmm/mmm_agentd.pid

    bin_path                /usr/libexec/mysql-mmm/

    replication_user        replication

    replication_password    replication_password

    agent_user              mmm_agent

    agent_password          agent_password

</host>

.

.

.

root@mmm-wf-01:/etc/mysql-mmm> cat mmm_mon_9702.conf 

include mmm_common_9702.conf

<monitor>

    ip                  10.0.0.33

    pid_path            /var/run/mysql-mmm/mmm_mond-9702.pid

    port 9702

    bin_path            /usr/libexec/mysql-mmm

    status_path         /var/lib/mysql-mmm/mmm_mond-9702.status

    ping_ips            db1, db2

    auto_set_online     60

    # The kill_host_bin does not exist by default, though the monitor will

    # throw a warning about it missing.  See the section 5.10 "Kill Host 

    # Functionality" in the PDF documentation.

    #

    # kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host

    #

</monitor>

<host default>

    monitor_user        mmm_monitor

    monitor_password    monitor_password

</host>

debug 0

============================================================================

2. 대상 서버들 mmm-agent 설치작업 진행

(mster db 2대, slave 서버들 n개 모두 설치 및 설정파일 공유)

root@slave-03:/root> cat /etc/mysql-mmm/mmm_common.conf   

ㄴ 해당 파일은 이더넷 장치값 이외의 다른 설정파일은 전부 동일 내용으로 되어 있어야함

active_master_role      writer 

<host default>

    cluster_interface       eth0   (해당 서버의 이더넷 장치값)

    pid_path                /var/run/mysql-mmm/mmm_agentd.pid

    bin_path                /usr/libexec/mysql-mmm/

    replication_user        replication

    replication_password    replication_password

    agent_user              mmm_agent

    agent_password          agent_password

</host>

<host master-01>

    ip      10.0.0.11

    mode    master

    peer    master-02

</host>

<host master-02>

    ip      10.0.0.12

    mode    master

    peer    master-01

</host>

<host slave-01>

    ip                      10.0.0.13

    mode                    slave

</host>

<host slave-02>

    ip                      10.0.0.14

    mode                    slave

</host>

<host slave-03>

    ip                      10.0.0.15

    mode                    slave

</host>

<role writer>

    hosts   master-01, master-02

    ips     10.0.0.111

    mode    exclusive

</role>

root@slave-03:/root> 

root@slave-03:/root> cat /etc/mysql-mmm/mmm_agent.conf

ㄴ this 값이 위의 설정값중 host 값과 일치해야함

include mmm_common.conf

# The 'this' variable refers to this server.  Proper operation requires 

# that 'this' server (db1 by default), as well as all other servers, have the 

# proper IP addresses set in mmm_common.conf.

this slave-03

root@master-02:/data/mysql> root_mysql  (서버접근 alias)

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9142

Server version: 5.5.37-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'10.0.0.%' IDENTIFIED BY 'monitor_password';

mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'10.0.0.%'   IDENTIFIED BY 'agent_password';

mysql> flush privileges;

mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.11', MASTER_USER='replication', MASTER_PASSWORD='replication_password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000309', MASTER_LOG_POS=208573385;

mysql> slave start;

mysql> show slave status \G

위와같은 작업을 서버마다 진행 (slave 서버는 replication 말고 monitor, agent 유저 등록만 진행

root@mcrm-02:/data/mysql> cat /usr/local/mysql/my.cnf

.

.

.

bind-address = 0.0.0.0

auto-increment-increment = 2

auto-increment-offset = 2

.

.

.

master-01 서버는 auto-increment-offset 값을 1로 수정

root@mmm-wf-01:/etc/mysql-mmm> mmm_control @9702 checks

slave-02  ping         [last change: 2016/04/10 07:41:06]  OK

slave-02  mysql        [last change: 2016/01/07 16:49:27]  OK

slave-02  rep_threads  [last change: 2016/01/07 16:49:27]  OK

slave-02  rep_backlog  [last change: 2016/04/06 12:32:39]  OK: Backlog is null

master-02  ping         [last change: 2016/03/21 23:38:54]  OK

master-02  mysql        [last change: 2016/03/21 23:38:54]  OK

master-02  rep_threads  [last change: 2016/01/08 15:45:01]  OK

master-02  rep_backlog  [last change: 2016/03/28 19:08:55]  OK: Backlog is null

master-01  ping         [last change: 2016/01/07 16:49:27]  OK

master-01  mysql        [last change: 2016/01/07 16:49:27]  OK

master-01  rep_threads  [last change: 2016/01/07 16:59:26]  OK

master-01  rep_backlog  [last change: 2016/01/07 16:49:27]  OK: Backlog is null

slave-03  ping         [last change: 2016/01/07 16:49:27]  OK

slave-03  mysql        [last change: 2016/01/07 16:49:27]  OK

slave-03  rep_threads  [last change: 2016/01/07 16:49:27]  OK

slave-03  rep_backlog  [last change: 2016/04/06 12:32:54]  OK: Backlog is null

slave-01  ping         [last change: 2016/05/06 18:37:25]  OK

slave-01  mysql        [last change: 2016/05/07 14:07:07]  OK

slave-01  rep_threads  [last change: 2016/01/07 16:49:27]  OK

slave-01  rep_backlog  [last change: 2016/05/07 14:05:04]  OK: Backlog is null

root@mmm-wf-01:/etc/mysql-mmm> 

root@master-01:/root> ip a s

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 

    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

    inet 127.0.0.1/8 scope host lo

    inet6 ::1/128 scope host 

       valid_lft forever preferred_lft forever

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000

    link/ether 00:1e:0b:dc:35:c8 brd ff:ff:ff:ff:ff:ff

    inet 10.0.0.11/24 brd 10.0.0.255 scope global eth0

    inet 10.0.0.111/32 scope global eth0

    inet6 fe80::21e:bff:fedc:35c8/64 scope link 

       valid_lft forever preferred_lft forever

3: eth1: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN qlen 1000

    link/ether 00:1e:0b:dc:35:c9 brd ff:ff:ff:ff:ff:ff

root@master-01:/root> 

위와같이 설정해놓은 대표 IP가 자동으로 설정됨