LVS
lvs-users
Google
 
Web LinuxVirtualServer.org

Re: [lvs-users] ldirectord + mysql woes

To: lvs-users@xxxxxxxxxxxxxxxxxxxxxx
Subject: Re: [lvs-users] ldirectord + mysql woes
From: Tom Hendrikx <tom@xxxxxxxxxxxxx>
Date: Mon, 29 Jun 2015 16:38:31 +0200
-----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,  <support@xxxxxxxxxx> 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@xxxxxxxxxxxxx> An:
>>> lvs-users@xxxxxxxxxxxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxxxxxxxx Send requests to
>>> lvs-users-request@xxxxxxxxxxxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxxxxxxxx Send requests to
>> lvs-users-request@xxxxxxxxxxxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxxxxxxxx
Send requests to lvs-users-request@xxxxxxxxxxxxxxxxxxxxxx
or go to http://lists.graemef.net/mailman/listinfo/lvs-users
<Prev in Thread] Current Thread [Next in Thread>