Load-Balance MySQL 8 Multi-Primary Cluster with ProxySQL 

In this video, you will learn how to set up ProxySQL using Ubuntu 22.04 server to load-balance a MySQL cluster of multi-primary replication.
Join this channel to get access to perks: www.youtube.com/@lazysysad/join
PLEASE SUBSCRIBE :)
PLEASE HIT LIKE IF IT HELPED :)
This tutorial assume that you already have MySQL setup. If you haven't done so, see the description below for the link on how to set up MySQL multi-primary group replication.
GIVE SUPPORT - www.patreon.com/lazysysad
BUY ME A COFFEE - www.buymeacoffee.com/lazysysad
PAYPAL - paypal.me/lazysysad
LINKS:
How to Set Up MySQL 8 Multi-Master Group Replication on Ubuntu 22.04
- ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-FvTyAfOjN74.html
STEPS:
Install ProxySQL
ProxySQL Server:
apt-get update; apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates gnupg
wget -O - 'repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key' | apt-key add -
echo deb repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list
apt-get update; apt-get -y install proxysql
systemctl start proxysql
Connect to ProxySQL
ProxySQL Server:
apt-get -y install mysql-client
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt 'ProxySQL Admin : '
admin-admin_credentials configuration variable in the global_variables database.
SELECT * FROM global_variables WHERE variable_name = 'admin-admin_credentials';
UPDATE global_variables SET variable_value='admin:Password123' WHERE variable_name='admin-admin_credentials';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;
Add Backends
ProxySQL Server:
SHOW CREATE TABLE mysql_group_replication_hostgroups\G
INSERT INTO mysql_group_replication_hostgroups (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind) VALUES (2, 4, 3, 1, 1, 3, 1, 100);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'mysql1',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'mysql2',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'mysql3',3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SELECT * FROM mysql_servers;
Configure Monitoring
MySQL Server:
CREATE USER 'monitor'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'Monitor123!';
GRANT SELECT on sys.* to 'monitor'@'%';
GRANT SELECT on performance_schema.* to 'monitor'@'%';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;
ProxySQL Server:
proxysql.com/documentation/main-runtime/#mysql_group_replication_hostgroups
gist.github.com/lefred/77ddbde301c72535381ae7af9f968322?permalink_comment_id=2690887#gistcomment-2690887
SELECT * FROM gr_member_routing_candidate_status;
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='Monitor123!' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
Backend’s Health Check
ProxySQL Server:
SHOW TABLES FROM monitor;
SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
SELECT * FROM mysql_servers;
MySQL Users
MySQL Server:
CREATE USER 'lazy'@'%' IDENTIFIED BY 'password123';
GRANT ALL PRIVILEGES ON *.* TO 'lazy'@'%';
ProxySQL Server:
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('lazy', 'password123', 2);
SELECT * FROM mysql_users;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
mysql -u lazy -ppassword123 -h 127.0.0.1 -P6033 -e "SELECT @@hostname;"
Enable Web Interface
ProxySQL Server:
SELECT * FROM global_variables WHERE variable_name = "admin-web_enabled";
SET admin-web_enabled='true';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;
SELECT * FROM global_variables WHERE variable_name = 'admin-stats_credentials';
Chapters:
00:00 Intro
00:20 Install ProxySQL
01:06 Connect to ProxySQL
01:52 Change ProxySQL Admin Password
02:58 Add Backends
06:25 Configure Monitoring
08:45 MySQL Users
09:48 Verification
11:20 Enable Web Interface
Drop me your feedback and comments below.
That's all for now.
If this video helped you in any way, please like share and subscribe!
Thank you!!!