MySQL高可用方案之MMM

  • A+
所属分类：Database

之前介绍过一种MySql的高可用方案是DRDB方案，此次介绍一种MySql另外一种MMM方案。

MMM方案一般用于MySql主主复制方案中。

0
1
2
3
[root@DB1 ~]# tail -3 /etc/hosts
1.1.1.131   DB1.t4x.org
1.1.1.132   DB2.t4x.org
1.1.1.133   DB3.t4x.org

0
1
2
3
4
5
6
7
[root@DB3 ~]# ln -s /opt/mysql-5.7.16 /usr/local/mysql
[root@DB3 ~]# mv /etc/my.cnf /etc/my.cnf.$(date +%F)
[root@DB3 ~]# mkdir /usr/local/mysql/tmp
[root@DB3 ~]# chown -R mysql.mysql /usr/local/mysql/tmp/
[root@DB3 ~]# cp /byrd/tools/mysql-5.7.16/support-files/mysql.server /etc/init.d/mysqld
[root@DB3 ~]# chmod +x /etc/init.d/mysqld
[root@DB3 ~]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql
[root@DB3 ~]# /etc/init.d/mysqld start

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> create user 'repl'@'1.1.%.%' identified by 'admin';        
Query OK, 0 rows affected (0.03 sec)
 
mysql> grant replication slave on *.* to 'repl'@'1.1.%.%';
Query OK, 0 rows affected (0.01 sec)
 
mysql> grant replication client on *.* to mmm_monitor@'1.1.%.%' identified by 'admin';
Query OK, 0 rows affected, 1 warning (0.02 sec)
 
mysql> grant super,replication client,process on *.* to mmm_agent@'1.1.%.%' identified by 'admin';  
mysql> flush privileges;
 
Query OK, 0 rows affected (0.03 sec)
[root@DB1 ~]# /usr/local/mysql/bin/mysqldump --master-data --single-transaction --triggers --routines --all-databases --events >> all.sql
[root@DB1 ~]# scp all.sql root@1.1.1.132:/tmp/
[root@DB1 ~]# scp all.sql root@1.1.1.133:/tmp/
[root@DB1 ~]# cat all.sql | head -22 | tail -1
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=759;

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@DB2 ~]# /usr/local/mysql/bin/mysql -root -p < /tmp/all.sql
[root@DB3 ~]# /usr/local/mysql/bin/mysql -root -p < /tmp/all.sql
mysql> change master to master_host='1.1.1.131',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='admin',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=759;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start slave;
Query OK, 0 rows affected (0.07 sec)
mysql> show slave status \G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0

0
1
2
3
4
5
6
mysql> change master to master_host='1.1.1.132',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='admin',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=757950;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

0
1
2
3
4
5
6
7
8
[root@DB3 ~]# wget http://mirrors.opencas.org/epel/epel-release-latest-6.noarch.rpm
[root@DB3 ~]# rpm -ivh epel-release-latest-6.noarch.rpm
[root@DB3 ~]# wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
[root@DB3 ~]# sed -i s/enabled=0/enabled=1/g /etc/yum.repos.d/remi.repo
[root@DB3 ~]# yum install mysql-mmm-agent    #都必须安装
####
cairo dejavu-fonts-common dejavu-lgc-sans-mono-fonts dejavu-sans-mono-fonts fontconfig fontpackages-filesystem freetype libX11 libX11-common libXau libXft libXrender libpng libthai libxcb mailcap mysql-mmm pango perl-Algorithm-Diff perl-Compress-Raw-Zlib perl-Compress-Zlib perl-DBD-MySQL perl-DBI perl-Date-Manip perl-Email-Date-Format perl-HTML-Parser perl-HTML-Tagset perl-IO-Compress-Base perl-IO-Compress-Zlib perl-Log-Dispatch perl-Log-Dispatch-FileRotate perl-Log-Log4perl perl-MIME-Lite perl-MIME-Types perl-Mail-Sender perl-Mail-Sendmail perl-MailTools perl-Net-ARP perl-Params-Validate perl-Proc-Daemon perl-Proc-ProcessTable perl-Time-HiRes perl-TimeDate perl-URI perl-XML-DOM perl-XML-Parser perl-XML-RegExp perl-YAML-Syck perl-libwww-perl pixman rrdtool rrdtool-perl
####
[root@DB3 ~]# yum install mysql-mmm-monitor perl-Class-Singleton mysql-mmm-tools perl-Path-Class    #监控结点

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
[root@DB1 mysql-mmm]# pwd
/etc/mysql-mmm
[root@DB1 mysql-mmm]# scp mmm_common.conf root@1.1.1.132:/etc/mysql-mmm/mmm_common.conf    
[root@DB1 mysql-mmm]# scp mmm_common.conf root@1.1.1.133:/etc/mysql-mmm/mmm_common.conf
[root@DB1 mysql-mmm]# cat mmm_
mmm_agent.conf   mmm_common.conf  
[root@DB1 mysql-mmm]# cat 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        repl
    replication_password    admin
    agent_user              mmm_agent
    agent_password          admin
