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

4. To show the current status of the slave server I/O thread, run the following command from
the slave server:
mysql> show processlist\G
*************************** 1. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 173581
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 81990
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 225
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
3 rows in set (0.00 sec)
The show processlist command on the slave server displays the current status of the
slave I/O thread, which connects to the master server and writes events from the master
server to relay logs. This command also shows the status of the slave server SQL thread,
which reads events from these relay logs and enters them into the slave server database.
The values of the State field in the output indicate that the slave server I/O thread and the
slave server SQL thread have finished their tasks and are waiting for additional updates
from the master server.
Scaling MySQL Using Replication
There are many different methods to scale out MySQL using replication. The primary concern
in configuring scale-out installations is how to spread out a large number of queries across two
or more replicated slave servers.
Although a single master server is responsible for all data modification in the database, many
different slave servers might be ready to service read-only queries at any one time.
This process is known as load balancing. The most popular methods for load-balancing queries
across several slave servers are:
• Round-robin DNS—In this method, the Domain Name System (DNS) within the IT
environment is configured to map two or more IP addresses to a single host name. When
clients query DNS for the IP address of a given host name, DNS cycles through all the
possible IP addresses, returning one after the other with each DNS query. In this simple and
lightweight scheme, all the slave servers have the same host name in DNS, although each
has a unique IP address. When a MySQL client wants to query the MySQL database using
a slave server, it makes the request using the host name. DNS automatically refers the request
to the next slave server IP address in turn. The shortcoming of the round-robin load balancing
scheme is that it does not take into account any dynamic load considerations, such as how
Configuring MySQL Server Replication 21