mysql-mmm 安装配置过程（两主一备份情况

最新推荐文章于 2021-04-30 16:19:54 发布

weixin_33806914

最新推荐文章于 2021-04-30 16:19:54 发布

阅读量847

点赞数

文章标签：数据库 shell 运维

原文链接：http://blog.51cto.com/leeforget/1343110

版权

mysql-mmm 安装配置过程（两主一备份情况）

一、环境说明
系统：centos 6.2 x86_64
192.168.101.77 mysql-mmm-agent,mysql-master
192.168.101.88 mysql-mmm-agent,mysql-master
192.168.101.99 mysql-mmm-agent,mysql-slave
192.168.101.33 mysql-mmm monitor

vip:192.168.101.7 write
vip:192.168.101.8 read
vip:192.168.101.9 read
vip:192.168.101.10 read

192.168.101.77 与192.168.101.88 安装mysql主主同步
192.168.101.99 配置成192.168.101.77的备

二、mysql 安装配置过程略

三、mysql主主、主从配置
192.168.101.77添加主备账号
grant replication slave on *.* to 'slave-user'@'192.168.101.88' identified by 'slave';
grant replication slave on *.* to 'slave-user'@'192.168.101.99' identified by 'slave';
192.168.101.88添加主备账号
grant replication slave on *.* to 'slave-user'@'192.168.101.77' identified by 'slave';
grant replication slave on *.* to 'slave-user'@'192.168.101.99' identified by 'slave';
192.168.101.99添加主备账号
grant replication slave on *.* to 'slave-user'@'192.168.101.77' identified by 'slave';
grant replication slave on *.* to 'slave-user'@'192.168.101.88' identified by 'slave';

192.168.101.77 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

192.168.101.88 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=1
skip_slave_start

192.168.101.99 my.cnf 添加下面配置文件
server-id=3
log-bin=mysql-bin
log-slave-updates

注意：192.168.101.77，192.168.101.88，192.168.101.99三个里面的server-id 不能重复

我是新安装的数据库，所以数据库拷贝就略过了

192.168.101.77/88/99上执行
stop slave;
flush logs; #关闭当前的二进制日志文件并创建一个新文件，新的二进制日志文件的名字在当前的二进制文件的编号上加1。
FLUSH TABLES WITH READ LOCK; #服务器锁表
mysql> show master status; #主要获取binlog文件名和pos值
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 120 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

192.168.101.77上执行
mysql> change master to
-> master_host='192.168.101.88',
-> master_user='slave-user',
-> master_password='slave',
-> master_log_file='binlog.000001',
-> master_log_pos=120,
-> master_connect_retry=10;

192.168.101.88/99执行
mysql> change master to
-> master_host='192.168.101.77',
-> master_user='slave-user',
-> master_password='slave',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=120,
-> master_connect_retry=10;

start slave;
show slave status\G; 查看状态
下面两项为Yes说明主备成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

解锁77/88/99服务器
UNLOCK TABLES;

192.168.101.77/88/99三台都要添加权限
grant super,replication client,process on *.* to 'mmm_agent'@'192.168.101.%' identified by 'agentpass';

四、mysql-mmm配置
# yum -y install perl perl-devel
wget http://search.cpan.org/CPAN/authors/id/A/AN/ANDK/CPAN-1.9205.tar.gz
[root@centos6 MMM]# tar zxvf CPAN-1.9205.tar.gz
[root@centos6 MMM]# cd CPAN-1.9205
[root@open-source CPAN-1.94]# perl Makefile.PL
[root@open-source CPAN-1.94]# make
[root@open-source CPAN-1.94]# make install

# 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

安装mysql-mmm
# tar -zxvf mysql-mmm-2.2.1.tar.gz
# cd mysql-mmm-2.2.1
# make install

192.168.101.77/88/99
修改配置文件两个配置文件一样
[root@open-source ~]# cat /etc/mysql-mmm/mmm_common.conf
active_master_role writer

<host default>
cluster_interface eth0
pid_path /var/run/mysql-mmm/mmm_agentd.pid
bin_path /usr/lib/mysql-mmm/
replication_user slave-user
replication_password slave
agent_user mmm_agent
agent_password agentpass
</host>

<host open77>
ip 192.168.101.77
mode master
peer open77
</host>

<host open88>
ip 192.168.101.88
mode master
peer open88
</host>

<host open99>
ip 192.168.101.99
mode slave
</host>

<role writer>
hosts open77, open88
ips 192.168.101.7
mode exclusive
</role>

<role reader>
hosts open77,open88,open99
ips 192.168.101.8,192.168101.9,192.168.101.10
mode balanced
</role>

192.168.101.77
# cat /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this open77

192.168.101.88
# cat /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this open88

192.168.101.99
# cat /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this open99

192.168.101.77/88/99启动mysql-mmm-agent
[root@open-source ~]# /etc/init.d/mysql-mmm-agent start

192.168.101.33
[root@open ~]# cat /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf

<monitor>
ip 192.168.101.33
pid_path /var/run/mysql-mmm/mmm_mond.pid
bin_path /usr/libexec/mysql-mmm
status_path /var/lib/mysql-mmm/mmm_mond.status
ping_ips 192.168.101.77, 192.168.101.88,192.168.101.99
auto_set_online 60

</monitor>

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

