Mysql cluster实验
1 测试环境:
1.1 软件系统版本
Gentoo 3.8.13
Mysql 5.1.67
Haproxy-1.4.24
1.2 架构
1.3 主机信息
hostname
Network
interface
IP addr
note
主机编号
Mysql111
Enp2s1
192.168.254.111
master1
1
Mysql112
192.168.254.112
master2
2
Mysql121
192.168.254.121
master1/slave1
3
Mysql122
192.168.254.122
master1/slave2
4
Mysql123
192.168.254.123
master2/slave3
5
Mysql124
192.168.254.124
master2/slave4
6
Haproxy110
192.168.254.110
Double port
Enp2s4
192.168.254.120
1.Mysql111和mysql112为主主模式同步数据
2.Mysql121,mysql122,mysql123,mysql124和mysql111,mysql112为主从模式,其中mysql111和mysql121,mysql122为主从; mysql112和mysql123,mysql124为主从
3.当mysql112停止同步,mysql111为主库,代理停止发送请求到mysql112,mysql123,mysql124
4.当mysql111停止同步,mysql112为主库,代理停止发送请求到mysql111,mysql121,mysql122
5.当mysql111和mysql112同时停止同步,集群变为readonly状态
6.当mysql112宕机,mysql111为backup模式,haproxy停止发送请求到mysql112,mysql123,mysql124
7.当mysql111宕机,mysql112为backup模式,haproxy停止发送请求到mysql111,mysql121,mysql122
8.当mysql111和mysql112同时宕机,集群停止工作
2. 准备工作
主机1-6
Vi /etc/mysql/mysql.cnf
<a href="http://s3.51cto.com/wyfs02/M02/24/6E/wKiom1NPbTTzRSxgAAAo_XSqTEw824.jpg" target="_blank"></a>
如果有注销掉,或者修改为0.0.0.0
重启数据库
/etc/init.d/mysql restart
3. 配置mysql111和mysql112主主同步
3.1 主机1上
Vi /etc/mysql/my.cnf
主机 2上
<a href="http://s3.51cto.com/wyfs02/M02/24/6E/wKioL1NPbTTzxWPYAAB19obvD_w924.jpg" target="_blank"></a>
主机1和2上
添加同步用户
Mysql –uroot -p
Grant replication slave on *.* to ‘copy’@’192.168.254.%’ identified by ‘copy’;
<a href="http://s3.51cto.com/wyfs02/M00/24/6E/wKioL1NPbUjzzB1wAAESeKrTJVk961.jpg" target="_blank"></a>
如上图,root是原有的账号,copy是刚才添加的
记录日志文件名称和pos号
<a href="http://s3.51cto.com/wyfs02/M02/24/6E/wKiom1NPbYHQ1xfjAADKd1MXV-M741.jpg" target="_blank"></a>
<a href="http://s3.51cto.com/wyfs02/M01/24/6E/wKioL1NPbVii1Pt1AADORtlYVg4538.jpg" target="_blank"></a>
在主机1上
Change master to
Master_host=’192.168.254.112’,
Master_port=3306,
Master_user=’copy’,
Master_password=’copy’,
Master_log_file=’mysql-bin.000004’
Master_log_pos=’192’;
Start slave;
Show slave status
<a href="http://s3.51cto.com/wyfs02/M02/24/6E/wKioL1NPbZnjOqAbAAApA5910Sc475.jpg" target="_blank"></a>
在主机2上
Master_host=’192.168.254.111’,
Master_log_file=’mysql-bin.000005’,
Master_log_pos=’106’;
<a href="http://s3.51cto.com/wyfs02/M00/24/6E/wKiom1NPbdXxQagOAAAs_dvXX-o713.jpg" target="_blank"></a>
测试主主同步的可用性
分别在主机1和主机2 上创建个数据库,然后看另一台主机上是否存在就可以了
4. 在主机1,2,3,4,5,6上配置主从同步
在主机3-6上
备注:server-id不能相同,其他换成改主机主机号就可以了
重启mysql
在主机3-4上
Mysql –uroot –p
Show slave status;
<a href="http://s3.51cto.com/wyfs02/M00/24/6E/wKioL1NPbdLxGoldAAAmwj3DIMs209.jpg" target="_blank"></a>
在主机5-6上
Master_log_file=’mysql-bin.000004’,
<a href="http://s3.51cto.com/wyfs02/M02/24/6E/wKiom1NPbg7i80zpAAAmwj3DIMs683.jpg" target="_blank"></a>
测试
同上,分别在1和2上创建1个数据库,看是否同步
5 安装xinetd
在主机1-6上
Emerge –av xinetd
Rc-update add xinetd default
/etc/init.d/xinetd start
在主机1-2上
Vi /etc/xinetd.d/mysqlchk
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<code>#</code>
<code># /etc/xinetd.d/mysqlchk</code>
<code>service mysqlchk_write</code>
<code>{</code>
<code>flags = REUSE</code>
<code>socket_type = stream</code>
<code>port = 9200</code>
<code>wait = no</code>
<code>user = nobody</code>
<code>server = </code><code>/opt/mysqlchk_status</code><code>.sh</code>
<code>log_on_failure += USERID</code>
<code>disable = no</code>
<code>only_from =192.168.254.0</code><code>/24</code>
<code>}</code>
<code>service mysqlchk_replication</code>
<code>port = 9201</code>
<code>server = </code><code>/opt/mysqlchk_replication</code><code>.sh</code>
<code>only_from = 192.168.254.0</code><code>/24</code>
添加服务端口
Mysqlchk_write9200/tcp# mysqlchk_write
Mysqlchk_replication9201/tcp# mysqlchk_replication
备注:
如果9200和9201端口被占用也可以使用其他端口号
Vi /opt/mysqlchk_status.sh
<code>#!/bin/bash</code>
<code>MYSQL_HOST=</code><code>"localhost"</code>
<code>MYSQL_PORT=</code><code>"3306"</code>
<code>MYSQL_USERNAME=</code><code>"root"</code>
<code>MYSQL_PASSWORD=</code><code>"root"</code>
<code>ERROR_MSG=`</code><code>/usr/bin/mysql</code> <code>--host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e </code><code>"show databases;"</code> <code>2>dev</code><code>/null</code><code>`</code>
<code>if</code> <code>[ </code><code>"$ERROR_MSG"</code> <code>!= </code><code>""</code> <code>]</code>
<code>then</code>
<code># mysql is fine, return http 200</code>
<code>/bin/echo</code> <code>-e </code><code>"HTTP/1.1 200 OK\r\n"</code>
<code>/bin/echo</code> <code>-e </code><code>"Content-Type: Content-Type:text\r\n"</code>
<code>/bin/echo</code> <code>-e </code><code>"\r\n"</code>
<code>/bin/echo</code> <code>-e </code><code>"MySQL is running.\r\n"</code>
<code>else</code>
<code>#mysql is down, return http 503</code>
<code>/bin/echo</code> <code>-e </code><code>"HTTP/1.1 503 Service Unavailable\r\n"</code>
<code>/bin/echo</code> <code>-e </code><code>"Content-Type: Content-Type:text/plain\r\n"</code>
<code>/bin/echo</code> <code>-e </code><code>"MYSQL is *down*.\r\n"</code>
<code>fi</code>
Vi /opt/mysqlchk_replication.sh
<code>/usr/bin/mysql</code> <code>--host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e </code><code>"show slave status;"</code> <code>> </code><code>/tmp/check_repl</code><code>.txt</code>
<code>iostat=`</code><code>grep</code> <code>"Slave_IO_Running"</code> <code>/tmp/check_repl</code><code>.txt | </code><code>awk</code> <code>'{print $2}'</code><code>`</code>
<code>sqlstat=`</code><code>grep</code> <code>"Slave_SQL_Running"</code> <code>/tmp/check_repl</code><code>.txt | </code><code>awk</code> <code>'{print $2}'</code><code>`</code>
<code>#echo iostat:$iostat and sqlstat:$sqlstat</code>
<code>if</code> <code>[ </code><code>"$iostat"</code> <code>= </code><code>"No"</code> <code>] || [ </code><code>"$sqlstat"</code> <code>= </code><code>"No"</code> <code>];</code>
<code>#mysql is down,return http 503</code>
<code>/bin/echo</code> <code>-e </code><code>"MySQL replication is *down*.\r\n"</code>
<code>#mysql is fine,return http 200</code>
<code>/bin/echo</code> <code>-e </code><code>"MySQL replication is running.\r\n"</code>
<code>/usr/bin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e </code><code>"show slave status;"</code> <code>> /tmp/check_repl.txt</code>
<code>iostat=`grep </code><code>"Slave_IO_Running"</code> <code>/tmp/check_repl.txt | awk </code><code>'{print $2}'</code><code>`</code>
<code>sqlstat=`grep </code><code>"Slave_SQL_Running"</code> <code>/tmp/check_repl.txt | awk </code><code>'{print $2}'</code><code>`</code>
<code>#mysql </code><code>is</code> <code>down,</code><code>return</code> <code>http </code><code>503</code>
<code>/bin/echo -e </code><code>"HTTP/1.1 503 Service Unavailable\r\n"</code>
<code>/bin/echo -e </code><code>"Content-Type: Content-Type:text/plain\r\n"</code>
<code>/bin/echo -e </code><code>"\r\n"</code>
<code>/bin/echo -e </code><code>"MySQL replication is *down*.\r\n"</code>
<code>#mysql </code><code>is</code> <code>fine,</code><code>return</code> <code>http </code><code>200</code>
<code>/bin/echo -e </code><code>"HTTP/1.1 200 OK\r\n"</code>
<code>/bin/echo -e </code><code>"MySQL replication is running.\r\n"</code>
<code>flags = REUSE</code>
<code>socket_type = stream</code>
<code>port = 9201</code>
<code>wait = no</code>
<code>user = nobody</code>
<code>server = </code><code>/opt/mysqlchk_replication</code><code>.sh</code>
<code>log_on_failure += USERID</code>
<code>disable = no</code>
<code>only_from = 192.168.254.0</code><code>/24</code>
<code>/usr/bin/mysql</code> <code>--host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e </code><code>"show slave status\G"</code><code>></code><code>/tmp/check_repl</code><code>.txt</code>
<code># echo iostat:$iostat and sqlstat:$sqlstat</code>
<code>if</code> <code>[ </code><code>"$iostat"</code> <code>= </code><code>"No"</code> <code>] || [ </code><code>"$sqlstat"</code> <code>= </code><code>"No"</code> <code>]</code>
Chmod +x /opt/mysql*.sh
重启系统或者重启xinetd
reboot
|| /etc/init.d/xinetd restart
查看监听端口
<a href="http://s3.51cto.com/wyfs02/M01/24/6F/wKiom1NPbhmirX0AAAExUSeBVNs814.jpg" target="_blank"></a>
如上图9201和9200开始监听
<a href="http://s3.51cto.com/wyfs02/M01/24/6E/wKioL1NPbgDhg5Z_AAD32lj38qI744.jpg" target="_blank"></a>
如上图9201开始监听
7 haproxy 安装配置
下载haproxy*.tar.gz,这里我下载的是最新版本1.4.24
Tar zxvf haproxy-1.4.24.tar.gz
Cd haproxy-1.4.24
Make TARGET=linux2628
Make install
配置haproxy配置文件
## 在源码包里example目录下有个,可以参考!这里配置如下
Vi /etc/haproxy.cfg
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
<code>global</code>
<code>maxconn 40000</code>
<code>debug</code>
<code>#quiet</code>
<code>user haproxy</code>
<code>group haproxy</code>
<code>nbproc 1</code>
<code>log 127.0.0.1 local3</code>
<code>spread-checks 2</code>
<code>defaults</code>
<code>timeout server 3s</code>
<code>timeout connect 3s</code>
<code>timeout client 60s</code>
<code>timeout http-request 3s</code>
<code>timeout queue 3s</code>
<code>frontend db_write</code>
<code>bind 192.168.254.110:3306</code>
<code>default_backend cluster_db_write</code>
<code>frontend db_read</code>
<code>bind 192.168.254.120:3306</code>
<code>default_backend cluster_db_read</code>
<code>frontend web_haproxy_status</code>
<code>bind *:80</code>
<code>default_backend web_status</code>
<code>frontend monitor_mysql111</code>
<code>bind 127.0.0.1:9301</code>
<code>mode http</code>
<code>acl no_repl_mysql111 nbsrv(mysql111_replication) </code><code>eq</code> <code>0</code>
<code>acl no_repl_mysql112 nbsrv(mysql112_replication) </code><code>eq</code> <code>0</code>
<code>acl no_mysql111 nbsrv(mysql111_status) </code><code>eq</code> <code>0</code>
<code>acl no_mysql112 nbsrv(mysql112_status) </code><code>eq</code> <code>0</code>
<code>monitor-uri </code><code>/dbs</code>
<code>monitor fail unless no_repl_mysql111 no_repl_mysql112 no_mysql112</code>
<code>monitor fail </code><code>if</code> <code>no_mysql111 no_mysql112</code>
<code>frontend monitor_mysql112</code>
<code>bind 127.0.0.1:9302</code>
<code>monitor fail unless no_repl_mysql111 no_repl_mysql112 no_mysql111</code>
<code>frontend monitor_mysql121</code>
<code>bind 127.0.0.1:9303</code>
<code>acl no_repl_mysql121 nbsrv(mysql121_replication) </code><code>eq</code> <code>0</code>
<code>acl no_mysql112 nbsrv(mysql112_status) </code><code>eq</code> <code>1</code>
<code>monitor fail </code><code>if</code> <code>no_repl_mysql121</code>
<code>monitor fail </code><code>if</code> <code>no_repl_mysql111 no_mysql112</code>
<code>frontend monitor_mysql122</code>
<code>bind 127.0.0.1:9304</code>
<code>acl no_repl_mysql122 nbsrv(mysql122_replication) </code><code>eq</code> <code>0</code>
<code>monitor fail </code><code>if</code> <code>no_repl_mysql122</code>
<code>monitor fail </code><code>if</code> <code>no_repl_mysql111 no_mysql112</code>
<code>frontend monitor_mysql123</code>
<code>bind 127.0.0.1:9305</code>
<code>acl no_repl_mysql123 nbsrv(mysql123_replication) </code><code>eq</code> <code>0</code>
<code>acl no_mysql111 nbsrv(mysql111_status) </code><code>eq</code> <code>1</code>
<code>monitor fail </code><code>if</code> <code>no_repl_mysql123</code>
<code>monitor fail </code><code>if</code> <code>no_repl_mysql112 no_mysql111</code>
<code>frontend monitor_mysql124</code>
<code>bind 127.0.0.1:9306</code>
<code>acl no_repl_mysql124 nbsrv(mysql124_replication) </code><code>eq</code> <code>0</code>
<code>monitor fail </code><code>if</code> <code>no_repl_mysql124</code>
<code>frontend monitor_splitbrain</code>
<code>bind 127.0.0.1:9300</code>
<code>acl no_repl01 nbsrv(mysql111_replication) </code><code>eq</code> <code>0</code>
<code>acl no_repl02 nbsrv(mysql112_replication) </code><code>eq</code> <code>0</code>
<code>acl mysql111 nbsrv(mysql111_status) </code><code>eq</code> <code>1</code>
<code>acl mysql112 nbsrv(mysql112_status) </code><code>eq</code> <code>1</code>
<code>monitor fail unless no_repl01 no_repl02 mysql111 mysql112</code>
<code>backend mysql111_replication</code>
<code>mode tcp</code>
<code>balance roundrobin</code>
<code>option tcpka</code>
<code>option httpchk</code>
<code>server mysql111 192.168.254.111:3306 check port 9201 inter 1s rise 1 fall 1</code>
<code>backend mysql112_replication</code>
<code>server mysql112 192.168.254.112:3306 check port 9201 inter 1s rise 1 fall 1</code>
<code>backend mysql121_replication</code>
<code>server mysql121 192.168.254.121:3306 check port 9201 inter 1s rise 1 fall 1</code>
<code>backend mysql122_replication</code>
<code>server mysql122 192.168.254.122:3306 check port 9201 inter 1s rise 1 fall 1</code>
<code>backend mysql123_replication</code>
<code>server mysql123 192.168.254.123:3306 check port 9201 inter 1s rise 1 fall 1</code>
<code>backend mysql124_replication</code>
<code>server mysql124 192.168.254.124:3306 check port 9201 inter 1s rise 1 fall 1</code>
<code>backend mysql111_status</code>
<code>server mysql111 192.168.254.111:3306 check port 9200 inter 1s rise 2 fall 2</code>
<code>backend mysql112_status</code>
<code>server mysql112 192.168.254.112:3306 check port 9200 inter 1s rise 2 fall 2</code>
<code>backend cluster_db_write</code>
<code>option httpchk GET </code><code>/dbs</code>
<code>server mysql111 192.168.254.111:3306 weight 1 check port 9201 inter 1s rise 5 fall 1</code>
<code>server mysql112 192.168.254.112:3306 weight 1 check port 9201 inter 1s rise 5 fall 1 backup</code>
<code>server mysql111_backup 192.168.254.111:3306 weight 1 check port 9301 inter 1s rise 2 fall 2 addr 127.0.0.1 backup</code>
<code>server mysql112_backup 192.168.254.112:3306 weight 1 check port 9302 inter 1s rise 2 fall 2 addr 127.0.0.1 backup</code>
<code>backend cluster_db_read</code>
<code>server mysql111 192.168.254.111:3306 weight 1 track cluster_db_write</code><code>/mysql111</code>
<code>server mysql112 192.168.254.112:3306 weight 1 track cluster_db_write</code><code>/mysql112</code>
<code>server mysql111_backup 192.168.254.111:3306 weight 1 track cluster_db_write</code><code>/mysql111_backup</code>
<code>server mysql112_backup 192.168.254.112:3306 weight 1 track cluster_db_write</code><code>/mysql112_backup</code>
<code>server mysql111_splitbrain 192.168.254.111:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1</code>
<code>server mysql112_splitbrain 192.168.254.112:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1</code>
<code>server mysql121_slave 192.168.254.121:3306 weight 1 check port 9303 inter 1s rise 5 fall 1 addr 127.0.0.1</code>
<code>server mysql122_slave 192.168.254.122:3306 weight 1 check port 9304 inter 1s rise 5 fall 1 addr 127.0.0.1</code>
<code>server mysql123_slave 192.168.254.123:3306 weight 1 check port 9305 inter 1s rise 5 fall 1 addr 127.0.0.1</code>
<code>server mysql124_slave 192.168.254.124:3306 weight 1 check port 9306 inter 1s rise 5 fall 1 addr 127.0.0.1</code>
<code>backend web_status</code>
<code>stats </code><code>enable</code>
<code>#stats scope</code>
<code>#stats hide-version</code>
<code>stats refresh 5s</code>
<code>stats uri </code><code>/status</code>
<code>stats realm Haproxy</code><code>/statistics</code>
<code>stats auth copy:copy</code>
启动haproxy
/usr/local/sbin/haproxy –f /etc/haproxy.cfg
查看状态
<a href="http://s3.51cto.com/wyfs02/M00/24/6F/wKiom1NPbj_zDrr5AABo9FHlYac167.jpg" target="_blank"></a>
访问haproxy监控页面
<a href="http://192.168.254.110/status" target="_blank">http://192.168.254.110/status</a>
<a href="http://s3.51cto.com/wyfs02/M02/24/6E/wKioL1NPbiygz2ECAAfw_ikTLrA701.jpg" target="_blank"></a>
<a href="http://s3.51cto.com/wyfs02/M01/24/6F/wKiom1NPblax55SGAAU0VTgkczE090.jpg" target="_blank"></a>
到这里配置完成
测试,按照以下步骤看是否可以达到要求
1.停止主机2,的同步
2.停止主机1,2的同步
3.停止主机1的同步
4.断开主机1网络或者关闭它
5.断开主机2网络或者关闭它
6.同时端口主机1,2网络或者关闭它们
本文转自 chengchow 51CTO博客,原文链接:http://blog.51cto.com/chengchow/1397138,如需转载请自行联系原作者