MariaDB starting with 10.0.4
The Spider storage engine was first released in MariaDB 10.0.4.
The Spider storage engine supports partitioning and XA transactions, and allows tables of different MariaDB instances to be handled as if they were on the same instance.
To use Spider, you need two or more instances of MariaDB, typically running on separate hosts. The Spider node is the MariaDB server that receives queries from your application. It then processes these queries, connecting to one or more data nodes. The data nodes are the MariaDB servers that actually store the table data.
In order for this to work, you need to configure the data nodes to accept queries from the Spider node and you need to configure the Spider node to use the data nodes as remote storage.
You don't need to install any additional packages to use it, but it does require some configuration.
Configuring Data Nodes
Spider deployments use data nodes to store the actual table data. In order for a MariaDB server to operate as a data node for Spider, you need to create a table or tables on which to store the data and configure the server to accept client connections from the Spider node.
For instance, first create the table:
CREATE TABLE test.spider_example ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) ) ENGINE=InnoDB;
Next, create a user for the Spider node and set a password for that user. For the sake of the example, assume the Spider node is at the IP address 192.168.1.1:
CREATE USER firstname.lastname@example.org; SET PASSWORD FOR email@example.com = PASSWORD('passwd');
Then grant the
spider user privileges on the example table.
GRANT ALL ON test.spider_example TO firstname.lastname@example.org;
The data node is now ready for use. You can test it by attempting to connect the MariaDB client to the data from the Spider node. For instance, assuming the data node is at the IP address 192.168.1.5, SSH into the Spider node then try to establish a client connection.
$ mysql -u spider -p -h 192.168.1.5 test -e "SHOW TABLES;" +----------------+ | Tables_in_test | +----------------+ | spider_example | +----------------+
Configuring Spider Nodes
With the data node or data nodes configured, you can set up the Spider node to use them. The Spider node is the MariaDB server that receives queries for the table, (in this case
test.spider_example). It then uses the Spider storage engine to connect to the tables on the data nodes to retrieve data and return the result-set.
In order to use Spider, you need to run the configuration script on the Spider node. You can check whether it has been installed already by querying the Information Schema.
SELECT ENGINE, SUPPORT FROM information_schema.ENGINES; +--------------------+---------+ | ENGINE | SUPPORT | +--------------------+---------+ | CSV | YES | | MyISAM | YES | | BLACKHOLE | YES | | FEDERATED | YES | | MRG_MyISAM | YES | | ARCHIVE | YES | | MEMORY | YES | | PERFORMANCE_SCHEMA | YES | | Aria | YES | | InnoDB | DEFAULT | +--------------------+---------+
If Spider does not appear in this list, you need to run the configuration script. This is installed with the MariaDB server in CentOS. Debian distributions need to install the
mariadb-plugin-spider package first. The package is usually found in
Note: If you're running a source code distribution of MariaDB, (that is, where you compiled it from source), the script is located in the build directory at
To confirm that it has been installed, query the Information Schema again,
SELECT ENGINE, SUPPORT FROM information_schema.ENGINES WHERE ENGINE = 'SPIDER'; +--------------------+---------+ | ENGINE | SUPPORT | +--------------------+---------+ | SPIDER | YES | +--------------------+---------+
Running this configuration script also creates a series of new tables in the
mysql database, including:
It also upgrades the format of the tables if upgrading from an earlier version.
Configure the Server
In order to connect the Spider node to the data nodes, you need to issue a
CREATE SERVER statement for each data node. You can then use the server definition in creating the Spider table.
CREATE SERVER dataNode1 FOREIGN DATA WRAPPER mysql OPTIONS ( HOST '192.168.1.5', DATABASE 'test', USER 'spider' PASSWORD 'passwd', PORT 3306);
In the event that you need to modify or replace this server after setting up the Spider table, remember to issue a
FLUSH statement to update the server definition.
Create the Table
With the data nodes set up and the Spider node configured for use, you can create the Spider table. The Spider table must have the same column definitions as the InnoDB tables on the data nodes. Spider is configured through table system variables passed to the
CREATE TABLE test.spider_example ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) ) ENGINE=Spider COMMENT='wrapper "mysql", srv "dataNode1", table "spider_example"';
This configures Spider to use the server
dataNode1, (defined above), as a remote table. Any data you write to this table is actually stored on the MariaDB server at 192.168.1.5.