mysql mmm安裝管理詳解

標籤：mmm

機器簡介：

monitor192.168.146.131監控伺服器

master-001192.168.146.129讀寫主機001

master-002192.168.146.130讀寫主機002

slave-001192.168.146.131 唯讀主機001

虛擬IP分配

master-001 192.168.146.135writer

master-002 192.168.146.136reader

slave-001  192.168.146.137reader

三台相同操作：

關閉防火牆

一，安裝mysql

二，mysql 許可權賦值

GRANT REPLICATION SLAVE ON *.* TO ‘myrepl‘@‘192.168.146.%‘ IDENTIFIED BY ‘123456‘;

GRANT REPLICATION CLIENT                 ON *.* TO ‘mmm_monitor‘@‘192.168.146.%‘ IDENTIFIED BY ‘monitor‘;

GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO ‘mmm_agent‘@‘192.168.146.%‘   IDENTIFIED BY ‘agent‘;

二，安裝MMM

安裝mysql-MMM

 安裝依賴包：

 yum install epel-release

 yum install  perl-Log-Log4perl-Appender-Socket-UNIX

 rpm 安裝

 rpm -ivh mysql-mmm-2.2.1-2.el6.noarch.rpm 

安裝mysql-agent

 安裝依賴包：

 yum install -y perl-Algorithm-Diff perl-Class-Singleton perl-Proc-Daemon perl-Net-ARP perl-DBD-mysql

 rpm 安裝

 rpm -ivh mysql-mmm-agent-2.2.1-2.el6.noarch.rpm 

chkconfig mysql-mmm-agent on

（131上）monitor 機器上安裝monitor包

 rpm -ivh mysql-mmm-monitor-2.2.1-2.el6.noarch.rpm 

 

三.配置MMM

 修改my.cnf

  db1 

server-id= 1

gtid_mode= on

log_slave_updates=1           #當一個主故障，另一個立即接管

enforce_gtid_consistency= 1

auto-increment-increment = 2

auto-increment-offset = 1

replicate-ignore-db=mysql,information_schema #不同步的資料庫，多個寫多行

  db2

server-id= 2

gtid_mode= on

log_slave_updates=1           #當一個主故障，另一個立即接管

enforce_gtid_consistency= 1

auto-increment-increment = 2

auto-increment-offset = 2

replicate-ignore-db=mysql,information_schema #不同步的資料庫，多個寫多行

  db3

server-id= 3

gtid_mode= on

log_slave_updates=1           #當一個主故障，另一個立即接管

enforce_gtid_consistency= 1

replicate-ignore-db=mysql,information_schema #不同步的資料庫，多個寫多行

read_only=1 

配置主從

 130 和131 上操作

 CHANGE MASTER TO MASTER_HOST=‘192.168.146.129‘,MASTER_USER=‘myrepl‘,MASTER_PASSWORD=‘123456‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘mysql-bin.000022‘, MASTER_CONNECT_RETRY=311;

 start slave;

 129上操作

 130 和131 上操作

 CHANGE MASTER TO MASTER_HOST=‘192.168.146.130‘,MASTER_USER=‘myrepl‘,MASTER_PASSWORD=‘123456‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘mysql-bin.000032‘, MASTER_CONNECT_RETRY=234;

 start slave;

配置/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        myrepl

    replication_password    123456

    agent_user              mmm_agent

    agent_password          agent

</host>

<host db1>

    ip      192.168.146.129

    mode    master

    peer    db2

</host>

<host db2>

    ip      192.168.146.130

    mode    master

    peer    db1

</host>

<host db3>

    ip      192.168.146.131

    mode    slave

</host>

<role writer>

    hosts   db1, db2

    ips     192.168.146.135

    mode    exclusive

</role>

<role reader>

    hosts   db1, db2, db3

    ips     192.168.146.136, 192.168.146.137, 192.168.146.138

    mode    balanced

</role>

配置/etc/mysql-mmm/mmm_agent.conf

this db1 #是誰就寫誰

管理機上配置 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.146.129,192.168.146.130  #其他機器IP

    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

</host>

debug 0

加入自啟動：

chkconfig mysql-mmm-monitor on

chkconfig mysql-mmm-agent on

開啟服務：

 啟動 mmm_agent 

 service mysql-mmm-agent start

 啟動 mmm_monitor

 service mysql-mmm-monitor start

   

 管理命令

 [[email protected] mysql-mmm]# mmm_control --help

Invalid command ‘--help‘

Valid commands are:

    help                              - show this message

    ping                              - ping monitor

    show                              - show status

    checks [<host>|all [<check>|all]] - show checks status

    set_online <host>                 - set host <host> online

    set_offline <host>                - set host <host> offline

    mode                              - print current mode.

    set_active                        - switch into active mode.

    set_manual                        - switch into manual mode.

    set_passive                       - switch into passive mode.

    move_role [--force] <role> <host> - move exclusive role <role> to host <host>

                                        (Only use --force if you know what you are doing!)

    set_ip <ip> <host>                - set role with ip <ip> to host <host>

