Stand-alone deployment and cluster deployment of ProxySQL integrated with MHA

Description: MHA provides master node failover function for master-slave replicated MySQL clusters, but if you need to achieve read-write separation at this time, you need to introduce other technical components, ProxySQL is a good choice, it The read_only parameter can automatically identify the read node and write node in the MySQL cluster, so as to realize the separation of read and write operations. If you are worried that ProxySQL will have a single point of failure, the following article also provides a cluster version of ProxySQL implementation (through Keepalived).

Install ProxySQL

Remarks: The kernel version of the test machine installed this time is CentOS-7.4.1708

Upload the installation package mysql_cluster_ha_pack.zip to all servers in the cluster and decompress it.
Upload location /root/
Click to get the installation package

1. Stand-alone version

1 Install Proxysql

cd /root/mysql_cluster_ha_pack/proxysql/rpms/proxysql
rpm -ivh *.rpm --force

2 Install mysql client

cd /root/mysql_cluster_ha_pack/proxysql/rpms/mysql_client
rpm -Uvh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -Uvh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -Uvh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
rpm -Uvh mysql-community-client-5.7.28-1.el7.x86_64.rpm

Note: If it fails try adding –nodeps –force after the command and try again.

3 Configuration

# Modify configuration by command
sed -i 's/threads=4/threads=8/' /etc/proxysql.cnf
sed -i 's/max_connections=2048/max_connections=10000/' /etc/proxysql.cnf

4 MySQL create user

(mysql master node)

-- connect to mysql, enter root password
mysql-uroot-p
CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@2023";
CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@2023";
GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%';
FLUSH PRIVILEGES;
-- View all MySQL read_only configurations, make sure the master node is OFF and the slave node is ON
show variables like "read_only";

5 start

rm -rf /var/lib/proxysql/proxysql.db
systemctl start proxysql

Log in to the proxysql management interface

# Log in to proxysql as an administrator
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin>'
# Modify the write node to not provide read service
set mysql-monitor_writer_is_also_reader='false';
# add role
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor@2023';
INSERT INTO mysql_users(username,password,active,default_hostgroup,transaction_persistent ) VALUES ('proxysql','proxysql@2023',1,10,1);
# Add back-end node Pay attention to modify to the corresponding ip!
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.x.101',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.x.102',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.x.103',3306);
# Set read-write host group
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type) VALUES (10,20,'read_only');
# Set read-write separation rules
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',20,1);
# save enabled
load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;
load mysql query rules to runtime;
save mysql query rules to disk;

Log location: /var/lib/proxysql/proxysql.log

6 View cluster status

# Log in to proxysql as an administrator
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin>'

# Status check
select hostgroup_id, hostname, port, status from runtime_mysql_servers;

[External link picture transfer failed, the source site may have an anti-leeching mechanism, it is recommended to save the picture and upload it directly (img-vbo93NKh-1679397678055) (C:\Users\dragon\AppData\Roaming\Typora\typora-user-images\ image-20230315093506069.png)]

Except for the hostname the rest should be the same.

# Then test whether you can log in to the database normally (if the test command is executed multiple times, it will log in to different mysql nodes)
[root@ProxySQL-node1 ~]# mysql -uproxysql -pproxysql@2023 -h 127.0.0.1 -P6033 -e "select @@hostname"
 + ------------+
| @@hostname |
 + ------------+
|MGR-node1|
 + ------------+

