PROXY SQL MYSQL REPLICATION READ-WRTE SPLITUP

ProxySQL can split read and write operations in MySQL replication setups by directing read queries to replica servers while sending write queries to the primary/master server. This improves performance by distributing the workload and utilizing replica servers for read-intensive tasks, reducing the load on the primary server. ProxySQL intelligently routes queries based on their type, optimizing resource utilization in a MySQL replication environment. This read-write split enhances scalability and allows for better handling of high-traffic database systems.

• Write (Master)
• Read (Slave)
• Proxysql Server

STEP1 : MySQL installation on Master & Slave Proxysql server

Link :  https://linuxquery.com/index.php/2022/09/25/how-to-install-mysql-8-0-x-or-any-version-in-centos-7-redhat/

STEP2 : Master server setup and slave server setup

Link :  https://linuxquery.com/index.php/2023/09/11/master-slave-replication-mysql8-0-21/

Proxysql Server

STEP3 : Installing Proxy

sudo yum install -y https://github.com/sysown/proxysql/releases/download/v2.0.6/proxysql-2.0.6-1-centos7.x86_64.rpm
sudo systemctl enable proxysql.service
sudo systemctl start proxysql.service
sudo systemctl status proxysql.service

STEP4 : To verify the installation, check the Proxysql log

cat /var/lib/proxysql/proxysql.log

STEP5 : Ensure the firewall allows connections to Proxysql

port=6033

STEP6 : Access Proxysql administration interface

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL> '

STEP7 : Configuration Proxysql Changing Admin Password

SELECT * FROM global_variables WHERE variable_name LIKE 'admin-admin%';
UPDATE global_variables SET variable_value='admin:Proxy@123' WHERE variable_name='admin-admin_credentials';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

STEP8 : Setting up the Monitor User

SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor%';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='Password@123' WHERE variable_name='mysql-monitor_password';
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');

STEP9 : Configuring MYSQL servers

INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (1,'54.147.42.127',3306,'Master');
INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (2,'54.234.54.195',3306,'Slave_1');
INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (2,'52.66.198.38',3306,'Slave_2');

STEP10 : Verify the configuration

SELECT hostgroup_id,hostname,port,status,comment FROM mysql_servers;

STEP11 : Creating Replication Hostgroups

SHOW CREATE TABLE main.mysql_replication_hostgroups\G
INSERT INTO main.mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'Proxy_cluster');
LOAD MYSQL VARIABLES TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL SERVERS TO DISK;
EXIT

STEP12 : Check the ProxySQL log for any issues

mysql -uroot -pAdmin@123 -A
CREATE USER 'monitor'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'Password@123';
GRANT SELECT ON *.* TO 'monitor'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;

STEP14 : Read Server Instance

CREATE USER 'read1'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'Read1@123';
GRANT SELECT ON *.* TO 'read1'@'%';
FLUSH PRIVILEGES;
CREATE USER 'read2'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'Read2@123';
GRANT SELECT ON . TO 'read2'@'%';
FLUSH PRIVILEGES;

STEP15 : Configuring PROXYSQL Users Access ProxySQL’s administration interface

mysql -u admin -pProxy@123 -h 127.0.0.1 -P6032 --prompt='ProxySQL> '

STEP16 : Configuring Proxysql Users

INSERT INTO mysql_users(username,password,default_hostgroup,comment) VALUES ('write','Write@123',1,'Write_User');
INSERT INTO mysql_users(username,password,default_hostgroup,comment) VALUES ('read1','Read1@123',2,'Slave_1_user');
INSERT INTO mysql_users(username,password,default_hostgroup,comment) VALUES ('read2','Read2@123',2,'Slave_2_user');
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

STEP17 : Check the connections from ProxySQL for MySQL

mysql -u write -pWrite@123 -h 127.0.0.1 -P6033 --prompt='mysql Write> ' -e "select @@hostname,@@version;"
mysql -u read1 -pRead1@123 -h 127.0.0.1 -P6033 --prompt='mysql Read1> ' -e "select @@hostname,@@version;"
mysql -u read2 -pRead2@123 -h 127.0.0.1 -P6033 --prompt='mysql Read2> ' -e "select @@hostname,@@version;"

STEP18 : Install Sysbench for Read/Write Testing

sudo yum install -y epel-release
sudo yum install -y automake libtool make gcc mysql-devel
sudo yum install -y sysbench
ls -ltrh /usr/share/sysbench

STEP19 : Master Server

mysql -uroot -pAdmin@123 -A
create database proxytest;
use proxytest;

STEP20 : Proxysql Server

mysql -u admin -pProxy@123 -h 127.0.0.1 -P6032 --prompt='ProxySQL> '

STEP21 : Proxysql Query Rules

SELECT * FROM mysql_query_rules;
SHOW CREATE TABLE mysql_query_rules\G
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES  (1, 1, '^SELECT.*FOR UPDATE$', 1, 1),(2, 1, '^SELECT', 2, 1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

STEP22 : Write Test

sysbench oltp_common --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=6033 --mysql-user=write --mysql-password=Write@123 --mysql-db=proxytest --table-size=50 --tables=5 --threads=1 prepare
Creating table 'sbtest1'...
Inserting 50 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 50 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 50 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 50 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 50 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
[root@ip-172-31-89-103 ~]#

Output ( Master Server)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| Testdb             |
| information_schema |
| mysql              |
| performance_schema |
| proxytest          |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use proxytest
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_proxytest |
+---------------------+
| sbtest1             |
| sbtest2             |
| sbtest3             |
| sbtest4             |
| sbtest5             |
+---------------------+
5 rows in set (0.00 sec)

mysql>