Mycat学习手册--09分库实现

文章目录

11.分库

11.1数据库准备

1 停止之前的数据库2 安装两个数据库服务（容器）3.创建数据库 11.2配置mycat11.3 启动mycat11.4 登录mycat创建表结构11.5验证

1.Mycat客户端验证2.dn1节点验证3.dn2节点验证

11.分库 11.1数据库准备

此处模拟东软云医院管理系统当数据库连接压力过大时进行数据库的拆分，计划拆分成两个数据库。

数据库设计关系如下：

根据上述业务表关联关系科室、用户表、挂号信息表三个表之间是有关联关系的所以应该放到一个数据节点上，另外两张表为了测试放到另外一个数据节点上，表示成如下形式：

1 停止之前的数据库

分库一定要在新的数据库上准备，此处抛弃上述数据库重新创建干净的数据库

删除掉原来的数据库(容器),可以仅停止不删除容器用于测试之前的逻辑

[root@mycat ~]# docker stop m1 && docker stop m2 && docker stop s1 && docker stop s2
m1
m2
s1
s2
[root@mycat ~]# docker rm m1 && docker rm m2 && docker rm  s1 && docker rm s2
m1
m2
s1
s2
[root@mycat ~]#

2 安装两个数据库服务（容器）

创建两个数据库,此处暂时不配置主从数据库复制(Master-Slave模式)所以不需要在docker宿主机上单独映射配置文件，执行如下命令直接创建2个MySQL数据库容器

docker run --name dn1 -p 3316:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7 &&

docker run --name dn2 -p 3326:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7

具体执行过程如下：

[root@mycat ~]# docker run --name dn1 -p 3316:3306   -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7 &&
> docker run --name dn2 -p 3326:3306   -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7
b9b3297b30ae82cd25889414e76e16c0fa20c186535a1c6b0d2d15469163b40e
e61487a76ef284927f031897ac38d7c6d00c88c191265f946fcf6bb237f04f54
[root@mycat ~]#

查看两个数据库机器（容器）的ip

[root@mycat ~]# docker ps
ConTAINER ID   IMAGE       COMMAND                  CREATED         STATUS         PORTS                                                  NAMES
fd2bea0b1f01   mysql:5.7   "docker-entrypoint.s…"   5 seconds ago   Up 3 seconds   33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp   dn2
6ae712c5f59c   mysql:5.7   "docker-entrypoint.s…"   6 seconds ago   Up 5 seconds   33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp   dn1


[root@mycat ~]# docker inspect --format '{{ .NetworkSettings.IPAddress }}'  dn1  &&
> docker inspect --format '{{ .NetworkSettings.IPAddress }}'  dn2
172.17.0.2
172.17.0.3
[root@mycat ~]#

ip地址分别是：

容器	IP
dn1	172.17.0.2
dn2	172.17.0.3

3.创建数据库

在两个空白数据库机器（容器）上创建数据库语句如下：

CREATE DATAbase his_mycat DEFAULT CHARACTER SET utf8mb4;

在dn1上创建数据库

[root@mycat ~]# mysql -u root -proot -h 172.17.0.2 -P3306
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MySQL [(none)]> CREATE DATAbase his_mycat DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| his_mycat          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

MySQL [(none)]> exit
Bye
[root@mycat ~]#

在dn2上创建数据库

[root@mycat ~]# mysql -u root -proot -h 172.17.0.3 -P3306
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MySQL [(none)]> CREATE DATAbase his_mycat DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| his_mycat          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

MySQL [(none)]> exit
Bye
[root@mycat ~]#

11.2配置mycat

分库规则：

dn1：department部门表、user用户表、register患者挂号表dn2：drugs 药品表、disease 疾病表

修改mycat的schema.xml重新配置分库规则




    
        
        
    

    
    

    
        select user()
        
        
        
    
    
        select user()

11.3 启动mycat

在mycat/bin目录中执行

./mycat console

11.4 登录mycat创建表结构



