3.1 配置mysql-master-1/2主库(主主同步)
4.3 所有主机配置/etc/mysql-mmm/mmm_common.conf
4.4.数据库主机配置/etc/mysql-mmm/mmm_agent.conf
4.5 monitor主机配置/etc/mysql-mmm/mmm_mon.conf
一.mysql-mmm简介
MMM即Master-Master Replication Manager for MySQL(mysql主主复制管理器)关于mysql主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件(在任何时候只有一个节点可以被写入),这个套件也能对居于标准的主从配置的任意数量的从服务器进行读负载均衡,所以你可以用它来在一组居于复制的服务器启动虚拟ip,除此之外,它还有实现数据备份、节点之间重新同步功能的脚本。
MySQL本身没有提供replication failover的解决方案,通过MMM方案能实现服务器的故障转移,从而实现mysql的高可用。MMM项目来自 Google:http://code.google.com/p/mysql-master-master
官方网站为:http://mysql-mmm.org
mysql-mmm主要功能由下面三个脚本提供
mmm_mond 负责所有的监控工作的监控守护进程,决定节点的移除等等
mmm_agentd 运行在mysql服务器上的代理守护进程,通过简单远程服务集提供给监控节点
mmm_control 通过命令行管理mmm_mond进程
(以上来自来自网友飞鸿无痕)
mysql-mmm的监管端会提供多个虚拟IP(VIP),包括一个可写VIP,多个可读VIP,通过监管的管理,这些IP会绑定在可用mysql之上,当某一台mysql宕机时,监管会将VIP迁移至其他mysql。
在整个监管过程中,需要在mysql中添加相关授权用户,以便让mysql可以支持监理机的维护。授权的用户包括一个mmm_monitor用户和一个mmm_agent用户,如果想使用mmm的备份工具则还要添加一个mmm_tools用户。
优点:高可用性,扩展性好,出现故障自动切换,对于主主同步,在同一时间只提供一台数据库写操作,保证的数据的一致性。
缺点:Monitor节点是单点,可以结合Keepalived实现高可用。
(以上来自来自网友going)
部署开始,由于机器资源有限,这里的实验,slave就用一台了。
vmvare虚拟机:4台
系统版本:CentOS release 6.4 (Final) 2.6.32-358.el6.x86_64
mysql版本:5.5.32
mysql-mmm版本:
4台虚拟机信息:
hostname | ip | my.cnf -serverid | dbname |
mysql-monitor | 192.168.1.10 | - | - |
mysql-master-1 | 192.168.1.11 | 1 | db1 |
mysql-master-2 | 192.168.1.12 | 2 | db2 |
mysql-slave | 192.168.1.13 | 3 | db3 |
虚拟IP:
VIP | role | description |
192.168.1.50 | writer | 你的应用程序应该连接到这个ip进行写操作 |
192.168.1.51 | reader | 你的应用程序连接到这些ip中的一个进行读操作 |
192.168.1.52 | reader | |
192.168.1.53 | reader |
①mysql-master-1,mysql-master-2两台安装mysql,并做主主的配置(互为主从)
②mysql-slave上安装mysql,并配置作为mysql-master-1的从服务器。
③mysql-master-1/2,mysql-slave,mysql-monitor这四台都要安装mysql-mmm,并配置:mmm_common.conf、mmm_agent.conf以及mmm_mon.conf文件
注:mysql的安装及主从的配置参考地址
http://chocolee.blog.51cto.com/8158455/1415173
3.1 配置mysql-master-1/2主库(主主同步)
注:我所有的mysql都是新安装的,所以没有任何数据,环境相同。
①修改my.cnf然后重启服务
mysql-master-1:
vi /etc/my.cnf
[mysqld]
server-id = 1
log-bin=mysql-bin
log-slave-updates
auto_increment_offset=1
auto_increment_increment=2
mysql-master-2:
vi /etc/my.cnf
[mysqld]
server-id = 2
log-bin=mysql-bin
log-slave-updates
auto_increment_offset=2
auto_increment_increment=2
②master-1和2端添加同步用户
mysql> grant replication slave on *.* to 'rep'@'192.168.1.%' identified by '123456';
mysql> show grants for 'rep'@'192.168.1.%';
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
③master-2端配置同步参数
CHANGE MASTER TO
MASTER_HOST='192.168.1.11',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;
############################################################
mysql> start slave;
mysql> system mysql -e "show slave status\G" |egrep "Master_Host:|Slave_IO_Running:|Slave_SQL_Running:|Seconds_Behind_Master:"
Master_Host: 192.168.1.11
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
③master-1端配置同步参数
CHANGE MASTER TO
MASTER_HOST='192.168.1.12',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;
##################################################
mysql> start slave;
mysql> system mysql -e "show slave status\G" |egrep "Master_Host:|Slave_IO_Running:|Slave_SQL_Running:|Seconds_Behind_Master:"
Master_Host: 192.168.1.12
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
⑤测试(略)
①修改my.cnf并重启服务
vi /etc/my.cnf
[mysqld]
server-id = 3
/etc/init.d/mysqld restart
①配置同步参数
先去主库show master status,查看下bin-log文件名及位置点
CHANGE MASTER TO
MASTER_HOST='192.168.1.11',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1139;
#########################################
mysql> start slave;
mysql> system mysql -e "show slave status\G" |egrep "Master_Host:|Slave_IO_Running:|Slave_SQL_Running:|Seconds_Behind_Master:"
Master_Host: 192.168.1.11
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
注:需要在这四台server上都安装mysql-mmm
CentOS软件仓库默认是不含这些软件的,必须要有epel这个包的支持。所以我们必须先安装epel。
wget http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm
rpm -Uvh epel-release-6-8.noarch.rpm
yum install -y mysql-mmm*
由于环境已经配置好,我没有配置忽略mysql库和user表,所以只要在任意一台主库上执行下面的操作,其他的库就都有这俩账号了。
GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.1.%' IDENTIFIED BY 'monitor_password';
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.1.%' IDENTIFIED BY 'agent_password';
flush privileges;
4.3 所有主机配置/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 rep #同步用户
replication_password 123456 #同步密码
agent_user mmm_agent #mmm_agent用户
agent_password agent_password #mmm_agent用户的密码
</host>
<host db1>
ip 192.168.1.11 #db1 master-1的ip
mode master
peer db2
</host>
<host db2>
ip 192.168.1.12 #db2 master-2的ip
mode master
peer db1
</host>
<host db3>
ip 192.168.1.13 #db3 slave的ip
mode slave
</host>
<role writer>
hosts db1, db2
ips 192.168.1.50 #wrtie的VIP
mode exclusive
</role>
<role reader>
hosts db1, db2, db3
ips 192.168.1.51, 192.168.1.52, 192.168.1.53 #read的VIP
mode balanced
</role>
4.4.数据库主机配置/etc/mysql-mmm/mmm_agent.conf
根据其他主机的不同更改db1的值(db2就将db1更改成db2…….)
[root@mysql-master-1 mysql-mmm]# vim 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
4.5 monitor主机配置/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.1.11, 192.168.1.12, 192.168.1.13 #监控的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 #mmm_monitor 用户
monitor_password monitor_password #mmm_monitor 密码
</host>
debug 0
master-1,master-2启动代理:
编辑/etc/default/mysql-mmm-agent来开启
[root@mysql-master-1 mysql-mmm]# vim /etc/default/mysql-mmm-agent
# mysql-mmm-agent defaults
ENABLED=1
所有数据库主机启动mmm-agent:
/etc/init.d/mysql-mmm-agent start
monitor主机启动mmm-monitor
/etc/init.d/mysql-mmm-monitor start
[root@mysql-monitor mysql-mmm]# mmm_control show
db1(192.168.1.11) master/ONLINE. Roles: reader(192.168.1.52), writer(192.168.1.50)
db2(192.168.1.12) master/ONLINE. Roles: reader(192.168.1.51)
db3(192.168.1.13) slave/ONLINE. Roles: reader(192.168.1.53)
master-1:
将db1的mysql服务停止
[root@mysql-master-1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!
monitor:
[root@mysql-monitor mysql-mmm]# mmm_control show
db1(192.168.1.11) master/HARD_OFFLINE. Roles:
db2(192.168.1.12) master/ONLINE. Roles: reader(192.168.1.51), writer(192.168.1.50)
db3(192.168.1.13) slave/ONLINE. Roles: reader(192.168.1.52), reader(192.168.1.53)
slave检查master_host是否切换到了另一个主库地址:
[root@mysql-slave ~]# mysql -e "show slave status\G"|egrep "Master_Host:"
Master_Host: 192.168.1.12
恢复master-1(db1)
[root@mysql-master-1 ~]# /etc/init.d/mysqld start
Starting MySQL... SUCCESS!
monitor端检查db1的恢复情况
[root@mysql-monitor mysql-mmm]# mmm_control show
db1(192.168.1.11) master/AWAITING_RECOVERY. Roles:
db2(192.168.1.12) master/ONLINE. Roles: reader(192.168.1.51), writer(192.168.1.50)
db3(192.168.1.13) slave/ONLINE. Roles: reader(192.168.1.52), reader(192.168.1.53)
[root@mysql-monitor mysql-mmm]# mmm_control show
db1(192.168.1.11) master/ONLINE. Roles: reader(192.168.1.53)
db2(192.168.1.12) master/ONLINE. Roles: reader(192.168.1.51), writer(192.168.1.50)
db3(192.168.1.13) slave/ONLINE. Roles: reader(192.168.1.52)
需要等一会儿(默认60秒,auto_set_online的设置),就看到之前宕机的master起来了。我们可以看到当db1恢复后就充当slave的角色了!只有当db2挂了以后db1又会担当起主服务器的写入功能。
Valid commands are:
help - show this message
#查看帮助信息
ping - ping monitor
#ping监控
show - show status
#查看状态信息
checks [<host>|all [<check>|all]] - show checks status
#显示检查状态,包括(ping、mysql、rep_threads、rep_backlog)
set_online <host> - set host <host> online
#设置某host为online状态
set_offline <host> - set host <host> offline
#设置某host为offline状态
mode - print current mode.
#打印当前的模式,是ACTIVE、MANUAL、PASSIVE?
#默认ACTIVE模式
set_active - switch into active mode.
#更改为active模式
set_manual - switch into manual mode.
#更改为manual模式
set_passive - switch into passive mode.
#更改为passive模式
move_role [--force] <role> <host> - move exclusive role <role> to host <host>
#更改host的模式,比如更改处于slave的mysql数据库角色为write
(Only use --force if you know what you are doing!)
set_ip <ip> <host> - set role with ip <ip> to host <host>
#为host设置ip,只有passive模式的时候才允许更改!
本文出自 “Chocolee” 博客,请务必保留此出处http://chocolee.blog.51cto.com/8158455/1418506