In a previous post, I wrote an overview about Spider for MySQL with its advantages and disadvantages. Now I’ll go through a simple example demonstrating how to implement Spider for MySQL.

System information: MySQL instances information (shards):

• Server: 3 VMs (here in after will be mentioned as mysqla “192.168.56.51”, mysqlb “192.168.56.52” and mysqlc “192.168.56.53”).
• OS: CentOS 6.4
• RAM: 512MB
• MySQL version: 5.5.43 (tar-balls)
• Download link: http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.43-linux2.6-x86_64.tar.gz

Spider instance information:

• Server: VM (here in after will be mentioned as spider_node “192.168.56.50”).
• OS: CentOS 6.4
• RAM: 512MB
• MySQL version: 5.5.34 (tar-ball)
• Download link: http://spiderformysql.com/downloads/spider-3.2/mysql-5.5.34-spider-3.2-vp-1.1-hs-1.2-q4m-0.95-linux-x86_64-glibc25.tgz

Note:

More information on how to install tar-ball binaries can be checked out here.

Testing steps:

1. Install MySQL server (Oracle binaries) on the instances mysqla, mysqlb and mysqlc.
2. Install MySQL server (Spider binaries) on the spider_node.
3. Load the spider plugin on the spider_node by the following SQL command:
   shell> mysql < /$mysql_basedir/share/install_spider.sql"
4. Check if the spider SE is now available or not:
   SQL> SHOW ENGINES;
+--------------------+---------+---------------------------------------------
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+---------------------------------------------
| SPIDER | YES | Spider storage engine | YES | YES | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+---------------------------------------------
5. Create database called “spider_db” on the 4 MySQL instances (shards and spider):
   SQL> CREATE DATABASE spider_db;
6. Create a testing table called – let’s say – sp_test in the spider_db database to store 1 million records in each shard as follows:
   On mysqla:
   SQL> CREATE TABLE spider_db.sp_test
(id INT PRIMARY KEY,name CHAR(5) DEFAULT 'MySQL')
PARTITION BY RANGE (id)
(
PARTITION p0 VALUES LESS THAN (200000),
PARTITION p1 VALUES LESS THAN(400000),
PARTITION p2 VALUES LESS THAN(600000),
PARTITION p3 VALUES LESS THAN(800000),
PARTITION p4 VALUES LESS THAN(MAXVALUE)
);
   On mysqlb:
   SQL> CREATE TABLE spider_db.sp_test
(id INT PRIMARY KEY,name CHAR(5) DEFAULT 'MySQL')
PARTITION BY RANGE (id)
(
PARTITION p0 VALUES LESS THAN (1200000),
PARTITION p1 VALUES LESS THAN(1400000),
PARTITION p2 VALUES LESS THAN(1600000),
PARTITION p3 VALUES LESS THAN(1800000),
PARTITION p4 VALUES LESS THAN(MAXVALUE)
);
   On mysqlc:
   SQL> CREATE TABLE spider_db.sp_test
(id INT PRIMARY KEY,name CHAR(5) DEFAULT 'MySQL')
PARTITION BY RANGE (id)
(
PARTITION p0 VALUES LESS THAN (2200000),
PARTITION p1 VALUES LESS THAN(2400000),
PARTITION p2 VALUES LESS THAN(2600000),
PARTITION p3 VALUES LESS THAN(2800000),
PARTITION p4 VALUES LESS THAN(MAXVALUE)
);
7. Create a MySQL user to be used by the spider storage engine on the shards:
   SQL> GRANT ALL ON *.* TO 'sp_user'@'192.168.56.50' IDENTIFIED BY 'T3$T';
8. Create the same table on the spider_node using the Spider SE to access the 3 million rows from the shards as follows:
   SQL> CREATE TABLE spider_db.sp_test
(id INT PRIMARY KEY,name CHAR(5) DEFAULT 'MySQL')
ENGINE=Spider
connection 'table"sp_test",database"spider_db",user"sp_user",password"T3$T",port"3306"'
PARTITION BY RANGE (id)
(
PARTITION p0 VALUES LESS THAN(1000000)COMMENT 'host "192.168.56.51"',
PARTITION p1 VALUES LESS THAN(2000000)COMMENT 'host "192.168.56.52"',
PARTITION p2 VALUES LESS THAN(MAXVALUE)COMMENT 'host "192.168.56.53"'
);

Now, you can manage the tables (select, insert, update, … etc) on the shards through the spider node.
Give it a try and have fun with Spider!

Note:

Although some companies use Spider for MySQL in production systems but – at the time of writing this post – it is NOT production ready yet.