[lvs-users] ldirectord + mysql woes

[Malcolm Turnbull]

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,  <support at 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" <tom at whyscream.net>
>> An: lvs-users at 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 at LinuxVirtualServer.org
>> Send requests to lvs-users-request at 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 at LinuxVirtualServer.org
> Send requests to lvs-users-request at 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/