Configuring MySQL profiles

FortiADC (Version 4.7.0 and later) supports MySQL server load-balancing.

MySQL application profiles are user-specific and must be configured only by the user on a case by case basis. For this reason, FortiADC does not provide any default predefined MySQL application profiles that you can use out of the box. So you must configure your own MySQL load-balancing application profiles to take advantage of this feature.

FortiADC supports two MySQL database load-balancing modes: single primary and data sharding.

Single-primary mode

The single-primary mode is a database server configuration in which a single primary MySQL server is responsible for all write operations (i.e., create, update, or delete requests), and one or more secondary servers handle all read-only operations. The primary server replicates data to the secondary servers in a close to real-time fashion. This mode can improve database performance to a certain extent by offloading read-intensive operations to secondary servers. It is ideal for load-balancing database traffic that involves more read operations.

Single-primary mode illustrates the network topology of database server load-balancing in single-primary mode.

By default, FortiADC passes all write requests to the primary server and all read requests (such as select) to the secondary servers. So once you have created a MySQL server load-balancing profile, FortiADC will automatically apply this default mode when load-balancing MySQL traffic on the network. However, if you do not like the default behavior, you can change it by setting up your own MySQL server load-balancing rules when configuring your MySQL application profile. For more information, see Configuring MySQL rules.

Sharding mode

Database sharding is a "shared-nothing" database partitioning technique that breaks down a large database involving a number of database servers into small database chunks and spread them across a number of distributed servers. It's a highly scalable approach to improving the throughput and performance of large enterprise business applications that are transaction-extensive and database-centric because it provides scalability across independent servers, each having its own CPU, memory, and disks.

Sharding mode illustrates MySQL server load-balancing in data-sharding mode.

In sharding mode, FortiADC stores global data on the primary Global—it sends all requests that do not belong to any group to global servers. Using the keys that you have specified, it sends part of the requests to Group) and some to Group 1. It supports split read/write in every group.

It must be noted that Data Manipulation Language (DDL) is not supported in sharding mode.

Creating a MySQL profile

Creating a MySQL profile involves the following steps:

1. Create a MySQL configuration object.
2. Specify the existing user name and password of the MySQL database to be used by the MySQL profile configuration object.
3. Configure MySQL Rule (for single-primary mode, optional) or MySQL Sharding (for database sharding mode).

Note: You can create MySQL profiles from either the GUI or the CLI. The following paragraphs discuss how to configure a MySQL profile using the GUI. For instructions on how to create MySQL profiles from the CLI, refer to the CLI Reference.

Before you begin:

• You must have already created MySQL database objects to be used the MySQL profile.
• You must have read-write permission for load-balance settings.

Creating a MySQL configuration object

1. Go to Server Load Balance > Application Resources.
2. Select the Application Profile tab if it is not already selected.
3. Click Create New to open the Application Profile configuration editor.
4. In the Name field, enter a unique profile name.
5. In the Type field, click the down arrow and select MySQL from the drop-down menu.
6. For MySQL Mode, select Single primary or Sharding. Refer to MySQL profile configuration guidelines.
7. Click Save. Your newly created MySQL profile configuration object is automatically appended to the bottom of the Server Load Balancing > Application Resources > Application Profile page.
8. Click the newly created MySQL profile to open it. See MySQL application profile configuration.

Note: The image above shows a sample MySQL profile configuration object named "1". Once a MySQL profile is created, you need to specify the MySQL database user account, and create MySQL Rule or Sharding depending on which MySQL mode you choose to use. The following paragraphs discuss the procedures for each of those tasks.

Specifying the MySQL user account

Once a MySQL profile is created, you must specify a MySQL user account to be used with the profile by entering the user name and password of that account.

It's important to note that you are asked to provide the user name and password of an existing MySQL account. So do not try to create a new user account here.

To specify a MySQL user account:

1. In the MySQL User Password pane (see the illustration above), click Create New. The Edit MySQL User Password dialog opens.
2. Enter the user name and password of the MySQL database account.
3. Click Save.

Configuring MySQL rules

When configuring a MySQL rule, you first need to decide whether you want FortiADC to send requests to the primary database server or the secondary database server(s). Then you can set a few conditions (rules) to tell FortiADC how to send the requests . It must be noted that all the conditions are of an "OR" relationship.

To configure a MySQL rule:

1. In the MySQL Rule pane, click Create New. The Application Profile > Edit MySQL Rule dialog opens.
2. Make the desired entries or selections as described in MySQL profile configuration guidelines.
3. Click Save.

Configuring sharding

FortiADC supports two types of database-sharding: by range or by hash. In the former case, FortiADC distributes the data to different groups according to the key range. In the latter case, it first hashes the keys and then automatically distributes the data to different groups.

To configure MySQL sharding:

1. In the MySQL Sharding pane, click Create New. The Application Profile > Edit MySQL Sharding dialog opens.
2. Make the desired entries or selections as described in MySQL profile configuration guidelines.
3. Click Save.

Note: When configuring pool members in the CLI to match the real server pool members on the GUI, you can use the set mysql-group-id command to set the groups that match the pool members:

config load-balance pool

edit "sharding"

set real-server-ssl-profile NONE

config pool_member

edit 1

set pool_member_service_port 3306

set pool_member_cookie rs

set real-server primary

next

edit 2

set pool_member_service_port 3306

set pool_member_cookie rs2

set real-server primary2

set mysql-group-id 1

next

edit 3

set pool_member_service_port 3306

set pool_member_cookie rs3

set real-server secondary

set mysql-read-only enable

next

edit 4

set pool_member_service_port 3306

set pool_member_cookie rs4

set real-server secondary2

set mysql-read-only enable

set mysql-group-id 1

next

end

next

end

You can clone a predefined configuration object to help you get started with a user-defined configuration. To clone a configuration object, click the clone icon that appears in the tools column on the configuration summary page.

MySQL profile configuration guidelines

Parameter	Description
Application Profile
Name	A unique name for the MySQL profile you are creating.
Type	MySQL
MySQL Mode	Select either of the following: Single primary—If selected, FortiADC will configure the MySQL profile in single-primary mode. See Single-primary mode. Sharding—If selected, FortiADC will configure the MySQL profile in database-sharding mode. See Sharding mode.
MySQL User Password
User Name	The user name of the MySQL database.
Password	The password for the MySQL user name you've entered above.
MySQL Rule
Type	Select either of the following: Primary—If selected, FortiADC will send all data specified in the MySQL rule to the primary MySQL database server. Secondary—If selected, FortiADC will send all data specified in the MySQL rule to the secondary MySQL database server.
Database List	A list of up to eight MySQL database names separated by space
User List	A list of up to eight user names separated by space
Table List	A list of up to eight MySQL Database tables separated by space
Client IP List	A list of up to eight FortiADC client IP addresses separated by space
SQL List	A list of up to eight MySQL statements separated by space
Sharding
Type	Select either of the following: Range—If selected, FortiADC will send data in the data tables to different groups based on the specified range of the keys. Hash—If selected, FortiADC will perform hash calculations and then automatically send data to different groups.
Database	The database name
Table	The table name
Key	The column name
Group List	A list of up to eight group IDs Note: The group IDs must match the real server pool members.