Mysql-mmm安装配置文档

 

Mysql-mmm安装配置文档

一、IP分配（两主两从）

Db5(monitor): 19.2.168.141/16

Db1(master-writer):19.2.168.142/16    19.2.34.2/16

Db2(master-backup):19.2.168.143/16   19.2.34.3/16

Db3(master-writer):19.2.168.144/16    19.2.34.4/16

Db4(master-writer):19.2.168.145/16    19.2.34.5/16

二、主从复制配置

Db1,db2,db3,db4的server-id分别人1，2，3，4

Db1,db2互为从库；

Db3为DB1的从库；

DB4为DB2的从库；

在Db1,db2,db3,db4的my.cnf添加如下配置

server-id           = 2

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

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

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

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

expire_logs_days    = 10

max_binlog_size     = 100M

log_slave_updates   = 1

#除writer role外全设read_only=1

read_only           = 1

#db1,db2,db3,db4添加几个帐号

#应用帐号

grant select,insert,update,delete on *.* to 'zhangjq'@'192.168.1.%' identified by '123456';

#monitor帐号

grant REPLICATION CLIENT on *.* to 'mmm_monitor'@'19.2.%' identified by '963214785';

#agent帐号

grant SUPER, REPLICATION CLIENT, PROCESS  on *.* to 'mmm_agent'@'19.2.%' identified by '963214785';

#复制帐号

grant replication slave on *.* to 'replication'@'19.2.%' identified by '963214785';

 

#以下为mysql-mmm安装一些必要包，所有5台服务器上安装

cd /data/setup

wget http://19.2.168.171/install/mysql/Algorithm-Diff-1.1902.tar.gz

tar -zxvf Algorithm-Diff-1.1902.tar.gz

cd Algorithm-Diff-1.1902

perl  Makefile.PL

make

make test

make install

cd ..

wget http://19.2.168.171/install/mysql/DBI-1.616.tar.gz

tar -zxvf DBI-1.616.tar.gz

cd DBI-1.616

perl Makefile.PL

make

make test

make install

cd ..

wget http://19.2.168.171/install/mysql/Log-Dispatch-2.29.tar.gz

tar -zxvf Log-Dispatch-2.29.tar.gz

cd Log-Dispatch-2.29

perl Makefile.PL

make

make test

make install

cd ..

wget http://19.2.168.171/install/mysql/Log-Log4perl-1.34.tar.gz

tar -zxvf Log-Log4perl-1.34.tar.gz

cd Log-Log4perl-1.34

perl Makefile.PL

make

make test

make install

cd ..

wget http://19.2.168.171/install/mysql/MailTools-2.08.tar.gz

tar -zxvf MailTools-2.08.tar.gz

cd MailTools-2.08

perl Makefile.PL

make

make test

make install

cd ..

wget http://19.2.168.171/install/mysql/Net-ARP-1.0.6.tgz

tar -zxvf Net-ARP-1.0.6.tgz

cd Net-ARP

perl Makefile.PL

make

make test

make install

cd ..

wget http://19.2.168.171/install/mysql/Proc-Daemon-0.14.tar.gz

tar -zxvf Proc-Daemon-0.14.tar.gz

cd Proc-Daemon-0.14

perl Makefile.PL

make

make test

make install

cd ..

wget http://19.2.168.171/install/mysql/Time-HiRes-1.9724.tar.gz

tar -zxvf Time-HiRes-1.9724.tar.gz

cd Time-HiRes-1.9724

perl Makefile.PL

make

make test

make install

cd ..

wget http://19.2.168.171/install/mysql/DBD-mysql-4.020.tar.gz

tar -zxvf DBD-mysql-4.020.tar.gz

cd DBD-mysql-4.020

