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 [email protected] 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 <[email protected]> wrote: > Hi, > > > 2017-06-11 17:37 GMT+02:00 Luis Daniel Lucio Quiroz < > [email protected]>: > >> 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 > >
