Chinaunix首页 | 论坛 | 博客
  • 博客访问: 100044
  • 博文数量: 63
  • 博客积分: 25
  • 博客等级: 民兵
  • 技术积分: 12
  • 用 户 组: 普通用户
  • 注册时间: 2012-02-05 15:27
文章分类
文章存档

2017年(2)

2016年(1)

2015年(31)

2014年(29)

分类: Mysql/postgreSQL

2015-05-12 18:18:33

原文地址:MySQL高可用之:MySQL-MMM 作者:airmy

简介
MMM即:Master-Master Replication Manager For MySQL,MySQL主主复制管理器的功能包括监控、故障转移和等一系列脚本构成,
这个脚本也能对基本的主从复制配置的任意数量的从服务器进行读负载均衡,所以可以用它来实现一组居于复制的虚拟IP,同时它还有数据备份、节点之间重新同步功能的能力.

IP
DB1:192.168.11.198
DB2:192.168.11.88
DB3:192.168.11.238
MONITOR:192.168.11.116

结构图如下:


以下就看看如何搭建MySQL-MMM



一、编译MySQL5.6
此步省略

二、编辑my.cnf

db1
[mysql@localhost ~]$ sudo cat /etc/my.cnf 
[sudo] password for mysql: 
[client]
socket=/tmp/mysql.sock

[mysqld]
server-id=2
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=innodb
character_set_server=utf8
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
log-queries-not-using-indexes
log-slow-admin-statements
innodb_buffer_pool_size=50M
innodb_flush_log_at_trx_commit=1
max_allowed_packet=100M
binlog_format=mixed
log-bin=/mysql/log/mysql-bin
log_bin_trust_function_creators = 1
innodb_fast_shutdown = 0
binlog-do-db=test
replicate-do-db=test
log-slave-updates=on
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid

db2
[mysql@localhost ~]$ sudo cat /etc/my.cnf 
[client]
socket=/tmp/mysql.sock

[mysqld]
server-id=4
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=InnoDB
character_set_server=utf8
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
log-queries-not-using-indexes
log-slow-admin-statements
log_bin_trust_function_creators = 1
log-bin=/mysql/log/mysql-bin
report_host=192.168.23.164
binlog_format=mixed
log-bin=/mysql/log/mysql-bin
binlog-do-db=test
replicate-do-db=test
log-slave-updates=on
slave-skip-errors=1007,1050,1146,1051
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid

db3
[client]
socket=/tmp/mysql.sock
port=3306

[mysqld]
server-id=3
port=3306
basedir=/usr/local/mysql
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=innodb
character_set_server=utf8
log-bin=/mysql/log/mysql-bin
slave-skip-errors=1007,1050
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
relay-log=relay-bin   
relay-log-index=relay-bin.index
binlog_format=mixed
log-slave-updates=on
replicate-do-db=test
slave-skip-errors=1146
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid 

三、主从配置(master1和master2配置成主主,slave1配置成master1的从)
1、在master1上授权:
grant replication slave on *.* to repl@'192.168.11.198' identified by "XXXX";
grant replication slave on *.* to repl@'192.168.11.88' identified by "XXXX";
grant replication slave on *.* to repl@'192.168.11.238' identified by "XXXX";flush privileges;
 
2、在master2上授权:
grant replication slave on *.* to repl@'192.168.11.198' identified by "XXXX";
grant replication slave on *.* to repl@'192.168.11.88' identified by "XXXX";
grant replication slave on *.* to repl@'192.168.11.238' identified by "XXXX";flush privileges;

在master2、slave1执行
change master to master_host='192.168.11.198', master_port=3306, master_user='repl', master_password='XXXX';start slave;
把master1配置成master2的从库:
change master to master_host='192.168.11.88', master_port=3306, master_user='repl', master_password='XXXX';start slave;

在各个机器上执行:
db1
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.88
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000024
          Read_Master_Log_Pos: 124156
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 1068
        Relay_Master_Log_File: mysql-bin.000024
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 124156
              Relay_Log_Space: 1242
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 4
                  Master_UUID: a7e4c60d-62ca-11e3-8710-080027e08a30
             Master_Info_File: /mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

db2
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.198
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000064
          Read_Master_Log_Pos: 3324
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 1870
        Relay_Master_Log_File: mysql-bin.000064
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3324
              Relay_Log_Space: 2044
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 69a73914-62ca-11e3-870f-080027dff846
             Master_Info_File: /mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

db3
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.198
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000064
          Read_Master_Log_Pos: 3324
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 2655
        Relay_Master_Log_File: mysql-bin.000064
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3324
              Relay_Log_Space: 2822
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 69a73914-62ca-11e3-870f-080027dff846
             Master_Info_File: /mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.01 sec)

mysql> 

mysql-mmm安装
1、db节点:
yum -y install mysql-mmm-agent
 
2、monitor节点:
yum -y install mysql-mmm*

mysql-mmm的配置:
1、在三个db节点授权:
grant super, replication client, process on *.* to 'mmm_agent'@'192.168.11.%'   identified by 'XXXX';
grant replication client on *.* to 'mmm_monitor'@'192.168.11.%' identified by 'XXXX';flush privileges;


