mysql mmm配置_mysql-mmm配置文档--下

四、主主(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='ths';

mysql>start slave

主(master)服务器配置：

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

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='ths';

mysql>start slave

主(master)服务器配置：

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

五、其它从(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='ths';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.253master-user=repl

master-password=ths

问题1：

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

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

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

问题2：

当/etc/mysql-mmm/mmm_mon.conf的auto_set_online3时间设置为较长时，在瞬间关闭现有主，开启现未开启的备时，会导致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

root31130.07.6140008956 ?S16:270:00 mmm_mond

root31150.9 33.2 106440 38976 ?Sl16:270:00 mmm_mond

root31360.45.5103886456 ?S16:280:00 perl /usr/libexec/mysql-mmm/monitor/checker ping_ip

root31400.46.5152087640 ?S16:280:00 perl /usr/libexec/mysql-mmm/monitor/checker mysql

root31410.35.4103886448 ?S16:280:00 perl /usr/libexec/mysql-mmm/monitor/checker ping

root31420.56.5152087636 ?S16:280:00 perl /usr/libexec/mysql-mmm/monitor/checker rep_backlog

root31440.66.4152087592 ?S16:280:00 perl /usr/libexec/mysql-mmm/monitor/checker rep_threads

root31480.00.53916652 pts/0R+16:280: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