领购物优惠券9块9包邮
首页 > 数据库 > MySQL教程

MySQL高可用MMM方案安装部署分享

   2015-11-05 0
核心提示：这篇文章主要介绍了MySQL高可用MMM方案安装部署方法，需要的朋友可以参考下

1 install mysql

  请参考http://www.lexue001.com/article/47094.htm

2. Basic configuration of master 1

3. Create users
GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'%' IDENTIFIED BY 'mmm_monitor';
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'%' IDENTIFIED BY 'mmm_agent';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.88.49.%' IDENTIFIED BY 'repl';
grant insert,create,delete,update,select on *.* to 'tim'@'%' identified by 'tim';
Note: Don not use a replication_password longer than 32 characters


4. Synchronisation of data between both databases


5. Setup replication
set m-s:
change master to master_host='10.88.49.119',master_log_file='mysql56-bin.000026',master_log_pos=332, master_user='repl',master_password='repl';


6. Install mmm
6.1 download mmm.tar.gz
wget http://mysql-mmm.org/_media/:mmm2:mysql-mmm-2.2.1.tar.gz
6.2 mv :mmm2:mysql-mmm-2.2.1.tar.gz mysql-mmm-2.2.1.tar.gz
tar -xvf mysql-mmm-2.2.1.tar.gz
cd mysql-mmm-2.2.1
make
cmake
[] don not require make and make install, there have *.conf in /etc/mysql-mmm folder.

7. install lib package
yum install -y perl-*
yum install -y libart_lgpl.x86_64
yum install -y mysql-mmm.noarch fail
yum install -y rrdtool.x86_64
yum install -y rrdtool-perl.x86_64
7.1 []another way to install lib package in network
cpan -i Algorithm::Diff Class::Singleton DBI DBD::mysql Log::Dispatch Log::Log4perl Mail::Send Net::Ping Proc::Daemon Time::HiRes Params::Validate Net::ARP


8. Config MMM of DB host
vim /etc/mysql-mmm/mmm_common.conf
Don not forget to copy this file to all other hosts (including the monitoring host).
#Bugsfor$
scp /etc/mysql-mmm/mmm_common.conf 10.88.49.119:/etc/mysql-mmm/
scp /etc/mysql-mmm/mmm_common.conf 10.88.49.122:/etc/mysql-mmm/
scp /etc/mysql-mmm/mmm_common.conf 10.88.49.123:/etc/mysql-mmm/

On the database hosts we need to edit /etc/mysql-mmm/mmm_agent.conf. Change “db1” accordingly on the other hosts:
chkconfig --add mysql-mmm-agent

9. Config Monitor
On the monitor host(10.88.49.123) we need to edit /etc/mysql-mmm/mmm_mon.conf:
include mmm_common.conf
<monitor>
ip 127.0.0.1
pid_path /var/run/mmm_mond.pid
bin_path /usr/lib/mysql-mmm/
status_path /var/lib/misc/mmm_mond.status
auto_set_online 5
ping_ips 10.88.49.254,10.88.49.130,10.88.49.131,10.88.49.132,10.88.49.133,10.88.49.134
</monitor>
<host default>
monitor_user mmm_monitor
monitor_password mmm_monitor
</host>
debug 0
ping_ips are some ips that are pinged to determine whether the network connection of the monitor is ok. I used my switch of GATEWAY (10.88.49.254) and the four database server.follow this
[root@oracle mysql-mmm]# cat /etc/sysconfig/network-scripts/ifcfg-eth0
DEVICE=eth0
IPADDR=10.88.49.118
NETWASK=255.255.254.0
GATEWAY=10.88.49.254
DNS1=10.106.185.143
DNS2=10.106.185.138
ONBOOT=yes
BOOTPROTO=none
TYPE=Ethernet
HWADDR=00:15:5D:01:6A:0C


