架構圖

環境規劃

角色	IP	VIP	用途
DB1	192.168.56.101	192.168.56.111/113	主Master提供wirte+read
DB2	192.168.56.102	192.168.56.112	備Master提供read
DB3&&Monitor Server	192.168.56.103	192.156.56.114	MMM監控節點&&MySQL slave節點

新增到MMM環境中

1) 配置DB3的Master為DB1

D> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.101

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master_slave.000026

Read_Master_Log_Pos: 411

Relay_Log_File: MySQL_Manager-relay-bin.000002

Relay_Log_Pos: 286

Relay_Master_Log_File: master_slave.000026

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

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: 411

Relay_Log_Space: 467

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: 1

Master_UUID: 64e874c1-6a35-11e4-bcc1-0800276fc92d

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

1 row in set (0.00 sec)

2) 建立MMM代理和監控賬號

#建立監控賬號

DB3> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.56.%' IDENTIFIED BY 'monitor_password';

Query OK, 0 rows affected (0.02 sec)

#建立代理賬號

DB3>GRANT SUPER,REPLICATION CLIENT,PROCESS ON *.* TO 'mmm_agent'@'192.168.56.%' IDENTIFIED BY 'agent_password';

Query OK, 0 rows affected (0.00 sec)

DB3>flush privileges;

Query OK, 0 rows affected (0.03 sec)

3) 新增DB3到MMM中

a) mmm_common.conf

~]# cat /etc/mysql-mmm/mmm_common.conf

active_master_role writer

cluster_interface eth1

pid_path /var/run/mmm_agentd.pid

bin_path /usr/lib/mysql-mmm/

replication_user slave

replication_password mysqlab

agent_user mmm_agent

agent_password agent_password

ip 192.168.56.101

mode master

peer db1

ip 192.168.56.102

mode master

peer db2

ip 192.168.56.103

mode slave

peer db3

hosts db1, db2

ips 192.168.56.113

mode exclusive

hosts db1, db2,db3

ips 192.168.56.111,192.168.56.112,192.168.56.114

mode balanced

所有Node節點都修改為一致

b) mmm_agent.conf

#DB3上編輯mmm_agent.conf

Monitor~]# cat /etc/mysql-mmm/mmm_agent.conf

include mmm_common.conf

this db3

c) mmm_mon.conf

~]# cat /etc/mysql-mmm/mmm_mon.conf

include mmm_common.conf

ip 127.0.0.1

pid_path /var/run/mmm_mond.pid

bin_path /usr/lib/mysql-mmm/

status_path /var/lib/misc/mmm_mond.status

ping_ips 192.168.56.101, 192.168.56.102,192.168.56.103

auto_set_online 60

monitor_user mmm_monitor

monitor_password monitor_password

debug 0

4) 啟動MMM代理

DB1、DB2、DB3所有節點上啟動agent服務

~]# /etc/init.d/mysql-mmm-agent start

Daemon bin: '/usr/sbin/mmm_agentd'

Daemon pid: '/var/run/mmm_agentd.pid'

Starting MMM Agent daemon... Ok

#在監控主機上

~]# /etc/init.d/mysql-mmm-monitor start

Daemon bin: '/usr/sbin/mmm_mond'

Daemon pid: '/var/run/mmm_mond.pid'

Starting MMM Monitor daemon: Ok

5) 檢視狀態

~]# mmm_control show

db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)

db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.114)

db3(192.168.56.103) slave/ONLINE. Roles: reader(192.168.56.112)

~]# mmm_control checks

db2 ping [last change: 2014/12/03 14:45:49] OK

db2 mysql [last change: 2014/12/03 15:16:30] OK

db2 rep_threads [last change: 2014/12/03 14:45:49] OK

db2 rep_backlog [last change: 2014/12/03 14:45:49] OK: Backlog is null

db3 ping [last change: 2014/12/03 14:45:49] OK

db3 mysql [last change: 2014/12/03 15:16:58] OK

db3 rep_threads [last change: 2014/12/03 14:45:49] OK

db3 rep_backlog [last change: 2014/12/03 14:45:49] OK: Backlog is null

db1 ping [last change: 2014/12/03 14:45:49] OK

db1 mysql [last change: 2014/12/03 14:45:49] OK

db1 rep_threads [last change: 2014/12/03 14:45:49] OK

db1 rep_backlog [last change: 2014/12/03 14:45:49] OK: Backlog is nulldb1 rep_threads [last change: 2014/12/03 14:45:49] OK

db1 rep_backlog [last change: 2014/12/03 14:45:49] OK: Backlog is null

6) 測試slave的Master轉移

#正常

~]# mmm_control show

db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.112), writer(192.168.56.113)

db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.114)

db3(192.168.56.103) slave/ONLINE. Roles: reader(192.168.56.111)

#103節點的master是101,接下來stop 101上的mysql在檢視

#看監控節點mond.log

2014/12/10 20:21:28 WARN Check 'rep_threads' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111

2014/12/10 20:21:33 WARN Check 'rep_backlog' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111

2014/12/10 20:21:42 ERROR Check 'mysql' on 'db1' has failed for 10 seconds! Message: ERROR: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111

2014/12/10 20:21:45 FATAL State of host 'db1' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)

2014/12/10 20:21:45 INFO Removing all roles from host 'db1':

2014/12/10 20:21:45 INFO Removed role 'reader(192.168.56.112)' from host 'db1'

2014/12/10 20:21:45 INFO Removed role 'writer(192.168.56.113)' from host 'db1'

2014/12/10 20:21:45 INFO Orphaned role 'writer(192.168.56.113)' has been assigned to 'db2'

2014/12/10 20:21:45 INFO Orphaned role 'reader(192.168.56.112)' has been assigned to 'db3'

#檢視103的master已經轉移為102

從而實現了slave的master轉移

MySQL主主複製+slave+MMM實現高可用（二）