proxy_sql_tools
proxy_sql_tools copied to clipboard
Published
4 years ago
•
Tusamarco
Tusamarco
Allow failover between two segments
Hi @Tusamarco
I would like to request the addition of a flag or even a new --active_failover mode that allows the script to follow the rules from 8000 host group but ignore the gmcast.segment. Here is an example
Node1 - DC1 - gmcast.segment=1 Node2 - DC1 - gmcast.segment=1 Node3 - DC1 - gmcast.segment=1 Node4 - DC2 - gmcast.segment=2 Node5 - DC2 - gmcast.segment=2
I have my servers configured as below:
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 8050 | 192.168.91.41 | 3306 | ONLINE | 10000 |
| 8050 | 192.168.91.42 | 3306 | ONLINE | 1000 |
| 8050 | 192.168.91.43 | 3306 | ONLINE | 100 |
| 8050 | 192.168.91.44 | 3306 | ONLINE | 10 |
| 8050 | 192.168.91.45 | 3306 | ONLINE | 1 |
| 51 | 192.168.91.41 | 3306 | ONLINE | 100 |
| 51 | 192.168.91.42 | 3306 | ONLINE | 10000 |
| 51 | 192.168.91.43 | 3306 | ONLINE | 10000 |
| 51 | 192.168.91.44 | 3306 | ONLINE | 1 |
| 51 | 192.168.91.45 | 3306 | ONLINE | 1 |
| 50 | 192.168.91.41 | 3306 | ONLINE | 10000 |
+--------------+---------------+------+--------+--------+
In case I lose all nodes on DC1 but apps continue up, I would like to write on node4. If that node also goes down, writes should be redirected to node5.
@altmannmarcelo that should already happen, but test urself and let me know
([email protected]) [main]>select hostgroup_id,hostname,port,status,weight from runtime_mysql_servers order by hostgroup_id,weight desc;
+--------------+---------------+------+--------+---------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+---------+
| 50 | 192.168.1.205 | 3306 | ONLINE | 1000000 |
| 52 | 192.168.1.205 | 3306 | ONLINE | 1000 |
| 52 | 192.168.1.22 | 3306 | ONLINE | 1000 |
| 52 | 192.168.1.23 | 3306 | ONLINE | 1000 |
| 52 | 192.168.1.231 | 3306 | ONLINE | 1000 |
| 52 | 192.168.1.233 | 3306 | ONLINE | 1000 |
| 52 | 192.168.1.21 | 3306 | ONLINE | 10 |
| 8050 | 192.168.1.205 | 3306 | ONLINE | 1000000 |
| 8050 | 192.168.1.231 | 3306 | ONLINE | 100000 |
| 8050 | 192.168.1.22 | 3306 | ONLINE | 100 |
+--------------+---------------+------+--------+---------+
10 rows in set (0.01 sec)
([email protected]) [main]>select hostgroup_id,hostname,port,status,weight from runtime_mysql_servers order by hostgroup_id,weight desc;
+--------------+---------------+------+--------+---------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+---------+
| 50 | 192.168.1.231 | 3306 | ONLINE | 100000 |
| 52 | 192.168.1.22 | 3306 | ONLINE | 1000 |
| 52 | 192.168.1.23 | 3306 | ONLINE | 1000 |
| 52 | 192.168.1.231 | 3306 | ONLINE | 1000 |
| 52 | 192.168.1.233 | 3306 | ONLINE | 1000 |
| 52 | 192.168.1.21 | 3306 | ONLINE | 10 |
| 8050 | 192.168.1.205 | 3306 | ONLINE | 1000000 |
| 8050 | 192.168.1.231 | 3306 | ONLINE | 100000 |
| 8050 | 192.168.1.22 | 3306 | ONLINE | 100 |
| 9052 | 192.168.1.205 | 3306 | ONLINE | 1000 |
+--------------+---------------+------+--------+---------+
10 rows in set (0.00 sec)
([email protected]) [main]>select hostgroup_id,hostname,port,status,weight from runtime_mysql_servers order by hostgroup_id,weight desc;
+--------------+---------------+------+--------+---------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+---------+
| 50 | 192.168.1.22 | 3306 | ONLINE | 100 |
| 52 | 192.168.1.22 | 3306 | ONLINE | 1000 |
| 52 | 192.168.1.23 | 3306 | ONLINE | 1000 |
| 52 | 192.168.1.233 | 3306 | ONLINE | 1000 |
| 52 | 192.168.1.21 | 3306 | ONLINE | 10 |
| 8050 | 192.168.1.205 | 3306 | ONLINE | 1000000 |
| 8050 | 192.168.1.231 | 3306 | ONLINE | 100000 |
| 8050 | 192.168.1.22 | 3306 | ONLINE | 100 |
| 9052 | 192.168.1.205 | 3306 | ONLINE | 1000 |
| 9052 | 192.168.1.231 | 3306 | ONLINE | 1000 |
+--------------+---------------+------+--------+---------+
10 rows in set (0.00 sec)
+-----------+--------------------------------------+--------+-------------+---------+
| HOST_NAME | UUID | STATUS | LOCAL_INDEX | SEGMENT |
+-----------+--------------------------------------+--------+-------------+---------+
| node1 | 0233295c-cae0-11e7-afe9-c7b4f55afc65 | SYNCED | 0 | 1 |
| node3 | 21fa7084-c939-11e7-bf44-9e5c6e65b59f | SYNCED | 3 | 1 |
| node2 | 63870cc3-af5d-11e7-a0db-463be8426737 | SYNCED | 5 | 1 |
| node5 | 07f55a00-c57a-11e7-bd7d-8a568ca96345 | SYNCED | 1 | 2 |
| node6 | 09da243f-c883-11e7-b71e-7bccf3867968 | SYNCED | 2 | 2 |
| node4 | 42b85028-cbad-11e7-a4fc-4fa27eb54537 | SYNCED | 4 | 2 |
+-----------+--------------------------------------+--------+-------------+---------+
6 rows in set (0.01 sec)
+-----------+---------------+
| HOST_NAME | IP |
+-----------+---------------+
| node1 | 192.168.1.205 |
| node2 | 192.168.1.21 |
| node3 | 192.168.1.231 |
| node5 | 192.168.1.23 |
| node4 | 192.168.1.22 |
| node6 | 192.168.1.233 |
+-----------+---------------+
2017/11/17 12:52:18.829:[WARN] Move node:192.168.1.205;3306;50;3001 SQL: UPDATE mysql_servers SET hostgroup_id=9050 WHERE hostgroup_id=50 AND hostname='192.168.1.205' AND port='3306'
2017/11/17 12:52:18.836:[WARN] Move node:192.168.1.205;3306;52;3001 SQL: UPDATE mysql_servers SET hostgroup_id=9052 WHERE hostgroup_id=52 AND hostname='192.168.1.205' AND port='3306'
2017/11/17 12:52:21.155:[WARN] Fail-over in action Using Method = 1
2017/11/17 12:52:21.156:[INFO] Special Backup - Group found! I am electing a node to writer following the indications
This Node Try to become the new WRITER for HG 50 Server details: 192.168.1.231:3306:HG8050
2017/11/17 12:52:21.156:[INFO] This Node Try to become a WRITER promoting to HG 50 192.168.1.231:3306:HG 8050
2017/11/17 12:52:21.210:[WARN] Move node:192.168.1.231:33061000002000 SQL:INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.1.231',50,3306,100000,2000);
2017/11/17 12:52:31.628:[WARN] Move node:192.168.1.231;3306;50;3001 SQL: UPDATE mysql_servers SET hostgroup_id=9050 WHERE hostgroup_id=50 AND hostname='192.168.1.231' AND port='3306'
2017/11/17 12:52:31.632:[WARN] Move node:192.168.1.231;3306;52;3001 SQL: UPDATE mysql_servers SET hostgroup_id=9052 WHERE hostgroup_id=52 AND hostname='192.168.1.231' AND port='3306'
2017/11/17 12:52:33.666:[WARN] Fail-over in action Using Method = 1
2017/11/17 12:52:33.667:[INFO] Special Backup - Group found! I am electing a node to writer following the indications
This Node Try to become the new WRITER for HG 50 Server details: 192.168.1.22:3306:HG8050
2017/11/17 12:52:33.667:[INFO] This Node Try to become a WRITER promoting to HG 50 192.168.1.22:3306:HG 8050
2017/11/17 12:52:33.711:[WARN] Move node:192.168.1.22:33061002000 SQL:INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.1.22',50,3306,100,2000);