10. Start in database hosts
chkconfig --add mysql-mmm-agent
[root@oracle ~]# mysql-mmm-agent start
-bash: mysql-mmm-agent: command not found
[root@oracle ~]# service mysql-mmm-agent start
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Starting MMM Agent daemon... Can not locate Proc/Daemon.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/sbin/mmm_agentd line 7.
BEGIN failed--compilation aborted at /usr/sbin/mmm_agentd line 7.
[root@oracle ~]# cpan Proc::Daemon
[root@oracle ~]# cpan Log::Log4perl
[root@oracle ~]# /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


11. Start in monitor hosts
chkconfig --add mysql-mmm-monitor
[root@localhost mysql-mmm-2.2.1]# service mysql-mmm-monitor start
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Starting MMM Monitor daemon: Can not locate Proc/Daemon.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/sbin/mmm_mond line 11.
BEGIN failed--compilation aborted at /usr/sbin/mmm_mond line 11.
failed
[root@oracle ~]# cpan Proc::Daemon
[root@oracle ~]# cpan Log::Log4perl
[root@localhost mysql-mmm-2.2.1]# service mysql-mmm-monitor start
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Starting MMM Monitor daemon: Ok

12. Check fail
[root@oracle mysql-mmm]# ping 10.88.49.130
PING 10.88.49.130 (10.88.49.130) 56(84) bytes of data.
From 10.88.49.118 icmp_seq=2 Destination Host Unreachable
From 10.88.49.118 icmp_seq=3 Destination Host Unreachable
From 10.88.49.118 icmp_seq=4 Destination Host Unreachable
From 10.88.49.118 icmp_seq=6 Destination Host Unreachable
From 10.88.49.118 icmp_seq=7 Destination Host Unreachable
From 10.88.49.118 icmp_seq=8 Destination Host Unreachable

12.1 debug error info
在agent.conf 和 monitor.conf 分别加上 debug 1
然后看输出的日志

[root@localhost mysql-mmm]# mmm_control show
db1(10.88.49.118) master/AWAITING_RECOVERY. Roles:
db2(10.88.49.119) master/AWAITING_RECOVERY. Roles:
db3(10.88.49.122) slave/AWAITING_RECOVERY. Roles:
[root@localhost mysql-mmm]# 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]# 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]# mmm_control set_online db3
OK: State of 'db3' changed to ONLINE. Now you can wait some time and check its new roles!

12.2 ping vip fail
2013/02/19 10:00:15 FATAL Couldn't configure IP '10.88.49.131' on interface 'eth1': undef
2013/02/19 10:00:15 DEBUG Executing /usr/lib/mysql-mmm//agent/mysql_allow_write
Can't locate Net/ARP.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/share/perl5/vendor_perl/MMM/Agent/Helpers/Network.pm line 11.
[1.1]
cpan Net/ARP.pm
yum install libuuid* Error
[2.1] if [1.1] fail , try this
[root@localhost mysql-mmm]# perl -MCPAN -e shell
cpan> install Net::ARP
[ok]


12.3 fail info :
2013/02/19 10:25:23 INFO Added: reader(10.88.49.131), writer(10.88.49.130)
2013/02/19 10:25:23 DEBUG Executing /usr/lib/mysql-mmm//agent/configure_ip eth1 10.88.49.131
Device "eth1" does not exist.
2013/02/19 10:25:23 FATAL Couldn't configure IP '10.88.49.131' on interface 'eth1': ERROR: Could not check if ip 10.88.49.131 is configured on eth1:
2013/02/19 10:25:23 DEBUG Executing /usr/lib/mysql-mmm//agent/sync_with_master
2013/02/19 10:25:23 DEBUG Executing /usr/lib/mysql-mmm//agent/mysql_allow_write
2013/02/19 10:25:23 DEBUG Executing /usr/lib/mysql-mmm//agent/configure_ip eth1 10.88.49.130
Device "eth1" does not exist.
2013/02/19 10:25:23 FATAL Couldn't configure IP '10.88.49.130' on interface 'eth1': ERROR: Could not check if ip 10.88.49.130 is configured on eth1:
2013/02/19 10:25:23 DEBUG Fetching uptime from /proc/uptime
2013/02/19 10:25:23 DEBUG Uptime is 158489.10
2013/02/19 10:25:23 DEBUG Daemon: Answer = 'OK: Status applied successfully!'
[ok] cluster_interface should set the

