Tom, LVS certainly won't care what the traffic is but you probably need some kind of connection reset to make the new mysql server re-negotiate with the client. It may be your application that is confused by the server change.
On 29 June 2015 at 15:38, Tom Hendrikx <t...@whyscream.net> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA256 > > Hi Malcolm, > > I've tried this too, but it seemed that mysql on the fallback instance > received garbage. That is why I stated that it seems like the fallback > is explicitly designed for http traffic only... > > While this setup seems the most clean solution, I guess I'll have to > dig in using tcpdump then... > > I'll also try the suggestion from Horst, i.e. using the wrr scheduler. > > Regards, > Tom > > > On 29-06-15 10:04, Malcolm Turnbull wrote: >> Tom, >> >> I think you are just looking for the fallback functionality. i.e. >> if my one server dies then use the fallback server. >> >>> virtual=172.16.31.106:3306 real=172.16.31.103:3306 gate 1 # >>> mysql-1 fallback=172.16.31.102:3306 gate >> >> >> >> >> >> >> On 27 June 2015 at 20:02, <supp...@remsnet.de> wrote: >>> Hello Tom, >>> >>> >>> remove any sql real node weight's and use wrr scheduler , see >>> below. >>> >>> >>> I had used sutch ldirectord +mysql setup some years ago .... see >>> old howto : >>> https://www.howtoforge.com/loadbalanced_mysql_cluster_debian_p6 >>> ...stopped using it with heartbeat3 due funtionality on my >>> systems replaced by keepalived due quite faster IP handover.. >>> >>> "section 6.3 Configure ldirectord sample configuration file for >>> ldirectord, on the load balancers: >>> >>> "loadb1.example.com" / "loadb2.example.com": >>> >>> vi /etc/ha.d/ldirectord.cf >>> >>> # Global Directives checktimeout=10 checkinterval=2 >>> autoreload=no logfile="local0" quiescent=yes >>> >>> virtual = 192.168.0.105:3306 service = mysql real = >>> 192.168.0.101:3306 gate real = 192.168.0.102:3306 gate checktype >>> = negotiate login = "ldirector" passwd = "ldirectorpassword" >>> database = "ldirectordb" request = "SELECT * FROM >>> connectioncheck" scheduler = wrr >>> >>> Please fill in the correct virtual IP address (192.168.0.105) and >>> the correct IP addresses of your MySQL cluster nodes >>> (192.168.0.101 and 192.168.0.102). 3306 is the port that MySQL >>> runs on by default. We also specify a MySQL user (ldirector) and >>> password (ldirectorpassword), a database (ldirectordb) and an SQL >>> query. ldirectord uses this information to make test requests to >>> the MySQL cluster nodes to check if they are still available. We >>> are going to create the ldirector database with the ldirector >>> user in the next step." >>> >>> >>> >>> >>> ---------- besides of that.. >>> >>> Today i am use keepalived for that Mysql N+3 sized ndb cluster >>> with realtime storange sync and + one offside clone node equal >>> like this : http://www.databaseskill.com/1396079/ >>> >>> I suggest you to install & setup an Galea-Mariadb mysql cluster + >>> keepalived as an 5 node concept, 3 sql/storange nodes and 2 for >>> the loadbalancers. >>> >>> Google has a lot of howtow's for keepalived... >>> >>> Hope this helps. >>> >>> -- Mit freundlichen Grüßen / Best Regards >>> >>> Horst Venzke ; PGP NET : 1024G/082F2E6D ; http://www.remsnet.de >>> - 1995 - 2015 - 20 Jahre Linux/Unix Support. >>> >>> Legal Notice: This transmittal and/or attachments may be >>> privileged or confidential. It is intended solely for the >>> addressee named above. Any review, dissemination, or copying is >>> strictly prohibited. If you received this transmittal in error, >>> please notify us immediately by reply and immediately delete this >>> message and all >>> >>> >>>> Gesendet: Montag, 22. Juni 2015 um 12:34 Uhr Von: "Tom >>>> Hendrikx" <t...@whyscream.net> An: >>>> lvs-users@linuxvirtualserver.org Betreff: [lvs-users] >>>> ldirectord + mysql woes >>>> >>>> Hi, >>>> >>>> I've been trying to get mysql failover working using >>>> ldirectord. The general idea is to have 2 mysql instances >>>> running in master-master setup (works), then have a ldirectord >>>> in front of that sending all queries to server mysql-1 (works), >>>> and have it send the queries to the other mysql server >>>> (mysql-2) when server mysql-1 goes down (does not work). >>>> >>>> I've tried several options in ldirectord config, but no luck. >>>> Current config is: >>>> >>>> >>>> checktimeout=3 checkinterval=1 failurecount=5 autoreload=yes >>>> logfile="/var/log/ldirectord.log" >>>> >>>> virtual=172.16.31.106:3306 real=172.16.31.103:3306 gate 65535 # >>>> mysql-1 real=172.16.31.102:3306 gate 1 # mysql-2 service=mysql >>>> scheduler=wlc protocol=tcp checktype=negotiate quiescent=no >>>> login="ldirectord" passwd="redacted" database="mydb" >>>> request="SELECT COUNT(1) from mytable" >>>> >>>> >>>> When I stop mysql at mysql-1, the queries all fail, they don't >>>> seem to be sent to mysql-2. >>>> >>>> When I switch the weights in the real= lines, all traffic goes >>>> to mysql-2 (so mysql-2 + ldirectord setup seems to be >>>> functional), but when I take down mysql on server mysql-2, the >>>> traffic does not go to mysql-1 and queries fail again. >>>> >>>> I also tried to configure one server as real, and the other as >>>> fallback, but this does not work either. ldirectord >>>> documentation is not really clear on this, but it seems that >>>> the fallback directive is explicitly deviced for returning http >>>> traffic? >>>> >>>> Can somebody shed any light on how to get this working? Or how >>>> to debug why the queries are failing (when the setup is deemed >>>> correct)? >>>> >>>> Kind regards, Tom >>>> >>>> _______________________________________________ Please read the >>>> documentation before posting - it's available at: >>>> http://www.linuxvirtualserver.org/ >>>> >>>> LinuxVirtualServer.org mailing list - >>>> lvs-users@LinuxVirtualServer.org Send requests to >>>> lvs-users-requ...@linuxvirtualserver.org or go to >>>> http://lists.graemef.net/mailman/listinfo/lvs-users >>>> >>> _______________________________________________ Please read the >>> documentation before posting - it's available at: >>> http://www.linuxvirtualserver.org/ >>> >>> LinuxVirtualServer.org mailing list - >>> lvs-users@LinuxVirtualServer.org Send requests to >>> lvs-users-requ...@linuxvirtualserver.org or go to >>> http://lists.graemef.net/mailman/listinfo/lvs-users >> >> >> > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v2 > > iQIcBAEBCAAGBQJVkVhnAAoJEJPfMZ19VO/1iYsP/2FeP02Y7FwMxHLLny15Fovu > R7ppQpZj42Cll3UgREsusviSrDzr2HZXBwxPOdUblKCcKY6XkOcw32YAbCF3F2B0 > HlBO8TkPOAYjP2T/DZeNQ4S0x3wRnWc+GoE0D1/63jNR6aHKrfnu1tyj4KafJsZp > yX9LYKWhqmDngGbnY1Krcf5kcjpr9KB21imGVJbQrniyU0YP8fiA5GIdUaNC6IkC > ANcpLtKyKV2w6LV5BK5MhaaoLboVZzpf9djWH4qGHwjQdM/seA9458TBOjj909ut > lN2JCjSFes3QL3DD7Jlfedf0uwds2UEtVaDf/04lN01PmaF1qw2T6eeqCHUqwK0P > rurYIO6b2Y9//ROP6OLPt4gTY4whZ4WlkrYiJ4m/tDM+ubWellpe56IyqUU0RJBP > aycDLgbAZrfEslTUNjCMKjDULvJe3yYc+2ee1tyANNmRu70UI/tcnnuAKewMYYuE > mxxHTvTZgBzOfCqcuyL13SPeP/qQbvcuilr0HUvHQmK5CkR7TQivkKwdA2ntF1VV > Qc79QUZAzqhMSvJWCZsE8RTti8F3+mGRkwl6whZ0lI23Zs+/lDc3x4/wijplAzIG > P9GubNbvwW/OLBKieb3DoeokuibU8sZ6pd730b1WJktNNHehy+4CSpY/P1JP/ZIl > AgADIKBb5ga/9ehQ3huG > =n2U/ > -----END PGP SIGNATURE----- > > _______________________________________________ > Please read the documentation before posting - it's available at: > http://www.linuxvirtualserver.org/ > > LinuxVirtualServer.org mailing list - lvs-users@LinuxVirtualServer.org > Send requests to lvs-users-requ...@linuxvirtualserver.org > or go to http://lists.graemef.net/mailman/listinfo/lvs-users -- Regards, Malcolm Turnbull. Loadbalancer.org Ltd. Phone: +44 (0)330 1604540 http://www.loadbalancer.org/ _______________________________________________ Please read the documentation before posting - it's available at: http://www.linuxvirtualserver.org/ LinuxVirtualServer.org mailing list - lvs-users@LinuxVirtualServer.org Send requests to lvs-users-requ...@linuxvirtualserver.org or go to http://lists.graemef.net/mailman/listinfo/lvs-users