HP OSMS Blueprint: Database Server on HP Server Platforms with MySQL and RHEL5

long a given slave server is taking to service requests, whether a particular network path is
congested, or how the query load is changing over time.
• Hardware load balancer—A hardware load-balancer is dedicated, custom-built hardware,
sometimes referred to as an application switch, which is capable of directing large volumes
of specific application traffic simultaneously over multiple network paths. Such a switch is
often used in large website installations to balance web page traffic across several web
servers. Similarly, this type of device may be used to balance SQL queries across several
slave servers. Hardware load balancers use sophisticated high-speed switching fabrics and
algorithms to ensure that application traffic is routed quickly across a network to the most
appropriate slave server available. However, these systems are costly when compared with
other options.
• Software load balancer—A software load balancer serves the same purpose as a hardware
load balancer, but is implemented in software on an existing system rather than on a
dedicated, custom-built switch. This represents a compromise between a round-robin system
(which is simple but offers no intelligent routing or balancing) and a hardware load balancer
(which is a very capable and fast switching implementation but might be too costly for
smaller installations).
This section describes the configuration of a master/slave server replication scheme using
a software load balancer. The Linux Virtual Server (LVS) system is a free, open-source
application commonly used to implement load-balancing solutions on Linux systems. To
scale out a MySQL installation, the replication architecture must consist of at least three
servers: a master server and two slave servers, as shown in Figure 6.
For the installation and configuration of LVS, see the LVS overview documentation at:
http://www.linuxvirtualserver.org/Documents.html
Figure 6 Replication Servers
Any application that is accessing the replicated database must have two data sources configured.
One is the LVS, which acts as a load balancer for any read queries to the slave servers, and the
other is the master server for any write queries.
The MySQL master server records all write queries received from the clients and writes
corresponding updates in its binary log. This data is then updated to the local storage. The slave
servers connect to the master server, read queries from the master server's binary log, and execute
them against their local copy of the data. When the client-read queries arrive through the load
balancer, the query results can be found. This functionality is enabled by MySQL replication.
LVS acts as the load balancer, which is the traffic router that receives the requests and spreads
the workload among several slave servers. By having several slave servers behind a load balancer,
the network can better handle brief surges in traffic that can otherwise overwhelm a single server.
The LVS server is fully transparent to the MySQL clients.
22