Thanks to everyone Le 11 juin 2017 1:13 PM, "Andrew Smalley" <asmal...@loadbalancer.org> a écrit :
> HI Louis > > You asked about loadbalancing MySQL. > > What we suggest is two separate VIP's in TCP Mode on port 3306 > > One will be your Write VIP and the other will be your Read VIP > > Below is an example configuration with a ReadWriteMaster Server along with > read and write only servers in each VIP - You may not want a real server > with ReadWriteMaster but I have included this as an example as I wanted my > master server to be both read and write and the replication master. You may > not opt for a multimaster but here is a link to set that up > > https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster- > replication-multi-master.html > > Its worth noting the option "option mysql-check user checkuser post-41" > which will attempt to connect to MySQL real servers with user "checkuser" > > > Now your application will connect to 192.168.0.99 for writes and > 192.168.0.100 for reads. I hope this all makes sense > > listen MySQL_Write > bind 192.168.0.99:3306 transparent > mode tcp > balance leastconn > stick on src > stick-table type ip size 10240k expire 30m peers loadbalancer_replication > server backup 127.0.0.1:9081 backup non-stick > option mysql-check user checkuser post-41 > option redispatch > option abortonclose > maxconn 40000 > email-alert mailers email_mailer > server ReadWriteMaster 192.168.0.50 weight 100 check inter 4000 rise > 2 fall 2 minconn 0 maxconn 0 on-marked-down shutdown-sessions server > Write1 172.16.0.10 weight 100 check inter 4000 rise 2 fall 2 minconn 0 > maxconn 0 on-marked-down shutdown-sessions > server Write2 172.16.0.11 weight 100 check inter 4000 rise 2 fall 2 > minconn 0 maxconn 0 on-marked-down shutdown-sessions > > listen MySQL_Read > bind 192.168.0.100:3306 transparent > mode tcp > balance leastconn > stick on src > stick-table type ip size 10240k expire 30m peers loadbalancer_replication > server backup 127.0.0.1:9081 backup non-stick > option mysql-check user checkuser post-41 > option redispatch > option abortonclose > maxconn 40000 > email-alert mailers email_mailer > server ReadWriteMaster 192.168.0.50 weight 100 check inter 4000 rise > 2 fall 2 minconn 0 maxconn 0 on-marked-down shutdown-sessions server > Read1 192.168.0.12 weight 100 check inter 4000 rise 2 fall 2 minconn 0 > maxconn 0 on-marked-down shutdown-sessions > server Read2 192.168.0.13 weight 100 check inter 4000 rise 2 fall 2 > minconn 0 maxconn 0 on-marked-down shutdown-sessions > server Read3 192.168.0.14 weight 100 check inter 4000 rise 2 fall 2 > minconn 0 maxconn 0 on-marked-down shutdown-sessions > > > Andrew Smalley > > Loadbalancer.org > www.loadbalancer.org <https://www.loadbalancer.org?gclid=ES2017> > > <https://plus.google.com/+LoadbalancerOrg> > <https://twitter.com/loadbalancerorg> > <http://www.linkedin.com/company/3191352?trk=prof-exp-company-name> > <https://www.loadbalancer.org/?category=company&post-name=overview&?gclid=ES2017> > <https://www.loadbalancer.org?gclid=ES2017> > +1 888 867 9504 / +44 (0)330 380 1064 > asmal...@loadbalancer.org > > Leave a Review > <http://collector.reviews.io/loadbalancer-org-inc-/new-review> | Deployment > Guides > <https://www.loadbalancer.org/?category=resources&post-name=deployment-guides&?gclid=ES2017> > | Blog <https://www.loadbalancer.org/?category=blog&?gclid=ES2017> > > On 11 June 2017 at 17:51, Olivier Doucet <webmas...@ajeux.com> wrote: > >> Hi, >> >> >> 2017-06-11 17:37 GMT+02:00 Luis Daniel Lucio Quiroz < >> luis.daniel.lu...@gmail.com>: >> >>> Hi >>> >>> I'm new on the ML but not to haproxy. After googling I don't find what >>> I'm looking for. Maybe one of you knows it. >>> >>> I would like to force a specific MySQL server for writing operations >>> first policy (aka insert,update, delete) and let use round robin policy for >>> reading operations. >>> >>> Is this possible with haproxy? >>> >> >> Not with HAProxy. It does not understand MySQL L7, only HTTP. >> >> You can take a look at ProxySQL, MaxScale or MySQL Proxy for what you >> would like to achieve. >> A compare chart is available here : http://www.proxysql.com/compare >> >> Olivier >> >> >
