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: https://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 - / lazysysad
BUY ME A COFFEE - https://www.buymeacoffee.com/lazysysad
PAYPAL - https://paypal.me/lazysysad
LINKS:
How to Set Up MySQL 8 Multi-Master Group Replication on Ubuntu 22.04
• How to Set Up MySQL 8 Multi-Master Gr...
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 - 'https://repo.proxysql.com/ProxySQL/pr... | apt-key add -
echo deb https://repo.proxysql.com/ProxySQL/pr... -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:
https://proxysql.com/documentation/ma...
https://gist.github.com/lefred/77ddbd...
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!!!
Watch video Load-Balance MySQL 8 Multi-Primary Cluster with ProxySQL online without registration, duration hours minute second in high quality. This video was added by user The Lazy SysAdmin 14 December 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 3,519 once and liked it 68 people.