mysql-mmm的作用很简单就是读和写以ip的形式分离出来，并且可以全自动对mysql主从配置进行故障切换。

整个架构如下图：

mysql MM_mysql MM

操作系统全部为centos5.5 32bit

mysql版本为mysql-5.1.59

mysql-mmm版本为mysql-mmm-2.2.1

另外还需要4个虚拟IP，作用为：

192.168.93.141 数据库写入ip

192.168.93.142 数据库读取ip

192.168.93.143 数据库读取ip

192.168.93.144 数据库读取ip

一、安装mysql

useradd mysql
tar zxvf mysql-5.1.59.tar.gz
cd mysql-5.1.59
./configure --prefix=/usr/local/mysql --without-debug --enable-thread-safe-client --enable-assembler --enable-profiling --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-charset=utf8 --with-extra-charsets=all --with-big-tables --enable-largefile --without-ndb-debug --with-plugins=partition --localstatedir=/home/var
make
make install

vi /etc/my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock

skip-external-locking
#skip-bdb
skip-name-resolve
#skip-innodb
max_allowed_packet = 8M
table_cache = 2048
sort_buffer_size = 2M
read_buffer_size = 2M
query_cache_limit = 2M
myisam_sort_buffer_size = 128M
thread_cache_size = 256
query_cache_size = 128M
thread_concurrency = 8

log-bin=mysql-bin
slow_query_log=slowquery.log
long_query_time = 60

character-set-server=utf8
max_user_connections=5000
max_connections=8000
wait_timeout=31536000
tmp_table_size = 384M
bulk_insert_buffer_size = 512M
concurrent_insert = 2
back_log = 512

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
datadir=/home/var

[mysqld_safe]
open_files_limit = 65535

保存退出！

继续安装Mysql

/usr/local/mysql/bin/mysql_install_db --user=mysql

chown -R mysql:mysql /home/var

/usr/local/mysql/bin/mysqld_safe --user=mysql &

cp ./support-files/mysql.server /etc/rc.d/init.d/mysql


chmod +x /etc/rc.d/init.d/mysql

chkconfig --add mysql

service mysql start

/usr/local/mysql/bin/mysqladmin -u root password 123456

vi ~/.bash_profile

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin/

追加蓝色部分，然后保存退出

使配置立即生效

source ~/.bash_profile

二、配置主从关系

DB1：编辑/etc/my.cnf，加入以下内容：

server-id=1

log-bin=mysql-bin

binlog_format=ROW

log-slave-updates

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1

skip_slave_start

DB2：编辑/etc/my.cnf，加入以下内容：

server-id=2

log-bin=mysql-bin

binlog_format=ROW

log-slave-updates

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=2

DB3：编辑/etc/my.cnf，加入以下内容：

server-id=3

log-bin=mysql-bin

log-slave-updates

然后重启mysql服务！

接下创建一个同步用的帐号并授权：

DB1授权：

mysql -uroot -p123456 -e "GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.93.138' IDENTIFIED BY 'slave'; "

mysql -uroot -p123456 -e "GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.93.139' IDENTIFIED BY 'slave'; "

DB2授权：

mysql -uroot -p123456 -e "GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.93.137' IDENTIFIED BY 'slave'; "

mysql -uroot -p123456 -e "GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.93.139' IDENTIFIED BY 'slave'; "

DB3的话以谁为主都无所谓，当主出现故障的时候，mmm会自动切换主的，我这里就以DB2为主，配置方从的步骤：锁表——（主）导出数据——（主）查看指针位置——（主）解锁——（从）导入数据——（从）导入主服务器的指针位置——（双方）开启主从线程

DB2：（PS我这里是新的数据库，所以省了导出数据这一步！）

mysql> flush tables with read lock;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 586 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> unlock tables;

得出指针位置之后在DB1和DB3上操作：