12.4 When connect reader vip,Lost package, info :
[root@localhost mysql-mmm]# ping 10.88.49.134
PING 10.88.49.134 (10.88.49.134) 56(84) bytes of data.
64 bytes from 10.88.49.134: icmp_seq=3 ttl=64 time=0.265 ms
64 bytes from 10.88.49.134: icmp_seq=6 ttl=64 time=0.699 ms
64 bytes from 10.88.49.134: icmp_seq=9 ttl=64 time=0.482 ms
64 bytes from 10.88.49.134: icmp_seq=12 ttl=64 time=0.405 ms
64 bytes from 10.88.49.134: icmp_seq=15 ttl=64 time=0.430 ms


14. Check all
[root@localhost ~]# mmm_control checks
db2 ping [last change: 2013/02/19 12:41:45] OK
db2 mysql [last change: 2013/02/19 12:41:45] OK
db2 rep_threads [last change: 2013/02/19 12:41:45] OK
db2 rep_backlog [last change: 2013/02/19 12:41:45] OK: Backlog is null
db3 ping [last change: 2013/02/19 12:41:45] OK
db3 mysql [last change: 2013/02/19 12:41:45] OK
db3 rep_threads [last change: 2013/02/19 12:41:45] OK
db3 rep_backlog [last change: 2013/02/19 12:41:45] OK: Backlog is null
db1 ping [last change: 2013/02/19 12:41:45] OK
db1 mysql [last change: 2013/02/19 12:41:45] OK
db1 rep_threads [last change: 2013/02/19 12:41:45] OK
db1 rep_backlog [last change: 2013/02/19 12:41:45] OK: Backlog is null

15 Check m<->m<->s change
15.1change writer from 10.88.49.118 to 10.88.49.119 Stop mysqld in db1 10.88.49.118
show slave status on 10.88.49.122, see Master_Host is '10.88.49.118'
[root@oracle ~]# service mysqld56 stop
Shutting down MySQL... SUCCESS!


15.1.1 show info in monitor host log
[root@localhost ~]# tail -f /var/log/mysql-mmm/mmm_mond.log
2013/02/20 10:34:42 INFO Removing all roles from host 'db1':
2013/02/20 10:34:42 INFO Removed role 'reader(10.88.49.134)' from host 'db1'
2013/02/20 10:34:42 INFO Removed role 'writer(10.88.49.130)' from host 'db1'
2013/02/20 10:34:42 INFO Orphaned role 'writer(10.88.49.130)' has been assigned to 'db2'
2013/02/20 10:34:42 INFO Orphaned role 'reader(10.88.49.134)' has been assigned to 'db3'
15.1.2 show info in slave host of 10.88.49.122, slave will change its master_host
[root@localhost ~]# mysql -P3307 -S /data56/mysql.sock -p123456
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.88.49.119


15.1.3 but there are some bad info in monitor host of 10.88.49.123, when you run 'mmm_control show', it may hold on .
2013/02/20 10:37:25 DEBUG Listener: Waiting for connection...
2013/02/20 10:37:28 DEBUG Listener: Waiting for connection...

15.1.4 [] why ?
i have solved this problem, it maybe occur when:
(1): 'peer' paramter is wrong .
(2): 'ping_ips' and 'ips' are wrong


15.2 change writer from 10.88.49.119 to 10.88.49.118
15.2.1 start mysqld in 10.88.49.118, stop mysqld in 10.88.49.119, run 'mmm_control set_online db1' in monitor_host
15.2.2 show slave info in slave_host of 10.88.49.122
[root@localhost ~]# mysql -P3307 -S /data56/mysql.sock -p123456
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.88.49.118
15.3 stop mysqld on slave_host of 10.88.49.122
15.3.1 show info in monitor_host
[root@localhost mysql-mmm]# mmm_control show
db1(10.88.49.118) master/ONLINE. Roles: reader(10.88.49.134), reader(10.88.49.135), writer(10.88.49.130)
db2(10.88.49.119) master/ONLINE. Roles: reader(10.88.49.131), reader(10.88.49.132), reader(10.88.49.133)
db3(10.88.49.122) slave/HARD_OFFLINE. Roles:
[] the ips change to mm.