# Finally, check the separation of reading and writing on the proxysql management side
[root@ProxySQL-node ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032
.........
MySQL [(none)]> select hostgroup, username, digest_text, count_star from stats_mysql_query_digest;

2. Cluster version

1 Install Proxysql

(proxysql01, 02)

cd /root/mysql_cluster_ha_pack/proxysql/rpms/proxysql
rpm -ivh *.rpm --force

2 Install mysql client

(proxysql01, 02)

cd /root/mysql_cluster_ha_pack/proxysql/rpms/mysql_client
rpm -Uvh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -Uvh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -Uvh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
rpm -Uvh mysql-community-client-5.7.28-1.el7.x86_64.rpm

Note: If it fails try adding –nodeps –force after the command and try again.

3 Configuration

(proxysql01, 02)

vim /etc/proxysql.cnf
# replace part
admin_variables=
{<!-- -->
        admin_credentials="admin:admin;cluster_kevin:proxy2023"
        mysql_ifaces="0.0.0.0:6032"
        cluster_username="cluster_kevin"
        cluster_password="proxy2023"
        cluster_check_interval_ms=1000
        cluster_check_status_frequency=10
        cluster_mysql_query_rules_save_to_disk=true
        cluster_mysql_servers_save_to_disk=true
        cluster_mysql_users_save_to_disk=true
        cluster_proxysql_servers_save_to_disk=true
        cluster_mysql_query_rules_diffs_before_sync=3
        cluster_mysql_servers_diffs_before_sync=3
        cluster_mysql_users_diffs_before_sync=3
        cluster_proxysql_servers_diffs_before_sync=3
}

# Add part (note to modify IP)
proxysql_servers=
(
        {<!-- -->
                hostname="192.168.x.104"
                port=6032
                weight=1
                comment="proxysql01"
        },
        {<!-- -->
                hostname="192.168.x.105"
                port=6032
                weight=1
                comment="proxysql02"
        }
)

# Modify configuration by command
sed -i 's/threads=4/threads=8/' /etc/proxysql.cnf
sed -i 's/max_connections=2048/max_connections=10000/' /etc/proxysql.cnf

4 MySQL create user

(mysql master node)

-- connect to mysql, enter root password
mysql-uroot-p
CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@2023";
CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@2023";
GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%';
FLUSH PRIVILEGES;
-- View all MySQL read_only configurations, make sure the master node is OFF and the slave node is ON
show variables like "read_only";

5 start

(proxysql01, 02)

rm -rf /var/lib/proxysql/proxysql.db
systemctl start proxysql

Log in to the proxysql management interface (proxysql01)

# Log in to proxysql as an administrator
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin>'
# Modify the write node to not provide read service
set mysql-monitor_writer_is_also_reader='false';
# add role
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor@2023';
INSERT INTO mysql_users(username,password,active,default_hostgroup,transaction_persistent ) VALUES ('proxysql','proxysql@2023',1,10,1);
# Add back-end node Pay attention to modify to the corresponding ip!
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.x.101',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.x.102',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.x.103',3306);
# Set read-write host group
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type) VALUES (10,20,'read_only');
# Set read-write separation rules
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',20,1);
# save enabled
load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;
load mysql query rules to runtime;
save mysql query rules to disk;

Log location: /var/lib/proxysql/proxysql.log

6 View cluster status

(proxysql01, 02)

# Log in to proxysql as an administrator
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin>'

# Status check
select hostgroup_id, hostname, port, status from runtime_mysql_servers;

Except for the hostname the rest should be the same.

# Then test whether you can log in to the database normally (if the test command is executed multiple times, it will log in to different mysql nodes)
[root@ProxySQL-node1 ~]# mysql -uproxysql -pproxysql@2023 -h 127.0.0.1 -P6033 -e "select @@hostname"
 + ------------+
| @@hostname |
 + ------------+
|MGR-node1|
 + ------------+

# Finally, check the separation of reading and writing on the proxysql management side
[root@ProxySQL-node ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032
.........
MySQL [(none)]> select hostgroup, username, digest_text, count_star from stats_mysql_query_digest;

7 Keepalived installation

1) Install Keepalivedexit

(proxysql01, 02)

cd /root/mysql_cluster_ha_pack/keepalived/rpms/gcc
rpm -Uvh *.rpm --nodeps --force
cd /root/mysql_cluster_ha_pack/keepalived/rpms/gcc-c++
rpm -Uvh *.rpm --nodeps --force
cd /root/mysql_cluster_ha_pack/keepalived
tar -zxf keepalived-2.2.7.tar.gz -C /usr/local/src
cd /usr/local/src/keepalived-2.2.7/
./configure --prefix=/usr/local/keepalived
make & make install
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalived
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/keepalived
mkdir /etc/keepalived
# Monitoring script
cp /root/mysql_cluster_ha_pack/keepalived/proxysql_check.sh /etc/keepalived/proxysql_check.sh
chmod u+x /etc/keepalived/proxysql_check.sh

2) Configuration

View network card name (proxysql01, 02)

ifconfig

(proxysql01)

vim /etc/keepalived/keepalived.conf
# Only need to modify the network card name and virtual IP
!Configuration File for keepalived

vrrp_script chk_proxysql_port {<!-- -->
script "/usr/bin/sh /etc/keepalived/proxysql_check.sh"
interval 2
weight 1
}

vrrp_instance VI_1 {<!-- -->
state MASTER
interface ens192 #node network card name
virtual_router_id 51
priority 100
advert_int 2
authentication {<!-- -->
auth_type PASS
auth_pass keep2023
}
virtual_ipaddress {<!-- -->
192.168.0.99/24 #Virtual IP, and the IP of the external network requires a network segment
}
track_script {<!-- -->
chk_proxysql_port
}
}

(proxysql02)

vim /etc/keepalived/keepalived.conf
# Only need to modify the network card name and virtual IP
!Configuration File for keepalived

vrrp_script chk_proxysql_port {<!-- -->
script "sh /etc/keepalived/proxysql_check.sh"
interval 2
weight 1
}

vrrp_instance VI_1 {<!-- -->
state BACKUP
interface ens192 #node network card name
virtual_router_id 51
priority 99
advert_int 2
authentication {<!-- -->
auth_type PASS
auth_pass keep2023
}
virtual_ipaddress {<!-- -->
192.168.0.99/24 #Virtual IP, and the IP of the external network requires a network segment
}
track_script {<!-- -->
chk_proxysql_port
}
}

3) Launch

(proxysql01, 02)

systemctl start keepalived

Log location: /var/log/messages