Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.
2 Improve answer
source | link

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

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

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"';
1
source | link

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