mysql-mmm_MySQL

一、关系图

二、安装mysql-mmm

1)安装epel

相关文档参见：http://fedoraproject.org/wiki/EPEL

rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-4.noarch.rpm

2) monitor软件安装

[root@localhost ~]# yum install –y mysql-mmm-monitor-*

包如下：

[root@localhost ~]# rpm -qa|grep mysql

mysql-mmm-monitor-2.2.1-1.el5

mysql-mmm-2.2.1-1.el5

mysql-5.0.77-4.el5_5.4

3）数据库所在机器安装(包括master,slave)

[root@localhost ~]# yum install –y mysql-mmm-agent*

包如下：

[root@localhost ~]# rpm -qa|grep mysql

mysql-test-5.0.77-4.el5_5.4

mysql-mmm-agent-2.2.1-1.el5

mysql-5.0.77-4.el5_5.4

mysql-server-5.0.77-4.el5_5.4

mysql-devel-5.0.77-4.el5_5.4

mysql-bench-5.0.77-4.el5_5.4

mysql-mmm-2.2.1-1.el5

mysql-connector-odbc-3.51.26r1127-1.el5

三、配置文件

1)monitor配置文件

[root@localhost ~]# vi /etc/mysql-mmm/mmm_mon.conf

include mmm_common.conf

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 172.16.71.128,172.16.71.130 #master,slave实际数据库ip

auto_set_online 60

monitor_user mmm_monitor

monitor_password 10jqka

Debug

2）通用的配置文件，在monitor及master,slave上放置的这个文件都一致

[root@localhost ~]# vi /etc/mysql-mmm/mmm_common.conf

active_master_role writer

cluster_interface eth0

pid_path /var/run/mysql-mmm/mmm_agentd.pid

bin_path /usr/libexec/mysql-mmm/

replication_user replicant

replication_password 10jqka

agent_user mmm_agent

agent_password 10jqka

ip 172.16.71.128 #真实master数据库ip

mode master

peer db2

ip 172.16.71.130 #真实master数据库ip

mode master

peer db1

#

# ip 192.168.100.51 #真实slave数据库ip

# mode slave

#

hosts db1, db2

ips 172.16.71.253 #虚拟写数据库ip

mode exclusive

hosts db1, db2

ips 172.16.71.251,172.16.71.252 #虚拟读数据库ip

mode balanced

3）master,slave配置文件

[root@localhost ~]# vi /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#如为第一台master db则为db1如为第二台则为db2与mmm_common.conf里的机器对应

三、查看效果

1）启动db1的相关服务

service mysqld start

service mysql-mmm-agent start

数据库授权：所有数据库的授权一致

GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'172.16.71.%' IDENTIFIED BY '10jqka';

GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'172.16.71.%' IDENTIFIED BY '10jqka';

grant all on *.* to replicant@'172.16.71.%' identified by '10jqka';

flush privileges;

2）启动monitor相关服务

service mysql-mmm-monitor start

查看状态：

mmm_control show

把db1设置为online状态：

mmm_control set_online db1

开放mmm_agentd端口9989

真实机器查看分配的vip地址

[root@localhost ~]# ip a

1: lo: mtu 16436 qdisc noqueue

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

inet 127.0.0.1/8 scope host lo

inet6 ::1/128 scope host

valid_lft forever preferred_lft forever

2: eth0: mtu 1500 qdisc pfifo_fast qlen 1000

link/ether 00:0c:29:ef:79:70 brd ff:ff:ff:ff:ff:ff

inet 172.16.71.132/24 brd 172.16.71.255 scope global eth0

inet 172.16.71.251/32 scope global eth0

inet 172.16.71.253/32 scope global eth0

inet6 fe80::20c:29ff:feef:7970/64 scope link

valid_lft forever preferred_lft forever

四、主主（master-master）同步配置

1）172.16.71.132机器my.cnf

skip-name-resolve

server_id = 132

set-variable = key_buffer_size=512M

set-variable = table_cache=32

set-variable = sort_buffer_size=32M

set-variable = read_buffer_size=32M

set-variable = max_connections=2100

set-variable = log_slow_queries=/var/lib/mysql/log/mysql-slow.log

set-variable = log_bin=/var/lib/mysql/log/mysql-bin

set-variable = long_query_time=1

set-variable = thread_concurrency=16

set-variable = tmp_table_size=4000000000

set-variable = slave_skip_errors=all

#replicate-ignore-db=mysql

#replicate-ignore-db=test 发觉加上这个ignore选项后会导致mmm接管出现点小问题不知道为什么

log-slave-updates

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1

进入mysql配置设置为从

mysql> change master to master_host='172.16.71.128';

mysql>change master to master_user='repl';

mysql>change master to master_password='10jqka';

mysql>start slave

主（master）服务器配置：

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.16.71.%' IDENTIFIED BY '10jqka';

2）172.16.71.128机器my.cnf配置

skip-name-resolve

server_id = 1

set-variable = key_buffer_size=512M

set-variable = table_cache=32

set-variable = sort_buffer_size=32M

set-variable = read_buffer_size=32M

set-variable = max_connections=2100

set-variable = log_slow_queries=/var/lib/mysql/log/mysql-slow.log