debug 0

启动服务（注意修改启动文件，将bin和pid路径指到你自己的配置路径）
[root@open-source ~]# /etc/init.d/mysql-mmm-monitor start
[root@open-source ~]# /etc/init.d/mysql-mmm-agent start

测试
1.查看是否所有都正常
[root@salt-server ~]# mmm_control show
open77(192.168.101.77) master/ONLINE. Roles: reader(192.168.101.8), writer(192.168.101.7)
open88(192.168.101.88) master/ONLINE. Roles: reader(192.168.101.10)
open99(192.168.101.99) slave/ONLINE. Roles: reader(192.168101.9)

[root@salt-server ~]# mmm_control checks all
open88 ping [last change: 2013/12/04 23:45:12] OK
open88 mysql [last change: 2013/12/04 23:45:12] OK
open88 rep_threads [last change: 2013/12/04 23:45:12] OK
open88 rep_backlog [last change: 2013/12/04 23:48:28] OK: Backlog is null
open99 ping [last change: 2013/12/04 23:45:12] OK
open99 mysql [last change: 2013/12/04 23:45:12] OK
open99 rep_threads [last change: 2013/12/04 23:45:12] OK
open99 rep_backlog [last change: 2013/12/04 23:45:12] OK: Backlog is null
open77 ping [last change: 2013/12/04 23:45:12] OK
open77 mysql [last change: 2013/12/04 23:45:12] OK
open77 rep_threads [last change: 2013/12/04 23:45:12] OK
open77 rep_backlog [last change: 2013/12/04 23:48:28] OK: Backlog is null

2.停掉192.168.101.88 mysql
[root@open2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!

show可以看到读切换到了99备上
[root@salt-server ~]# mmm_control show
open77(192.168.101.77) master/ONLINE. Roles: reader(192.168.101.8), writer(192.168.101.7)
open88(192.168.101.88) master/HARD_OFFLINE. Roles:
open99(192.168.101.99) slave/ONLINE. Roles: reader(192.168.101.10), reader(192.168101.9)

[root@salt-server ~]# mmm_control checks all
open88 ping [last change: 2013/12/04 23:45:12] OK
open88 mysql [last change: 2013/12/04 23:51:37] ERROR: Connect error (host = 192.168.101.88:3306, user = mmm_agent)! Can't connect to MySQL server on '192.168.101.88' (111)
open88 rep_threads [last change: 2013/12/04 23:45:12] OK
open88 rep_backlog [last change: 2013/12/04 23:48:28] OK: Backlog is null
open99 ping [last change: 2013/12/04 23:45:12] OK
open99 mysql [last change: 2013/12/04 23:45:12] OK
open99 rep_threads [last change: 2013/12/04 23:45:12] OK
open99 rep_backlog [last change: 2013/12/04 23:45:12] OK: Backlog is null
open77 ping [last change: 2013/12/04 23:45:12] OK
open77 mysql [last change: 2013/12/04 23:45:12] OK
open77 rep_threads [last change: 2013/12/04 23:45:12] OK
open77 rep_backlog [last change: 2013/12/04 23:48:28] OK: Backlog is null

3.启动88上mysql
[root@salt-server ~]# mmm_control show
open77(192.168.101.77) master/ONLINE. Roles: reader(192.168.101.8), writer(192.168.101.7)
open88(192.168.101.88) master/AWAITING_RECOVERY. Roles:
open99(192.168.101.99) slave/ONLINE. Roles: reader(192.168.101.10), reader(192.168101.9)
等一会儿一切正常
[root@salt-server ~]# mmm_control show
open77(192.168.101.77) master/ONLINE. Roles: reader(192.168.101.8), writer(192.168.101.7)
open88(192.168.101.88) master/ONLINE. Roles: reader(192.168.101.10)
open99(192.168.101.99) slave/ONLINE. Roles: reader(192.168101.9)

4.停掉77写的数据库，可以看到写自动切换到88上了
[root@salt-server ~]# mmm_control show
open77(192.168.101.77) master/HARD_OFFLINE. Roles:
open88(192.168.101.88) master/ONLINE. Roles: reader(192.168.101.10), writer(192.168.101.7)
open99(192.168.101.99) slave/ONLINE. Roles: reader(192.168.101.8), reader(192.168101.9)

5.如果看到某一台总是AWAITING_RECOVERY. Roles:自动起不来，可以手动起来
[root@salt-server ~]# mmm_control show
open77(192.168.101.77) master/AWAITING_RECOVERY. Roles:
open88(192.168.101.88) master/ONLINE. Roles: reader(192.168.101.10), writer(192.168.101.7)
open99(192.168.101.99) slave/ONLINE. Roles: reader(192.168.101.8), reader(192.168101.9)

[root@salt-server ~]# mmm_control set_online open77
OK: State of 'open77' changed to ONLINE. Now you can wait some time and check its new roles!
[root@salt-server ~]# mmm_control show
open77(192.168.101.77) master/ONLINE. Roles: reader(192.168101.9)
open88(192.168.101.88) master/ONLINE. Roles: reader(192.168.101.10), writer(192.168.101.7)
open99(192.168.101.99) slave/ONLINE. Roles: reader(192.168.101.8)

6.如果99备一开始设置的77为master，当77mysql宕掉后，最自动切换到88为master