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)