Haproxy load balancer MySQL dual master

Haproxy load balancer MySQL dual master

650) this.width=650; "height=" 356 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ qve5nzzvbuqffqo9g6peti58q4ijtp*tc3tbzfscke0!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kgjlaqaaaaadagk! &su=0194203249&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgybaaaaaaaa&ek=1&kp=1&pt=0&bo= Kgjlaqaaa "/>

OS	Ip	Subnet mask	Routing gateways
Centos6.6 (Db:mysql)	eth0:192.168.26.211	255.255.252.0	192.168.25.3
Centos6.6 (Db:mysql)	eth0:192.168.26.212	255.255.252.0	192.168.25.3
Centos6.6 (HAproxy)	eth0:192.168.26.210	255.255.252.0	192.168.25.3

Installing mysql:192.168.26.211

Yum Install–y Mysql-server

650) this.width=650; "height=" 279 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/njopt6q* eoqsvdfenefgjzgmzptir.sqxxfsqpue9no!/b/daybaaaaaaaa&ek=1&kp=1&pt=0&bo=kgiyaqaaaaadabq!&su= 0119462769&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px;border-width : 0px;border-style:none;vertical-align:top; "alt=" Daybaaaaaaaa&ek=1&kp=1&pt=0&bo=kgiyaqaaa "/>

Service mysqld Start

650) this.width=650; "height=" 370 "src=" http://a4.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ 9js8zodbcuvtofqnemgl28ll3nuadcktjprr.gvseqe!/b/dgcbaaaaaaaa&ek=1&kp=1&pt=0&bo=kgjzaqaaaaadah8! &su=0154621905&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgcbaaaaaaaa&ek=1&kp=1&pt=0&bo= Kgjzaqaaa "/>

Installing mysql:192.168.26.212

Yum Install–y Mysql-server

650) this.width=650; "height=" 314 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ cqy4izmpbkd9rwebgdzpdza.4nhtohvwmlbch5sh.zm!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kgi7aqaaaaadadc! &su=0175416305&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgybaaaaaaaa&ek=1&kp=1&pt=0&bo= Kgi7aqaaa "/>

Service mysqld Start

650) this.width=650; "height=" 331 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ upy5oyijbc31ycmlm5ifx0a2bhym5xkoac.uytgnxko!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kqjmaqaaaaadaem! &su=073170881&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 552 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgybaaaaaaaa&ek=1&kp=1&pt=0&bo= Kqjmaqaaa "/>

Configure MySQL dual master: 192.168.26.211

Vim/etc/my.cnf

The configuration file is as follows:

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

server-id=211

Log-bin=mysq-bin

Relay-log=relay-bin

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld

650) this.width=650; "height=" 223 "src=" http://a4.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ c3vhovvsre.rs5gks0bykeyoapa*gv6shtdlptoo3xs!/b/dgcbaaaaaaaa&ek=1&kp=1&pt=0&bo=aahgaaaaaaadakw! &su=0112144289&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 359 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgcbaaaaaaaa&ek=1&kp=1&pt=0&bo= Aahgaaaaa "/>

To create a remote ownership account:

Grant replication Slave,replication Client on * * to ' Jerry ' @ ' 192.168.%.% ' identified by ' jerrypass ';

650) this.width=650; "height=" 280 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ r3su9lj9fgjtfs33tox6sorezwe7dzsvql6.zc4lrzi!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kgizaqaaaaadabu! &su=0256170209&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgybaaaaaaaa&ek=1&kp=1&pt=0&bo= Kgizaqaaa "/>

Change Master to master_host= ' 192.168.26.212 ', master_user= ' Jerrys ', master_password= ' Jerrypass ', master_log_file= ' Mysql-bin.000001 ', master_log_pos=282;

650) this.width=650; "height=" 281 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ sl.ipswei8dveupdra8zgzlgyvbpi0uybgxxq7vqyeq!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kgiaaqaaaaadaby! &su=0150802337&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgybaaaaaaaa&ek=1&kp=1&pt=0&bo= Kgiaaqaaa "/>