mysql> change master to
-> master_host='192.168.93.138',
-> master_user='slave',
-> master_password='slave',
-> master_log_file='mysql-bin.000004',
-> master_log_pos=586,
-> master_connect_retry=10;
Query OK, 0 rows affected (0.04 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.93.138
Master_User: slave
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 586
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 586
Relay_Log_Space: 410
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:
1 row in set (0.01 sec)

看见蓝色那两行已经yes了，表示配置成功，刚才一口气把DB2配成DB1和DB3的主，现在要把DB1配成DB2的主，实现DB1和DB2互为主从关系

查看DB1的指针位置：

mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000005 | 586 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

然后在DB2上导入指针位置：

mysql> change master to
-> master_host='192.168.93.137',
-> master_user='slave',
-> master_password='slave',
-> master_log_file='mysql-bin.000005',
-> master_log_pos=586,
-> master_connect_retry=10;
Query OK, 0 rows affected (0.01 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.93.137
Master_User: slave
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 586
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 586
Relay_Log_Space: 410
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:
1 row in set (0.00 sec)

现在三台DB的主从关系都配置好了，可以测试一下：

在DB1的test库建个表，在DB2插入一条数据，在DB3查看

DB1：

mysql> create table test.t (id int(11) not null auto_increment,name varchar(30),primary key (id));

Query OK, 0 rows affected (0.03 sec)

DB2：

mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
+----------------+
1 row in set (0.00 sec)

mysql> insert into t(name) values ('mysql-mmm');
Query OK, 1 row affected (0.02 sec)

DB3：

mysql> use test

Database changed

mysql> select * from t;

+----+-----------+

| id | name |

+----+-----------+

| 2 | mysql-mmm |

+----+-----------+

1 row in set (0.00 sec)

DB1：

mysql> select * from test.t;

+----+-----------+

| id | name |

+----+-----------+

| 2 | mysql-mmm |

+----+-----------+

1 row in set (0.00 sec)

OK！三台DB的主从配置正常工作

三、安装mysql-mmm

安装Mysql-mmm之前，需要先安装一些perl模块，安装方法用perl -MCPAN -e shell 直接在线安装，第一次使用perl -MCPAN -e shell 命令时会有一大堆选项的，我直接enter过去，比较关键的是选择地区、国家和源这三个，按的时候注意选择一下就没问题，DB1、DB2和DB3需要安装以下模块：

# perl -MCPAN -e shell

cpan> install Algorithm::Diff

cpan> install DBI

cpan>install Log::Dispatch

cpan> install Log::Log4perl

cpan> install Mail::Send

cpan> install Net::ARP

cpan> install Proc::Daemon

cpan> install Time::HiRes

cpan>install DBD::mysql

cpan>install File::stat

cpan>install File:basename

server4需要安装以下：

# perl -MCPAN -e shell

cpan> install Algorithm::Diff

cpan> install Class::Singleton

cpan> install Log::Dispatch

cpan> install Log::Log4perl

cpan> install Mail::Send

cpan> install Proc::Daemon

cpan> install Thread::Queue

cpan> install Time::HiRes

cpan> install DBI

cpan>install DBD::mysql

安装mysql-mmm，四台机器都要安装：

tar zxvf mysql-mmm-2.2.1.tar.gz 

cd mysql-mmm-2.2.1

make instal

mysql-mmm文件位置及作用如下：

/usr/lib/perl5/vendor_perl/5.8.8/MMM                 MMM 使用的 perl 模块

/usr/lib/mysql-mmm                                              MMM 的脚本揑件

/usr/sbin                                                               MMM 的命令保存路径

/var/log/mysql-mmm                                             MMM 的日志保存路径

/etc                                                                      MMM 配置文件保存的路径

/etc/mysql-mmm                                                  MMM 配置文件保存的路径，优先级最高

/etc/init.d/                                                            agentd 和 monitor 的启劢关闭脚本  

数据库授权一个Mysql-mmm专用用户（DB1、DB2和DB3都要授权）：

mysql -uroot -p123456 -e "grant super,replication client,process on *.* to 'mmm_agent'@'192.168.93.137' identified by 'mmm_agent';"

mysql -uroot -p123456 -e "grant super,replication client,process on *.* to 'mmm_agent'@'192.168.93.138' identified by 'mmm_agent';"

mysql -uroot -p123456 -e "grant super,replication client,process on *.* to 'mmm_agent'@'192.168.93.139' identified by 'mmm_agent';"

mysql -uroot -p123456 -e "grant super,replication client,process on *.* to 'mmm_agent'@'192.168.93.140' identified by 'mmm_agent';"

修改mysql-mmm配置文件：

DB1：

/etc/mysql-mmm/mmm_agent.conf

include mmm_common.conf

this db1

蓝色的部分DB2和DB3则分别修改为db2和db3

/etc/mysql-mmm/mmm_common.conf

active_master_role      writer

<host default>
        cluster_interface                       eth0

        pid_path                                /var/run/mmm_agentd.pid
        bin_path                                /usr/lib/mysql-mmm/

    replication_user        slave
    replication_password    slave

        agent_user                              mmm_agent
        agent_password                          mmm_agent
</host>

<host db1>
        ip                                      192.168.93.137
        mode                                    master
        peer                                    db2
</host>

<host db2>
        ip                                      192.168.93.138
        mode                                    master
        peer                                    db1
</host>

<host db3>
        ip                                      192.168.93.139
        mode                                    slave
</host>

<role writer>
        hosts                                   db1, db2
        ips                                     192.168.93.141
        mode                                    exclusive
</role>

<role reader>
        hosts                                   db1, db2, db3
        ips                                     192.168.93.144,192.168.93.143,192.168.93.142
        mode                                    balanced
</role>

将这份配置复制至其它三台机器，包括server4，然后启动DB1\、DB2和DB3可以启动mysql-mmm-agant:：

[root@localhost mysql-mmm-2.2.1]# /etc/init.d/mysql-mmm-agent start

Daemon bin: '/usr/sbin/mmm_agentd'

Daemon pid: '/var/run/mmm_agentd.pid'

Starting MMM Agent daemon... Ok

最后配置server4的mysql-mmm-monitor

/etc/mysql-mmm/mmm_mon.conf

include mmm_common.conf

<monitor>
        ip                                      192.168.93.140
        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.93.137, 192.168.93.138, 192.168.93.139
</monitor>

<host default>
        monitor_user                    mmm_agent
        monitor_password                mmm_agent
</host>

debug 0

启动mysql-mmm-monitor：

[root@localhost mysql-mmm-2.2.1]# /etc/init.d/mysql-mmm-monitor start

Daemon bin: '/usr/sbin/mmm_mond'

Daemon pid: '/var/run/mmm_mond.pid'

Starting MMM Monitor daemon: Ok

[root@localhost mysql-mmm-2.2.1]# mmm_control show

  db1(192.168.93.137) master/AWAITING_RECOVERY. Roles:

  db2(192.168.93.138) master/AWAITING_RECOVERY. Roles:

  db3(192.168.93.139) slave/AWAITING_RECOVERY. Roles:

将所有DB服务器设为online状态：

 [root@localhost mysql-mmm-2.2.1]# mmm_control set_online db1

OK: State of 'db1' changed to ONLINE. Now you can wait some time and check its new roles!

[root@localhost mysql-mmm-2.2.1]# mmm_control set_online db2

OK: State of 'db2' changed to ONLINE. Now you can wait some time and check its new roles!

[root@localhost mysql-mmm-2.2.1]# mmm_control set_online db3

OK: State of 'db3' changed to ONLINE. Now you can wait some time and check its new roles!

[root@localhost mysql-mmm-2.2.1]# mmm_control show

  db1(192.168.93.137) master/ONLINE. Roles: reader(192.168.93.143), writer(192.168.93.141)

  db2(192.168.93.138) master/ONLINE. Roles: reader(192.168.93.142)

  db3(192.168.93.139) slave/ONLINE. Roles: reader(192.168.93.144)

设置成功，下面查看节点状态：

 [root@localhost mysql-mmm-2.2.1]# mmm_control checks all

db2  ping         [last change: 2011/10/07 03:10:39]  OK

db2  mysql        [last change: 2011/10/07 03:10:39]  OK

db2  rep_threads  [last change: 2011/10/07 03:10:39]  OK

db2  rep_backlog  [last change: 2011/10/07 03:10:39]  OK: Backlog is null

db3  ping         [last change: 2011/10/07 03:10:39]  OK

db3  mysql        [last change: 2011/10/07 03:10:39]  OK

db3  rep_threads  [last change: 2011/10/07 03:10:39]  OK

db3  rep_backlog  [last change: 2011/10/07 03:10:39]  OK: Backlog is null

db1  ping         [last change: 2011/10/07 03:10:39]  OK

db1  mysql        [last change: 2011/10/07 03:10:39]  OK

db1  rep_threads  [last change: 2011/10/07 03:10:39]  OK

db1  rep_backlog  [last change: 2011/10/07 03:10:39]  OK: Backlog is null

最后将除write机器的DB的read_only状态设为on：

 set global read_only=on;

并写入my.cnf！

在write角色的机器上授权一个帐号给外部登陆：

 mysql -uroot -p123456 -e "grant all  privileges on test.* to lihuipeng@'192.168.93.%' identified by 'lihuipeng';"

然后从另外一台机器上试试登陆四个虚拟IP：

[root@localhost mysql-mmm-2.2.1]# mysql -ulihuipeng -plihuipeng -h192.168.93.141
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 716
 Server version: 5.1.59-log Source distributionCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 
  [root@localhost mysql-mmm-2.2.1]# mysql -ulihuipeng -plihuipeng -h192.168.93.142
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 554
 Server version: 5.1.59-log Source distributionCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
 
[root@localhost mysql-mmm-2.2.1]# mysql -ulihuipeng -plihuipeng -h192.168.93.143
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 758
 Server version: 5.1.59-log Source distributionCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 
 
[root@localhost mysql-mmm-2.2.1]# mysql -ulihuipeng -plihuipeng -h192.168.93.144
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 560
 Server version: 5.1.59-log Source distributionCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

四个ip都可以使用，其它功能大家可以自己去测试一下，这里不多写了，这里主要把数据库的write功能独立出来一个ip，让两台互为主从的机器中其中一台担任write角色，其余机器担任read角色，出现故障时mysql-mmm可以自动切换write角色和主从关系。