Unlock Distributed Data with MariaDB Spider: Installation, Single-Table Links, and Sharding

Introduction

Spider is a MariaDB storage engine that enables a standard MariaDB instance to act as a distributed database by linking tables across multiple servers.

MariaDB Storage Engines

Storage engines manage low‑level data access, handling writes, reads, row locking, MVCC, and transactions. Since MySQL, engines can be assigned per table and changed after creation.

What is Spider?

Spider allows one MariaDB server to access tables on another MariaDB server without requiring the remote server to run Spider code; a regular MySQL/MariaDB server suffices.

Installing the Spider Engine

Spider is bundled with MariaDB. Install it using the provided script:

$ mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2835
Server version: 10.4.6-MariaDB-log MariaDB Server
...
MariaDB> source /usr/share/mysql/install_spider.sql

Verify installation:

MariaDB> SHOW ENGINES;
+--------------------+---------+------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------+--------------+------+------------+
| SPIDER | YES | Spider storage engine | YES | YES | NO |
... (other engines) ...
+--------------------+---------+------------------------------------------+--------------+------+------------+
9 rows in set (0.001 sec)

Single‑Table Remote Link Example

Create a table on the target server (Server2) and insert sample data:

CREATE DATABASE spidertest;
USE spidertest;
CREATE TABLE customer(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL,
  address VARCHAR(255) NOT NULL);
INSERT INTO customer VALUES(NULL,'John Doe','1 Main Street');
INSERT INTO customer VALUES(NULL,'Bob Smith','45 Elm Street');
INSERT INTO customer VALUES(NULL,'Jane Jones','18 Second Street');

Create a user for Spider to connect remotely:

CREATE USER 'spider'@'192.168.0.11' IDENTIFIED BY 'spider';
GRANT ALL ON spidertest.* TO 'spider'@'192.168.0.11';
GRANT ALL ON mysql.* TO 'spider'@'192.168.0.11';

Define a remote server on the local instance:

CREATE SERVER Server2 FOREIGN DATA WRAPPER mysql
OPTIONS(HOST '192.168.0.11', DATABASE 'spidertest', PORT 10482,
        USER 'spider', PASSWORD 'spider');

Create a Spider table that points to the remote table:

DROP DATABASE IF EXISTS spidertest;
CREATE DATABASE spidertest;
USE spidertest;
CREATE TABLE customer(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL) ENGINE=Spider
COMMENT = 'wrapper "mysql", srv "Server2"';

Query the remote data:

SELECT * FROM customer;
+----+------------+
| id | name       |
+----+------------+
| 1  | John Doe   |
| 2  | Bob Smith  |
| 3  | Jane Jones |
+----+------------+

Use Cases Beyond Simple Links

Spider can replace replication for single‑table copies, provide read‑only access to tables in other instances, and serve as a building block for multi‑server aggregation.

Merging Tables from Multiple Servers

By creating views on each server that expose information_schema.GLOBAL_STATUS, you can combine them into a unified view:

CREATE OR REPLACE VIEW global_status_homer AS
SELECT 'homer' host, gs.variable_name, gs.variable_value
FROM information_schema.global_status gs;

CREATE OR REPLACE VIEW global_status_moe AS
SELECT 'moe' host, gs.variable_name, gs.variable_value
FROM information_schema.global_status gs;

CREATE OR REPLACE SERVER homer FOREIGN DATA WRAPPER mysql
OPTIONS(HOST '192.168.0.11', DATABASE 'mysql', PORT 10482,
        USER 'spider', PASSWORD 'spider');

CREATE OR REPLACE TABLE global_status_homer(host VARCHAR(2048),
    variable_name VARCHAR(64), variable_value VARCHAR(64))
ENGINE=Spider COMMENT='wrapper "mysql", srv "homer"';

CREATE OR REPLACE VIEW global_status_all AS
SELECT host, variable_name, variable_value FROM global_status_homer
UNION
SELECT host, variable_name, variable_value FROM global_status_moe;

CREATE OR REPLACE VIEW global_status_total AS
SELECT variable_name, SUM(variable_value) sum,
       MAX(variable_value) max,
       MIN(variable_value) min
FROM global_status_all
GROUP BY variable_name;

Querying the aggregated view shows combined status across the cluster.

Partitioned Tables with Spider (Sharding)

Spider also supports partitioned tables where each partition resides on a different server. Example with three servers:

CREATE DATABASE IF NOT EXISTS spidertest;
CREATE TABLE spidertest.customer(
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  address VARCHAR(255) NOT NULL) ENGINE=Spider
COMMENT 'wrapper "mysql", table "customer"'
PARTITION BY RANGE(id) (
  PARTITION p0 VALUES LESS THAN (1000) COMMENT = 'srv "Server2"',
  PARTITION p1 VALUES LESS THAN (2000) COMMENT = 'srv "Server3"');

INSERT INTO customer VALUES(1,'Larry','Main Street 1');
INSERT INTO customer VALUES(2,'Ed','Main Street 1');
INSERT INTO customer VALUES(3,'Bob','Main Street 1');
INSERT INTO customer VALUES(1001,'Monty','Main Street 1');
INSERT INTO customer VALUES(1002,'David','Main Street 1');
INSERT INTO customer VALUES(1003,'Allan','Main Street 1');

Rows 1‑3 are stored on Server2, rows 1001‑1003 on Server3, demonstrating horizontal scaling.

Conclusion

The Spider storage engine provides a flexible way to build distributed MariaDB solutions, from simple remote table links to full‑blown sharding and multi‑server aggregation, offering an alternative to multi‑source replication with less data redundancy.