ProxySQL Series: MySQL InnoDB Cluster balancing ( using config file )

Jan 27, 2019

Mins to Read

All

At Mydbops, we're passionate about exploring cutting-edge MySQL technologies to empower our clients. Recently i was worked for one of our client the project. The requirement is setting up a three node InnoDB cluster with 3 node ProxySQL cluster . During my ProxySQL configuration, i discovered that the ProxySQL group replication host groups are not loading from ProxySQL config file . Then i reached the ProxySQL official website and found that they are already have the bug for this issue and provided the fix as well ( BUG #1050 ). The fixed version is ProxySQL 1.4.9 .

I just had the interest to write the blog about this behaviour, as I had spent a good time to find out the root cause . By this blog i am going to explore the comparison results what i received on two different ProxySQL versions ( ProxySQL 1.4.4 & ProxySQL 1.4.9 ) .

MySQL Group Replication

I had the three Linux servers on AWS for InnoDB Cluster and the ProxySQL was installed on same boxes . Below are the three servers IP which i used to configure the InnoDB Cluster .

 
GR1 - 192.168.33.11
GR2 - 192.168.33.12
GR3 - 192.168.33.13

MySQL InnoDB Cluster settings

This is the sample MySQL config file for InnoDB Cluster configuration .

 
# General Replication Configuration
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_slave_updates = ON
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 = "fb65514e-920b-4ef9-b6c3-883db19ce5e1"
loose-group_replication_ip_whitelist = "192.168.33.11,192.168.33.12,192.168.33.13"
loose-group_replication_group_seeds = "192.168.33.11:33061,192.168.33.12:33061,192.168.33.13:33061"

# Host Configuration
server_id = 12
report_host = "192.168.33.12"
bind-address = "192.168.33.12"
loose-group_replication_local_address = "192.168.33.12:33061"

#Binlog Configuration
log_bin = mysql-bin
log_bin_index = mysql-bin.index
sync_binlog = 1
expire_logs_days = 10
binlog_format = ROW
binlog_row_image = MINIMAL
max_binlog_size = 100M
binlog_checksum = NONE
binlog_rows_query_log_events = ON
log_bin_trust_function_creators = ON

InnoDB Cluster Status

 
root@localhost:(none)> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 3f75209c-fc4b-11e8-adba-080027827325
MEMBER_HOST: 192.168.33.12
MEMBER_PORT: 3309
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 5111d809-fc4b-11e8-afae-080027827325
MEMBER_HOST: 192.168.33.13
MEMBER_PORT: 3309
MEMBER_STATE: ONLINE
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: c38982b7-fc4a-11e8-ab74-080027827325
MEMBER_HOST: 192.168.33.11
MEMBER_PORT: 3309
MEMBER_STATE: ONLINE
3 rows in set (0.00 sec)

Scenario 1 ( GR with ProxySQL version 1.4.4 )

ProxySQL version

This scenario explains trouble we observed with ProxySQL version 1.4.4 .

 
[root@mydbopslabs11 ~]# proxysql --version
ProxySQL version 1.4.4

Querying the mysql_group_replication_hostgroups table

 
[root@mydbopslabs11 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 -vvv -e "select * from mysql_group_replication_hostgroups;"

--------------
select * from mysql_group_replication_hostgroups
--------------
Empty set (0.00 sec)
Bye

It was not configured yet,

I just created the group inside the ProxySQL configuration file and tried to loaded the config file .

 
#vi /etc/proxysql.cnf

mysql_group_replication_hostgroups =

(
        {
                writer_hostgroup=2
                reader_hostgroup=3
                backup_writer_hostgroup=4
                offline_hostgroup=0
                active=1
                max_writers=1
                writer_is_also_reader=0
                max_transactions_behind=0
                comment="proxy GR"
       }
)

loading the config file inside the ProxySQL client,

 
admin@127.0.0.1:admin> load mysql servers from config;
Query OK, 0 row affected (0.00 sec)

Querying the mysql_group_replication_hostgroups again. But, it was still not loaded .

 
[root@mydbopslabs11 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 -vvv -e "select * from mysql_group_replication_hostgroups;"

--------------
select * from mysql_group_replication_hostgroups

--------------
Empty set (0.00 sec)
Bye

Scenario 2 ( GR with ProxySQL version 1.4.9 )

ProxySQL Version

Trying the same with ProxySQL version 1.4.9. Here, I was achieved what I exactly needed .

 
[root@mydbopslabs11 ~]# proxysql --version
ProxySQL version 1.4.9

Querying the mysql_group_replication_hostgroups table

 
[root@mydbopslabs11 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 -vvv -e "select * from mysql_group_replication_hostgroups;"

--------------

select * from mysql_group_replication_hostgroups

--------------
Empty set (0.00 sec)
Bye

Making entry in ProxySQL config file,

 
#vi /etc/proxysql.cnf

mysql_group_replication_hostgroups =

(
        {
                writer_hostgroup=2
                reader_hostgroup=3
                backup_writer_hostgroup=4
                offline_hostgroup=0
                active=1
                max_writers=1
                writer_is_also_reader=0
                max_transactions_behind=0
                comment="proxy GR"
       }
)

Loading the ProxySQL config file,

 
admin@127.0.0.1:admin> load mysql servers from config;
Query OK, 1 row affected (0.02 sec)

It loaded now as expected and I am able to see the configurations inside the ProxySQL client . The static config file is a better option for automated deployments and standardising the config

 
[root@mydbopslabs11 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 -vvv -e "select * from mysql_group_replication_hostgroups\G"

--------------
select * from mysql_group_replication_hostgroups
--------------
*************************** 1. row ***************************
       writer_hostgroup: 2
backup_writer_hostgroup: 4
       reader_hostgroup: 3
      offline_hostgroup: 0
                 active: 1
            max_writers: 1
  writer_is_also_reader: 0
max_transactions_behind: 0
                comment: proxy GR
1 row in set (0.00 sec)
Bye

This is the very good feature, which will be very helpful while constructing more ProxySQL with automated Ansible deployments (playbook) . I would like to say Thanks to ProxySQL team for this fix . At Mydbops, We are keep testing the new things on MySQL and related tools, will be coming back with a new blog soon.

Optimize performance, enhance reliability, and ensure data security with Mydbops' MySQL InnoDB Cluster Consulting and Support Services. Our team can help you fine-tune temporary tablespace configuration, troubleshoot performance issues, and implement best practices. Contact us today to schedule a free consultation.

No items found.