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.
An overview of the federated topology for Spider, where a single Spider node aggregates data from multiple remote data nodes, acting as a unified access point.
Choose an operation for the Sharded MariaDB Enterprise Spider topology:
How to create a new backup or restore an existing backup.
The character set or collation for the Spider Table can be updated or modified using the statement.
On the Spider Node, alter the Spider Table's character set and collation:
If the new character set and collation are not compatible with the character set and collation used by the Data Table, you must also alter the character set and collation for the Data Table on the Data Node.
In a Federated MariaDB Enterprise Spider topology, the connection options for a Data Node can be updated using the statement.
On the Spider Node*, alter the table's connection details:
How to create a new backup or restore an existing backup.
How to update the character set or collation for a Spider Table.
How to update the connection options for a Data Node.
The character set or collation for the Spider Table can be updated or modified using the ALTER TABLE statement.
On the Spider Node, alter the Spider Table's character set and collation:
If the new character set and collation are not compatible with the character set and collation used by the Data Table, you must also alter the character set and collation for the Data Table on the Data Node.
In a Sharded MariaDB Enterprise Spider topology, the connection options for a Data Node can be updated using the ALTER TABLE statement.
On the Spider Node, alter the partition's connection details:
How to add a new shard to a Spider Table.
How to update the character set or collation.
How to update the character set or collation.
How to update the connection options for a Data Node.
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"'
);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.
This page is: Copyright © 2025 MariaDB. All rights reserved.
This page is: Copyright © 2025 MariaDB. All rights reserved.
A guide on how to migrate tables from a standard MariaDB deployment to a Federated Spider topology, distributing data across multiple backend nodes.
The Federated MariaDB Enterprise Spider topology can be used to migrate tables from one MariaDB Enterprise Server node to another MariaDB Enterprise Server node:
The MariaDB Enterprise Server node with the source table is configured as a Data Node.
The MariaDB Enterprise Server node with the destination table is configured as a Spider Node.
The Data Table is the source table on the Data Node.
A Spider Table is created on the Spider Node that references the Data Table on the Data Node.
On the Spider node, the Data Table's data is migrated to the destination table by querying the Spider Table like the following:
Follow the steps below to migrate tables using the Federated MariaDB Enterprise Spider topology.
Before you can migrate tables, the Federated MariaDB Enterprise Spider topology must be deployed.
For additional information, see "".
A local copy of the table must be created. This new table will contain the migrated data.
On the Spider Node*, create a local copy of each table that is being migrated:
The table data can be migrated to the local table using the Spider Tables.
On the Spider Node*, migrate the table data to the local copy of the table using the statement:
On the Spider Node, read from the local copy of the table using a statement to confirm that the data has been migrated:
This page is: Copyright © 2025 MariaDB. All rights reserved.
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 |
+-----------+------------+-------------+----------------------------+---------------+----------------+This guide details how to configure Spider to connect to an Oracle database via ODBC, enabling data migration and federated access to Oracle tables.
This article describes how to use Spider ODBC to connect to Oracle. This can make it easier to migrate, by not requiring a wholesale cutover, but instead enabling a piecemeal approach.
The setup looks as shown in this figure:
In MariaDB,
The Spider storage engine must be installed.
The Spider storage engine plugin must be installed in MariaDB Enterprise Server. This is described in the instructions below.
An ODBC driver manager, unixODBC, must be installed.
In Oracle Database,
The ODBC driver must be installed.
The Oracle Basic Client must be installed. This is a prerequisite for installing the ODBC driver.
In your working directory, create a folder to hold the Oracle ODBC RPMs mkdir oracle_odbc_rpms, then change to that directory.
Download the following files (make sure to use the appropriate driver for your scenario):
Install the RPMs:
Spider ODBC to Oracle tables does not support INSERT SELECT statements, where the Spider table is the source of the data. (MENT-1588).
If the version of MariaDB does not have the variable, you aren't able to use a few aggregate functions on their own. (MENT-1558).
If you get the following error message: Error from ODBC 0 01004 [Oracle][ODBC]String data, right truncated., you need to set a larger value for the variable . (MENT-1557).
Blog post by Anders Karlsson, January 2024 - 13 minute read
This page is: Copyright © 2025 MariaDB. All rights reserved.
Instructions on how to expand a sharded Spider topology by adding new data nodes (shards) and rebalancing the data distribution.
Add the following to /etc/odbcinst.ini:
The driver path may be different if you downloaded a different version of the Oracle driver, be sure to update it with correct path for your scenario.
Add the following to /etc/odbc.ini:
Driver is the driver name in the /etc/odbcinst.ini .
Description can be whatever you like, just make sure you use the same value later.
ServerName is the TNSName given for our Oracle connection.
Be sure to populate your user name and password.
Add the following line to /etc/tnsnames.ora:
Name the connection string as you like — just be sure to use the same name in the odbc.ini file.
Make sure to include your hostname and IP address, and the service name for your instance.
Verify that the ODBC connection is working with:
ORARDS is the section name we gave our entry in odbc.ini. If you are using something different, replace ORARDS with whatever you use.
If any errors come up, double-check the values entered into the .ini files.
Start the MariaDB database:
Log in to MariaDB, and run the following:
This creates a new database and a table that should be able to connect to an Oracle server via ODBC.
DSN must be the same put in the odbc.ini file .
It's best to always use upper case for both column names and the table value in the comment section, because these values are passed directly to Oracle Database, which prefers upper-case object names.
You should now be able to select data from spider_test.contacts table. That data is coming from the remote Oracle database.
On the Data Node hosting the new shard, create the Spider user account for the Spider Node using the CREATE USER statement:
Privileges are granted to the user account in a later step.
On the Spider Node, confirm that the Spider user account can connect to the Data Node using MariaDB Client:
The Spider Node requires connection details for each Data Node.
On the Spider Node, create a server object to configure the connection details for the Data Node hosting the new shard using the CREATE SERVER statement:
The Data Node runs MariaDB Enterprise Server, so the FOREIGN DATA WRAPPER is set to mariadb.
Using a server object for connection details is optional. Alternatively, the connection details for the Data Node can be specified in the COMMENT table option of the CREATE TABLE statement when creating the Spider Table.
When queries read and write to a Spider Table, Spider reads and writes to the Data Tables for each partition on the Data Nodes. The Data Tables must be created on the Data Nodes with the same structure as the Spider Table.
If your Data Tables already exist, grant privileges on the tables to the Spider user.
On the Data Node hosting the new shard, create the Data Tables:
The Spider Node reads and writes to the Data Table using the server and user account configured previously. The user account must have privileges on the table.
The Spider Node connects to the Data Nodes with the user account configured previously.
On the Data Node hosting the new shard, grant the Spider user sufficient privileges to operate on the Data Table:
By default, the Spider user also requires the CREATE TEMPORARY TABLES privilege on the database containing the Data Table. The CREATE TEMPORARY TABLES privilege is required, because Spider uses temporary tables to optimize read queries when Spider BKA Mode is 1.
Spider BKA Mode is configured using the following methods:
The session value is configured by setting the Spider BKA Mode system variable on the Spider Node. The default value is -1. When the session value is -1, the value for each Spider Table is used.
The value for each Spider Table is configured by setting the bka_mode option in the COMMENT table option. When the bka_mode option is not set, the implicit value is 1.
The default spider_bka_mode value is -1, and the implicit Spider Table value is 1, so the default Spider BKA Mode is 1.
On the Data Node hosting the new shard, grant the Spider user the CREATE TEMPORARY TABLES privilege on the database:
A partition for the new shard must be added to the Spider Table on the Spider Node.
On the Spider Node, alter the Spider Table to add the partition and reference the name of the Data Node hosting the new shard in the COMMENT partition option:
On the Spider Node, read from the Spider Table using a SELECT statement:
This page is: Copyright © 2025 MariaDB. All rights reserved.
[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 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 |
+-----------+------------+-------------+----------------------------+---------------+----------------+Procedures for performing consistent backups and restores in a federated Spider topology using MariaDB Backup and MariaDB Dump, ensuring data synchronization.
When using Spider in the Federated MariaDB Enterprise Spider topology, backup and restore operations can be performed using MariaDB Backup or MariaDB Dump. These operations must be performed on the Spider Node and the Data Node.
MariaDB Backup can be used to perform backup operations on Spider deployments.
MariaDB Backup can be used to create a physical backup of a Federated MariaDB Enterprise Spider topology. MariaDB Backup must be used to backup the Spider Node and the Data Node. The Spider Node and Data Node must be locked to guarantee that the backups of the Spider Node and Data Node are consistent.
The backup of the Spider Node contains:
Physical backup for Spider Tables
Physical backup for tables that use InnoDB, Aria, MyISAM, and MyRocks
Physical backup for any other database objects on the Spider Node
The backup of the Data Node contains:
Physical backup for Data Tables
Physical backup for tables that use InnoDB, Aria, MyISAM, and MyRocks
Physical backup for any other database objects on the Data Node
The following procedure shows how to take a consistent backup of a Spider Node and a Data Node deployed in a Spider Federated MariaDB Enterprise Spider topology.
On the Spider Node and on the Data Node, create a user account to perform the backup using the and statements:
On the Data Node, grant the user account Spider uses to operate on the Data Node sufficient privileges to lock the Data Tables using the statement.
For example, on the hq_server Data Node:
On the Spider Node, acquire a read lock on Spider Tables using the statement:
Keep this session open during the rest of the procedure.
The read lock will propagate to the Data Tables on the Data Node. The read locks will prevent the Data Tables from changing during the backup, so the backups of the Spider Node and the Data Node are consistent.
On the Data Node, perform the backup using MariaDB Backup:
When the Data Node backup is complete, perform the backup on the Spider Node using MariaDB Backup:
When the Spider Node backup is complete, release the table locks in your original session using the statement:
On the Spider Node and the Data Node, prepare each of the backups using MariaDB Backup:
The Spider Node and the Data Node now have a complete backup of the data directory. Backups should be tested to confirm they are complete and consistent.
MariaDB Backup can restore a Federated MariaDB Enterprise Spider topology from a backup taken with MariaDB Backup.
On the Spider Node and on the Data Node, stop the MariaDB Server process:
On the Spider Node and on the Data Node, empty the data directory:
On the Spider Node and on the Data Node, restore the backup for that server using MariaDB Backup:
On the Spider Node and on the Data Node, confirm that the restored files are owned by the user that owns the MariaDB Server process:
On the Spider Node and on the Data Node, start the MariaDB Server process:
On the Spider Node, query a Spider Table to test it:
generates backup files containing the SQL statements necessary to recreate the database. is included with MariaDB Enterprise Server and can be used to backup databases in Spider deployments. The MariaDB Client can then be used to restore databases from a .
MariaDB Dump can be used to create a logical backup of a Federated MariaDB Enterprise Spider topology. MariaDB Dump must be used to backup the Spider Node and the Data Node. The Spider Node and Data Node must be locked to guarantee that the backups of the Spider Node and Data Node are consistent.
The backup of the Spider Node contains:
Table definitions for Spider Tables
Table definitions for all tables
Table data for tables that use InnoDB, Aria, MyISAM, and MyRocks
Definitions for any other user accounts, privileges, views, and triggers on the Spider Node
The backup of the Data Node contains:
Table definitions for Data Tables
Table definitions for all tables
Table data for tables that use InnoDB, Aria, MyISAM, and MyRocks
Definitions for any other user accounts, privileges, views, and triggers on the Data Node
The following procedure shows how to take a consistent backup of a Spider Node and a Data Node deployed in a Federated MariaDB Enterprise Spider topology.
On the Spider Node and on the Data Node, create a user account to perform the backup using the and statements:
On the Data Node, grant the user account Spider uses to operate on the Data Node sufficient privileges to lock the Data Tables using the GRANT statement.
For example, on the hq_server Data Node:
On the Spider Node, acquire a read lock on Spider Tables using the LOCK TABLES statement:
Keep this session open during the rest of the procedure.
The read lock will propagate to the Data Tables on the Data Node. The read locks will prevent the Data Tables from receiving any changes during the backup, which ensures the backups of the Spider Node and the Data Node are consistent.
On the Data Node, perform the backup using MariaDB Dump:
When the Data Node has been backed up, perform the backup on the Spider Node using MariaDB Dump:
When the Spider Node backup is complete, release the table locks in your original session using the UNLOCK TABLES statement:
The Spider Node and the Data Node now each have a mariadb_dump.sql backup file. This backup file contains the SQL statements needed to recreate the schema on the Spider Node and the schema and data on the Data Node. Backups should be tested to confirm they are complete and consistent.
MariaDB Client can restore a Federated MariaDB Enterprise Spider topology from a backup taken with MariaDB Dump backup.
Stop all traffic to the Spider Node and the Data Node.
On the Spider Node, restore the backup for that server using MariaDB Client:
On the Data Node, restore the backup for that server using MariaDB Client:
On the Spider Node, query a Spider Table to test it:
This page is: Copyright © 2025 MariaDB. All rights reserved.
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';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.
When using Spider in the Sharded MariaDB Enterprise Spider topology, backup and restore operations can be performed using MariaDB Backup or MariaDB Dump. These operations must be performed on the Spider Node as well as on each Data Node.
MariaDB Backup can be used to perform backup operations on Spider deployments.
can be used to create a physical backup of a Sharded MariaDB Enterprise Spider topology. MariaDB Backup must be used to backup the Spider Node and all Data Nodes. The Spider Node and Data Nodes must be locked to guarantee that the backups of the Spider Node and Data Nodes are consistent.
The backup of the Spider Node contains:
Physical backup for Spider Tables
Physical backup for tables that use InnoDB, Aria, MyISAM, and MyRocks
Physical backup for any other database objects on the Spider Node
The backups of the Data Nodes contain:
Physical backup for Data Tables
Physical backup for tables that use InnoDB, Aria, MyISAM, and MyRocks
Physical backup for any other database objects on the Data Node
The following procedure shows how to take a consistent backup of a Spider Node and Data Nodes deployed in a Sharded MariaDB Enterprise Spider topology.
On the Spider Node and on each Data Node, create a user account to perform the backup using the and statements.
For MariaDB Enterprise Server 10.5 and later:
On each Data Node, grant the user account Spider uses when operating on Data Nodes sufficient privileges to lock any Data Tables using the statement.
For example, on the hq_server Data Node:
On the eastern_server Data Node:
On the western_server Data Node:
On the Spider Node, acquire a read lock on Spider Tables using the LOCK TABLES statement:
Keep this session open during the rest of the procedure.
The read lock will propagate to the Data Tables on each Data Node as well. The read locks will prevent the Data Tables from changing during the backup, so the backups on the Spider Node and Data Nodes are consistent.
On each Data Node, perform the backup using .
With MariaDB Backup 10.5 and later, use the mariadb-backup command:
On the Spider Node, after backing up each Data Node, perform a backup with .
With MariaDB Backup 10.5 and later, use the mariadb-backup command:
On the Spider Node, after the backup is complete, in your original session, use the statement to release the table locks:
On the Spider Node and each of the Data Nodes, prepare each of the backups using MariaDB Backup.
With MariaDB Backup 10.5 and later, use the mariadb-backup command:
The Spider Node and Data Nodes now each have a complete backup of the data directory. Backups should be tested to confirm they are complete and consistent.
can restore a Sharded MariaDB Enterprise Spider topology from a backup taken with MariaDB Backup.
On the Spider Node and on each Data Node, stop the MariaDB Server process:
On the Spider Node and on each Data Node, empty the data directory:
On the Spider Node and on each Data Node, restore the backup for that server using MariaDB Backup.
With MariaDB Backup 10.5 and later, use the mariadb-backup command:
On the Spider Node and on each Data Node, confirm that the restored files are owned by the user that owns the MariaDB Server process:
On the Spider Node and on each Data Node, start the MariaDB Server process:
On the Spider Node, query a Spider Table to test it:
generates backup files containing the SQL statements necessary to recreate the database. MariaDB Dump is included with MariaDB Server and can be used to backup databases in Spider deployments. The MariaDB Client can then be used to restore databases from a MariaDB Dump backup.
can be used to create a logical backup of a Sharded MariaDB Enterprise Spider topology. MariaDB Dump must be used to backup the Spider Node and all Data Nodes. The Spider Node and Data Nodes must be locked to guarantee that the backups of the Spider Node and Data Nodes are consistent.
The backup of the Spider Node contains:
Table definitions for Spider Tables
Table definitions for all tables
Table data for tables that use InnoDB, Aria, MyISAM, and MyRocks
Definitions for any other user accounts, privileges, views, and triggers on the Spider Node
The backups of the Data Nodes contain:
Table definitions for Data Tables
Table definitions for all tables
Table data for tables that use InnoDB, Aria, MyISAM, and MyRocks
Definitions for any other user accounts, privileges, views, and triggers on the Data Node
The following procedure shows how to take a consistent backup of a Spider Node and Data Nodes deployed in a Sharded MariaDB Enterprise Spider topology.
On the Spider Node and on each Data Node, create a user account to perform the backup using the and statements:
On each Data Node, grant the user account Spider uses to operate on the Data Nodes sufficient privileges to lock any Data Tables using the GRANT statement.
For example, on the hq_server Data Node:
On the eastern_server Data Node:
On the western_server Data Node:
On the Spider Node, acquire a read lock on Spider Tables using the statement:
Keep this session open during the rest of the procedure.
The read lock will propagate to the Data Tables on each Data Node as well. The read locks will prevent the Data Tables from changing during the backup, so the backups of the Spider Node and Data Nodes are consistent.
On each Data Node, perform the backup using MariaDB Dump. With MariaDB Dump 10.5 and later, use the mariadb-dump command:
On the Spider Node, once the Data Nodes are backed up, perform a backup using MariaDB Dump.
With MariaDB Dump 10.5 and later, use the mariadb-dump command:
On the Spider Node, after the backups are complete, in your original session use the statement to release the table locks:
The Spider Node and the Data Node now each have a mariadb_dump.sql backup file. This backup file contains the SQL statements needed to recreate the schema on the Spider Node and the schema and data on the Data Nodes. Backups should be tested to confirm they are complete and consistent.
MariaDB Client can restore a Sharded MariaDB Enterprise Spider topology from a backup taken with backup.
Stop all traffic to the Spider Node and each Data Node.
On the Spider Node, restore the backup for that server using MariaDB Client.
With MariaDB Client 10.5 and later, use the mariadb command:
On the Data Node, restore the backup for that server using MariaDB Client.
With MariaDB Client 10.5 and later, use the mariadb command:
On the Spider Node, query a Spider Table to test it:
This page is: Copyright © 2025 MariaDB. All rights reserved.
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 |
+-----------+------------+-------------+----------------------------+---------------+----------------+