set-variable = log_bin=/var/lib/mysql/log/mysql-bin

set-variable = long_query_time=1

set-variable = thread_concurrency=16

set-variable = tmp_table_size=4000000000

set-variable = slave_skip_errors=all

#replicate-ignore-db=mysql

#replicate-ignore-db=test

log-slave-updates

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=2

进入mysql配置设置为从

mysql> change master to master_host='172.16.71.132';

mysql>change master to master_user='repl';

mysql>change master to master_password='10jqka';

mysql>start slave

主（master）服务器配置：

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.16.71.%' IDENTIFIED BY '10jqka';

五、其它从(slave)服务器配置：

my.cnf配置

skip-name-resolve

server_id = 1

set-variable = key_buffer_size=512M

set-variable = table_cache=32

set-variable = sort_buffer_size=32M

set-variable = read_buffer_size=32M

set-variable = max_connections=2100

set-variable = log_slow_queries=/var/lib/mysql/log/mysql-slow.log

set-variable = log_bin=/var/lib/mysql/log/mysql-bin

set-variable = long_query_time=1

set-variable = thread_concurrency=16

set-variable = tmp_table_size=4000000000

set-variable = slave_skip_errors=all

mysql>change master to master_host='172.16.71.253';

mysql>change master to master_user='repl';

mysql>change master to master_password='10jqka'; mysql>change master to master_log_file=’mysql-bin.00001’;

mysql>change master to master_log_pos=128; mysql>start slave

或者在my.cnf加入

server-id=2

master-host=172.16.71.253

master-user=repl

master-password=10jqka

问题1：

两台机器互为对方的主，此时不能使用master的VIP作为该两机器的主，如果使用VIP作为master的话没有一台先起来作为vip机器的话些会导致monitor不能使用，日志如下：

2010/12/06 16:29:46 WARN db1 is not replicating from db2

2010/12/06 16:29:46 WARN db2 is not replicating from db1

问题2：

当/etc/mysql-mmm/mmm_mon.conf的auto_set_online 3时间设置为较长时，在瞬间关闭现有主，开启现未开启的备时，会导致monitor不能自动切换VIP导致mmm不可用，需要手工设置online状态，有时候甚至会导致monitor启动问题。为防止这情况发生可以把该时间设置短些，或者先让一其中的一台主起来后，再关闭另一台。

问题3：ERROR：Can't connect to monitor daemon

查看进程一般少了perl相关进程，莫名其妙的感觉，经多次尝试试验，一般都是在mysql那些拥有agent机器上的配置问题，还有monitor的auto_set_online时间设置

[root@localhost ~]# ps axu|grep mmm

root 3113 0.0 7.6 14000 8956 ? S 16:27 0:00 mmm_mond

root 3115 0.9 33.2 106440 38976 ? Sl 16:27 0:00 mmm_mond

root 3136 0.4 5.5 10388 6456 ? S 16:28 0:00 perl /usr/libexec/mysql-mmm/monitor/checker ping_ip

root 3140 0.4 6.5 15208 7640 ? S 16:28 0:00 perl /usr/libexec/mysql-mmm/monitor/checker mysql

root 3141 0.3 5.4 10388 6448 ? S 16:28 0:00 perl /usr/libexec/mysql-mmm/monitor/checker ping

root 3142 0.5 6.5 15208 7636 ? S 16:28 0:00 perl /usr/libexec/mysql-mmm/monitor/checker rep_backlog

root 3144 0.6 6.4 15208 7592 ? S 16:28 0:00 perl /usr/libexec/mysql-mmm/monitor/checker rep_threads

root 3148 0.0 0.5 3916 652 pts/0 R+ 16:28 0:00 grep mmm

六、其它补充资料

主从状态查看：

查看master的状态

SHOW MASTER STATUS;

Position不应为0

查看slave的状态

show slave status;

Slave_IO_Running | Slave_SQL_Running这两个字段 应为YES|YES.

show processlist;查看进程

slave start; #启动从复制

STOP SLAVE; #停止从复制

RESET MASTER; #重设master日志

RESET SLAVE;#重设slave日志

当Slave_IO_Running: 为No时，从服务器无法同步时操作下如

SLAVE STOP;#停止slave

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;

#把master_log_file设置为从主show master status;得出来的日志名，POS设置为0

SLAVE START;#再次启用slave

Mysql普通主从配置（未使用mmm参考）

配置主从my.cnf文件：

skip-innodb

skip-name-resolve

server_id = 132

set-variable = key_buffer_size=512M

set-variable = table_cache=32

set-variable = sort_buffer_size=32M

set-variable = read_buffer_size=32M

set-variable = max_connections=2100

set-variable = log_slow_queries=/var/lib/mysql/log/mysql-slow.log

set-variable = log_bin=/var/lib/mysql/log/mysql-bin#从库这条语句注释掉

set-variable = long_query_time=1

set-variable = thread_concurrency=16

set-variable = tmp_table_size=4000000000

set-variable = slave_skip_errors=all

因为mysql版本不同在从服务器下开启了下面选项导致同步不能进行

replicate-ignore-db=mysql

replicate-do-db =test

本文出自 “运维人生” 博客