I try to setup High Availability using Spider Engine and MariaDB 10.0.14, but I a m not sure what configuration to use to make it work as expected.
What I want is:
- Accessing a remote table using Spider Engine on a remote server A (let's say "main server" below)
- If main server A is down => access "backup server" B
Based on the documentation, I set up the following configuration:
CREATE SERVER server_main
FOREIGN DATA WRAPPER mysql
OPTIONS(
HOST '10.2.0.1',
PORT 3306,
DATABASE 'db01',
USER 'spider',
PASSWORD '123456'
);
CREATE SERVER server_backup
FOREIGN DATA WRAPPER mysql
OPTIONS(
HOST '10.2.0.2',
PORT 3306,
DATABASE 'db01',
USER 'spider',
PASSWORD '123456'
);
INSERT INTO mysql.spider_link_mon_servers
(db_name, table_name, link_id, sid, server)
VALUES
('%', '%', '%', 100, 'server_main'),
('%', '%', '%', 101, 'server_backup');
SELECT spider_flush_table_mon_cache();
-- Created on 10.2.0.1 (server_main)
CREATE TABLE `np` (
`s` text
) ENGINE=InnoDB;
INSERT INTO np VALUES ('main');
-- Created on 10.2.0.2 (server_backup)
CREATE TABLE `np` (
`s` text
) ENGINE=InnoDB;
INSERT INTO np VALUES ('backup');
CREATE TABLE `np` (
`s` text
) ENGINE=SPIDER COMMENT='wrapper "mysql", srv "server_main server_backup", database "db01", table "np", mbk "2", mkd "2", msi "100 101", link_status "0 0"';
Half the time spider uses server_main and server_backup:
db_spider =# SELECT * FROM np;
+------+
| s |
+------+
| main |
+------+
1 row in set (0.00 sec)
db_spider =# Bye
[...]
db_spider =# SELECT * FROM np;
+--------+
| s |
+--------+
| backup |
+--------+
1 row in set (0.00 sec)
But if I stop main mysqld instance, it still try to access the failed node:
db_spider =# SELECT * FROM np;
ERROR 1032 (HY000): Can't find record in 'spider_tables'
db_spider =# Bye
[...]
db_spider =# SELECT * FROM np;
+--------+
| s |
+--------+
| backup |
+--------+
1 row in set (0.00 sec)
Maybe I misconfigured or missed some settings? Options "msi" and "link_status" are not very clear for me in fact...