首页 / MYSQL / MySQL 高可用架构
MySQL 高可用架构
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 高可用架构，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含14109字，纯文字阅读大概需要21分钟。
内容图文
db1.pancou.com db1192.168.110.130 db2.pancou.com db2
192.168.110.131 db3.pancou.com db3
2、mysql的安装和配置
db1:
server-id = 1
log-slave-updates=true
#gtid-mode=on
#enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-threads=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=www.pancou.com
db2:
server-id = 2
log-slave-updates=true
#gtid-mode=on
#enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-threads=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=www.pancou.com
db3:
server-id = 3
log-slave-updates=true
#gtid-mode=on
#enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-threads=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=www.pancou.com
3、主从复制，和双主复制看前面复制章节
4、安装mysql-mmm
1. 安装监控程序
在管理服务器和数据库服务器上分别要运行mysql-mmm monitor和agent程序。下面分别安装：
前提要安装
#rpm -ivh epel-release-6-8.noarch.rpm
在管理服务器（192.168.110.130）上，执行下面命令：
# yum -y install mysql-mmm-monitor*
与monitor依赖的所有文件也会随之安装，但是有一个例外perl-Time-HiRes，所以还需要执行下面的命令：
[plain] view plain copy print?
# yum -y install perl-Time-HiRes*
2. 安装代理程序
# yum -y install mysql-mmm-agent*
在192.168.110.128和192.168.110.131 上分别安装：
# yum -y install mysql-mmm-agent*
5、配置MMM
1.配置agent文件，需要在db1,db2,db3分别配置
完成安装后，所有的配置文件都放到了/etc/mysql-mmm/下面。管理服务器和数据库服务器上都要包含一个共同
的文件mmm_common.conf，
在db1上配置：
active_master_role writer
<host default>
cluster_interface eth0
pid_path /var/run/mysql-mmm/mmm_agentd.pid
bin_path /usr/libexec/mysql-mmm/
replication_user repl_user
replication_password pancou
agent_user mmm-agent
agent_password mmm-agent
</host>
<host db1>
ip 192.168.110.128
mode master
peer db2
</host>
<host db2>
ip 192.168.110.130
mode master
peer db1
</host>
<host db3>
ip 192.168.110.131
mode slave
</host>
<role writer>
hosts db1, db2
ips 192.168.110.132
mode exclusive
</role>
<role reader>
hosts db2, db3
ips 192.168.110.133, 192.168.110.134
mode balanced
</role>
可以在db1上编辑该文件后，通过scp命令分别复制到monitor、db2、db3和db4上。
复制到db2上：
scp /etc/mysql-mmm/mmm_com.conf db2:/etc/mysql-mmm/
复制到db3上：
scp /etc/mysql-mmm/mmm_com.conf db3:/etc/mysql-mmm/
2. 编辑mmm_agent.conf。在数据库服务器上，还有一个mmm_agent.conf需要修改
db1:
# vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
# The ‘this‘ variable refers to this server. Proper operation requires
# that ‘this‘ server (db1 by default), as well as all other servers, have the
# proper IP addresses set in mmm_common.conf.
this db1
db2:
# vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
# The ‘this‘ variable refers to this server. Proper operation requires
# that ‘this‘ server (db1 by default), as well as all other servers, have the
# proper IP addresses set in mmm_common.conf.
this db2
db3:
# vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
# The ‘this‘ variable refers to this server. Proper operation requires
# that ‘this‘ server (db1 by default), as well as all other servers, have the
# proper IP addresses set in mmm_common.conf.
this db3
3. 编辑mmm_mon.confg。在管理服务器上，修改mmm_mon.conf文件
在db2上
# vim /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
<monitor>
ip 127.0.0.1
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.110.128,192.168.110.130,192.168.110.131
auto_set_online 60
# The kill_host_bin does not exist by default, though the monitor will
# throw a warning about it missing. See the section 5.10 "Kill Host
# Functionality" in the PDF documentation.
#
# kill_host_bin /usr/libexec/mysql-mmm/monitor/kill_host
#
</monitor>
<host default>
monitor_user mmm_monitor
monitor_password mmm_monitor
</host>
6、创建监控
MariaDB [(none)]> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.110.%‘ identified by ‘mmm_monitor‘;
MariaDB [(none)]> grant super,replication client,process on *.* to ‘mmm-agent‘@‘192.168.110.%‘ identified by ‘mmm-agent‘;
MariaDB [(none)]> grant replication slave on *.* to ‘repl_user‘@‘192.168.110.%‘ identified by ‘pancou‘;
MariaDB [(none)]> flush priviliges;
7、启动MMM
1. 在数据库服务器上启动代理程序
# service mysql-mmm-agent start
Starting MMM Agent Daemon: [ OK ]
2. 在管理服务器上启动监控程序
# service mysql-mmm-monitor start
Starting MMM Monitor Daemon: [ OK ]
8、在monitor上检查集群主机的状态
[root@www ~]# mmm_control checks all
db2 ping [last change: 2016/07/04 08:54:52] OK
db2 mysql [last change: 2016/07/04 08:54:52] OK
db2 rep_threads [last change: 2016/07/04 08:54:52] ERROR: Replication is broken
db2 rep_backlog [last change: 2016/07/04 08:54:52] OK: Backlog is null
db3 ping [last change: 2016/07/04 08:54:52] OK
db3 mysql [last change: 2016/07/04 08:55:57] OK
db3 rep_threads [last change: 2016/07/04 08:55:54] OK
db3 rep_backlog [last change: 2016/07/04 08:55:54] OK: Backlog is null
db1 ping [last change: 2016/07/04 08:54:52] OK
db1 mysql [last change: 2016/07/04 08:54:52] OK
db1 rep_threads [last change: 2016/07/04 08:55:25] ERROR: Replication is broken
db1 rep_backlog [last change: 2016/07/04 08:54:52] OK: Backlog is null
复制问题解决以后：
[root@www ~]# mmm_control checks all
db2 ping [last change: 2016/07/05 03:54:20] OK
db2 mysql [last change: 2016/07/05 03:54:20] OK
db2 rep_threads [last change: 2016/07/05 03:54:20] OK
db2 rep_backlog [last change: 2016/07/05 03:54:20] OK: Backlog is null
db3 ping [last change: 2016/07/05 03:54:20] OK
db3 mysql [last change: 2016/07/05 03:54:20] OK
db3 rep_threads [last change: 2016/07/05 03:54:20] OK
db3 rep_backlog [last change: 2016/07/05 03:54:20] OK: Backlog is null
db1 ping [last change: 2016/07/05 03:54:20] OK
db1 mysql [last change: 2016/07/05 03:54:20] OK
db1 rep_threads [last change: 2016/07/05 03:54:20] OK
db1 rep_backlog [last change: 2016/07/05 03:54:20] OK: Backlog is null
[root@www ~]# mmm_control show
# Warning: agent on host db1 is not reachable
# Warning: agent on host db3 is not reachable
db1(192.168.110.128) master/REPLICATION_FAIL. Roles:
db2(192.168.110.130) master/ONLINE. Roles: reader(192.168.110.128), reader(192.168.110.130), writer(192.168.110.132)
db3(192.168.110.131) slave/ONLINE. Roles:
复制问题解决以后：
[root@www ~]# mmm_control show
# Warning: agent on host db1 is not reachable
# Warning: agent on host db2 is not reachable
db1(192.168.110.128) master/ONLINE. Roles:
db2(192.168.110.130) master/ONLINE. Roles:
db3(192.168.110.131) slave/ONLINE. Roles:
iptales -F
[root@www ~]# mmm_control show
db1(192.168.110.128) master/ONLINE. Roles:
db2(192.168.110.130) master/ONLINE. Roles: reader(192.168.110.133), writer(192.168.110.132)
db3(192.168.110.131) slave/ONLINE. Roles: reader(192.168.110.134)
9、MMM高可用环境测试
在db2上：
[root@www ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@www ~]# iptables -F
在monitor上：
[root@www ~]# mmm_control show
db1(192.168.110.128) master/ONLINE. Roles: writer(192.168.110.132)
db2(192.168.110.130) master/HARD_OFFLINE. Roles:
db3(192.168.110.131) slave/ONLINE. Roles: reader(192.168.110.133), reader(192.168.110.134)
[root@www ~]# tail -f /var/log/mysql-mmm/mmm_mond.log
2016/07/05 07:38:33 FATAL Agent on host ‘db2‘ is reachable again
2016/07/05 07:38:41 FATAL Can‘t reach agent on host ‘db2‘
2016/07/05 07:38:45 FATAL Agent on host ‘db2‘ is reachable again
2016/07/05 07:45:43 FATAL Agent on host ‘db1‘ is reachable again
2016/07/05 07:48:48 FATAL Can‘t reach agent on host ‘db3‘
2016/07/05 07:49:03 FATAL Can‘t reach agent on host ‘db2‘
2016/07/05 07:49:12 FATAL Can‘t reach agent on host ‘db1‘
2016/07/05 07:49:18 FATAL Agent on host ‘db1‘ is reachable again
2016/07/05 07:49:34 FATAL Agent on host ‘db2‘ is reachable again
2016/07/05 07:49:46 FATAL Agent on host ‘db3‘ is reachable again
2016/07/05 07:56:00 FATAL State of host ‘db2‘ changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)
此时，db2,的状态由ONLINE 变为 HARD_OFFLINE，把db2的读角色转移到db3,写角色转移到db1.
[root@www ~]# service mysqld start
Starting MySQL.. SUCCESS!
2016/07/05 08:00:29 FATAL State of host ‘db2‘ changed from HARD_OFFLINE to AWAITING_RECOVERY
2016/07/05 08:01:29 FATAL State of host ‘db2‘ changed from AWAITING_RECOVERY to ONLINE because of auto_set_online(60 seconds). It was in state AWAITING_RECOVERY for 60 seconds
查看集群状态：
[root@www ~]# mmm_control show
db1(192.168.110.128) master/ONLINE. Roles: writer(192.168.110.132)
db2(192.168.110.130) master/ONLINE. Roles: reader(192.168.110.133)
db3(192.168.110.131) slave/ONLINE. Roles: reader(192.168.110.134)
[root@www ~]# mysql -ummm-monitor -p -h192.168.110.132
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9108
Server version: 10.0.15-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]>
[root@www ~]# mysql -ummm-monitor -p -h192.168.110.133
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 184
Server version: 10.0.15-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]>
[root@www ~]# mysql -ummm-monitor -p -h192.168.110.134
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9446
Server version: 10.0.15-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]>
本文出自 “linunx运维专题” 博客，请务必保留此出处http://lijianmin2008.blog.51cto.com/621678/1925092
MySQL 高可用架构
标签：mmm mysql mariadb 高可用 主从复制
本文系统来源：http://lijianmin2008.blog.51cto.com/621678/1925092
内容总结
以上是互联网集市为您收集整理的MySQL 高可用架构全部内容，希望文章能够帮你解决MySQL 高可用架构所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。