ProxySQL: различия между версиями

Материал из noname.com.ua
Перейти к навигацииПерейти к поиску
Строка 39: Строка 39:
   
   
  +
<PRE>
   
  +
  +
INSERT INTO
  +
mysql_users(username, password, default_hostgroup)
  +
VALUES ('vena_test_user', 'vena_test_password', 20);
  +
  +
  +
  +
INSERT INTO
  +
mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
  +
VALUES (1, 1, '^SELECT .* FOR UPDATE$', 10, 1);
  +
  +
INSERT INTO
  +
mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
  +
VALUES (1, 1, '^SELECT', 20, 1);
  +
  +
INSERT INTO
  +
mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
  +
VALUES
  +
</PRE>
   
   

Версия 15:37, 30 января 2018

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_servers(hostgroup_id, hostname, port) VALUES (10, 'mysql-master', 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,                      20,               1,                 1,      3,           0,                     100);




INSERT INTO 
    mysql_users(username,          password,            default_hostgroup) 
	VALUES     ('vena_test_user', 'vena_test_password', 20);



INSERT INTO 
    mysql_query_rules (rule_id, active, match_pattern,            destination_hostgroup, apply) 
    VALUES            (1,       1,      '^SELECT .* FOR UPDATE$', 10,                    1);

INSERT INTO 
    mysql_query_rules (rule_id, active, match_pattern,            destination_hostgroup, apply) 
    VALUES            (1,       1,      '^SELECT',                20,                    1);

INSERT INTO 
    mysql_query_rules (rule_id, active, match_pattern,            destination_hostgroup, apply) 
    VALUES   


Проверить: http://lefred.be/content/mysql-group-replication-native-support-in-proxysql/

Ссылки