15.3.2 start mysqld on slave_host of 10.88.49.122

15.3.3 show info in monitor_host
[root@localhost mysql-mmm]# mmm_control show
db1(10.88.49.118) master/ONLINE. Roles: reader(10.88.49.134), reader(10.88.49.135), writer(10.88.49.130)
db2(10.88.49.119) master/ONLINE. Roles: reader(10.88.49.131), reader(10.88.49.132), reader(10.88.49.133)
db3(10.88.49.122) slave/AWAITING_RECOVERY. Roles:
[] need set online
[root@localhost mysql-mmm]# 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]# mmm_control show
db1(10.88.49.118) master/ONLINE. Roles: reader(10.88.49.135), writer(10.88.49.130)
db2(10.88.49.119) master/ONLINE. Roles: reader(10.88.49.131), reader(10.88.49.133)
db3(10.88.49.122) slave/ONLINE. Roles: reader(10.88.49.132), reader(10.88.49.134)


15.4 stop master_host db2 of 10.88.49.119
15.4.1 show info in monitor_host
[root@localhost mysql-mmm]# mmm_control show
db1(10.88.49.118) master/ONLINE. Roles: reader(10.88.49.131), reader(10.88.49.135), writer(10.88.49.130)
db2(10.88.49.119) master/HARD_OFFLINE. Roles:
db3(10.88.49.122) slave/ONLINE. Roles: reader(10.88.49.132), reader(10.88.49.133), reader(10.88.49.134)
see db2 is HARD_OFFLINE

15.4.2 start master_host db2 of 10.88.49.119
[root@localhost mysql-mmm]# mmm_control show
db1(10.88.49.118) master/ONLINE. Roles: reader(10.88.49.131), reader(10.88.49.135), writer(10.88.49.130)
db2(10.88.49.119) master/AWAITING_RECOVERY. Roles:
db3(10.88.49.122) slave/ONLINE. Roles: reader(10.88.49.132), reader(10.88.49.133), reader(10.88.49.134)
see db2 is AWAITING_RECOVERY, so need set online
[root@localhost mysql-mmm]# 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]# mmm_control show
db1(10.88.49.118) master/ONLINE. Roles: reader(10.88.49.135), writer(10.88.49.130)
db2(10.88.49.119) master/ONLINE. Roles: reader(10.88.49.131), reader(10.88.49.132)
db3(10.88.49.122) slave/ONLINE. Roles: reader(10.88.49.133), reader(10.88.49.134)
see db2 is online


15.5 change writer to db2
mmm_control move_role writer db2

相关文章:

http://blog.csdn.net/hguisu/article/details/7349562
http://blog.chinaunix.net/uid-28437434-id-3471237.html
http://mysql-mmm.org/downloads ;
http://mysql-mmm.org/mmm2:guide
http://dev.mysql.com/doc/internals/en/optimizer-primary-optimizations.html

 
标签： MySQL 安装部署
反对 0举报 0 评论 0
 

