Load balancing is essential when 1 database cannot sufficiently process the requests and we want to distribute the load across multiple databases that are part of group replication.

This option falls into the industry category of IaaS. With this service, you can run MySQL Server inside a fully managed virtual machine on the Azure cloud platform.

The load balancing and high availability must be designed and maintained by us.

1. Enable group replication.
   Assume – member 1 =IP1 and member 2=IP2
2. Changes to the config file:
   • # General replication settingsgtid_mode = ONenforce_gtid_consistency = ONmaster_info_repository = TABLErelay_log_info_repository = TABLE

     binlog_checksum = NONE

     log_slave_updates = ON

     log_bin = binlog

     binlog_format = ROW

     transaction_write_set_extraction = XXHASH64

     loose-group_replication_bootstrap_group = OFF

     loose-group_replication_start_on_boot = OFF

     #loose-group_replication_ssl_mode = REQUIRED

     #loose-group_replication_recovery_use_ssl = 1

     # Shared replication group configuration

     loose-group_replication_group_name = “0b7989f4-146c-4ef3-812b-3825f0db4428”

     loose-group_replication_ip_whitelist = “IP1,IP2”

     loose-group_replication_group_seeds = “IP1:33061,IP2:33061”

     # Single or Multi-primary mode? Uncomment these two lines

     # for multi-primary mode, where any host can accept writes

     #loose-group_replication_single_primary_mode = OFF

     #loose-group_replication_enforce_update_everywhere_checks = ON

     # Host specific replication configuration

     ###Each member should have unique id####

     server_id = 1

     ###Change to IP2 when configuring 2nd member###

     bind-address = “IP1”

     report_host = “IP1”

     loose-group_replication_local_address = “IP1:33061”

3. Restart MySQL
   • sudo systemctl restart mysqlsudo systemctl status mysql
4. Install the replication plugin and set the user and password for the replication channel
   • CHANGE MASTER TO MASTER_USER=’replicator’, MASTER_PASSWORD=’password’ FOR CHANNEL ‘group_replication_recovery’;INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;
5. Bootstrap and start the replication only for the 1st member
   • SET GLOBAL group_replication_bootstrap_group=ON;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=OFF;
6. For rest of the members just start replication without bootstrap:
   • START GROUP_REPLICATION;
7. Once group replication initial testing/setup is done, change mysql config to ON. This will ensure, DB joins the group when it is restarted
   • loose-group_replication_start_on_boot = ONsudo systemctl restart mysql
8. Configure proxies
   1. There are different proxies available in market – HAProxy, ProxySQL, MySQL router. Each has its own set of features.
   2. ProxySQL does not need 2 different ports to be configured
   3. Let us go through the steps to set up MySQL router – 1 port to send the requests for read/write and 1 port to send the requests for reads in round robin fashion
      1. Install MySQL client and router. Usually this is done in the same machine that has the application running.
         • cd /tmpcurl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.14-1_all.debsudo dpkg -i mysql-apt-config_0.8.14-1_all.debsudo apt-get install mysql-routersudo apt-get install mysql-client
      2. Change the config file and start the router.
         • cd /etc/mysqlrouter
         • nano mysqlrouter.conf
         • bind_address = 0.0.0.0:3306bind_port = 3306destinations = IP1:3306,IP2:3306routing_strategy = first-available

           bind_address = 0.0.0.0:3307

           bind_port = 3307

           destinations = IP1:3306,IP2:3306

           routing_strategy = round-robin

         • mysqlrouter –version/etc/init.d/mysqlrouter start
      3. Connect to the router using port 1 for read/writes. The router in turn will connect to the primary/master DB.
      4. Connect to the router using port 2 for reads. The router will connect to the secondary DBs for read requests.

This option is a fully managed MySQL database engine based on the stable version of MySQL community edition.

Let us see how we can achieve the same balancing in Azure MySQL.

1. Create read replicas – 1 for each secondary database where you want to route the read requests to. This can be easily done using Azure portal or CLI.
2. Replica creation using CLI:
   • az mysql server replica create –name ngreplicaserver –source-server ngmysqlserver –resource-group ngresourcegroup
   • The read replicas can be created in the,
     1. Same region as the primary/master DB
     2. In the paired region for maximum disaster recovery
     3. In any of the universal regions regardless of the primary DB region
3. Connect to primary/master for read/writes
4. Connect to read replicates for reads.
5. Note that if there are multiple read replicas and we want to load balance the reads to these replicas, ProxySQL must be configured in a VM.

As we can see MySQL PaaS greatly reduces our effort for load balancing the MySQL requests.

https://techcommunity.microsoft.com/t5/Azure-Database-for-MySQL/Load-balance-read-replicas-using-ProxySQL-in-Azure-Database-for/ba-p/880042

https://www.slideshare.net/Severalnines/mysql-load-balancers-maxscale-proxysql-haproxy-mysql-router-nginx-a-close-up-look

https://www.digitalocean.com/community/tutorials/how-to-use-proxysql-as-a-load-balancer-for-mysql-on-ubuntu-16-04