Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Learn about Spider operations in MariaDB Enterprise Server. This section covers advanced configurations, management, and best practices for leveraging Spider in enterprise environments.
This section covers operational guides for managing a Federated Spider topology, including migrating tables and performing backup and restore.
ALTER TABLE spider_hq_sales.invoices
DEFAULT CHARACTER SET 'utf8mb4'
DEFAULT COLLATE 'utf8mb4_general_ci';ALTER TABLE spider_hq_sales.invoices
COMMENT = 'server "new_hq_server", table "invoices"'Learn about ODBC operations in MariaDB Enterprise Spider topology. This section covers configuring and managing ODBC connections to distributed Spider environments for seamless data access.
Learn about ODBC operations in MariaDB Enterprise Spider topology. This section covers configuring and managing ODBC connections to distributed Spider environments for seamless data access.
ALTER TABLE spider_sharded_sales.invoices
DEFAULT CHARACTER SET 'utf8mb4'
DEFAULT COLLATE 'utf8mb4_general_ci';ALTER TABLE spider_sharded_sales.invoices
REORGANIZE PARTITION hq_partition INTO (
PARTITION hq_partition VALUES IN (1) COMMENT = 'server "new_hq_server", table "invoices"'
);INSERT INTO innodb_tab
SELECT * FROM spider_tab;CREATE DATABASE hq_sales;
CREATE SEQUENCE hq_sales.invoice_seq;
CREATE TABLE hq_sales.invoices (
branch_id INT NOT NULL DEFAULT (1) CHECK (branch_id=1),
invoice_id INT NOT NULL DEFAULT (NEXT VALUE FOR migrated_hq_sales.invoice_seq),
customer_id INT,
invoice_date DATETIME(6),
invoice_total DECIMAL(13, 2),
payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
PRIMARY KEY(branch_id, invoice_id)
) ENGINE=InnoDB;INSERT INTO hq_sales.invoices
SELECT * FROM spider_hq_sales.invoices;SELECT * FROM hq_sales.invoices;+-----------+------------+-------------+----------------------------+---------------+----------------+
| branch_id | invoice_id | customer_id | invoice_date | invoice_total | payment_method |
+-----------+------------+-------------+----------------------------+---------------+----------------+
| 1 | 1 | 1 | 2020-05-10 12:35:10.000000 | 1087.23 | CREDIT_CARD |
| 1 | 2 | 2 | 2020-05-10 14:17:32.000000 | 1508.57 | WIRE_TRANSFER |
| 1 | 3 | 3 | 2020-05-10 14:25:16.000000 | 227.15 | CASH |
+-----------+------------+-------------+----------------------------+---------------+----------------+Instructions on how to expand a sharded Spider topology by adding new data nodes (shards) and rebalancing the data distribution.
CREATE USER spider_user@192.0.2.1 IDENTIFIED BY "password";$ mariadb --user spider_user --host 192.0.2.2 --passwordCREATE SERVER southern_server
FOREIGN DATA WRAPPER mariadb
OPTIONS (
HOST '192.0.2.6',
PORT 5004,
USER 'spider_user',
PASSWORD 'password',
DATABASE 'southern_sales'
);CREATE DATABASE southern_sales;
CREATE SEQUENCE southern_sales.invoice_seq;
CREATE TABLE southern_sales.invoices (
branch_id INT NOT NULL DEFAULT (4) CHECK (branch_id=4),
invoice_id INT NOT NULL DEFAULT (NEXT VALUE FOR hq_sales.invoice_seq),
customer_id INT,
invoice_date DATETIME(6),
invoice_total DECIMAL(13, 2),
payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
PRIMARY KEY(branch_id, invoice_id)
) ENGINE=InnoDB;
INSERT INTO southern_sales.invoices
(customer_id, invoice_date, invoice_total, payment_method)
VALUES
(1, '2020-05-25 10:22:11', 2083.56, 'CREDIT_CARD'),
(2, '2020-05-25 11:42:33', 515.22, 'WIRE_TRANSFER'),
(3, '2020-05-25 13:15:16', 1213.80, 'CASH');GRANT ALL PRIVILEGES ON southern_sales.invoices TO 'spider_user'@'192.0.2.1';GRANT CREATE TEMPORARY TABLES ON southern_sales.* TO 'spider_user'@'192.0.2.1';ALTER TABLE spider_sharded_sales.invoices
ADD PARTITION (
PARTITION southern_partition VALUES IN (4) COMMENT = 'server "southern_server", table "invoices"'
);SELECT * FROM spider_sharded_sales.invoices;+-----------+------------+-------------+----------------------------+---------------+----------------+
| branch_id | invoice_id | customer_id | invoice_date | invoice_total | payment_method |
+-----------+------------+-------------+----------------------------+---------------+----------------+
| 1 | 1 | 1 | 2020-05-10 12:35:10.000000 | 1087.23 | CREDIT_CARD |
| 1 | 2 | 2 | 2020-05-10 14:17:32.000000 | 1508.57 | WIRE_TRANSFER |
| 1 | 3 | 3 | 2020-05-10 14:25:16.000000 | 227.15 | CASH |
| 2 | 1 | 2 | 2020-05-10 12:31:00.000000 | 1351.04 | CREDIT_CARD |
| 2 | 2 | 2 | 2020-05-10 12:45:27.000000 | 162.11 | WIRE_TRANSFER |
| 2 | 3 | 4 | 2020-05-10 13:11:23.000000 | 350.00 | CASH |
| 3 | 1 | 5 | 2020-05-10 12:31:00.000000 | 111.50 | CREDIT_CARD |
| 3 | 2 | 8 | 2020-05-10 12:45:27.000000 | 1509.23 | WIRE_TRANSFER |
| 3 | 3 | 3 | 2020-05-10 13:11:23.000000 | 3301.66 | CASH |
| 4 | 1 | 1 | 2020-05-25 10:22:11.000000 | 2083.56 | CREDIT_CARD |
| 4 | 2 | 2 | 2020-05-25 11:42:33.000000 | 515.22 | WIRE_TRANSFER |
| 4 | 3 | 3 | 2020-05-25 13:15:16.000000 | 1213.80 | CASH |
+-----------+------------+-------------+----------------------------+---------------+----------------+[oracle]
Description = Oracle ODBC Connection
Driver = /usr/lib/oracle/19.16/client64/lib/libsqora.so.19.1[ORARDS]
Description = Oracle
Driver = oracle
ServerName = REMOTE
UserID = {user_name}
Password = {password}
TNSNamesFile = /etc/tnsnames.oraREMOTE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={host_name/ip address})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME={service_name})))isql -v ORARDS systemctl start mariadb or systemctl start mysqld INSTALL SONAME 'ha_spider';
CREATE DATABASE spider_test;
USE spider_test;
CREATE OR REPLACE TABLE spider_test.contacts
(
CONTACT_ID BIGINT NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR( 255 ) NOT NULL,
LAST_NAME VARCHAR( 255 ) NOT NULL,
EMAIL VARCHAR( 255 ) NOT NULL,
PHONE VARCHAR( 20 ) ,
CUSTOMER_ID BIGINT) ENGINE=SPIDER
CONNECTION='WRAPPER "odbc", DSN "ORARDS", table "CONTACTS"';wget https://download.oracle.com/otn_software/linux/instantclient/1916000/oracle-instantclient19.16-odbc-19.16.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/1916000/oracle-instantclient19.16-sqlplus-19.16.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/1916000/oracle-instantclient19.16-basic-19.16.0.0.0-1.x86_64.rpmyum localinstall *.rpmCREATE USER 'mariadb-backup'@'localhost'
IDENTIFIED BY 'mb_passwd';
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR
ON *.*
TO 'mariadb-backup'@'localhost';GRANT LOCK TABLES ON hq_sales.* TO 'spider_user'@'192.0.2.2';LOCK TABLES spider_federated_sales.invoices READ;$ sudo mariadb-backup --backup \
--target-dir=/data/backups/full \
--user=mariadb-backup \
--password='mb_passwd'$ sudo mariadb-backup --backup \
--target-dir=/data/backups/full \
--user=mariadb-backup \
--password='mb_passwd'UNLOCK TABLES;$ sudo mariadb-backup --prepare \
--target-dir=/data/backups/full$ sudo systemctl stop mariadb$ sudo rm -fr /var/lib/mysql/*$ sudo mariadb-backup --copy-back \
--target-dir=/data/backups/full$ sudo chown -R mysql:mysql /var/lib/mysql$ sudo systemctl start mariadbSELECT * FROM spider_hq_sales.invoices;+-----------+------------+-------------+----------------------------+---------------+----------------+
| branch_id | invoice_id | customer_id | invoice_date | invoice_total | payment_method |
+-----------+------------+-------------+----------------------------+---------------+----------------+
| 1 | 1 | 1 | 2020-05-10 12:35:10.000000 | 1087.23 | CREDIT_CARD |
| 1 | 2 | 2 | 2020-05-10 14:17:32.000000 | 1508.57 | WIRE_TRANSFER |
| 1 | 3 | 3 | 2020-05-10 14:25:16.000000 | 227.15 | CASH |
+-----------+------------+-------------+----------------------------+---------------+----------------+CREATE USER 'mariadb-dump'@'localhost'
IDENTIFIED BY 'md_passwd';
GRANT SELECT, INSERT, SHOW VIEW, TRIGGER, CREATE, ALTER, EVENT, RELOAD, LOCK TABLES
ON *.*
TO 'mariadb-dump'@'localhost';GRANT LOCK TABLES ON hq_sales.* TO 'spider_user'@'192.0.2.2';LOCK TABLES spider_federated_sales.invoices READ;$ mariadb-dump \
--user=mariadb-dump \
--password='md_passwd' \
--ssl-verify-server-cert \
--ssl-ca ~/mariadb_chain.pem \
--all-databases \
--single-transaction \
--default-character-set=utf8mb4 \
> mariadb_dump.sql$ mariadb-dump \
--user=mariadb-dump \
--password='md_passwd' \
--ssl-verify-server-cert \
--ssl-ca ~/mariadb_chain.pem \
--all-databases \
--single-transaction \
--default-character-set=utf8mb4 \
> mariadb_dump.sqlUNLOCK TABLES;$ mariadb \
--user=mariadb-dump \
--password='md_passwd' \
--ssl-verify-server-cert \
--ssl-ca ~/mariadb_chain.pem \
--default-character-set=utf8mb4 \
< mariadb_dump.sql$ mariadb \
--user=mariadb-dump \
--password='md_passwd' \
--ssl-verify-server-cert \
--ssl-ca ~/mariadb_chain.pem \
--default-character-set=utf8mb4 \
< mariadb_dump.sqlSELECT * FROM spider_hq_sales.invoices;+-----------+------------+-------------+----------------------------+---------------+----------------+
| branch_id | invoice_id | customer_id | invoice_date | invoice_total | payment_method |
+-----------+------------+-------------+----------------------------+---------------+----------------+
| 1 | 1 | 1 | 2020-05-10 12:35:10.000000 | 1087.23 | CREDIT_CARD |
| 1 | 2 | 2 | 2020-05-10 14:17:32.000000 | 1508.57 | WIRE_TRANSFER |
| 1 | 3 | 3 | 2020-05-10 14:25:16.000000 | 227.15 | CASH |
+-----------+------------+-------------+----------------------------+---------------+----------------+Guidelines for backing up and restoring a sharded Spider topology, ensuring consistency across multiple shards using tools like MariaDB Backup.
CREATE USER 'mariadb-backup'@'localhost'
IDENTIFIED BY 'mb_passwd';
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR
ON *.*
TO 'mariadb-backup'@'localhost';GRANT LOCK TABLES ON hq_sales.* TO 'spider_user'@'192.0.2.2';GRANT LOCK TABLES ON eastern_sales.* TO 'spider_user'@'192.0.2.2';GRANT LOCK TABLES ON western_sales.* TO 'spider_user'@'192.0.2.2';LOCK TABLES spider_sharded_sales.invoices READ;$ sudo mariadb-backup --backup \
--target-dir=/data/backups/full \
--user=mariadb-backup \
--password='mb_passwd'$ sudo mariadb-backup --backup \
--target-dir=/data/backups/full \
--user=mariadb-backup \
--password='mb_passwd'UNLOCK TABLES;$ sudo mariadb-backup --prepare \
--target-dir=/data/backups/full$ sudo systemctl stop mariadb$ sudo rm -fr /var/lib/mysql/*$ sudo mariadb-backup --copy-back \
--target-dir=/data/backups/full$ sudo chown -R mysql:mysql /var/lib/mysql$ sudo systemctl start mariadbSELECT * FROM spider_sharded_sales.invoices;+-----------+------------+-------------+----------------------------+---------------+----------------+
| branch_id | invoice_id | customer_id | invoice_date | invoice_total | payment_method |
+-----------+------------+-------------+----------------------------+---------------+----------------+
| 1 | 1 | 1 | 2020-05-10 12:35:10.000000 | 1087.23 | CREDIT_CARD |
| 1 | 2 | 2 | 2020-05-10 14:17:32.000000 | 1508.57 | WIRE_TRANSFER |
| 1 | 3 | 3 | 2020-05-10 14:25:16.000000 | 227.15 | CASH |
| 2 | 1 | 2 | 2020-05-10 12:31:00.000000 | 1351.04 | CREDIT_CARD |
| 2 | 2 | 2 | 2020-05-10 12:45:27.000000 | 162.11 | WIRE_TRANSFER |
| 2 | 3 | 4 | 2020-05-10 13:11:23.000000 | 350.00 | CASH |
| 3 | 1 | 5 | 2020-05-10 12:31:00.000000 | 111.50 | CREDIT_CARD |
| 3 | 2 | 8 | 2020-05-10 12:45:27.000000 | 1509.23 | WIRE_TRANSFER |
| 3 | 3 | 3 | 2020-05-10 13:11:23.000000 | 3301.66 | CASH |
+-----------+------------+-------------+----------------------------+---------------+----------------+CREATE USER 'mariadb-dump'@'localhost'
IDENTIFIED BY 'md_passwd';
GRANT SELECT, INSERT, SHOW VIEW, TRIGGER, CREATE, ALTER, EVENT, RELOAD, LOCK TABLES
ON *.*
TO 'mariadb-dump'@'localhost';GRANT LOCK TABLES ON hq_sales.* TO 'spider_user'@'192.0.2.2';GRANT LOCK TABLES ON eastern_sales.* TO 'spider_user'@'192.0.2.2';GRANT LOCK TABLES ON western_sales.* TO 'spider_user'@'192.0.2.2';LOCK TABLES spider_sharded_sales.invoices READ;$ mariadb-dump \
--user=mariadb-dump \
--password='md_passwd' \
--ssl-verify-server-cert \
--ssl-ca ~/mariadb_chain.pem \
--all-databases \
--single-transaction \
--default-character-set=utf8mb4 \
> mariadb_dump.sql$ mariadb-dump \
--user=mariadb-dump \
--password='md_passwd' \
--ssl-verify-server-cert \
--ssl-ca ~/mariadb_chain.pem \
--all-databases \
--single-transaction \
--default-character-set=utf8mb4 \
> mariadb_dump.sqlUNLOCK TABLES;$ mariadb \
--user=mariadb-dump \
--password='md_passwd' \
--ssl-verify-server-cert \
--ssl-ca ~/mariadb_chain.pem \
--default-character-set=utf8mb4 \
< mariadb_dump.sql$ mariadb \
--user=mariadb-dump \
--password='md_passwd' \
--ssl-verify-server-cert \
--ssl-ca ~/mariadb_chain.pem \
--default-character-set=utf8mb4 \
< mariadb_dump.sqlSELECT * FROM spider_sharded_sales.invoices;+-----------+------------+-------------+----------------------------+---------------+----------------+
| branch_id | invoice_id | customer_id | invoice_date | invoice_total | payment_method |
+-----------+------------+-------------+----------------------------+---------------+----------------+
| 1 | 1 | 1 | 2020-05-10 12:35:10.000000 | 1087.23 | CREDIT_CARD |
| 1 | 2 | 2 | 2020-05-10 14:17:32.000000 | 1508.57 | WIRE_TRANSFER |
| 1 | 3 | 3 | 2020-05-10 14:25:16.000000 | 227.15 | CASH |
| 2 | 1 | 2 | 2020-05-10 12:31:00.000000 | 1351.04 | CREDIT_CARD |
| 2 | 2 | 2 | 2020-05-10 12:45:27.000000 | 162.11 | WIRE_TRANSFER |
| 2 | 3 | 4 | 2020-05-10 13:11:23.000000 | 350.00 | CASH |
| 3 | 1 | 5 | 2020-05-10 12:31:00.000000 | 111.50 | CREDIT_CARD |
| 3 | 2 | 8 | 2020-05-10 12:45:27.000000 | 1509.23 | WIRE_TRANSFER |
| 3 | 3 | 3 | 2020-05-10 13:11:23.000000 | 3301.66 | CASH |
+-----------+------------+-------------+----------------------------+---------------+----------------+