修改配置文件
sudo vim /etc/mysql-mmm/mmm_common.conf (同时编辑db、monitor)

[mysql@localhost ~]$ sudo cat /etc/mysql-mmm/mmm_common.conf 
[sudo] password for mysql: 
active_master_role      writer


    cluster_interface       eth0
    pid_path                /var/run/mysql-mmm/mmm_agentd.pid
    bin_path                /usr/libexec/mysql-mmm/
    replication_user        repl
    replication_password    XXXX
    agent_user              mmm_agent
    agent_password          123456



    ip      192.168.11.198
    mysql_port          3306
    mode    master
    peer    db2



    ip      192.168.11.88
    mysql_port          3306
    mode    master
    peer    db1



    ip      192.168.11.238
    mysql_port          3306
    mode    slave
    peer    db3



    hosts   db1, db2
    ips     192.168.11.170
    mode    exclusive



    hosts   db3
    ips     192.168.11.171,192.168.11.172
    mode    balanced


peer的意思相当于等同,表示db1与db2同等
ips指定VIP
mode exclusive 有两种模式:exclusive排他,此模式下任何时候只能一个host拥有该角色
balanced模式可以有多个host同时拥有此角色。一般writer是exclusive,reader是balanced

sudo vim /etc/mysql-mmm/mmm_agent.conf (同时编辑master1、master2、slave1分别修改为:this db1、this db2、this db3)

sudo vim /etc/mysql-mmm/mmm_mon.conf (仅编辑monitor节点)
mysql@localhost bin]$ sudo cat /etc/mysql-mmm/mmm_mon.conf 
include mmm_common.conf


    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.11.198,192.168.11.88
    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_user        mmm_monitor
    monitor_password    123456


debug 0

mmm启动
1、db节点:
[mysql@localhost mysql-mmm]$ sudo /etc/init.d/mysql-mmm-agent start
[sudo] password for mysql: 
Starting MMM Agent Daemon: [  OK  ]

[mysql@localhost bin]$ sudo /etc/init.d/mysql-mmm-monitor start
Starting MMM Monitor Daemon: [  OK  ]

[mysql@localhost ~]$ sudo mmm_control show
 db1(192.168.11.198) master/ONLINE. Roles: writer(192.168.11.170)
 db2(192.168.11.88) master/ONLINE. Roles: 
 db3(192.168.11.238) slave/ONLINE. Roles: reader(192.168.11.171), reader(192.168.11.172)

[mysql@localhost bin]$ sudo mmm_control checks all
db2  ping         [last change: 2014/05/06 17:53:36]  OK
db2  mysql        [last change: 2014/05/06 17:53:36]  OK
db2  rep_threads  [last change: 2014/05/06 17:53:36]  OK
db2  rep_backlog  [last change: 2014/05/06 17:53:36]  OK: Backlog is null
db3  ping         [last change: 2014/05/06 17:53:36]  OK
db3  mysql        [last change: 2014/05/06 19:04:39]  OK
db3  rep_threads  [last change: 2014/05/06 19:04:36]  OK
db3  rep_backlog  [last change: 2014/05/06 19:04:39]  OK: Backlog is null
db1  ping         [last change: 2014/05/06 17:53:36]  OK
db1  mysql        [last change: 2014/05/06 17:53:36]  OK
db1  rep_threads  [last change: 2014/05/06 17:53:36]  OK
db1  rep_backlog  [last change: 2014/05/06 17:53:36]  OK: Backlog is null

测试:
停止DB1看192.168.11.170会不会漂移到DB2上去,同时DB3的Slave的IP会不会从DB1改到DB2


DB1:
[mysql@localhost ~]$ mysqladmin -u root -pXXXXXX shutdown
Warning: Using a password on the command line interface can be insecure.
140522 16:38:47 mysqld_safe mysqld from pid file /mysql/mysqld.pid ended
[1]+  Done                    mysqld_safe

MONITOR:
[mysql@localhost ~]$ sudo mmm_control show
  db1(192.168.23.198) master/ONLINE. Roles: writer(192.168.11.170)
  db2(192.168.23.88) master/ONLINE. Roles: 
  db3(192.168.23.238) slave/ONLINE. Roles: reader(192.168.11.171), reader(192.168.11.172)

[mysql@localhost ~]$ sudo mmm_control show
  db1(192.168.23.198) master/HARD_OFFLINE. Roles: 
  db2(192.168.23.88) master/ONLINE. Roles: 
  db3(192.168.23.238) slave/ONLINE. Roles: reader(192.168.11.171), reader(192.168.11.172)

[mysql@localhost ~]$ sudo mmm_control show
  db1(192.168.23.198) master/HARD_OFFLINE. Roles: 
  db2(192.168.23.88) master/ONLINE. Roles: writer(192.168.11.170)
  db3(192.168.23.238) slave/ONLINE. Roles: reader(192.168.11.171), reader(192.168.11.172)

DB3:
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.11.88
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000065
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000065
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 120
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 4
                  Master_UUID: 
             Master_Info_File: /mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

^_^,OK.切换成功了.在生产环境中可以利用NAGIOS把AGENT、MONITOR、PEPLICATION等进程监控起来发现问题迅速处理解决.今天先到此吧.
阅读(848) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~