Setting up MariaDB Spider HA

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? Opti

I found my mistake. I realized that spider_link_mon_servers table must contain spider nodes, not backend nodes. And my spider nodes are not running on the same instance as backends. Spider tables run on port 3307.

So I need to configure monitoring servers like this (let's call them "proxy"):

CREATE SERVER server_main_proxy
 FOREIGN DATA WRAPPER mysql
OPTIONS(
 HOST '10.2.0.1',
 PORT 3307,
 DATABASE 'db01',
 USER 'spider',
 PASSWORD '123456'
);

CREATE SERVER server_backup_proxy
 FOREIGN DATA WRAPPER mysql
OPTIONS(
 HOST '10.2.0.2',
 PORT 3307,
 DATABASE 'db01',
 USER 'spider',
 PASSWORD '123456'
);

TRUNCATE mysql.spider_link_mon_servers;
INSERT INTO mysql.spider_link_mon_servers 
(db_name, table_name, link_id, sid, server) 
VALUES
('%', '%', '%', 100, 'server_main_proxy'),
('%', '%', '%', 101, 'server_backup_proxy');

SELECT spider_flush_table_mon_cache();

Now, if I stop "main" instance (3306 one), spider detects it and access only backup node.

There is still one feature I have not been able to operate: use only main backend if it is alive, and use backup backend only if the main backend dies...

EDIT 11/02/2014 :

The parameter "alc" (active_link_count) set to 1 is what I need in order to use second backend as a failover backend only. So something like this did the trick:

ALTER TABLE np ENGINE=SPIDER COMMENT='wrapper "mysql", srv "server_main server_backup", database "db01", table "np", mbk "2", mkd "2", alc "1", msi "100 101", link_status "0 0"';

StackExchange Database Administrators Q#86792, answer score: 5