650) this.width=650; "height=" 285 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/SlUAKcRDOoW4x *rqcytfw4iy2u4izw0wcjvm4qz15i8!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kgieaqaaaaadabi!&su= 0242284209&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px;border-width : 0px;border-style:none;vertical-align:top; "alt=" Dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kgieaqaaa "/>

Start slave;

650) this.width=650; "height=" 283 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/h42ejpxxj** uo3pkisuef3nzhbfgol*tlujg2aareqy!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kgicaqaaaaadaba!&su= 0150935921&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px;border-width : 0px;border-style:none;vertical-align:top; "alt=" Dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kgicaqaaa "/>

650) this.width=650; "height=" 248 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ buzbhc9wi3cfth7a6ry0tu5ofobu8r5zzb*19*3xvw8!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kgl5aaaaaaadapq! &su=0155682369&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgybaaaaaaaa&ek=1&kp=1&pt=0&bo= Kgl5aaaaa "/>

650) this.width=650; "height=" 428 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ dsnkmek6nsyzjrmzjyaiwvupnqro4aou1kvb04f7m6a!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=gqktaqaaaaadaji! &su=0187317265&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 536 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgybaaaaaaaa&ek=1&kp=1&pt=0&bo= Gqktaqaaa "/>

Dual master configuration: 192.168.26.212

Vim/etc/my.cnf

The configuration file is as follows:

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

server-id=212

Log-bin=mysql-bin

Relay-log=relay-bin

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

650) this.width=650; "height=" 235 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ z95owasqx4v.apgvidtd8u31mkdpxprv*td6q.8ppmo!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kglsaaaaaaadaoe! &su=08599297&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgybaaaaaaaa&ek=1&kp=1&pt=0&bo= Kglsaaaaa "/>

Authorized Remote Access Account:

Grant replication Slave,replication Client on * * to ' Jerry ' @ ' 192.168.%.% ' identified by ' jerrypass ';

650) this.width=650; "height=" 227 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ 5boc.uxfjcv9rymtxc*6niatnqma.hy47a3r3cnyjk8!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kglkaaaaaaadaok! &su=0125551057&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgybaaaaaaaa&ek=1&kp=1&pt=0&bo= Kglkaaaaa "/>

Change Master to master_host= ' 192.168.26.211 ', master_user= ' Jerry ', master_password= ' Jerrypass ', master_log_file= ' Mysql-bin.000001 ', master_log_pos=281;

650) this.width=650; "height=" 276 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ vwr7uasck3gvybynk6abdfrvbweg0xcdy.5416lbd2y!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kgivaqaaaaadabk! &su=0170821489&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgybaaaaaaaa&ek=1&kp=1&pt=0&bo= Kgivaqaaa "/>

Start slave;

650) this.width=650; "height=" 289 "src=" http://a4.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/* elubzmcefpgzz8i5d3m5pzc6tn5k5amh9mec9crz68!/b/dgcbaaaaaaaa&ek=1&kp=1&pt=0&bo=kgiiaqaaaaadac4! &su=079112593&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgcbaaaaaaaa&ek=1&kp=1&pt=0&bo= Kgiiaqaaa "/>

650) this.width=650; "height=" 419 "src=" http://a4.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ Rximvjbojrl2dsc5rh.ldef2lieul2ekson4tofypj0!/b/dgcbaaaaaaaa&ek=1&kp=1&pt=0&bo=kakkaqaaaaadako! &su=022299857&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 551 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgcbaaaaaaaa&ek=1&kp=1&pt=0&bo= Kakkaqaaa "/>

Configure haproxy:192.168.26.210

Installation: Yum Install–y haproxy

Edit Profile: Vim/etc/haproxy/haproxy.cfg

The configuration file is as follows:

#---------------------------------------------------------------------

# Example configuration for a possible Web application. See the

# Full configuration options online.

#

# Http://haproxy.1wt.eu/download/1.4/doc/configuration.txt

