How To Set Up A Load-Balanced MySQL Cluster

Discussion in 'HOWTO-Related Questions' started by Ridd1ck, Oct 25, 2011.

  1. Ridd1ck

    Ridd1ck New Member

    I Get An error wher try to run

    /etc/init.d/mysql.server start

    the error is:

    error: Found option without preceding group in config file: /etc/my.cnf at line: 1
    Fatal error in defaults handling. Program aborted
    Unable to connect with connect string: nodeid=0,localhost:1186
    Retrying every 5 seconds. Attempts left:

    can you help me please?
    the nodes can't connect to managment server but managment server is running as well...

    Help me please...
  2. falko

    falko Super Moderator ISPConfig Developer

    What's in /etc/my.cnf?
  3. Ridd1ck

    Ridd1ck New Member

    I Solved this Problem, there is an error on my.cnf file,
    The cluster Node is active in managment server,
    but now i have problem when try to run /etc/init.d/mysql.server start

    i get an error that says:
    Starting MySQL. ERROR! Manager of pid-file quit without updating file.

    please Help me.
    thanks for previous reply.
  4. Ridd1ck

    Ridd1ck New Member

    and when try to run mysql with this command service mysql start
    i get this error:
    Starting mysqld (via systemctl): Job failed. See system logs and 'systemctl status' for details.
    What's going Wrong?
  5. falko

    falko Super Moderator ISPConfig Developer

    Did you check your logs in /var/log/ for MySQL errors?
  6. Ridd1ck

    Ridd1ck New Member

    I have set-up the cluster and all working perfect.
    The big problem is the Load balancer,
    My OS is fedora 15 and i try to configure load balancing with piranha tool.
    How can i test if load balanser works for mysql nodes?
    When i try to connect to mysql cluster which ip is correct to use?
    Load Balancer's public ip or Node's ip?

    My solution have 3 machines:
    1 load balancer-Managment Server (eth0:public ip, eth0:1 :
    and 2 nodes with Private ip's ( and 165)

    please help me..
  7. Ridd1ck

    Ridd1ck New Member

    Can anyone Help me plz?
  8. nbhadauria

    nbhadauria New Member

    can you share your /etc/ configuretion then it would be easy to understand your setup..
  9. Ridd1ck

    Ridd1ck New Member

    I Use Piranha tool on linux fedora 15 to configure my load balancer,
    this is the output of the file tha you request. thank you for reply, i need help because this is my first time to configure mysql load balancing,
    any question you have post here and replay to you as soon as possible.

    output of cat /etc/piranha/

    serial_no = 64
    primary =
    service = lvs
    backup =
    heartbeat = 1
    heartbeat_port = 539
    keepalive = 6
    deadtime = 18
    network = nat
    nat_router = eth0:1
    nat_nmask =
    debug_level = NONE
    virtual MySQL_1 {
    active = 1
    address = eth0:1
    vip_nmask =
    port = 80
    use_regex = 0
    load_monitor = none
    scheduler = wlc
    protocol = tcp
    timeout = 6
    reentry = 15
    quiesce_server = 0
    server MySQLNode1 {
    address =
    active = 1
    weight = 1
    server MySQLNode2 {
    address =
    active = 1
    weight = 1

    In my case. i have three machines.
    All machines have one NIC card installed,
    and have 1 public IP in their NIC card in eth0 interface,
    i configure one subinterface eth0:1 because i need one Virtual network,
    this Virtual network is and eth0:1 interface has one ip from that network.

    I need to load balance MySQL & HTTP traffic from one machine to others.
    can you help me with configuration i need iptables or ip rules ??
    i'm really confused about this...
    Last edited: Nov 1, 2011
  10. Ridd1ck

    Ridd1ck New Member

    Here is a screenshot from my piranha tool

    Attached Files:

  11. nbhadauria

    nbhadauria New Member

    no you don't need to setup any iptables or ip rules.
    they will we created by lvs it reads your /etc/ and use ipvsadm to create IPVS routing table. you can use ipvsadm -Ln to see what is going on.

    What i want to know will you we using or to access your setup.

    your setup is fine and must be working but you need to setup your virtual ip on all the node to maintain the stat.
  12. Ridd1ck

    Ridd1ck New Member

    The output of ipvsadm command shows:

    IP Virtual Server version 1.2.1 (size=4096)
    Prot LocalAddress:port Scheduler Flags
    -> RemoteAddress:port Forward Weight ActiveConn InActConn
    TCP wlc
    -> Masq 1 0 0
    -> Masq 1 0 0

    All that i need is to use public ip address form external network and load balancer take the request that i send and and balances this in two nodes.

    For example, i need to send one mysql update with public ip (79.143.186...)
    and load balancer, forward packets to node1 or node2.
    sorry for my english..
  13. nbhadauria

    nbhadauria New Member

    ok no what happen if you try to access your site from this ip

    also use tcpdump to monitor the traffic.
  14. Ridd1ck

    Ridd1ck New Member

    This is Private ip,
    i cannot access from internet...
  15. nbhadauria

    nbhadauria New Member

    then you need to use same ip range in vip that you want to access.

    Change some thing like.

    virtual MySQL_1 {
    active = 1
    address = eth0:2
    vip_nmask =
  16. Ridd1ck

    Ridd1ck New Member

    For Example,
    I will create one sub interface eth0:2 with public virtual ip address
    in the same subnet?
    and this is my virtual ip address?
    in one interface i will have 3 ip adress?
    virtual public:77....
    and private public:
  17. nbhadauria

    nbhadauria New Member

    yes that's what I meen unless you have an option of adding one more ethernet card. because in case of privet and public ip we generally use two Ethernet ports.
  18. Ridd1ck

    Ridd1ck New Member

    Change this or add one new virtual server?

    virtual MySQL_1 {
    active = 1
    address = eth0:2
    vip_nmask =
  19. nbhadauria

    nbhadauria New Member

    not virtual server one more ethernet card.

    Like. eth0 and eth0:1 public and eth1 for local.
  20. Ridd1ck

    Ridd1ck New Member

    At last
    i have for ip address in load balancer
    one public virtual and one normal
    and one private virtual and one normal.
    that's it?

Share This Page