ProxySQL

ProxySQL

Подготовка

сделать три ноды
- мастер
-2 слейва

CREATE DATABASE vena_test_db;
USE vena_test_db;
CREATE TABLE master_table (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, data varchar(255) NOT NULL);
INSERT INTO master_table VALUES(1, 'data1');
GRANT ALL ON vena_test_db.* to 'vena_test_user'@'%' identified by 'vena_test_password';

На обоих слейвах

USE vena_test_db;
CREATE TABLE slave_table (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, data varchar(255) NOT NULL);
INSERT INTO slave_table VALUES(1, 'slave-data1');
GRANT ALL ON vena_test_db.* to 'vena_test_user'@'%' identified by 'vena_test_password';

на ProxySQL

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, 'mysql-slave-1', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, 'mysql-slave-2', 3306);

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                               (10,                         11,                                        3, 1, 1, 3, 1, 100);

Ссылки

• Переменные :https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
• https://www.digitalocean.com/community/tutorials/how-to-use-proxysql-as-a-load-balancer-for-mysql-on-ubuntu-16-04