ProxySQL: различия между версиями
Материал из noname.com.ua
Перейти к навигацииПерейти к поискуSirmax (обсуждение | вклад) |
Sirmax (обсуждение | вклад) |
||
(не показаны 22 промежуточные версии этого же участника) | |||
Строка 1: | Строка 1: | ||
+ | [[Категория:SQL]] |
||
+ | [[Категория:Linux]] |
||
+ | [[Категория:ProxySQL]] |
||
+ | [[Категория:MySQL]] |
||
=ProxySQL= |
=ProxySQL= |
||
Строка 27: | Строка 31: | ||
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-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 (20, 'mysql-slave-2', 3306); |
||
+ | INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, 'mysql-master', 3306); |
||
</PRE> |
</PRE> |
||
Строка 32: | Строка 37: | ||
<PRE> |
<PRE> |
||
INSERT INTO |
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) |
+ | mysql_group_replication_hostgroups (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind) |
− | VALUES |
+ | VALUES (10, 11, 20, 1, 1, 3, 0, 100); |
</PRE> |
</PRE> |
||
+ | |||
+ | |||
+ | |||
+ | <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> |
||
+ | |||
+ | |||
+ | |||
+ | Проверить: http://lefred.be/content/mysql-group-replication-native-support-in-proxysql/ |
||
==Ссылки== |
==Ссылки== |
||
* Переменные :https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password |
* Переменные :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 |
* https://www.digitalocean.com/community/tutorials/how-to-use-proxysql-as-a-load-balancer-for-mysql-on-ubuntu-16-04 |
||
+ | |||
+ | |||
+ | https://github.com/sysown/proxysql/tree/master/doc |
||
+ | |||
+ | Настройка репликации |
||
+ | * https://ruhighload.com/post/Как+настроить+MySQL+Master-Slave+репликацию |
||
+ | |||
+ | Типы логов MySQL |
||
+ | * https://habrahabr.ru/sandbox/22772/ |
||
+ | |||
+ | Репликация |
||
+ | * http://highload.guide/blog/mysql-replication.html |
||
+ | * http://docplayer.ru/26091768-Asinhronnaya-replikaciya-mysql-bez-cenzury-oleg-caryov.html |
||
+ | |||
+ | Мульти-сорс репликация |
||
+ | * https://habrahabr.ru/post/276581/ |
||
+ | |||
+ | GTID |
||
+ | * https://habrahabr.ru/post/276581/ |
||
+ | * https://habrahabr.ru/post/262925/ |
||
+ | * https://www.youtube.com/watch?v=v68l2YOur5M |
||
+ | |||
+ | GTID DEBUG |
||
+ | <PRE> |
||
+ | Last_SQL_Error: Could not execute Delete_rows event on table vena_test_db.master_table; Can't find record in 'master_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 455 |
||
+ | </PRE> |
||
+ | |||
+ | * https://www.fromdual.ch/replication-troubleshooting-classic-vs-gtid |
||
+ | |||
+ | |||
+ | ===ProxySQL=== |
||
+ | * Введение https://habrahabr.ru/post/303612/ |
||
+ | * Презентация https://www.percona.com/live/plam16/sites/default/files/slides/ProxySQL%20Tutorial%20-%20PLAM%202016.pdf |
||
+ | * Мирроринг |
||
+ | ** https://www.awsinfradb.com/single-post/2017/04/09/ProxySQL-Mirroring |
||
+ | ** https://github.com/sysown/proxysql/wiki/Mirroring |
Текущая версия на 09:11, 30 октября 2023
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/
Ссылки
- Переменные :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
https://github.com/sysown/proxysql/tree/master/doc
Настройка репликации
Типы логов MySQL
Репликация
- http://highload.guide/blog/mysql-replication.html
- http://docplayer.ru/26091768-Asinhronnaya-replikaciya-mysql-bez-cenzury-oleg-caryov.html
Мульти-сорс репликация
GTID
* https://habrahabr.ru/post/276581/ * https://habrahabr.ru/post/262925/ * https://www.youtube.com/watch?v=v68l2YOur5M
GTID DEBUG
Last_SQL_Error: Could not execute Delete_rows event on table vena_test_db.master_table; Can't find record in 'master_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 455