Environment Setup

Required components:

• Ubuntu 22.04.3 LTS (four virtual machines)
• MySQL 8.0.35
• ProxySQL 2.5.5

MySQL Master-Slave Replication Configuration

Installing MySQL on All Servers

Update package index and install MySQL server:

sudo apt update
sudo apt install mysql-server

Master Server Configuration

Edit MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add these settings under [mysqld] section:

server-id = 1
bind-address = 0.0.0.0
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = target_database
default_authentication_plugin=mysql_native_password

Restart MySQL service:

sudo systemctl restart mysql

Create replication user:

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'secure_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;

Record replication position:

SHOW MASTER STATUS;

Slave Server Configuration

Edit MySQL configuraton file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add these settings under [mysqld] section:

server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
read_only = 1
binlog_do_db = target_database

Configure replication:

CHANGE MASTER TO
MASTER_HOST='master_server_ip',
MASTER_USER='replication_user',
MASTER_PASSWORD='secure_password',
MASTER_LOG_FILE='recorded_file_name',
MASTER_LOG_POS=recorded_position;

Start replication and verify status:

START SLAVE;
SHOW SLAVE STATUS\G

ProxySQL Installation and Configuration

Installing ProxySQL

apt-get update && apt-get install -y lsb-release wget apt-transport-https ca-certificates gnupg
wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key' | apt-key add -
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./" | tee /etc/apt/sources.list.d/proxysql.list
apt-get -y update
apt-get -y install proxysql
sudo systemctl start proxysql

ProxySQL Admin Interface Configuration

Connect to admin interface:

mysql -u admin -padmin -h 127.0.0.1 -P 6032

Configure backend servers:

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, 'master_ip', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, 'slave1_ip', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, 'slave2_ip', 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Add database users:

INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('db_user', 'user_password', 10);
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('monitor_user', 'monitor_password', 10);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Configure replication hostgroups:

INSERT INTO mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup) VALUES (10, 20);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Set query routing rules:

INSERT INTO mysql_query_rules(active, match_digest, destination_hostgroup, apply) VALUES(1, '^SELECT.*FOR UPDATE$', 10, 1);
INSERT INTO mysql_query_rules(active, match_digest, destination_hostgroup, apply) VALUES(1, '^SELECT', 20, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

MySQL 8.0 Compatibility Fix

Update ProxySQL server version for MySQL 8.0 compatibility:

UPDATE global_variables SET variable_value="8.0.4 (ProxySQL)" WHERE variable_name='mysql-server_version';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Authentication Plugin Confgiuration

Update authentication method on MySQL servers:

ALTER USER 'username'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'new_password';
FLUSH PRIVILEGES;

Spring Boot Application Configuration

Configure datasource in application.yml:

spring:
  datasource:
    url: jdbc:mysql://proxysql_ip:6033/database_name
    username: database_user
    password: database_password
    driver-class-name: com.mysql.jdbc.Driver