훈스토리

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

bind-address = 0.0.0.0

auto_increment_increment = 2

auto_increment_offset = 2

/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

.

.

.

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

CLUSTER='9702'

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

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/

</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> 

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';

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가 자동으로 설정됨

### Keepalived install

mkdir /root/src

cd /root/src

wget http://keepalived.org/software/keepalived-1.2.20.tar.gz

tar xfz keepalived-1.2.20.tar.gz

cd keepalived-1.2.20

./configure && make && make install

ln -s /usr/local/etc/keepalived /etc/keepalived

ln -s /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/keepalived

ln -s /usr/local/etc/sysconfig/keepalived /etc/sysconfig/keepalived

vi /etc/rc.d/init.d/keepalived 

daemon /usr/local/sbin/keepalived

PATH=/sbin:/bin:/usr/bin:/usr/sbin:/usr/local/sbin

# 설정 파일

global_defs {

    router_id haproxy-01

}

vrrp_script haproxy {

  script "killall -0 haproxy"

  interval 2

  weight 2

}

vrrp_instance 임의로 {

    state MASTER

    interface eth0

    virtual_router_id 50

    priority 200

    advert_int 1

    virtual_ipaddress {

        10.0.0.100 (사용할 VIP)

    }

   track_script {

       haproxy

   }

}

### haproxy install

useradd haproxy -s /sbin/nologin

wget http://www.haproxy.org/download/1.6/src/haproxy-1.6.4.tar.gz

tar xfz haproxy-1.6.4.tar.gz

cd haproxy-1.6.4

make TARGET=linux2628 && make install

cp /usr/local/sbin/haproxy /usr/sbin/

cp ./examples/haproxy.init /etc/init.d/haproxy

chmod 755 /etc/init.d/haproxy

mkdir -p /etc/haproxy

mkdir -p /run/haproxy

mkdir -p /var/lib/haproxy

touch /var/lib/haproxy/stats

vi /etc/haproxy/haproxy.cfg

### 

global

    log         127.0.0.1 local2

    chroot      /var/lib/haproxy

    pidfile     /var/run/haproxy.pid

    maxconn     4000

    user        haproxy

    group       haproxy

    daemon

    # turn on stats unix socket

    stats socket /var/lib/haproxy/stats

defaults

    option                  redispatch

    retries                 3

    timeout queue           1m

    timeout connect         10s

    timeout client          1m

    timeout server          1m

    timeout check           10s

    maxconn                 3000

listen http

    bind *:80

    acl rose_acl hdr_beg(host) -i rose

    use_backend rose if rose_acl

backend rose

    mode http

    cookie  SERVERID insert indirect nocache

    option  forwardfor header X-Real-IP

    option  http-server-close

    option  httplog

    balance     roundrobin

    server  rose1 서버01:80 check cookie rose1

    server  rose2 서버02:80 check cookie rose2

    server  rose3 서버03:80 check cookie rose3

### 커널 튜닝

sysctl.conf

net.ipv4.ip_forward = 1

net.ipv4.conf.eth0.arp_ignore = 1

net.ipv4.conf.eth0.arp_announce = 2

### DNS

yum -y install bind bind-utils bind-libs bind-chroot

vi /etc/named.conf

acl trust {

    사용할대역;

    10.0.0.0/24;

};

options {

        listen-on port 53 { any; };

        directory       "/var/named";

        dump-file       "/var/named/data/cache_dump.db";

        statistics-file "/var/named/data/named_stats.txt";

        memstatistics-file "/var/named/data/named_mem_stats.txt";

        allow-query     { any; };

        allow-query-cache  { any; };

        allow-transfer  { any; };

        recursion no;

        allow-recursion { trust; };

        dnssec-enable yes;

        dnssec-validation yes;

        dnssec-lookaside auto;

        bindkeys-file "/etc/named.iscdlv.key";

        managed-keys-directory "/var/named/dynamic";

};

vi /etc/named.rfc1912.zones

zone "localhost.localdomain" IN {

        type master;

        file "named.localhost";

        allow-update { none; };

};

zone "localhost" IN {

        type master;

        file "named.localhost";

        allow-update { none; };

};

zone "1.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.ip6.arpa" IN {

        type master;

        file "named.loopback";

        allow-update { none; };

};

zone "1.0.0.127.in-addr.arpa" IN {

        type master;

        file "named.loopback";

        allow-update { none; };

};

zone "0.in-addr.arpa" IN {

        type master;

        file "named.empty";

        allow-update { none; };

};

vi 존파일

$TTL 1D

@ IN SOA @ rname.invalid. (

0 ; serial

1D ; refresh

1H ; retry

1W ; expire

3H ) ; minimum

IN NS 사용할도메인.

IN A 도메인IP

ns IN A 네임서버IP

rose IN A 도메인IP