ProxySQL: различия между версиями
Материал из noname.com.ua
Перейти к навигацииПерейти к поискуSirmax (обсуждение | вклад) |
Sirmax (обсуждение | вклад) |
||
| Строка 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/
