The easiest way to understand MySQL Load Balancer is to look at a typical example of how MySQL Load Balancer can be used to improve the distribution of work to multiple MySQL servers.

Given an existing setup of several replicating MySQL servers, you can set up the MySQL Load Balancer to provide you with replication-aware load distribution.

Suppose you have three slaves replicating from one master, the slaves running on the machines slave-1, slave-2, and slave-3, the master being on master-1. Each MySQL server listens on the default port of 3306.

For client connectivity, typical configurations are in one of two topologies. The first topology uses applications that are aware of multiple clients and choose a MySQL server based either on a random selection or by choosing a slave based on a known quantity, such as user ID.

Figure 16.1. Replication architecture with clients using multiple MySQL slaves

In this scenario, it is possible for a client application to choose a slave that is unavailable, or in a replication situation, a slave that is not up to date compared to the master, or lagging behind the master in terms of processing replication data such that queries accessing the information would fail to return data, or return data that was out of date. In all these cases, the client would be unable to determine the issue (without checking the situation itself). In the event of a failed server, the connection would timeout and another server could be chosen, but the delay could cause problems in the application.

In this scenario, it is also possible for a single MySQL server to become overloaded with requests. For example, if the application was using an ID-based decision model to choose a MySQL server, then a high number of requests for a given ID could produce a very high load on the chosen server. This could affect the replication thread and place the server further behind compared to the master.

The second topology uses a model where each client has a dedicated MySQL server.

Figure 16.2. Replication architecture with clients using dedicated MySQL slaves

In this scenario, a problem with the MySQL server for an individual client could render the client useless. If the MySQL server is significantly behind the master, you would get out of date or incorrect information. If the MySQL server has failed, the client will be unable to access any information.

Using the MySQL Load Balancer, you can replace the individual connections from the clients to the slaves and instead route the connections through the MySQL Load Balancer. This will distribute the requests over the individual slave servers, automatically taking account of the load, and accounting for problems or delays in the replication of the data from the master.

Figure 16.3. Replication architecture with clients using MySQL Load Balancer

In the scenario using MySQL Load Balancer, any failure of a single MySQL server automatically removes it from the pool of available servers and distributes the incoming client connection to one of the other, available, servers. Problems with replication are addressed in the same way, redirecting the connection to a server that is up to date with the master. The possibility of overloading a single MySQL server should also be reduced, since the connections would be distributed evenly among each server.

To start the MySQL Load Balancer in this scenario you would specify the configuration of the master and slave servers on the command line when starting mysql-lb:

$ bin/mysql-lb --proxy-backend-addresses=master-1 \
  --proxy-read-only-backend-addresses=slave-1:3306 \
  --proxy-read-only-backend-addresses=slave-2:3306 \
  --proxy-read-only-backend-addresses=slave-3:3306 \
  --proxy-lua-script=share/mysql-load-balancer/monitored-ro-balance.lua \
  --monitor-lua-script=share/mysql-load-balancer/monitor-backends.lua

This will start the load balancer, which listens for incoming client connections on port 4040. The monitor component will connect to each backend MySQL server with the MySQL user monitor and no password, to be able to execute queries on them. If you do not have a MySQL user with that name or have a password set for the user, you can specify those using the options `--monitor-username` and `--monitor-password`.

The options in this example set the following options:

To get a list of all the available options, run

 $ mysql-lb --help-all