spider ha 20100922(dtt#7)
DESCRIPTION
Introducing HA feature of SpiderTRANSCRIPT
HA feature of Spider
Kentoku SHIBAkentokushiba at gmail dot com
Basic structure
Basic structure
Spider's HA structure is composed ofdata nodes, spider nodes and monitoring nodes.
DB11tbl_a
DB12tbl_a
DB13tbl_a
DB7DB6DB21tbl_a
Data nodes
Monitoring nodes
DB7DB6
DB1tbl_a
Spider nodes
The part of structure
- Data nodesData nodes store real data.Each data node that linked same Spider table store same data.(like RAID 1)
- Spider nodesThe entrance of user access.Spider nodes offer load balance, fail over and transparently access todata node.
- Monitoring nodesMonitoring nodes monitor data nodes.
Monitoring parameters- monitoring_bg_interval(mbi)
Interval of background monitoring.(microsecond)The default value is 10000000
- monitoring_bg_kind(mbk)The kind of background monitoring.0 : It doesn't monitor.1 : It monitors state of connection.2 : It monitors state of a table without where clause.
The default value is 0
Monitoring parameters- monitoring_kind(mkd)
The kind of monitoring.0 : It doesn't monitor.1 : It monitors state of connection.2 : It monitors state of a table without where clause.
The default value is 0
- monitoring_limit(mlt)The limititation of records at monitoring table.(Only when monitoring_kind is bigger than 1, it is effective.)0 or more : Number of limitation.The default value is 1
- monitoring_server_id(msi)The server_id of monitoring MySQL server.The default value is self server_id
How to set up HAwithout stopping the service
How to set up HA
Spider can set up HA by each table.
Here, I will explain how to set up HA,without stopping service.
Initial Structure
There are 1 MySQL server with Spider and 1 remote MySQL server without Spider.
DB2
DB1tbl_a
tbl_a
Create table tbl_a (col_a int,col_b int,primary key(col_a)
) engine = SpiderConnection ‘table “tbl_a”,user “user”,password “pass”,host “DB2”‘;
Create table tbl_a (col_a int,col_b int,primary key(col_a)
) engine = InnoDB;
DB1
Step 1
Add new data nodes(DB3 and DB4) and tables.
DB2
tbl_a
tbl_aDB3tbl_a
DB4tbl_a
Create table tbl_a (col_a int,col_b int,primary key(col_a)
) engine = InnoDB;
DB1
Step 2
Add new monitoring nodes(DB5, DB6, DB7) and tables.
DB2
tbl_a
tbl_aDB3tbl_a
DB4tbl_a
DB7DB6
DB5tbl_a
Create table tbl_a (col_a int,col_b int,primary key(col_a)
) engine = SpiderConnection ‘table “tbl_a”,user “user”,password “pass”,host “DB2 DB3 DB4”‘;
DB1
Step 3
Register monitornig node information toMySQL servers with Spider.
Then alter table on DB1.
DB2
tbl_a
tbl_aDB3tbl_a
DB4tbl_a
DB7DB6
DB5tbl_a
insert into mysql.spider_link_mon_servers(db_name, table_name, link_id, sid, server, scheme, host, port, socket, username, password)values('db_name', 'tbl_a', 0, DB5_sid, null, 'mysql', 'DB5', 3306, null, 'user', 'pass‘),('db_name', 'tbl_a', 0, DB6_sid, null, 'mysql', 'DB6', 3306, null, 'user', 'pass‘),('db_name', 'tbl_a', 0, DB7_sid, null, 'mysql', 'DB7', 3306, null, 'user', 'pass‘);
Alter table tbl_aConnection ‘table “tbl_a”,user “user”,password “pass”,host “DB2 DB3 DB4”,mbk “2”, mkd “2”,msi “DB5_sid”,link_status “0 2 2”‘;
DB7DB6
DB5tbl_aDB1
Select spider_copy_tables(‘tbl_a’, ‘’, ‘’);
Step 4
Copy data from DB2 to DB3 and DB4.
DB2
tbl_a
tbl_aDB3tbl_a
DB4tbl_a
DB1
Alter table tbl_aConnection ‘table “tbl_a”,user “user”,password “pass”,host “DB2 DB3 DB4”,mbk “2”, mkd “2”,msi “DB5_sid”,link_status “0 1 1”‘;
Finish
Alter table on DB1.
DB2
tbl_a
tbl_aDB3tbl_a
DB4tbl_a
DB7DB6
DB5tbl_a
How to add new nodeafter failoverand preparing new server
without stopping the service
Create a table of a new node to the clustered table
You need to create a new node, in order tomaintain redundancy, when there is a troubleat the node that composes the cluster.
Here, I will explain how to add a table of a new node, without stopping the service.
DB1
Initial Structure
There are 4 MySQL servers with Spider(include 3 monitoring nodes) and
3 MySQL servers without Spider (including 1 broken node).
DB2
tbl_a
tbl_aDB3tbl_a
DB4tbl_a
DB7DB6
DB5tbl_a
DB1
Step 1
Add new data node(DB8) and table.
DB2
tbl_a
tbl_aDB3tbl_a
DB4tbl_a
DB7DB6
DB5tbl_a
DB8tbl_a
Create table tbl_a (col_a int,col_b int,primary key(col_a)
) engine = InnoDB;
DB1
Step 2
Alter table on monitoring nodes(DB5, DB6 and DB7).
DB2
tbl_a
tbl_aDB3tbl_a
DB4tbl_a
DB7DB6
DB5tbl_a
DB8tbl_a
Alter table tbl_aConnection ‘table “tbl_a”,user “user”,password “pass”,host “DB2 DB4 DB8”‘;
DB1
Step 3
Alter table on DB1.
DB2
tbl_a
tbl_aDB3tbl_a
DB4tbl_a
DB7DB6
DB5tbl_a
DB8tbl_a
Alter table tbl_aConnection ‘table “tbl_a”,user “user”,password “pass”,host “DB2 DB4 DB8”,mbk “2”, mkd “2”,msi “DB5_sid”,link_status “0 0 2”‘;
DB1
Step 4
Copy data from DB2 to DB8.
DB2
tbl_a
tbl_aDB3tbl_a
DB4tbl_a
DB7DB6
DB5tbl_a
DB8tbl_a
Select spider_copy_tables(‘tbl_a’, ‘’, ‘’);
DB1
Finish
Alter table on DB1.
DB2
tbl_a
tbl_aDB3tbl_a
DB4tbl_a
DB7DB6
DB5tbl_a
DB8tbl_a
Alter table tbl_aConnection ‘table “tbl_a”,user “user”,password “pass”,host “DB2 DB4 DB8”,mbk “2”, mkd “2”,msi “DB5_sid”,link_status “0 0 1”‘;
http://wild-growth.blogspot.com/http://spiderformysql.com
Kentoku SHIBA (kentokushiba at gmail dot com)
Any Questions?
Thank you for taking
your time!!