3

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...

1 Answer 1

5

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"';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.