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"';
JSON support will come to MariaDB 10.2. See the official MariaDB blog post from 28.02.2017. There are some example SQL statements and validation.
JSON is fast becoming the standard format for data interchange and for
unstructured data, and MariaDB 10.2 adds a range on JSON supporting
functions, even though a JSON datatype isn't implemented yet. There
are some reasons why there isn't a JSON datatype, but one is that
there are actually not that many advantages to that as JSON is a
text-based format. This blog post aims to describe JSON and the use
cases for it, as well as to describe the MariaDB 10.2 JSON functions
and uses for these, as well as showing some other additions to MariaDB
10.2 that are useful for JSON processing.
Best Answer
You may want to look into a solution such as SeveralNines' ClusterControl which I believe can automate a lot of this.