|
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@xxxxxxxxxxxxx> 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@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
>>>>
>>>>
>>>>
>
>>
>> _______________________________________________
>> 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
|