[[email protected] mysql-mmm]# mmm_control show

  db1(192.168.146.129) master/ONLINE. Roles: reader(192.168.146.138), writer(192.168.146.135)

  db2(192.168.146.130) master/ONLINE. Roles: reader(192.168.146.137)

  db3(192.168.146.131) slave/ONLINE. Roles: reader(192.168.146.136

  

 [[email protected] mysql-mmm]# mmm_control checks all

db2  ping         [last change: 2016/11/10 19:18:54]  OK

db2  mysql        [last change: 2016/11/10 19:18:54]  OK

db2  rep_threads  [last change: 2016/11/10 19:18:54]  OK

db2  rep_backlog  [last change: 2016/11/10 19:18:54]  OK: Backlog is null

db3  ping         [last change: 2016/11/10 19:18:54]  OK

db3  mysql        [last change: 2016/11/10 19:19:17]  OK

db3  rep_threads  [last change: 2016/11/10 19:19:17]  OK

db3  rep_backlog  [last change: 2016/11/10 19:19:17]  OK: Backlog is null

db1  ping         [last change: 2016/11/10 19:18:54]  OK

db1  mysql        [last change: 2016/11/10 19:18:54]  OK

db1  rep_threads  [last change: 2016/11/10 19:18:54]  OK

db1  rep_backlog  [last change: 2016/11/10 19:18:54]  OK: Backlog is null

測試轉換:

DB1上操作：

[[email protected] mysql-mmm]# mysqladmin -uroot -p -S /tmp/mysql.sock shutdown

Enter password: 

管理機器上查看：

Bye

[[email protected] mysql-mmm]# mmm_control checks all

db2  ping         [last change: 2016/11/10 19:18:54]  OK

db2  mysql        [last change: 2016/11/10 19:18:54]  OK

db2  rep_threads  [last change: 2016/11/10 19:18:54]  OK

db2  rep_backlog  [last change: 2016/11/10 19:18:54]  OK: Backlog is null

db3  ping         [last change: 2016/11/10 19:18:54]  OK

db3  mysql        [last change: 2016/11/10 19:39:20]  OK

db3  rep_threads  [last change: 2016/11/10 19:39:20]  OK

db3  rep_backlog  [last change: 2016/11/10 19:19:17]  OK: Backlog is null

db1  ping         [last change: 2016/11/10 19:18:54]  OK

db1  mysql        [last change: 2016/11/10 19:31:34]  ERROR: Connect error (host = 192.168.146.129:3306, user = mmm_monitor)! Lost connection to MySQL server at ‘reading initial communication packet‘, system error: 111

db1  rep_threads  [last change: 2016/11/10 19:18:54]  OK

db1  rep_backlog  [last change: 2016/11/10 19:18:54]  OK: Backlog is null

[[email protected] mysql-mmm]# mmm_control show

  db1(192.168.146.129) master/HARD_OFFLINE. Roles: 

  db2(192.168.146.130) master/ONLINE. Roles: reader(192.168.146.137), writer(192.168.146.135)

  db3(192.168.146.131) slave/ONLINE. Roles: reader(192.168.146.136), reader(192.168.146.138)

啟動DB1 等待60s再看:

[[email protected] mysql-mmm]# mmm_control show

  db1(192.168.146.129) master/ONLINE. Roles: reader(192.168.146.136)

  db2(192.168.146.130) master/ONLINE. Roles: reader(192.168.146.137), writer(192.168.146.135)

  db3(192.168.146.131) slave/ONLINE. Roles: reader(192.168.146.138)

我們想把writer角色切回到db1上：

[[email protected] mysql-mmm]# mmm_control move_role writer db1

OK: Role ‘writer‘ has been moved from ‘db2‘ to ‘db1‘. Now you can wait some time and check new roles info!

[[email protected] mysql-mmm]# mmm_control show

  db1(192.168.146.129) master/ONLINE. Roles: reader(192.168.146.136), writer(192.168.146.135)

  db2(192.168.146.130) master/ONLINE. Roles: reader(192.168.146.137)

  db3(192.168.146.131) slave/ONLINE. Roles: reader(192.168.146.138)

  

  

遇到兩個問題

 1. 監控程式伺服器無法啟動

   在管理伺服器上，一切都完成後，通過mmm_control查看狀態，得到下面的錯誤資訊：

   ERROR: Cant connect to monitor daemon! 通過編輯/etc/mysql-mmm/mmm_mon.conf檔案將debug 0改為debug 1，開啟監控程式的debug狀態。重新啟動監控程式（service mysql-mmm-monitor restart），就會看到詳細的錯誤資訊，找不到Perl Time HiRes庫。執行yum -y install perl-Time-HiRes*就可以解決。   

 2. 防火牆問題導致Warning: agent on host db1 is not reachable.

 

    控制台程式正確啟動後，再次執行mmm_control show，卻看到下面的輸出：

# Warning: agent on host db1 is not reachable

# Warning: agent on host db2 is not reachable

  db1(192.168.146.129) master/ONLINE. Roles: 

  db2(192.168.146.130) master/ONLINE. Roles: 

  db3(192.168.146.131) slave/ONLINE. Roles: reader(192.168.146.138)

再次開啟debug，查看錯誤資訊

  通過telnet 192.168.146.130 9989下面檢查網路連接，得到了No route to host的錯誤資訊。登入db1，關閉iptables（這不是一個好主意）。同樣，關閉db2、db3上的防火牆，再次重啟監控程式，一切回到正常狀態！

mysql mmm安裝管理詳解