免责声明：本文仅代表作者个人观点，与乐学笔记（本网）无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
    本网站有部分内容均转载自其它媒体，转载目的在于传递更多信息，并不代表本网赞同其观点和对其真实性负责，若因作品内容、知识产权、版权和其他问题，请及时提供相关证明等材料并与我们留言联系，本网站将在规定时间内给予删除等相关处理.

  • sql：mysql：函数：TIMESTAMPDIFF函数实现TimeStamp字段相减，求得时间差
    sql：mysql：函数：TIMESTAMPDIFF函数实现TimeS
     函数内指定是minute，则最终结果value值的单位是分钟，如果函数内指定为hours，则最终结果value值单位为小时。//UPLOAD_TIME 减去 CREATE_DTTM 求得时间差，以分钟数计时select avg(TIMESTAMPDIFF(MINUTE,CREATE_DTTM,UPLOAD_TIME)) value,LEFT(CREATE_DTTM
    03-08
  • mysql下如何执行sql脚本 执行SQL脚本
    1.编写sql脚本,假设内容如下:  create database dearabao;  use dearabao;  create table niuzi (name varchar(20));  保存脚本文件,假设我把它保存在F盘的hello world目录下,于是该文件的路径为:F:\hello world\niuzi.sql2.执行sql脚本,可以有2种方法: 
    02-10
  • MySQL 5.7版本sql_mode=only_full_group_by问题
    用到GROUP BY 语句查询时com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'col_user_6.a.START_TIME' which is not functionally dependent on colu
    02-10
  • Oracle迁移到MySQL性能下降的注意点 oracle数据
    背景：最近有较多的客户系统由原来由Oracle改造到MySQL后出现了性能问题CPU 100%，或是后台的CRM系统复杂SQL在业务高峰的时候出现堆积导致业务故障。在我的记忆里面淘宝最初从Oracle迁移到MySQL期间也遇到了很多SQL的性能问题，记忆最为深刻的子查询，当初的
    02-10
  • MySQL与Oracle 差异比较之六触发器
    触发器编号类别ORACLEMYSQL注释1创建触发器语句不同create or replace trigger TG_ES_FAC_UNIT  before insert or update or delete on ES_FAC_UNIT  for each rowcreate trigger `hs_esbs`.`TG_INSERT_ES_FAC_UNIT` BEFORE INSERT on `hs_esbs`.`es_fac_u
    02-10
  • mysql where条件：某时间字段为今天的sql语句
    1.查询：注册时间为今天的所有用户数：select count(*) from customer where TO_DAYS(createtime) = TO_DAYS(NOW())2.获取当前时间到凌晨24点还有多长时间：（Java中可用于判断某时间是否为今天）final Calendar cal = Calendar.getInstance();    ca
    02-10
  • mysql中的sql
    变量用户变量: 在用户变量前加@系统变量: 在系统变量前加@@运算符算术运算符有: +(加), -(减), * (乘), / (除) 和％ (求模) 五中运算位运算符有:(位于), | (位或), ^ (位异或), ~ (位取反),(位右移),(位左移)比较运算符有: = (等于),(大于),(小于), = (大
    02-10
  • mysql5.7配置文件修改sql_mode 重启无效解决方法。this is incompatible with sql_mode=only_full_group_by
    mysql5.7配置文件修改sql_mode 重启无效解决方
    whereis my.cnf找到配置路径：/etc/my.cnf找到[mysqld]，在下面添加sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION重要：如果没有[mysqld]，一定要先添加[mysqld]再在下
    02-10
  • mysql 8 查询报错(sql_mode=only_full_group_by)
    mysql 8 查询报错(sql_mode=only_full_group_by
    Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_
    02-10
  • Oracle、MySql、Sql Server比对
    MySql:廉价(部分免费)：当前，MySQL採用双重授权(DualLicensed)，他们是GPL和MySQLAB制定的商业许可协议。假设你在一个遵循GPL的***（开源）项目中使用MySQL，那么你能够遵循GPL协议免费使用MySQL。否则，你须要购买MySQLAB制定的那个商业许可协议。Windows $
    02-10
更多>MySQL教程
MySQL中表子查询与关联子查询的基础学习教程 CentOS系统下编译安装MySQL以及设置相关yum源的教程
更多>Oracle教程
Oracle数据库中表空间的基本管理操作小结 在Linux系统上同时监控多个Oracle数据库表空间的方法
更多>SQL Server
SQL SERVER修改函数名容易引发的问题分析 SQLSERVER分页查询关于使用Top方式和row_number()解析函数的不同
更多>MongoDB
教大家8天学通MongoDB——第一天 基础入门篇 MongoDB运行状态监控、性能分析工具mongostat详解
更多>PostgreSQL
PostgreSQL角色与用户管理介绍 Windows下PostgreSQL安装图解
点击排行
网站首页  |  关于我们  |  联系方式  |  使用协议  |  版权隐私  |  网站地图  |  网站留言  |  违规举报