Mailing List Archive

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@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-request@LinuxVirtualServer.org
> or go to http://lists.graemef.net/mailman/listinfo/lvs-users
>

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@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@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-request@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-request@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-request@LinuxVirtualServer.org
or go to http://lists.graemef.net/mailman/listinfo/lvs-users

-----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@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@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-request@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-request@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-request@LinuxVirtualServer.org
or go to http://lists.graemef.net/mailman/listinfo/lvs-users

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 <tom@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, <support@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@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-request@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-request@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-request@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-request@LinuxVirtualServer.org
or go to http://lists.graemef.net/mailman/listinfo/lvs-users

Hi,

Happily reporting that both scenarios work now. When using two real
servers with unbalanced load, I had to set the 'standby' server to take
at least 1 weight unit (0 didn't work). So working config is:

virtual=172.16.31.106:3306
real=172.16.31.102:3306 gate # mysql-1
fallback=172.16.31.103:3306 gate # mysql-2
scheduler=wrr

But this seems to work equally well (with the 1/65535 chance of a query
that can cause data inconsistency):

virtual=172.16.31.106:3306
real=172.16.31.102:3306 gate 65535 # mysql-1
fallback=172.16.31.103:3306 gate 1 # mysql-2
scheduler=wrr

I settled with the real+fallback setup since that perfectly documents
what the setup is supposed to be doing. In the end, it all came down to
using the proper scheduler. Thanks for all your help.

Regards,
Tom

On 29-06-15 19:34, Malcolm Turnbull wrote:
> 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 <tom@whyscream.net> wrote:
> 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@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@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-request@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-request@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-request@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-request@LinuxVirtualServer.org
or go to http://lists.graemef.net/mailman/listinfo/lvs-users

On 29-06-15 19:34, Malcolm Turnbull wrote:
...

> 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.

@malcom , thats the pitty part of ldirectord , still till today .
Ldirectord can not run more complex chat scripts .


Maybe your Team mates take over that ldirectord developemnt too :-) and enhance it ...


was reaseon for me to move to keepalived as its complex chat scripts capable ,
and quite better supported then any other lvs daemons.



--
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

Do you mean extended health checks?
We have an example here of external checks with our version of ldirectord:
http://blog.loadbalancer.org/ntlm-authenticating-proxy-check-script/

All of the code that Loadbalancer.org uses in LVS / Ldirectord /
HAProxy is open source,
I think Horms was looking at merging some of it into Ldirectord
...Although he would probably cringe at the quality of some of our
hacks :-).

However in the meantime if you want a copy of our version of
ldirectord just drop an email to support@loadbalancer.org and they
will send it to you.
Ps. Our Ldirectord also includes compatibility with the Feedback Agent
code (windows service agent is open source as well):
http://blog.loadbalancer.org/open-source-windows-service-for-reporting-server-load-back-to-haproxy-load-balancer-feedback-agent/

















On 1 July 2015 at 09:13, <support@remsnet.de> wrote:
> On 29-06-15 19:34, Malcolm Turnbull wrote:
> ...
>
>> 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.
>
> @malcom , thats the pitty part of ldirectord , still till today .
> Ldirectord can not run more complex chat scripts .
>
>
> Maybe your Team mates take over that ldirectord developemnt too :-) and enhance it ...
>
>
> was reaseon for me to move to keepalived as its complex chat scripts capable ,
> and quite better supported then any other lvs daemons.
>
>
>
> --
> 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
>
> _______________________________________________
> 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-request@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-request@LinuxVirtualServer.org
or go to http://lists.graemef.net/mailman/listinfo/lvs-users

Thanks malcome for infos.

I whuold just suggest to create an clean oss based ldirectord GIT same way as ipvsadm as my suggestion i pushed here to Horms and lvs folks +3 years ago .

ldirectord HACKS could be GIT branch of sutch GIT ...

how men find unknown fixes ? -> i.e google .
If no result - most simply give up and search for another stable solution witch was keepalived for me .


--
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: Mittwoch, 01. Juli 2015 um 10:37 Uhr
> Von: "Malcolm Turnbull" <malcolm@loadbalancer.org>
> An: "LinuxVirtualServer.org users mailing list." <lvs-users@linuxvirtualserver.org>
> Betreff: Re: [lvs-users] ldirectord + mysql woes
>
> Do you mean extended health checks?
> We have an example here of external checks with our version of ldirectord:
> http://blog.loadbalancer.org/ntlm-authenticating-proxy-check-script/
>
> All of the code that Loadbalancer.org uses in LVS / Ldirectord /
> HAProxy is open source,
> I think Horms was looking at merging some of it into Ldirectord
> ...Although he would probably cringe at the quality of some of our
> hacks :-).
>
> However in the meantime if you want a copy of our version of
> ldirectord just drop an email to support@loadbalancer.org and they
> will send it to you.
> Ps. Our Ldirectord also includes compatibility with the Feedback Agent
> code (windows service agent is open source as well):
> http://blog.loadbalancer.org/open-source-windows-service-for-reporting-server-load-back-to-haproxy-load-balancer-feedback-agent/
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> On 1 July 2015 at 09:13, <support@remsnet.de> wrote:
> > On 29-06-15 19:34, Malcolm Turnbull wrote:
> > ...
> >
> >> 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.
> >
> > @malcom , thats the pitty part of ldirectord , still till today .
> > Ldirectord can not run more complex chat scripts .
> >
> >
> > Maybe your Team mates take over that ldirectord developemnt too :-) and enhance it ...
> >
> >
> > was reaseon for me to move to keepalived as its complex chat scripts capable ,
> > and quite better supported then any other lvs daemons.
> >
> >
> >
> > --
> > 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
> >
> > _______________________________________________
> > 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-request@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-request@LinuxVirtualServer.org
> or go to http://lists.graemef.net/mailman/listinfo/lvs-users