CREATE TABLE `department` (
  `id` int(9) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `DeptCode` varchar(64) NOT NULL COMMENT '科室编码',
  `DeptName` varchar(64) NOT NULL COMMENT '科室名称',
  `DeptCategory` varchar(64) DEFAULT NULL COMMENT '科室分类',
  `DeptTypeID` int(9) NOT NULL COMMENT '科室类型',
  `DelMark` int(1) DEFAULT NULL COMMENT '删除标记',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `user` (
  `id` int(9) NOT NULL COMMENT 'id',
  `UserName` varchar(64) NOT NULL COMMENT '登录名',
  `Password` varchar(64) DEFAULT NULL COMMENT '密码',
  `RealName` varchar(64) NOT NULL COMMENT '真实姓名',
  `UserTypeID` int(9) DEFAULT NULL COMMENT '1 - 挂号人员  2 - 门诊医生  3 - 医技医生 4 - 药房人员   5 - 财务人员  6 - 行政人员 ',
  `DocTitleID` int(9) DEFAULT NULL COMMENT '医生职称',
  `IsScheduling` int(9) DEFAULT NULL COMMENT '是否排班',
  `DeptId` int(9) NOT NULL COMMENT '所在科室ID',
  `RegistId` int(9) DEFAULT NULL COMMENT '挂号级别ID',
  `DelMark` int(1) DEFAULT NULL COMMENT '删除标记',
  PRIMARY KEY (`id`),
  KEY `FK_科室id` (`DeptId`),
  CONSTRAINT `FK_科室id` FOREIGN KEY (`DeptId`) REFERENCES `department` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `register` (
  `id` int(9) NOT NULL COMMENT 'id',
  `RealName` varchar(64) DEFAULT NULL COMMENT '真实姓名',
  `Gender` int(9) DEFAULT NULL COMMENT '性别',
  `IDnumber` varchar(18) DEFAULT NULL COMMENT '身份证号',
  `BirthDate` date DEFAULT NULL COMMENT '出生日期',
  `Age` int(3) DEFAULT NULL COMMENT '年龄',
  `AgeType` int(9) DEFAULT NULL COMMENT '年龄类型',
  `HomeAddress` varchar(64) DEFAULT NULL COMMENT '家庭住址',
  `CaseNumber` varchar(64) DEFAULT NULL COMMENT '一名患者在同一医院看诊多次，根据患者是否使用同一个病历本，确定该患者的“病历号码”是否相同。',
  `VisitDate` date NOT NULL COMMENT '本次看诊日期',
  `Noon` int(9) NOT NULL COMMENT '午别',
  `DeptId` int(9) DEFAULT NULL COMMENT '本次挂号科室ID',
  `UserId` int(9) DEFAULT NULL COMMENT '本次挂号医生id',
  `IsBook` int(1) NOT NULL COMMENT '病历本要否',
  `RegisterTime` datetime DEFAULT NULL COMMENT '挂号时间',
  `RegisterID` int(9) NOT NULL COMMENT '挂号员ID',
  `VisitState` int(9) DEFAULT NULL COMMENT '本次看诊状态',
  PRIMARY KEY (`id`),
  KEY `FK_医生id` (`UserId`),
  CONSTRAINT `FK_医生id` FOREIGN KEY (`UserId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `disease` (
  `id` int(9) NOT NULL COMMENT 'id',
  `DiseaseCode` varchar(64) DEFAULT NULL COMMENT '疾病助记编码',
  `DiseaseName` varchar(255) DEFAULT NULL COMMENT '疾病名称',
  `DiseaseICD` varchar(64) DEFAULT NULL COMMENT '国际ICD编码',
  `DiseaseType` varchar(64) DEFAULT NULL COMMENT '疾病所属分类',
  `DelMark` int(1) DEFAULT NULL COMMENT '删除标记',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `drugs` (
  `id` int(9) NOT NULL COMMENT 'id',
  `Drugs_Code` char(14) DEFAULT NULL COMMENT '药品编码',
  `Drugs_Name` varchar(64) DEFAULT NULL COMMENT '药品名称',
  `Drugs_Format` varchar(64) DEFAULT NULL COMMENT '药品规格',
  `Drugs_Unit` varchar(64) DEFAULT NULL COMMENT '包装单位',
  `Manufacturer` varchar(512) DEFAULT NULL COMMENT '生产厂家',
  `Drugs_Dosage` varchar(64) DEFAULT NULL COMMENT '药品剂型',
  `Drugs_Type` varchar(64) DEFAULT NULL COMMENT '药品类型',
  `Drugs_Price` decimal(8,2) DEFAULT NULL COMMENT '药品单价',
  `Mnemonic_Code` varchar(64) DEFAULT NULL COMMENT '拼音助记码',
  `Creation_Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `DelMark` int(1) DEFAULT NULL COMMENT '有效性标记',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

11.5验证

创建完成后分别在mycat客户端、dn1节点、dn2节点查看表存储情况

1.Mycat客户端验证

执行过程如下：

[root@mycat ~]# mysql -umycat -p123456 -h 127.0.0.1 -P8066
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MySQL [(none)]> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed


MySQL [TESTDB]> CREATE TABLE `department` (
    ->   `id` int(9) NOT NULL AUTO_INCREMENT COMMENT 'id',
    ->   `DeptCode` varchar(64) NOT NULL COMMENT '科室编码',
    ->   `DeptName` varchar(64) NOT NULL COMMENT '科室名称',
    ->   `DeptCategory` varchar(64) DEFAULT NULL COMMENT '科室分类',
    ->   `DeptTypeID` int(9) NOT NULL COMMENT '科室类型',
    ->   `DelMark` int(1) DEFAULT NULL COMMENT ' 删除标记',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)


MySQL [TESTDB]> 
MySQL [TESTDB]> CREATE TABLE `user` (
    ->   `id` int(9) NOT NULL COMMENT 'id',
    ->   `UserName` varchar(64) NOT NULL COMMENT '登录名',
    ->   `Password` varchar(64) DEFAULT NULL COMMENT '密码',
    ->   `RealName` varchar(64) NOT NULL COMMENT '真实姓名',
    ->   `UserTypeID` int(9) DEFAULT NULL COMMENT '1 - 挂号人员  2 - 门诊医生  3 - 医技医生 4 - 药房人员   5 - 财务人员  6 - 行政人员 ',
    ->   `DocTitleID` int(9) DEFAULT NULL COMMENT '医生职称',
    ->   `IsScheduling` int(9) DEFAULT NULL COMMENT '是否排班',
    ->   `DeptId` int(9) NOT NULL COMMENT '所在科室ID',
    ->   `RegistId` int(9) DEFAULT NULL COMMENT '挂号级别ID',
    ->   `DelMark` int(1) DEFAULT NULL COMMENT ' 删除标记',
    ->   PRIMARY KEY (`id`),
    ->   KEY `FK_科室id` (`DeptId`),
    ->   ConSTRAINT `FK_科室id` FOREIGN KEY (`DeptId`) REFERENCES `department` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

MySQL [TESTDB]> 
MySQL [TESTDB]> 
MySQL [TESTDB]> CREATE TABLE `register` (
    ->   `id` int(9) NOT NULL COMMENT 'id',
    ->   `RealName` varchar(64) DEFAULT NULL COMMENT '真实姓名',
    ->   `Gender` int(9) DEFAULT NULL COMMENT '性别',
    ->   `IDnumber` varchar(18) DEFAULT NULL COMMENT '身份证号',
    ->   `BirthDate` date DEFAULT NULL COMMENT ' 出生日期',
    ->   `Age` int(3) DEFAULT NULL COMMENT '年龄',
    ->   `AgeType` int(9) DEFAULT NULL COMMENT ' 年龄类型',
    ->   `HomeAddress` varchar(64) DEFAULT NULL COMMENT '家庭住址',
    ->   `CaseNumber` varchar(64) DEFAULT NULL COMMENT '一名患者在同一医院看诊多次，根据患者是否使用同一个病历本，确定该患者的“病历号码”是否相同。',
    ->   `VisitDate` date NOT NULL COMMENT '本次 看诊日期',
    ->   `Noon` int(9) NOT NULL COMMENT '午别',
    ->   `DeptId` int(9) DEFAULT NULL COMMENT '本次挂号科室ID',
    ->   `UserId` int(9) DEFAULT NULL COMMENT '本次挂号医生id',
    ->   `IsBook` int(1) NOT NULL COMMENT '病历本要否',
    ->   `RegisterTime` datetime DEFAULT NULL COMMENT '挂号时间',
    ->   `RegisterID` int(9) NOT NULL COMMENT '挂号员ID',
    ->   `VisitState` int(9) DEFAULT NULL COMMENT '本次看诊状态',
    ->   PRIMARY KEY (`id`),
    ->   KEY `FK_医生id` (`UserId`),
    ->   ConSTRAINT `FK_医生id` FOREIGN KEY (`UserId`) REFERENCES `user` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

MySQL [TESTDB]> 
MySQL [TESTDB]> 
MySQL [TESTDB]> 
MySQL [TESTDB]> CREATE TABLE `disease` (
    ->   `id` int(9) NOT NULL COMMENT 'id',
    ->   `DiseaseCode` varchar(64) DEFAULT NULL COMMENT '疾病助记编码',
    ->   `DiseaseName` varchar(255) DEFAULT NULL COMMENT '疾病名称',
    ->   `DiseaseICD` varchar(64) DEFAULT NULL COMMENT '国际ICD编码',
    ->   `DiseaseType` varchar(64) DEFAULT NULL COMMENT '疾病所属分类',
    ->   `DelMark` int(1) DEFAULT NULL COMMENT ' 删除标记',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

MySQL [TESTDB]> 
MySQL [TESTDB]> 
MySQL [TESTDB]> CREATE TABLE `drugs` (
    ->   `id` int(9) NOT NULL COMMENT 'id',
    ->   `Drugs_Code` char(14) DEFAULT NULL COMMENT '药品编码',
    ->   `Drugs_Name` varchar(64) DEFAULT NULL COMMENT '药品名称',
    ->   `Drugs_Format` varchar(64) DEFAULT NULL COMMENT '药品规格',
    ->   `Drugs_Unit` varchar(64) DEFAULT NULL COMMENT '包装单位',
    ->   `Manufacturer` varchar(512) DEFAULT NULL COMMENT '生产厂家',
    ->   `Drugs_Dosage` varchar(64) DEFAULT NULL COMMENT '药品剂型',
    ->   `Drugs_Type` varchar(64) DEFAULT NULL COMMENT '药品类型',
    ->   `Drugs_Price` decimal(8,2) DEFAULT NULL COMMENT '药品单价',
    ->   `Mnemonic_Code` varchar(64) DEFAULT NULL COMMENT '拼音助记码',
    ->   `Creation_Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
    ->   `DelMark` int(1) DEFAULT NULL COMMENT ' 有效性标记',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

MySQL [TESTDB]> show tables;
+---------------------+
| Tables_in_his_mycat |
+---------------------+
| disease             |
| drugs               |
| department          |
| register            |
| user                |
+---------------------+
5 rows in set (0.01 sec)

MySQL [TESTDB]>

2.dn1节点验证

[root@mycat bin]# mysql -uroot -proot -h 172.17.0.2 -P 3306
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 17
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MySQL [(none)]> use his_mycat
Database changed
MySQL [his_mycat]> show tables;
+---------------------+
| Tables_in_his_mycat |
+---------------------+
| department          |
| register            |
| user                |
+---------------------+
3 rows in set (0.00 sec)

MySQL [his_mycat]>

3.dn2节点验证

[root@mycat ~]# mysql -uroot -proot -h 172.17.0.3 -P 3306
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 13
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MySQL [(none)]> use his_mycat
Database changed
MySQL [his_mycat]> show tables;
+---------------------+
| Tables_in_his_mycat |
+---------------------+
| disease             |
| drugs               |
+---------------------+
2 rows in set (0.00 sec)

MySQL [his_mycat]>

添加“指尖架构师”公众号，了解更多资讯

Mycat学习手册--09分库实现

Linux相关栏目本月热门文章