The load balancing in database servers is a typical method which is executed to distribute the incoming network traffic and workload over multiple servers to diminish the downtime and increment the efficiency.

There are a few methods that can be utilized for the load balancing ie HAProxy or MySQL Proxy.

Types of load balancing

Load balancing can be performed in three ways as mentioned below.

Load Balancing With Hardware

Hardware load balancing is a typical strategy for balancing the traffic. The dedicated hardware devices which are used to handle the network traffic and load balancing is called hardware load balancing. Majority of the dedicated hardware load balancers runs Linux. The main disadvantage of load balancing with hardware is that it’s very expensive.

Load Balancing With Software

The Software-based load balancing is exceptionally a very effective and reliable method for distributing load between servers. The software performs the balancing of requests usually on a Linux platform with a wide variety of algorithms for server allocation.

Load balancing by using with DNS.

The DNS has the ability to balance a load of a domain over multiple servers.

When performing network traffic load adjusting with DNS, we don’t have any control over the balancing algorithm. It generally utilizes round-robin fashion to dedicate a server it has listed for a given record. The unique feature of load balancing using DNS is the very limited DNS caching. Even if the server becomes unavailable in the listed pool, DNS provides the IP address to clients that are sending the requests.

The basic understanding of load balancing with HAProxy and MySQL Proxy is mentioned below.

Load balancing in HAProxy.

The HAproxy, universally known as High Availability Proxy is mainly used for TCP and HTTP-based applications. HAproxy is used in the case where too many concurrent connections over-saturate the capability of a single server. In HAproxy, a client connecting to a single server processes all of the requests. The client will connect to a HAProxy instance. Based on a load-balancing algorithm the HAProxy instance use a reverse proxy to forward the request to one of the available endpoints.

HAProxy Algorithms

A Wide range of load balancing algorithms can be used in HAproxy and the algorithms are chosen based on our requirements.

Let’s now look into the common algorithms

Round Robin:

The Round Robin algorithm is commonly implemented in HAproxy. The algorithm chooses the server sequentially in the list. When it reaches the end of the server, the algorithm forwards the new request to the first server in the list.

Weighted round robin:

The weighted round robin uses the weight allocation to the server to forward the request.

Dynamic round robin algorithm:

The Dynamic round robin algorithm uses the real-time updated weight list of each server to forward the request.

Source algorithm:

The source algorithm selects the server based on the source IP address using the hash to connect it to the matching server.

High Availability

Load balancers become slow when they receive too many requests. As a result, the end user will receive the response with the delay in time.

To solve this issue, we will use another load balancer which we can call as the secondary load balancer. If the primary load balancer is getting too many requests, the second load balancer will come to rescue. The load balancer checks the health of the each other.

We use a primary load balancer in active mode and the secondary load balancer in passive mode. We can add more load balancers in the passive mode. We use failover mechanism in load balancing.

This is how the high availability of the load balancers are maintained.

Load balancing in MySQL Proxy

MySQL Proxy passes data between a client and a MySQL server. The proxy can review the information flow in both directions and change it whenever necessary, which could be helpful for protecting the MySQL server from malicious queries or for modifying the information of clients without making changes to the database.

In CentOS, the bundle is available from the EPEL repository. EPEL provides numerous extra packages that are not accessible from the main CentOS repository./etc/sysconfig/mysql-proxy is the location of MySQL Proxy main configuration file, we can affirm it using the command rpm -qc mysql-proxy, where the q stands for query and c for configuration files. Inside the /etc/sysconfig/mysql-proxy file you can set following options:

ADMIN_USER – the username for the proxy’s admin interface. You can leave the default for the admin user.

ADMIN_PASSWORD – the password for the admin user. Change the default password for security.

ADMIN_LUA_SCRIPT – It is the admin script in the Lua programming language. Without this, the admin interface can’t work. You can leave the field to load default value.

PROXY_USER – It is the user under which the proxy work. By default it is mysql-proxy, and it’s safe to leave it.

PROXY_OPTIONS – It will help in you in configuring proxy options such as logging level, plugins, and Lua scripts to be loaded.

When you finish the setup you can begin MySQL Proxy using the command “mysql proxy start”. After you’ve had some action through the proxy you can check its status and start observing. To monitor, utilize the administrator interface, which is open by a MySQL client on the server’s port 4041. If your MySQL Proxy has an IP address of 192.168.1.11, you can connect to its admin interface using the command:
mysql –host=192.168.1.11 –port=4041 -u admin -p<em>secret_pa$s</em>.
The admin login username and password are already specified in /etc/sysconfig/mysql-proxy.

MySQL Proxy is a simple and effective utility. Even though it has few disadvantages like insufficient documentation, it is easy to use and it is under persistent advancement which interprets changes quickly.

 

ServerAdminz provides Outsourced 24/7 Technical Support, Remote Server Administration, Server Security, Linux Server Management, Windows Server Management and Helpdesk Management to Datacenters, Hosting companies and ISPs around the world. We specialize in Extended Server Security, Server Hardening, Support of Linux/UNIX/Windows servers, products and services. If you are looking for a server management service provider, you can contact us on sales@serveradminz.com or +1 (845) 271 7172.

ServerAdminz is a server support company specialized in Outsourced 24/7 Web Hosting Support, Remote Infrastructure Management, NOC, Cloud and Enterprise Security Services. With over 10+ of years of experience in working with major Data Centers and ISPs with 130+ experienced technicians, we continue to manage more than 49,000 servers from 85+ countries and has bagged 5 international awards.

If you have any queries on implementing load balancing in database servers feel free to leave us a message and our representative will get back to you.[two_third last=”yes” spacing=”yes” center_content=”no” hide_on_mobile=”no” background_color=”” background_image=”” background_repeat=”no-repeat” background_position=”left top” border_position=”all” border_size=”0px” border_color=”” border_style=”” padding=”” margin_top=”” margin_bottom=”” animation_type=”” animation_direction=”” animation_speed=”0.1″ class=”” id=””]

    [/two_third]