cp /data/mysql/lib/mysql/* /usr/lib64/

mysql -S /usr/local/mysql/tmp/mysql.sock -uroot -e"grant all on *.* to 'root'@'localhost' identified by '123456';"

perl Makefile.PL --testdb=test --testuser=root --testpassword=123456  --with-mysql=/data/mysql/ --mysql_config=/data/mysql/bin/mysql_config

make

make test

make install

cd ..

#所有机上安装mysql-mmm-2.2.1

wget http://19.2.168.171/install/mysql/mysql-mmm-2.2.1.tar.gz

tar -zxvf mysql-mmm-2.2.1.tar.gz

cd mysql-mmm-2.2.1

make install

cd ..

#编辑mmm_agent.conf

vi /etc/mysql-mmm/mmm_agent.conf

include mmm_common.conf

this db1

#编辑mmm_mon.conf

vi /etc/mysql-mmm/mmm_common.conf

 

active_master_role      writer

 

 

<host default>

        cluster_interface               eth1

 

        pid_path                                /var/run/mmm_agentd.pid

        bin_path                                /usr/lib/mysql-mmm/

 

    replication_user        replication

    replication_password    123456

 

        agent_user                              mmm_agent

        agent_password                  123456

</host>

 

<host db1>

        ip                                              19.2.168.142

        mode                                    master

        peer                                    db2

</host>

 

<host db2>

        ip                                              19.2.168.143

        mode                                    master

        peer                                    db1

</host>

 

<host db3>

        ip                                              19.2.168.144

        mode                                    slave

</host>

 

<host db4>

        ip                                              19.2.168.145

        mode                                    slave

</host>

 

 

<role writer>

        hosts                                   db1, db2

        ips                                             19.2.34.2

        mode                                    exclusive

</role>

 

<role reader>

        hosts                                   db1, db2, db3, db4

        ips                                             19.2.34.3,19.2.34.4,19.2.34.5

        mode                                    balanced

</role>

#把mmm_common.conf文件同步到所有机

 scp /etc/mysql-mmm/mmm_common.conf  'root'@'20.37.18.143':/etc/mysql-mmm/mmm_common.conf

 

# db1, db2, db3, db4上启动agent进程

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

 

#监控机DB5上编辑mmm_mon.conf

vi /etc/mysql-mmm/mmm_mon.conf

include mmm_common.conf

 

<monitor>

        ip                                      19.2.33.141

        pid_path                                /var/run/mmm_mond.pid

        bin_path                                /usr/lib/mysql-mmm/

        status_path                             /var/lib/misc/mmm_mond.status

        ping_ips                                19.2.34.2,19.2.34.3,19.2.34.4,19.2.34.5

        auto_set_online 120

</monitor>

 

<host default>

        monitor_user                    mmm_monitor

        monitor_password                123456

</host>

 

#db5上启动monitor进程

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

 

#agent,monitor进程日志在/var/log/mysql-mmm/下，一般monitor进程无法启动均是配置不正确，包括mysql服务器的mmm_commom.conf配置

 

好，部署完毕，可以开始测试了

1、  在db1,db2上写数据，检验数据同步，OK，通过；

2、  查看monitor状态:

[root@OVM2011-11-023 mysql-mmm]# mmm_control show

  db1(19.2.168.142) master/ONLINE. Roles: writer(19.2.34.2)

  db2(19.2.168.143) master/ONLINE. Roles: reader(19.2.34.3)

  db3(19.2.168.144) slave/ONLINE. Roles: reader(19.2.34.4)

db4(19.2.168.145) slave/ONLINE. Roles: reader(19.2.34.5)

 

[root@OVM2011-11-023 mysql-mmm]# mmm_control checks all

db4  ping         [last change: 2011/11/28 15:40:14]  OK

db4  mysql        [last change: 2011/11/28 15:40:54]  OK

db4  rep_threads  [last change: 2011/11/28 15:40:14]  OK

db4  rep_backlog  [last change: 2011/11/28 15:40:14]  OK: Backlog is null

db2  ping         [last change: 2011/11/28 15:40:14]  OK

db2  mysql        [last change: 2011/11/28 15:40:14]  OK

db2  rep_threads  [last change: 2011/11/28 15:40:14]  OK

db2  rep_backlog  [last change: 2011/11/28 15:40:14]  OK: Backlog is null

db3  ping         [last change: 2011/11/28 15:40:14]  OK

db3  mysql        [last change: 2011/11/28 15:40:14]  OK

db3  rep_threads  [last change: 2011/11/28 15:40:14]  OK

db3  rep_backlog  [last change: 2011/11/28 15:40:14]  OK: Backlog is null

db1  ping         [last change: 2011/11/28 15:40:14]  OK

db1  mysql        [last change: 2011/11/28 15:40:14]  OK

db1  rep_threads  [last change: 2011/11/28 15:40:14]  OK

db1  rep_backlog  [last change: 2011/11/28 15:40:14]  OK: Backlog is null

3、  连接19.2.34.5，查询操作，OK，只能读不能写，通过；

4、  停止db4的mysql服务，

15秒后

[root@OVM2011-11-023 mysql-mmm]# mmm_control show

  db1(19.2.168.142) master/ONLINE. Roles: writer(19.2.34.2)

  db2(19.2.168.143) master/ONLINE. Roles: reader(19.2.34.3)

  db3(19.2.168.144) slave/ONLINE. Roles: reader(19.2.34.4)

db4(19.2.168.145) slave/HARD_OFFLINE. Roles:

 

5秒后：

[root@OVM2011-11-023 mysql-mmm]# mmm_control show

  db1(19.2.168.142) master/ONLINE. Roles: reader(19.2.34.5), writer(19.2.34.2)

  db2(19.2.168.143) master/ONLINE. Roles: reader(19.2.34.3)

  db3(19.2.168.144) slave/ONLINE. Roles: reader(19.2.34.4)

  db4(19.2.168.145) slave/HARD_OFFLINE. Roles:

再查询操作，OK，读写都可以，因为19.2.34.5被分配到了writer所在的19.2.34.2上，

通过，不过切换时间20秒

5、  启动db4的mysql服务

[root@OVM2011-11-023 mysql-mmm]# mmm_control show

  db1(19.2.168.142) master/ONLINE. Roles: reader(19.2.34.5), writer(19.2.34.2)

  db2(19.2.168.143) master/ONLINE. Roles: reader(19.2.34.3)

  db3(19.2.168.144) slave/ONLINE. Roles: reader(19.2.34.4)

db4(19.2.168.145) slave/AWAITING_RECOVERY. Roles:

2分钟后（因为auto_set_online 120）：

[root@OVM2011-11-023 mysql-mmm]# mmm_control show

  db1(19.2.168.142) master/ONLINE. Roles: writer(19.2.34.2)

  db2(19.2.168.143) master/ONLINE. Roles: reader(19.2.34.3)

  db3(19.2.168.144) slave/ONLINE. Roles: reader(19.2.34.4)

  db4(19.2.168.145) slave/ONLINE. Roles: reader(19.2.34.5)

查询操作，只读不能写，并且确认19.2.34.5 的arp指向19.2.168.145的mac,OK,通过

6、  停writer role所有服务器writer(19.2.34.2)的MYSQL服务

20秒后

[root@OVM2011-11-023 mysql-mmm]# mmm_control show

  db1(19.2.168.142) master/HARD_OFFLINE. Roles:

  db2(19.2.168.143) master/ONLINE. Roles: reader(19.2.34.3), writer(19.2.34.2)

  db3(19.2.168.144) slave/ONLINE. Roles: reader(19.2.34.5)

db4(19.2.168.145) slave/ONLINE. Roles: reader(19.2.34.4)

连接writer(19.2.34.2)读写正常，连接reader(19.2.34.5),读正常，查看reader(19.2.34.5)的同步状态（show slave status \G;）,正常，此时master已切换指向db2(19.2.168.143)

7、  启动db1的mysql服务

[root@OVM2011-11-023 mysql-mmm]# mmm_control show

  db1(19.2.168.142) master/AWAITING_RECOVERY. Roles:

  db2(19.2.168.143) master/ONLINE. Roles: reader(19.2.34.3), writer(19.2.34.2)

  db3(19.2.168.144) slave/ONLINE. Roles: reader(19.2.34.5)

db4(19.2.168.145) slave/ONLINE. Roles: reader(19.2.34.4)

 

2分钟后（因为auto_set_online 120）：

[root@OVM2011-11-023 mysql-mmm]# mmm_control show

  db1(19.2.168.142) master/ONLINE. Roles: reader(19.2.34.3)

  db2(19.2.168.143) master/ONLINE. Roles: writer(19.2.34.2)

  db3(19.2.168.144) slave/ONLINE. Roles: reader(19.2.34.5)

  db4(19.2.168.145) slave/ONLINE. Roles: reader(19.2.34.4)

连接检查读写、同步状态一切OK，通过

8、  反复测试各台DB宕机，均能正常故障转移

9、  停止monitor服务进程

[root@OVM2011-11-023 mysql-mmm]# /etc/init.d/mysql-mmm-monitor stop

Daemon bin: '/usr/sbin/mmm_mond'

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

Shutting down MMM Monitor daemon: .. Ok

[root@OVM2011-11-023 mysql-mmm]# mmm_control show                 

ERROR: Can't connect to monitor daemon!

[root@OVM2011-11-023 mysql-mmm]#

连接检查读写、同步状态一切OK，通过

10、              停止db4的agent服务进程

[root@OVM2010-09-004 mysql-mmm]# /etc/init.d/mysql-mmm-agent stop

Daemon bin: '/usr/sbin/mmm_agentd'

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

Shutting down MMM Agent daemon. Ok

[root@OVM2010-09-004 mysql-mmm]#

此时monitor状态显示：

[root@OVM2011-11-023 mysql-mmm]# mmm_control show

# Warning: agent on host db4 is not reachable

  db1(19.2.168.142) master/ONLINE. Roles: reader(19.2.34.3)

  db2(19.2.168.143) master/ONLINE. Roles: writer(19.2.34.2)

  db3(19.2.168.144) slave/ONLINE. Roles: reader(19.2.34.5)

db4(19.2.168.145) slave/ONLINE. Roles: reader(19.2.34.4)

 

连接检查读写、同步状态一切OK，通过

11、              在停止db4的agent服务进程后，再停止db4的mysql服务；

[root@OVM2011-11-023 mysql-mmm]# mmm_control show

# Warning: agent on host db4 is not reachable

  db1(19.2.168.142) master/ONLINE. Roles: reader(19.2.34.3)

  db2(19.2.168.143) master/ONLINE. Roles: reader(19.2.34.4), writer(19.2.34.2)

  db3(19.2.168.144) slave/ONLINE. Roles: reader(19.2.34.5)

db4(19.2.168.145) slave/HARD_OFFLINE. Roles:

连接检查读写、同步状态一切OK，通过

12、              启动db4的mysql服务（不启动db4的agent）

10分钟后查看monitor

[root@OVM2011-11-023 mysql-mmm]# mmm_control show

# Warning: agent on host db4 is not reachable

  db1(19.2.168.142) master/ONLINE. Roles: reader(19.2.34.3)

  db2(19.2.168.143) master/ONLINE. Roles: reader(19.2.34.4), writer(19.2.34.2)

  db3(19.2.168.144) slave/ONLINE. Roles: reader(19.2.34.5)

db4(19.2.168.145) slave/ONLINE. Roles:

些时显示db4没有reader分配在上面，因为db4的agent处于关闭状态，

连接检查读写、同步状态一切OK，通过

启动agent后：

[root@OVM2011-11-023 mysql-mmm]# mmm_control show                    

  db1(19.2.168.142) master/ONLINE. Roles: reader(19.2.34.3)

  db2(19.2.168.143) master/ONLINE. Roles: writer(19.2.34.2)

  db3(19.2.168.144) slave/ONLINE. Roles: reader(19.2.34.5)

  db4(19.2.168.145) slave/ONLINE. Roles: reader(19.2.34.4)