#

#---------------------------------------------------------------------

#---------------------------------------------------------------------

# Global Settings

#---------------------------------------------------------------------

Global

# to has these messages end up In/var/log/haproxy.log you'll

# Need to:

#

# 1) Configure Syslog to accept network log events. This was done

# by adding the '-r ' option to the syslogd_options in

#/etc/sysconfig/syslog

#

# 2) Configure Local2 events to go to The/var/log/haproxy.log

# file. A line like the following can is added to

#/etc/sysconfig/syslog

#

# local2.*/var/log/haproxy.log

#

Log 127.0.0.1 Local2

Chroot/var/lib/haproxy

Pidfile/var/run/haproxy.pid

Maxconn 4000

User Haproxy

Group Haproxy

Daemon

# Turn on stats UNIX socket

Stats Socket/var/lib/haproxy/stats

#---------------------------------------------------------------------

# Common defaults that all the ' listen ' and ' backend ' sections would

# Use if not designated in their block

#---------------------------------------------------------------------

Defaults

Mode TCP

Log Global

Option Httplog

Option Dontlognull

Option Http-server-close

Option Forwardfor except 127.0.0.0/8

Option Redispatch

Retries 3

Timeout Http-request 10s

Timeout Queue 1m

Timeout Connect 10s

Timeout Client 1m

Timeout Server 1m

Timeout Http-keep-alive 10s

Timeout Check 10s

Maxconn 3000

#---------------------------------------------------------------------

# main frontend which proxys to the backends

#---------------------------------------------------------------------

#frontend Main *:5000

# ACL url_static path_beg-i/static/images/javascript/stylesheets

# ACL url_static path_end-i. jpg. gif. png. css. js

#

# use_backend Static If url_static

# Default_backend App

#

##---------------------------------------------------------------------

# # Static backend for serving up images, stylesheets and such

##---------------------------------------------------------------------

#backend Static

# balance Roundrobin

# Server static 127.0.0.1:4331 check

Frontend websrv *:3306

Default_backend mysqlservers

Backend Mysqlservers

Balance Leastconn

Server M1 192.168.26.211:3306 Check Port 3306 maxconn 300

Server M2 192.168.26.212:3306 Check port 3306 maxconn 300

Listen static

Mode http

Bind *:8008

Stats enable

Stats Uri/haadmin?stats

Stats Auth Admin:admin

Stats Hide-version

Stats Admin If TRUE

650) this.width=650; "height=" 307 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ exgedmzrcoivmqnortgef47wfd3c9gpn47dq8*q5wui!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=9ae0aqaaaaadaou! &su=082440289&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 499 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgybaaaaaaaa&ek=1&kp=1&pt=0&bo= 9ae0aqaaa "/>

650) this.width=650; "height=" 282 "src=" http://a4.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ Wjwwzpx69yxg1rmavlntxzntb5mu68kyaualg1ma*bi!/b/dgcbaaaaaaaa&ek=1&kp=1&pt=0&bo=haebaqaaaaadalo! &su=078094737&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 387 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgcbaaaaaaaa&ek=1&kp=1&pt=0&bo= Haebaqaaa "/>

650) this.width=650; "height=" 294 "src=" http://a3.qpic.cn/psb?/594581eb-e62e-4426-a878-953c87dd5729/ jnegudf1qlhjp3ojaoikq.qpwidd63c2cz2qolgo.fs!/b/dgybaaaaaaaa&ek=1&kp=1&pt=0&bo=kginaqaaaaadacs! &su=095378065&tm=1481871600&sce=0-12-12&rf=2-9 "width=" 553 "style=" margin:0px;padding:0px; border-width:0px;border-style:none;vertical-align:top; "Alt=" dgybaaaaaaaa&ek=1&kp=1&pt=0&bo= Kginaqaaa "/>

This article is from the "on the Road" blog, please be sure to keep this source http://jdonghong.blog.51cto.com/3473478/1883376

Haproxy load balancer MySQL dual master