Application → Spider Node → Shard 1
                         → Shard 2
                         → Shard 3

# Spider biasanya sudah included dalam MariaDB
# Cek apakah sudah tersedia
mysql -e "INSTALL SONAME 'ha_spider';"

# Atau enable di my.cnf
[mysqld]
plugin-load-add=ha_spider

-- Spider node (frontend)
CREATE SERVER shard1
FOREIGN DATA WRAPPER mysql
OPTIONS (
    HOST '192.168.1.101',
    DATABASE 'shard_db',
    USER 'spider_user',
    PASSWORD 'password',
    PORT 3306
);

CREATE SERVER shard2
FOREIGN DATA WRAPPER mysql
OPTIONS (
    HOST '192.168.1.102',
    DATABASE 'shard_db',
    USER 'spider_user',
    PASSWORD 'password',
    PORT 3306
);

-- Create Spider table dengan sharding
CREATE TABLE users (
    id INT,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP,
    
    -- Shard key (partitioning)
    PARTITION BY LIST COLUMNS(id) (
        PARTITION p1 VALUES IN (1, 2, 3) COMMENT 'shard1',
        PARTITION p2 VALUES IN (4, 5, 6) COMMENT 'shard2'
    )
) ENGINE=SPIDER
COMMENT 'wrapper "mysql", table "users"';

-- Atau dengan HASH sharding
CREATE TABLE orders (
    id INT,
    user_id INT,
    total DECIMAL(10,2),
    
    PARTITION BY HASH(user_id) PARTITIONS 4
) ENGINE=SPIDER;

# my.cnf
[mysqld]
# Spider settings
spider_internal_sql_log_off = 1
spider_direct_sql = 1
spider_support_xa = 1

# Connection settings
spider_connect_timeout = 10
spider_net_read_timeout = 30
spider_net_write_timeout = 30

CREATE TABLE distributed_table (...) ENGINE=SPIDER
COMMENT '
    wrapper "mysql",
    srv "shard1,shard2,shard3",
    table "table_name",
    mbk "1"
';

-- Sharding untuk big data
CREATE TABLE logs (
    log_id BIGINT,
    log_time DATETIME,
    level VARCHAR(10),
    message TEXT
) ENGINE=SPIDER
PARTITION BY RANGE (YEAR(log_time)) (
    PARTITION p2022 VALUES LESS THAN (2023) COMMENT 'srv "shard1"',
    PARTITION p2023 VALUES LESS THAN (2024) COMMENT 'srv "shard2"',
    PARTITION p2024 VALUES LESS THAN (2025) COMMENT 'srv "shard3"'
);

-- Sharding berdasarkan region
CREATE TABLE customers (
    customer_id INT,
    region VARCHAR(20),
    name VARCHAR(100)
) ENGINE=SPIDER
PARTITION BY LIST COLUMNS(region) (
    PARTITION asia VALUES IN ('SG', 'JP', 'ID') COMMENT 'srv "asia_shard"',
    PARTITION europe VALUES IN ('UK', 'DE', 'FR') COMMENT 'srv "eu_shard"',
    PARTITION americas VALUES IN ('US', 'CA', 'BR') COMMENT 'srv "us_shard"'
);

-- Spider otomatis route query ke shard yang tepat
SELECT * FROM users WHERE id = 5;
-- Akan di-route ke shard2 (berdasarkan partitioning)

SELECT * FROM orders WHERE user_id = 123;
-- Akan di-route berdasarkan hash(user_id)

-- Aggregations across shards
SELECT region, COUNT(*) as customer_count
FROM customers
GROUP BY region;

-- Spider akan query semua shards dan aggregate results

-- Check Spider servers
SELECT * FROM mysql.servers;

-- Check Spider table status
SHOW TABLE STATUS WHERE Engine = 'SPIDER';

-- Spider statistics
SHOW STATUS LIKE 'Spider%';

-- Check slow queries
SELECT * FROM mysql.slow_log 
WHERE db LIKE '%spider%'
ORDER BY query_time DESC;