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

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

阅读量：6120 次

发布时间：2019-06-21

本文共 9056 字，大约阅读时间需要 30 分钟。

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值

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

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

| 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

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>

ip 192.168.101.77

mode master

peer open77

</host>

ip 192.168.101.88

mode master

peer open88

</host>

ip 192.168.101.99

mode slave

</host>

hosts open77, open88

ips 192.168.101.7

mode exclusive

</role>

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

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>

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

转载于:https://blog.51cto.com/leeforget/1343110

你可能感兴趣的文章

android studio修改新项目package名称

实验吧 recursive write up

查看>>

High-speed Charting Control--MFC绘制图表（折线图、饼图、柱形图）控件