首页 > 代码库 > Mysql MMM 高可用
Mysql MMM 高可用
一、Mysql MMM 高可用概况:
mmm_mond 负责所有的监控工作的监控守护进程，决定节点的移除等；
mmm_agentd 运行在mysql服务器上的代理守护进程，通过简单远程服务集提供给监控节点；
mmm_control 命令行管理mmm_mond进程；
Monitor:192.168.209.18
DB1:192.168.209.19
DB2:192.168.209.20
DB3:192.168.209.21
virtual write ip:192.168.209.4
virtual read ip:192.168.209.5
virtual read ip:192.168.209.6
实验所有密码都为123456
数据库为：test1
数据库用户为：repl
replication_user repl
agent_user mmm_agent
monitor_user mmm_monitor
架构:
DB1,DB2互为主从,DB3为DB1的从服务
此类架构,在DB1正常的时候,DB3是与DB1同步数据,在DB1的mysql服务不可用时,DB3将与DB2同步数据.
二、安装mysql ,参见:《以普通用户权限安装mysql》，http://www.cnblogs.com/krainbow/p/3894154.html
三、mysql主从配置及状态检查
安装好mysql之后，需要配置各服务之间的主从关系
3.1 编辑修改mysql配置文件my.cnf，如下：
Db1：
cat /etc/my.cnf | grep -v "#"
[mysqld]
log_bin=mysql-bin
server-id=19
replicate-do-db = test1
auto-increment-increment = 3
auto-increment-offset = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
Db2:
[mysqld]
log_bin=mysql-bin
server-id=20
replicate-do-db = test1
auto-increment-increment = 3
auto-increment-offset = 2
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
Db3：
[mysqld]
log_bin=mysql-bin
server_id=21
replicate-do-db = test1
auto-increment-increment = 3
auto-increment-offset = 3
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
3.2 配置主从关系：
db1和db2配置成主主模式，db3配置为db1的从，用户为repl
在db1上执行：
grant replication slave on *.* to repl@‘192.168.209.%‘ identified by "123456";
flush privileges;
db2上执行：
grant replication slave on *.* to repl@‘192.168.209.%‘ identified by "123456";
flush privileges;
db3上执行：
grant replication slave on *.* to repl@‘192.168.209.%‘ identified by "123456";
flush privileges;
配置db2和db3都与db1数据同步，在db2和db3上面执行：
change master to master_host=‘192.168.209.19‘, master_port=3306, master_user=‘repl‘, master_password=‘123456‘;
start slave;
配置db1为db2的从库，在db1上面执行：
change master to master_host=‘192.168.209.20‘, master_port=3306, master_user=‘repl‘, master_password=‘123456‘;
start slave;
以上均为简略写法，只有关键部分，详细的授权和主从配置命令，需要根据实际情况进行修改。
3.3 在主从没有问题之后，需要检查三台db服务器的slave状态是否正确。
DB1
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.209.20
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 444
Relay_Log_File: cdn19-relay-bin.000024
Relay_Log_Pos: 607
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1
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: 444
Relay_Log_Space: 780
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: 20
Master_UUID: 318ae305-235a-11e4-aeb7-c81f66ec516e
Master_Info_File: /mysql/mysql-5.6/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.209.19
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 120
Relay_Log_File: cdn20-relay-bin.000023
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1
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: 619
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: 19
Master_UUID: 2f276e4a-235a-11e4-aeb7-b8ca3af2484f
Master_Info_File: /mysql/mysql-5.6/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.209.19
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 120
Relay_Log_File: cdn21-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1
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: 456
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: 19
Master_UUID: 2f276e4a-235a-11e4-aeb7-b8ca3af2484f
Master_Info_File: /mysql/mysql-5.6/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)
四、安装配置mysql-mmm软件
4.1 软件安装
monitor
yum install -y mysql-mmm*
all db
yum install -y mysql-mmm-agent*
4.2 数据库授权
all db
grant super, replication client, process on *.* to ‘mmm_agent‘@‘192.168.209.%‘ identified by ‘123456‘;
grant replication client on *.* to ‘mmm_monitor‘@‘192.168.209.%‘ identified by ‘123456‘;
flush privileges;
4.3 配置文件修改
all 服务器
四台服务器完全一致的配置
[root@cdn18 ~]# vi /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 repl
replication_password 123456
agent_user mmm_agent
agent_password 123456
</host>
<host db1>
ip 192.168.209.19
mode master
peer db2
</host>
<host db2>
ip 192.168.209.20
mode master
peer db1
</host>
<host db3>
ip 192.168.209.21
mode slave
peer db3
</host>
<role writer>
hosts db1, db2
ips 192.168.209.4
mode exclusive
</role>
<role reader>
hosts db3
ips 192.168.209.5,192.168.209.6
mode balanced
</role>
peer的表示等同，表示db1与db2同等。
ips指定VIP
mode有两种模式：exclusive排他，此模式下任何时候只能一个host拥有该角色
balanced模式可以有多个host同时拥有此角色。一般writer是exclusive，reader是balanced
修改每台db的agent配置，标注对应的数据库id
vim /etc/mysql-mmm/mmm_agent.conf
db1，db2，db3对应修改为：
this db1、this db2、this db3
形如：
[root@cdn19 ~]# cat /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
# The ‘this‘ variable refers to this server. Proper operation requires
# that ‘this‘ server (db1 by default), as well as all other servers, have the
# proper IP addresses set in mmm_common.conf.
this db1
修改monitor服务器配置
[root@cdn18 ~]# cat /etc/mysql-mmm/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.209.19,192.168.209.20
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 123456
</host>
debug 0
六、启动服务
6.1 启动agent服务
db服务器：/etc/init.d/mysql-mmm-agent restart
6.2 启动monitor服务
monitor服务器：/etc/init.d/mysql-mmm-monitor restart
6.3 状态检查
monitor服务器
[root@cdn18 ~]# mmm_control checks all
db2 ping [last change: 2014/08/19 13:50:08] OK
db2 mysql [last change: 2014/08/19 13:51:36] OK
db2 rep_threads [last change: 2014/08/19 13:51:30] OK
db2 rep_backlog [last change: 2014/08/19 13:51:36] OK: Backlog is null
db3 ping [last change: 2014/08/19 13:50:08] OK
db3 mysql [last change: 2014/08/19 13:50:08] OK
db3 rep_threads [last change: 2014/08/19 13:50:08] OK
db3 rep_backlog [last change: 2014/08/19 13:50:08] OK: Backlog is null
db1 ping [last change: 2014/08/19 13:50:08] OK
db1 mysql [last change: 2014/08/19 13:50:08] OK
db1 rep_threads [last change: 2014/08/19 13:50:08] OK
db1 rep_backlog [last change: 2014/08/19 13:50:08] OK: Backlog is null
[root@cdn18 ~]# mmm_control show
db1(192.168.209.19) master/ONLINE. Roles: writer(192.168.209.4)
db2(192.168.209.20) master/ONLINE. Roles:
db3(192.168.209.21) slave/ONLINE. Roles: reader(192.168.209.5), reader(192.168.209.6)
6.4 vip切换
停掉db1，看192.168.209.4是否会切换到192.168.209.20（db2）服务器，同时，192.168.209.21（db3）的mysql是否会将master服务器切换为192.168.209.20（db2）
登录db1
执行：service mysql stop
停止db1的mysql服务
在monitor服务器执行：
[root@cdn18 ~]# mmm_control show
db1(192.168.209.19) master/HARD_OFFLINE. Roles:
db2(192.168.209.20) master/ONLINE. Roles: writer(192.168.209.4)
db3(192.168.209.21) slave/ONLINE. Roles: reader(192.168.209.5), reader(192.168.209.6)
[root@cdn18 ~]# mmm_control checks all
db2 ping [last change: 2014/08/19 13:50:08] OK
db2 mysql [last change: 2014/08/19 13:51:36] OK
db2 rep_threads [last change: 2014/08/19 13:51:30] OK
db2 rep_backlog [last change: 2014/08/19 13:51:36] OK: Backlog is null
db3 ping [last change: 2014/08/19 13:50:08] OK
db3 mysql [last change: 2014/08/19 13:50:08] OK
db3 rep_threads [last change: 2014/08/19 13:50:08] OK
db3 rep_backlog [last change: 2014/08/19 13:50:08] OK: Backlog is null
db1 ping [last change: 2014/08/19 13:50:08] OK
db1 mysql [last change: 2014/08/19 14:06:58] ERROR: Connect error (host = 192.168.209.19:3306, user = mmm_monitor)! Lost connection to MySQL server at ‘reading initial communication packet‘, system error: 111
db1 rep_threads [last change: 2014/08/19 13:50:08] OK
db1 rep_backlog [last change: 2014/08/19 13:50:08] OK: Backlog is null
在db3执行，可以发现master_host已经切换到db2了
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.209.20
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 120
Relay_Log_File: cdn21-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1
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: 456
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: 20
Master_UUID: 318ae305-235a-11e4-aeb7-c81f66ec516e
Master_Info_File: /mysql/mysql-5.6/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)