</host>
<host db1>
    ip      1.1.1.131
    mode    master
    peer    db2
</host>
<host db2>
    ip      1.1.1.132
    mode    master
    peer    db1
</host>
<host db3>
    ip      1.1.1.133
    mode    slave
</host>
<role writer>
    hosts   db1, db2
    ips     1.1.1.100
    mode    exclusive
</role>
<role reader>
    hosts   db1, db2, db3
    ips     1.1.1.101,1.1.1.102,1.1.1.103
    mode    balanced
</role>
[root@DB1 mysql-mmm]# tail -1 /etc/mysql-mmm/mmm_agent.conf
this db1
[root@DB2 ~]# tail -1 /etc/mysql-mmm/mmm_agent.conf
this db2
[root@DB3 ~]# tail -1 /etc/mysql-mmm/mmm_agent.conf  
this db3

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@DB3 mysql-mmm]# grep -v "^$\|#" mmm_mon.conf
include mmm_common.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            1.1.1.131,1.1.1.132,1.1.1.133
    auto_set_online     60
</monitor>
<host default>
    monitor_user        mmm_monitor
    monitor_password    admin
</host>
debug 0

0
1
2
3
[root@DB1 ~]# /etc/init.d/mysql-mmm-agent start
[root@DB2 ~]# /etc/init.d/mysql-mmm-agent start
[root@DB3 mysql-mmm]# /etc/init.d/mysql-mmm-agent start
[root@DB3 mysql-mmm]# /etc/init.d/mysql-mmm-monitor start

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@DB3 mysql-mmm]# mmm_control show
  db1(1.1.1.131) master/ONLINE. Roles: reader(1.1.1.101), writer(1.1.1.100)
  db2(1.1.1.132) master/ONLINE. Roles: reader(1.1.1.102)
  db3(1.1.1.133) slave/ONLINE. Roles: reader(1.1.1.103)
[root@DB1 mysql-mmm]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 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 pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:0f:01:e1 brd ff:ff:ff:ff:ff:ff
    inet 1.1.1.131/24 brd 1.1.1.255 scope global eth0
    inet 1.1.1.101/32 scope global eth0
    inet 1.1.1.100/32 scope global eth0
    inet6 fe80::20c:29ff:fe0f:1e1/64 scope link
       valid_lft forever preferred_lft forever
 
[root@DB3 mysql-mmm]# mmm_control show                    
  db1(1.1.1.131) master/ONLINE. Roles: reader(1.1.1.101), writer(1.1.1.100)
  db2(1.1.1.132) master/HARD_OFFLINE. Roles:
  db3(1.1.1.133) slave/ONLINE. Roles: reader(1.1.1.102), reader(1.1.1.103)

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
[client]
port    = 3306
socket  = /usr/local/mysql/tmp/mysql.sock
[mysqld]
user    = mysql
port    = 3306
socket  = /usr/local/mysql/tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
open_files_limit    = 10240
back_log = 600
max_connections = 3000
max_connect_errors = 6000
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 300
query_cache_size = 64M
query_cache_limit = 4M
query_cache_min_res_unit = 2k
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 256M
max_heap_table_size = 256M
long_query_time = 2
log-error = /usr/local/mysql/tmp/error.log
pid-file = /usr/local/mysql/tmp/mysql.pid
log-bin = /usr/local/mysql/data/mysql-bin
relay_log = /usr/local/mysql/data/relay-bin
relay-log-info-file = /usr/local/mysql/data/relay-log.info
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 512M
expire_logs_days = 7
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_repair_threads = 1
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062,1007
#replicate-ignore-db = mysql
server-id = 131
#gtid_mode = on
#enforce_gtid_consistency = on
#explicit_defaults_for_timestamp = true
#ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_mode=NO_ENGINE_SUBSTITUTION
#sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log-bin = /usr/local/mysql/data/mysql-bin
log-slave-updates = 1
binlog_format = row
#skip_slave_start = 1
log-slave-updates = true
#enforce-gtid-consistency = true
auto_increment_increment = 2
auto_increment_offset = 1