【mysql】高可用集群之MMM

1、Slave_IO_Running = NO ，Slave I/O: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file‘, Error_code: 1236

2、Slave_SQL_Running=NO ，Error ‘Can‘t create database ‘abc‘; database exists‘ on query. Default database: ‘abc‘. Query: ‘create database abc‘

3、授权失败

主主同步配置完毕，查看同步状态Slave_IO和Slave_SQL是YES说明主主同步成功

slave-1和slave-2做为master-1的从库

change master to
master_host=‘192.168.1.25‘,
master_user=‘replication‘,
master_password=‘replication‘,
master_log_file=‘mysql-bin.000002‘,
master_log_pos=434;

在slave1和slave2查看如下说明主从复制成功。但是数据没过来，这是因为主从复制原理只同步配置完后的增删改记录，以前的数据是不能同步的，我们可以把主的数据库备份了，然后再还原

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.25
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000009
          Read_Master_Log_Pos: 410
               Relay_Log_File: mysql-relay-bin.000012
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql_bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: abc
          Replicate_Ignore_DB: information_schema,mysql
           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: 410
              Relay_Log_Space: 725
              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: fc4e74ed-563f-11e5-bff1-000c29ee3b5c
             Master_Info_File: /data/mysql/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

MySQL-MMM安装配置

CentOS 默认没有 mysql-mmm 软件包，官方推荐使用 epel 的网络源，五台都安装epel：

rpm -ivh http://mirrors.ustc.edu.cn/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm

monitor节点安装

yum -y install mysql-mmm-monitor

db节点安装

yum -y install mysql-mmm-agent

db节点授权monitor访问

GRANT REPLICATIONCLIENT ON *.* TO ‘mmm_monitor‘@‘192.168.1.%‘ IDENTIFIED BY ‘123456‘;
GRANT SUPER,REPLICATION CLIENT, PROCESS ON *.* TO ‘mmm_agent‘@‘192.168.1.%‘ IDENTIFIED BY ‘123456‘;

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
    agent_user              mmm_agent
    agent_password          agent
</host>
<host db1>
    ip     192.168.1.25
    mode   master
    peer   db2
</host>
<host db2>
    ip     192.168.0.26
    mode   master
    peer   db1
</host>
<host db3>
    ip     192.168.0.27
    mode   slave
</host>
<host db4>
    ip     192.168.0.28
    mode   slave
</host>
<role writer>
    hosts  db1, db2
    ips    192.168.1.31
    mode   exclusive    #只有一个host可以writer
</role>
<role reader>
    hosts  db3, db4
    ips    192.168.1.32,192.168.1.33
    mode   balanced     #多个host可以reader
</role>

db端 mmm_agent.conf

include mmm_common.conf
this db1   #分别修改为本机的主机名，即db1、db2、db3和db4

管理端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            192.168.1.25,192.168.1.26,192.168.1.27,192.168.0.28
#真实数据库IP，来检测网络是否正常
    auto_set_online     10  #恢复后自动设置在线的时间
</monitor>
<host default>
    monitor_user        mmm_monitor
    monitor_password    monitor
</host>
debug 0

启动MySQL-MMM

db代理端启动

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

monitor管理端启动

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

查看集群状态

[root@localhost ~]# mmm_control show

参考文章

《MMM 之 MySQL》

《从零开始配置 MySQL MMM》

【mysql】高可用集群之MMM

标签：