Mysql MMM 项目官方网站（http://mysql-mmm.org/）


Mysql MMM(Multi-Master Replication Manager for Mysql)是一套mysql扩展脚本。用来在MySQL master-master模式下管理配置、监控、执行mysql故障转移。


这套工具可以实现在mysql 读模式下的负载平衡功能，在任何情况下有且只有一个节点可以写入。mysql mmm 利用浮动IP来实现故障转移。


MySQL MMM 环境搭建


操作系统 CentOS 6.0


MySQL 5.1


db1   ip  192.168.10.90


db2  ip  192.168.10.91


monitor  ip 192.168.10.92


192.168.10.93  writer


192.168.10.94  reader


192.168.10.95  reader


mysql 用yum 方式安装 略


DB1 my.cnf配置


[mysqld]

#datadir=/var/lib/mysql

server_id =1

datadir=/home/mysqldata

socket=/home/mysqldata/mysql.sock

port=3306

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0log_bin             = /var/log/mysql/mysql-bin.log

log_bin_index       = /var/log/mysql/mysql-bin.log.index

relay_log           = /var/log/mysql/mysql-relay-bin

relay_log_index     = /var/log/mysql/mysql-relay-bin.index

expire_logs_days    = 10

max_binlog_size     = 100M

log_slave_updates   = 1

bind-address        = 0.0.0.0

auto_increment_increment = 2

auto_increment_offset = 1[mysqld_safe]

log-error=/var/log/mysql/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

log_bin =/var/log/mysql/mysql-bin.log

DB2  my.cnf配置


[mysqld]

#datadir=/var/lib/mysql

server_id = 2

datadir = /home/mysqldata

socket  = /home/mysqldata/mysql.sock

port=3306

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log_bin             = /var/log/mysql/mysql-bin.log

log_bin_index       = /var/log/mysql/mysql-bin.log.index

relay_log           = /var/log/mysql/mysql-relay-bin

relay_log_index     = /var/log/mysql/mysql-relay-bin.index

expire_logs_days    = 10

max_binlog_size     = 100M

log_slave_updates   = 1

bind-address        = 0.0.0.0

auto_increment_increment = 2

auto_increment_offset = 1[mysqld_safe]

log-error = /var/log/mysql/mysqld.log

pid-file  = /var/run/mysqld/mysqld.pid

log_bin   = /var/log/mysql/mysql-bin.log

创建帐号及赋权


监控帐户 用来监控测试mysql服务器   权限 REPLICATION CLIENT


代理帐户 用来改变mysql 读模式等      权限 SUPER REPLICATION CLIENT PROCESS


复制用户 用来复制数据                           权限 REPLICATION SLAVE


GRANT REPLICATION CLIENT ON  *.*  TO 'mmm_monitor'@'192.168.10.%'  IDENTIFIED BY 'monitor_password';


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


GRANT REPLICATION SLAVE  ON  *.* TO 'replication'@'192.168.10.%' IDENTIFIED BY 'replication_password';


数据同步


对DB1作备份


(db1) mysql> FLUSH TABLES WITH READ LOCK;


db1)mysql>SHOW MASTER STATUS;


mysql> show master status;

+——————+———-+————–+——————+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000014 |      352 |              |                  |

+——————+———-+————–+——————+

1 row in set (0.00 sec)


(db1)mysqldump -uroot -p –all-databases >/root/db1.sql


(db1)mysql>UNLOCK TABLES;


#########################################################


(db2) scp root@192.168.10.90:/root/db1.sql /root


(db2)mysql -u root -p </root/db1.sql


(db2)mysql>FLUSH PRIVILEGES;


(db2)mysql>CHANGE MASTER TO master_host='192.168.10.90',master_port=3306,mster_user='replication',master_password='replication_password',master_log_file='mysql-bin.000014',master_log_pos=352;


(db2)mysql>START SLAVE;


(db2)mysql>show slave status\G


(db2)mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.10.90

Master_User: replication

Master_Port: 3306

Connect_Retry: 60


(db2)mysql>show master status;


mysql> show master status;

+——————+———-+————–+——————+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000030 |      106 |              |                  |

+——————+———-+————–+——————+

1 row in set (0.00 sec)


############################################


(db1)mysql>CHANGE MASTER TO master_host='192.168.10.91',master_port=3306,master_user='replication',master_password='relication_password',master_log_file='mysql-bin.000030',master_log_pos=106;


(db1)mysql>START SLAVE;


(db1)mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.10.91

Master_User: replication

Master_Port: 3306

Connect_Retry: 60


#############################################


安装MMM


监控主机


perl with ithreads support


fping (if you want to run


1

mmm_mond

as non-root user)


Perl modules:


- Algorithm::Diff

- Class:Singleton

- DBI and DBD::mysql

- File::Basename

- File::stat

- File::Temp

- Log::Dispatch

- Log::Log4perl

- Mail::Send

- Net::Ping

- Proc::Daemon

- Thread::Queue

- Time::HiRes

节点主机需求


perl


iproute


send_arp (solaris)


Perl modules:


- Algorithm::Diff

- DBI and DBD::mysql

- File::Basename

- File::stat

- Log::Dispatch

- Log::Log4perl

- Mail::Send

- Net::ARP (linux)

- Proc::Daemon

- Time::HiRes

用perl 在线安装 perl -MCPAN -e shell

wget http://mysql-mmm.org/_media/:mmm2:mysql-mmm-2.2.1.tar.gz


tar xvzf mysql-mmm-2.2.1.tar.gz


cd mysql-mmm-2.2.1


make install


vi /etc/mysql-mmm/mmm_common.conf


active_master_role writer

<host default>

cluster_interface eth0pid_path /var/run/mmm_agentd.pid

bin_path /usr/lib/mysql-mmm/replication_user replication

replication_password 2012





agent_user agent

agent_password 2012

</host>


<host db1>

ip 192.168.10.90

mode master

peer db2

</host>


<host db2>

ip 192.168.10.91

mode master

peer db1

</host>



<role writer>

hosts db1, db2

ips 192.168.10.93

mode exclusive

</role>


<role reader>

hosts db1, db2

ips 192.168.10.94, 192.168.10.95

mode balanced

</role>


把这个配置文件复制到 db2 和monitor中


在monitor中编辑/etc/mysql-mmm/mmm_mon.conf


vi /etc/mysql-mmm/mmm_mon.conf


include mmm_common.conf<monitor>

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.10.90, 192.168.10.91,192.168.10.3

</monitor><host default>

monitor_user                    monitor

monitor_password                2012

</host>debug 0

192.168.10.3是交换机的IP


在db1 和db2中


chkconfig mysql-mmm-agent on


/etc/init.d/mysql-mmm-agent start


在monitor中


monitor$ chkconfig mysql-mmm-monitor on


monitor$ /etc/init.d/mysql-mmm-monitor start


monitor$ mmm_control set_online db1


monitor$ mmm_control set_online db2


monitor$ mmm_control show


db1(192.168.10.90) master/ONLINE. Roles: reader(192.168.10.94), writer(192.168.10.93)

db2(192.168.10.91) slave/ONLINE. Roles: reader(192.168.10.95)