Spider storage engine Dockerfile

Overview

The Spider storage engine is a storage engine with built-in sharding features. It supports partitioning and xa transactions, and allows tables of different MariaDB instances to be handled as if they were on the same instance. It refers to one possible implementation of ISO/IEC 9075-9:2008 SQL/MED. When a table is created with the Spider storage engine, the table links to the table on a remote server. The remote table can be of any storage engine. The table link is concretely achieved by the establishment of the connection from a local MariaDB server to a remote MariaDB server. The link is shared for all tables that are part of a the same transaction.
https://mariadb.com/kb/en/library/spider-storage-engine-overview/

Setup

1. Create docker network.
   $ docker network create --gateway 192.168.10.1 --subnet 192.168.10.0/24 spider
2. Build image.
   $ make build
3. To bash into spider_node.
   $ make bash
4. Install SPIDER ENGINE.
   $ mariadb -uroot -p$MYSQL_ROOT_PASSWORD -e "source /usr/share/mysql/install_spider.sql"
5. Creates the definition of a server for use with the Spider.
   $ mariadb -uroot -p$MYSQL_ROOT_PASSWORD -e "CREATE SERVER data_node1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'root', PASSWORD 'password', HOST '192.168.10.101', PORT 3307);"
$ mariadb -uroot -p$MYSQL_ROOT_PASSWORD -e "CREATE SERVER data_node2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'root', PASSWORD 'password', HOST '192.168.10.102', PORT 3308);"

Usage

• Records can be inserted on the spider_node, and they will be stored on the data_node.

# spider_node
$ make bash
$ mariadb -u root -p$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE

MariaDB [spider_db]> INSERT INTO employees(name, department_id, created_at) VALUES ('Tom', 1, NOW()),('Jim', 2, NOW()),('Watson', 3, NOW());
Query OK, 3 rows affected (0.020 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [spider_db]> select * from employees;
+----+--------+---------------+---------------------+------------+
| id | name   | department_id | created_at          | updated_at |
+----+--------+---------------+---------------------+------------+
|  2 | Jim    |             2 | 2019-09-11 08:01:56 | NULL       |
|  1 | Tom    |             1 | 2019-09-11 08:01:56 | NULL       |
|  3 | Watson |             3 | 2019-09-11 08:01:56 | NULL       |
+----+--------+---------------+---------------------+------------+
3 rows in set (0.005 sec)

# data_node1
$ make bash/node1
$ mariadb -u root -p$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE

MariaDB [spider_db]> select * from employees;
+----+------+---------------+---------------------+------------+
| id | name | department_id | created_at          | updated_at |
+----+------+---------------+---------------------+------------+
|  2 | Jim  |             2 | 2019-09-11 08:01:56 | NULL       |
+----+------+---------------+---------------------+------------+
1 row in set (0.000 sec)

# data_node2
$ make bash/node2
$ mariadb -u root -p$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE

MariaDB [spider_db]> select * from employees;
+----+--------+---------------+---------------------+------------+
| id | name   | department_id | created_at          | updated_at |
+----+--------+---------------+---------------------+------------+
|  1 | Tom    |             1 | 2019-09-11 08:01:56 | NULL       |
|  3 | Watson |             3 | 2019-09-11 08:01:56 | NULL       |
+----+--------+---------------+---------------------+------------+
2 rows in set (0.000 sec)