Load balancing on Fedora with Heartbeat (MySQL Cluster)

Discussion in 'HOWTO-Related Questions' started by rwillie6, Nov 10, 2007.

  1. rwillie6

    rwillie6 New Member

    I'm following the tutorial on creating a MySQL cluster on 4 machines:

    mysql-1: load balancer (primary) and cluster management
    mysql-2: MySQL SQL node 1
    mysql-3: MySQL SQL node 2
    mysql4: load balancer (secondary)

    All machines previously only had a base install of Fedora 7. I've got the MySQL cluster up and running great, but I'm having trouble with the load balancing via heartbeat. Here is the output I receive whenever I try to stop ldirectout or start heartbeat:

    [root@mysql-1 ~]# /etc/init.d/ldirectord stop
    Stopping ldirectord Error [] reading file /etc/ha.d/ldirectord.cf at line 8: Unknown command service=mysql
    [FAILED]
    [root@mysql-1 ~]# /etc/init.d/heartbeat start
    logd is already running
    Starting High-Availability services:
    Error [] reading file /etc/ha.d/ldirectord.cf at line 8: Unknown command service=mysql
    [ OK ]

    However, mysql client is installed (on both load balancers, mysql-1 and mysql4), as verified on mysql-1 with:

    [root@mysql-1 ~]# mysql -u ldirector -h xxx.xxx.xxx.242 -P 3306 -p
    Enter password:
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 8
    Server version: 5.0.45 MySQL Community Server (GPL)
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    mysql>

    and on mysql4 with:

    [root@mysql4 ~]# mysql -u ldirector -h xxx.xxx.xxx.243 -P 3306 -p
    Enter password:
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.0.45 MySQL Community Server (GPL)
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    mysql>

    So, using the installed mysql client on mysql-1 or mysql4, the two load balancers, I am able to connect to either of the two sql nodes just fine.

    Here are all my configuration files:


    From mysql-1 (cluster management, and primary load balancer):

    /etc/modules:
    ip_vs_dh
    ip_vs_ftp
    ip_vs
    ip_vs_lblc
    ip_vs_lblcr
    ip_vs_lc
    ip_vs_nq
    ip_vs_rr
    ip_vs_sed
    ip_vs_sh
    ip_vs_wlc

    /etc/sysctl.conf:
    # Kernel sysctl configuration file for Red Hat Linux
    # sysctl.conf(5) for more details.
    # Controls IP packet forwarding
    net.ipv4.ip_forward = 1
    # Controls source route verification
    net.ipv4.conf.default.rp_filter = 1
    # Do not accept source routing
    net.ipv4.conf.default.accept_source_route = 0
    # Controls the System Request debugging functionality of the kernel
    kernel.sysrq = 0
    # Controls whether core dumps will append the PID to the core filename.
    # Useful for debugging multi-threaded applications.
    kernel.core_uses_pid = 1
    # Controls the use of TCP syncookies
    net.ipv4.tcp_syncookies = 1

    /etc/ha.d/ha.cf
    logfacility local0
    bcast
    mcast eth0 225.0.0.1 694 1 0
    auto_failback off
    node mysql-1
    node mysql4
    respawn hacluster /usr/lib/heartbeat/ipfail
    apiauth ipfail gid=haclient uid=hacluster

    /etc/ha.d/haresources
    mysql-1 ldirectord::ldirectord.cf \
    LVSSyncDaemonSwap::master \
    IPaddr2::192.168.0.105/24/eth0/192.168.0.255

    /etc/ha.d/authkeys
    auth 3
    3 md5 mypassword

    /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=xxx.xxx.xxx.242:3306 gate
    real=xxx.xxx.xxx.243:3306 gate
    checktype=negotiate
    login="ldirector"
    passwd="ldirectorpass"
    database="ldirectordb"
    request="SELECT * FROM connectioncheck"
    scheduler=wrr

    From mysql4 (secondary load balancer):

    /etc/modules
    ip_vs_dh
    ip_vs_ftp
    ip_vs
    ip_vs_lblc
    ip_vs_lblcr
    ip_vs_lc
    ip_vs_nq
    ip_vs_rr
    ip_vs_sed
    ip_vs_sh
    ip_vs_wlc

    /etc/sysctl.conf
    # Kernel sysctl configuration file for Red Hat Linux
    # sysctl.conf(5) for more details.
    # Controls IP packet forwarding
    net.ipv4.ip_forward = 1
    # Controls source route verification
    net.ipv4.conf.default.rp_filter = 1
    # Do not accept source routing
    net.ipv4.conf.default.accept_source_route = 0
    # Controls the System Request debugging functionality of the kernel
    kernel.sysrq = 0
    # Controls whether core dumps will append the PID to the core filename.
    # Useful for debugging multi-threaded applications.
    kernel.core_uses_pid = 1
    # Controls the use of TCP syncookies
    net.ipv4.tcp_syncookies = 1

    /etc/ha.d/ha.cf
    logfacility local0
    bcast
    mcast eth0 225.0.0.1 694 1 0
    auto_failback off
    node mysql-1
    node mysql4
    respawn hacluster /usr/lib/heartbeat/ipfail
    apiauth ipfail gid=haclient uid=hacluster

    /etc/ha.d/haresources
    mysql-1 ldirectord::ldirectord.cf \
    LVSSyncDaemonSwap::master \
    IPaddr2::192.168.0.105/24/eth0/192.168.0.255

    /etc/ha.d/authkeys
    auth 3
    3 md5 mypassword

    /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 = xxx.xxx.xxx.242:3306 gate
    real = xxx.xxx.xxx.243:3306 gate
    checktype = negotiate
    login = "ldirector"
    passwd = "ldirectorpass"
    database = "ldirectordb"
    request = "SELECT * FROM connectioncheck"
    scheduler = wrr

    From mysql-2 (sql node 1):

    /etc/sysctl.conf
    # Kernel sysctl configuration file for Red Hat Linux
    # sysctl.conf(5) for more details.
    # Controls IP packet forwarding
    net.ipv4.ip_forward = 0
    # Controls source route verification
    net.ipv4.conf.default.rp_filter = 1
    # Do not accept source routing
    net.ipv4.conf.default.accept_source_route = 0
    # Controls the System Request debugging functionality of the kernel
    kernel.sysrq = 0
    # Controls whether core dumps will append the PID to the core filename.
    # Useful for debugging multi-threaded applications.
    kernel.core_uses_pid = 1
    # Controls the use of TCP syncookies
    net.ipv4.tcp_syncookies = 1
    # Enable configuration of arp_ignore option
    net.ipv4.conf.all.arp_ignore = 1
    # When an arp request is received on eth0, only respond if that address is
    # configured on eth0. In particular, do not respond if the address is
    # configured on lo
    net.ipv4.conf.eth0.arp_ignore = 1
    # Ditto for eth1, add for all ARPing interfaces
    #net.ipv4.conf.eth1.arp_ignore = 1
    # Enable configuration of arp_announce option
    net.ipv4.conf.all.arp_announce = 2
    # When making an ARP request sent through eth0 Always use an address that
    # is configured on eth0 as the source address of the ARP request. If this
    # is not set, and packets are being sent out eth0 for an address that is on
    # lo, and an arp request is required, then the address on lo will be used.
    # As the source IP address of arp requests is entered into the ARP cache on
    # the destination, it has the effect of announcing this address. This is
    # not desirable in this case as adresses on lo on the real-servers should
    # be announced only by the linux-director.
    net.ipv4.conf.eth0.arp_announce = 2
    # Ditto for eth1, add for all ARPing interfaces
    #net.ipv4.conf.eth1.arp_announce = 2

    /etc/sysconfig/network-scripts/ifcfg-lo:0
    DEVICE=lo:0
    IPADDR=192.168.0.105
    NETMASK=255.255.255.255
    NETWORK=192.168.0.0
    BROADCAST=192.168.0.255
    ONBOOT=yes


    From mysql-3 (sql node 2):

    /etc/sysctl.conf
    # Kernel sysctl configuration file for Red Hat Linux
    # sysctl.conf(5) for more details.
    # Controls IP packet forwarding
    net.ipv4.ip_forward = 0
    # Controls source route verification
    net.ipv4.conf.default.rp_filter = 1
    # Do not accept source routing
    net.ipv4.conf.default.accept_source_route = 0
    # Controls the System Request debugging functionality of the kernel
    kernel.sysrq = 0
    # Controls whether core dumps will append the PID to the core filename.
    # Useful for debugging multi-threaded applications.
    kernel.core_uses_pid = 1
    # Controls the use of TCP syncookies
    net.ipv4.tcp_syncookies = 1
    # Enable configuration of arp_ignore option
    net.ipv4.conf.all.arp_ignore = 1
    # When an arp request is received on eth0, only respond if that address is
    # configured on eth0. In particular, do not respond if the address is
    # configured on lo
    net.ipv4.conf.eth0.arp_ignore = 1
    # Ditto for eth1, add for all ARPing interfaces
    #net.ipv4.conf.eth1.arp_ignore = 1
    # Enable configuration of arp_announce option
    net.ipv4.conf.all.arp_announce = 2
    # When making an ARP request sent through eth0 Always use an address that
    # is configured on eth0 as the source address of the ARP request. If this
    # is not set, and packets are being sent out eth0 for an address that is on
    # lo, and an arp request is required, then the address on lo will be used.
    # As the source IP address of arp requests is entered into the ARP cache on
    # the destination, it has the effect of announcing this address. This is
    # not desirable in this case as adresses on lo on the real-servers should
    # be announced only by the linux-director.
    net.ipv4.conf.eth0.arp_announce = 2
    # Ditto for eth1, add for all ARPing interfaces
    #net.ipv4.conf.eth1.arp_announce = 2

    /etc/sysconfig/network-scripts/ifcfg-lo:0
    DEVICE=lo:0
    IPADDR=192.168.0.105
    NETMASK=255.255.255.255
    NETWORK=192.168.0.0
    BROADCAST=192.168.0.255
    ONBOOT=yes


    I don't think I skipped anything in the tutorial, though I had to do a bit of switching of packages to try and make the tutorial work on Fedora (since it was written for debian). But I think I have all the package requirements for Fedora.

    What other output can I include to help diagnose this? Any ideas on what's up?
     
    Last edited: Nov 10, 2007
  2. falko

    falko Super Moderator ISPConfig Developer

    Try
    Code:
    service=mysqld
    instead on Fedora.
     
  3. rwillie6

    rwillie6 New Member

    Good suggestion, though I can't test it

    Ah, I didn't think of that. Unfortunately, I can't test it out now. I'm under a really right schedule and since I was stuck using heartbeat and ldirectord for load balancing, I decided to try a different solution. Since I'm using glassfish and my app is pure Java, HA-JDBC was easy to plugin and it handles the load-balancing and health checking quite well so far. If I end up going back to heartbeat and ldirectord, I'll definitely try mysqld. Thanks.
     
  4. aboyz

    aboyz New Member

    I got the same error

    Hre is my config file
    ldirectord.cf
    # Global Directives
    checktimeout=10
    checkinterval=2
    autoreload=no
    logfile="local0"
    quiescent=yes
    virtual=192.168.1.55:3306
    service=mysql
    real=192.168.1.48:3306 gate
    real=192.168.1.49:3306 gate
    checktype=negotiate
    login="ld"
    passwd="password"
    database="lddb"
    request="SELECT * FROM connectioncheck"
    scheduler=wrr

    Seem like line 8 is not right service=mysql
    I tried service=mysqld still same error
    I tried to remove all blank spaces still same error
    I tried to move service=mysql under real=192.168.1.49:3306 gate
    now it complain about the real=192.168.1.48:3306 gate

    # Global Directives
    checktimeout=10
    checkinterval=2
    autoreload=no
    logfile="local0"
    quiescent=yes
    virtual=192.168.1.55:3306
    real=192.168.1.48:3306 gate #line 8 error
    real=192.168.1.49:3306 gate
    service=mysql
    checktype=negotiate
    login="ld"
    passwd="password"
    database="lddb"
    request="SELECT * FROM connectioncheck"

    No matter what i put in always line 8? anyone got any solution to this?

    thanks

    scheduler=wrr
     
  5. falko

    falko Super Moderator ISPConfig Developer

    What'S the exact error message you're getting?
     
  6. petero

    petero New Member HowtoForge Supporter

    >Stopping ldirectord Error [] reading file /etc/ha.d/ldirectord.cf at line 8: >Unknown command service=mysql
    This error is due to formating, it took me some time to figure it out.
    The serivce= mysql is probably connected to /etc/services which is correct.
    If you have libmysqlclient15 or 14 installed, fixing right formating will do the trick like here:

    ---/etc/heartbeat/ldirectord.cf
    # Global Directives
    checktimeout=10
    checkinterval=2
    autoreload=no
    logfile="local0"
    quiescent=yes
    #after virtual=... make TABS (or double spaces) in every line
    virtual = 10.0.2.10:3306
      service = mysql
      real = 10.0.1.11:3306 gate
     real = 10.0.1.12:3306 gate
     checktype = negotiate
      login = "ldirector"
      passwd = "ldirector"
      database = "ldirectordb"
      request = "SELECT * FROM connectioncheck"
      scheduler = wrr
    -------------------------end of file---
    Hope this helps.

    Peter
     
  7. sathishfiles

    sathishfiles New Member

    how to load balance with only 2pc

    I have 2PC
    -PC1, PC2

    Is that possible i install the

    MYSQL in pc1,pc2
    Node in pc1,pc2
    cluster primary -pc1
    secondary - pc2

    How do i load balance with only 2 pc. How does this setup goes need kind advice,and steps.
     
  8. petero

    petero New Member HowtoForge Supporter

    2 nodes

    With two nodes, you can use other sort of load balance not with NDB cluster.
    Anyway i don't recomend this cluster if you need simple and easy installation and managenemnt.
    Cluster is very hard to tune, manage and backup.
    The setup i did works but it needs constatnt checking or reprograming few SQL clauses to use it well.
    I think you will be better of with any master-slave/master-master config with any kind of double connetion to mysql.
    Peter
     

Share This Page