1. 计划:
三个虚拟服务器,其中一个主控/对外接口服务器(spX),其余两个数据节点服务器 (dbX),每个dbX有两个数据库prj_mdbX,实现分库分表部署。
2. 服务器设置:
- 安装MariaDB 10.1.29
- 设置:
Spider节点服务器1: CentOS01 (192.168.1.230), 代号:sp1
MariaDB 数据节点服务器1: CentOS02 (192.168.1.231), 代号:db1
MariaDB 数据节点服务器2: CentOS03 (192.168.1.231), 代号:db2
MariaDB 数据节点服务器3: CentOS05 (192.168.1.232), 代号:db3
MariaDB 数据节点服务器4: CentOS06 (192.168.1.232), 代号:db4
2.1 sp1设置:
# 建立数据库, 关联servers
CREATE DATABASE IF NOT EXISTS zdt_apollopre_mdb default charset utf8 COLLATE utf8_general_ci;
CREATE SERVER db1 FOREIGN DATA WRAPPER mysql OPTIONS( HOST '192.168.1.231', DATABASE 'zdt_apollopre_mdb1',USER 'sp_user', PASSWORD 'sp_user',PORT 3306);
CREATE SERVER db2 FOREIGN DATA WRAPPER mysql OPTIONS( HOST '192.168.1.231', DATABASE 'zdt_apollopre_mdb2',USER 'sp_user', PASSWORD 'sp_user',PORT 3306);
CREATE SERVER db3 FOREIGN DATA WRAPPER mysql OPTIONS( HOST '192.168.1.232', DATABASE 'zdt_apollopre_mdb1',USER 'sp_user', PASSWORD 'sp_user',PORT 3306);
CREATE SERVER db4 FOREIGN DATA WRAPPER mysql OPTIONS( HOST '192.168.1.232', DATABASE 'zdt_apollopre_mdb2',USER 'sp_user', PASSWORD 'sp_user',PORT 3306);
DROP TABLE IF EXISTS `c2_attribute`;
CREATE TABLE `c2_attribute` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`eshop_id` bigint(20) DEFAULT 1,
`type` tinyint(4) DEFAULT 0,
`code` varchar(255) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`label` varchar(255) NOT NULL,
`hint` varchar(255) DEFAULT NULL,
`input_type` varchar(255) DEFAULT NULL,
`default_value` text DEFAULT NULL,
`memo` text DEFAULT NULL,
`is_sku` tinyint(4) DEFAULT 0,
`is_visible` tinyint(4) DEFAULT 1,
`is_required` tinyint(4) DEFAULT 0,
`is_unique` tinyint(4) DEFAULT 0,
`is_depend` tinyint(4) DEFAULT 0,
`depend_id` bigint(20) DEFAULT 0,
`status` tinyint(4) DEFAULT 1,
`position` int(11) DEFAULT 0,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `Index_1` (`code`),
KEY `Index_2` (`type`),
KEY `Index_3` (`position`)
) ENGINE=spider COMMENT='wrapper "mysql", table "c2_attribute"'
PARTITION BY KEY (id)
(
PARTITION pt1 COMMENT = 'srv "db1"',
PARTITION pt2 COMMENT = 'srv "db2"',
PARTITION pt3 COMMENT = 'srv "db3"',
PARTITION pt4 COMMENT = 'srv "db4"'
);
2.2 数据节点设置
CREATE DATABASE IF NOT EXISTS zdt_apollopre_mdb default charset utf8 COLLATE utf8_general_ci;
DROP TABLE IF EXISTS `c2_attribute`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `c2_attribute` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`eshop_id` bigint(20) DEFAULT 1,
`type` tinyint(4) DEFAULT 0,
`code` varchar(255) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`label` varchar(255) NOT NULL,
`hint` varchar(255) DEFAULT NULL,
`input_type` varchar(255) DEFAULT NULL,
`default_value` text DEFAULT NULL,
`memo` text DEFAULT NULL,
`is_sku` tinyint(4) DEFAULT 0,
`is_visible` tinyint(4) DEFAULT 1,
`is_required` tinyint(4) DEFAULT 0,
`is_unique` tinyint(4) DEFAULT 0,
`is_depend` tinyint(4) DEFAULT 0,
`depend_id` bigint(20) DEFAULT 0,
`status` tinyint(4) DEFAULT 1,
`position` int(11) DEFAULT 0,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `Index_1` (`code`),
KEY `Index_2` (`type`),
KEY `Index_3` (`position`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
3. 测试
法一:导入原数据库数据,看是否按id取模规则,平滑分布到各数据库中,成功。
法二:用sysbench做数据生成测试。
4. 参考资料
- https://www.2cto.com/net/201704/635533.html