文章目录
- 11.分库
-
- 11.1数据库准备
-
- 1 停止之前的数据库
- 2 安装两个数据库服务（容器）
- 3.创建数据库
- 11.2配置mycat
- 11.3 启动mycat
- 11.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重新配置分库规则
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="drugs" dataNode="dn2"></table>
<table name="disease" dataNode="dn2"></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="his_mycat"/>
<dataNode name="dn2" dataHost="host2" database="his_mycat"/>
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="172.17.0.2:3306" user="root" password="root">
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM2" url="172.17.0.3:3306" user="root" password="root">
</writeHost>
</dataHost>
</mycat:schema>
11.3 启动mycat
在mycat/bin目录中执行
./mycat console
11.4 登录mycat创建表结构
/****dn1****/
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;
/****dn2****/
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]> /****dn2****/
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]>
添加“指尖架构师”公众号，了解更多资讯
版权声明：本文不是「本站」原创文章，版权归原作者所有 | 原文地址: