MySQL applications can be load balanced using the Avi Load Balancer. This topic explains how to integrate MySQL services on Avi Load Balancer.

This section covers the following configuration steps.

  • Creating a Layer 4 virtual service.

  • Creating pool for load balancing MySQL servers.

  • Creating an external health monitor for the MySQL servers.

Prerequisites

IP routing must be enabled on SEs.

For more information on enabling IP routing, see Default Gateway (IP Routing on NSX Advanced Load Balancer SE).

Procedure

  1. Create a Layer 4 virtual service with System-L4-Profile for Application Profile and System-TCP for TCP Profile.
    1. Navigate to Applications > Virtual Services, click Create Virtual Service.
    2. Provide the desired name of the virtual service. Set Application Profile to System-L4-Application and TCP/UDP Profile to System-TCP-Fast-Path under Profiles section.
    3. Navigate to Service Port section, and set Services to 3306. The MySQL virtual service will listen to this port (3306) for incoming traffic.
  2. Creating Pool for MySQL Servers.
    1. Use the drop-down menu option from the Pool section to select Create Pool. A new pool can also be created by navigating to Applications > Pool.
    2. Set the Load Balance Algorithm for the pool to Consistent Hash and Type to Source IP Address in the General tab of the CREATE POOL page.
  3. Creating External Health Monitor for MySQL Servers: The two types of health monitors required for load balancing MySQL servers are System-TCP and a custom health monitor for MySQL services. Follow the steps mentioned below to create the required monitors:
    1. Click ADD, and select System-TCP.
    2. Click ADD, select Create from the drop-down menu.
    3. Provide the following attributes for the new custom health monitor:

    • Name: As required

    • Type: External

    • Set the following Script Code under the External Settings section:

    • 'mysql --host=$IP 
--user=$SQLUSER --password=$SQLPW -e "select 1"'

    • Set the following attributes for the Script Parameters option:

    • SQLUSER=<username> SQLPW=<password for the user>
      Note:

      The user SQLUSER is an MySQL user with no privileges to the schema or databases.

      For more information on creating external health monitor on Avi Load Balancer, see External Health Monitor.

  4. Set the value of Connections per Server to match the global variable maximum connections on the MySQL server.

Results

MySQL services are integrated with the Avi Load Balancer.