【赵渝强老师】MySQL数据库的分库与分表

MySQL数据库如今得到了广泛的应用。在使用过程中会通过搭建MySQL主从复制的架构来进步性能，同时接纳分库分表的模式来办理读写分离的题目。MySQL数据库的中央件就是为了更好地使MySQL支持这些应用的场景。主流的MySQL数据库中央件有ProxySQL、Mycat、Atlas和Cobar等等。引入了数据库中央件以后，客户端就不直接利用利用MySQL数据库集群，而是通过数据库中央件举行利用。
一、 Mycat简介与核心对象

Mycat是一个开源的分布式数据库体系，对于数据库用户而言可以把它看作是一个数据库署理。它的后端可以支持MySQL、SQL Server、Oracle、DB2、PostgreSQL 等主流的关系型数据库，同时也支持MongoDB等NoSQL数据库。Mycat的核心功能是实现数据库的分表分库。MyCat支持标准的SQL语句举行数据的利用，在低落了开辟难度的同时提拔了开辟速率。Mycat可以通过使用MySQL原生协议与MySQL数据库服务器举行通讯，而且Mycat基于Java语言开辟，因此也可以用DBC协议与大多数主流数据库服务器通讯。

点击这里检察视频教学：【赵渝强老师】MySQL数据库的分库分表下面罗列了Mycat中的核心对象以及它们的寄义：

• 逻辑库：由于Mycat相当于是数据库的署理，因此Mycat作为数据库中央件其自己就可以被看做是一个或多个数据库集群构成的逻辑库。开辟职员只必要面临该逻辑库举行开辟即可。
  
• 逻辑表：逻辑库由逻辑表构成。对于分布式数据库来说，客户端利用的就是逻辑表。逻辑表的数据泉源是分布在后端一个或多个物理数据库。
  
• 分片表：分片表是指将原有的大数据量表切分成多张表，此中的每一张表就是一个分片，此中均包罗一部门数据。全部分片数据的合集构成了完备的表数据。
  
• 非分片表：针对数据量小的表不必要举行分片，这些表就可以看成黑白分片表。实在，非分片是相对分片表来说的，就是那些不必要举行数据切分的表。
  
• ER表：ER与关系型数据库中的实体关系模子是雷同的，它可以将具有父子关系的表及其数据存放在同一个数据分片上，即子表依靠于父表。ER表通过表分组（Table Group）包管不会跨库利用。
  
• 全局表：全局表雷同数据库的数据字典。它内里的数据一样平常不会频仍变动，而且数据规模不大，通常数据量在十万以内。
  
• 分片节点：将大数据量表举行切分后，每个表的分片地点的数据库服务器节点就是分片节点。因此，可以把一个数据库实例的服务器就明确成是一个分片节点。
  
• 节点主机：每个分片节点（dataNode）不肯定都会独占一台呆板。在同一个物理主机上可以存在多个分片数据库实例。如许一个或多个分片节点（dataNode）地点的呆板就是节点主机。
  
• 分片规则：分片规则是指将大数据的表切分到多个数据分片的战略。分片表必要根据分片规则举行数据的切分。
  
• 全局序列号：大数据量的表被切分后，原有的关系数据库中的主键束缚在分布式条件下将无法使用。为了包管数据具有唯一性的标识，从而引入全局序列号的机制。
  

下图展示了基于MyCat的MySQL分库分表的结构。

二、 安装摆设Mycat

在相识到了Mycat的根本概念与核心对象后，下面通过具体的步调在proxy的主机上摆设Mycat环境。如今Mycat最新的版本是2.0，这里使用一个Mycat的稳固版本（1.6.7.5）来演示。

由于Mycat基于Java语言开辟，因此在安装摆设Mycat之前必要安装JDK。

（1）使用版本jdk-8u181-linux-x64.tar.gz安装Java运行环境。

1. mkdir -p /root/training
2. tar -zxvf jdk-8u181-linux-x64.tar.gz -C /root/training

复制代码

（2）编辑文件“/root/.bash_profile”设置Java的环境变量。

1. JAVA_HOME=/root/training/jdk1.8.0_181
2. export JAVA_HOME
4. PATH=$JAVA_HOME/bin:$PATH
5. export PATH

复制代码

（3）见效环境变量。

1. source /root/.bash_profile

复制代码

（4）验证Java环境。

1. java -version
3. # 输出的信息如下：
4. java version "1.8.0_181"
5. Java(TM) SE Runtime Environment (build 1.8.0_181-b13)
6. Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)

复制代码

（5）下载Mycat-server-1.6.7.5-release-20200410174409-linux.tar.gz安装包。

1. wget \
2. http://dl.mycat.org.cn/1.6.7.5/2020-4-10/Mycat-server-1.6.7.5-release-20200410174409-linux.tar.gz

复制代码

（6）将Mycat安装包解压到目次“/root/training”下。

1. tar -zxvf \
2.     Mycat-server-1.6.7.5-release-20200410174409-linux.tar.gz \
3.     -C /root/training/

复制代码

（7）编辑文件“/root/.bash_profile”设置Java的环境变量。

1. export MYCAT_HOME=/root/training/mycat
2. export PATH=$MYCAT_HOME/bin:$JAVA_HOME/bin:$PATH

复制代码

（8）见效环境变量。

1. source /root/.bash_profile

复制代码

（9）启动Mycat。

1. mycat start

复制代码

（10）检察Mycat的状态。

1. mycat status
3. # 输出的信息如下：
4. Mycat-server is running (89121).

复制代码

三、 使用Mycat实现分库分表

Mycat实现分库分表的核心是在于设置“/root/training/mycat/conf/schema.xml”文件和“/root/training/mycat/conf/rule.xml”文件。前者用于界说Mycat的逻辑库、逻辑表、分片节点和节点主机等信息；而后者紧张界说分片的规则。下面通过具体的步调来演示怎样基于Mycat实现后端MySQL数据库的分库与分表。这里将使用mysql11、mysql12和mysql13三台MySQL服务器作为Mycat的后端节点。
（1）在mysql11、mysql12和mysql13上创建数据库“TESTDB”，并在该库中创建一张员工表。

1. mysql> create database TESTDB;
2. mysql> use TESTDB;
3. mysql> create table emp
4.       (empno int primary key comment '员工号',
5.        ename varchar(10)  comment '员工姓名',
6.        job varchar(10) comment '员工职位',
7.        mgr int comment '员工老板的员工号',
8.        hiredate varchar(10) comment '员工的入职日期',
9.        sal int  comment '员工的月薪',
10.        comm int  comment '员工的奖金',
11.        deptno int comment '员工的部门号');
13. # 这里创建的数据库“TESTDB”也是Mycat默认的逻辑库名称。
14. # 在文件“/root/training/mycat/conf/schema.xml”中定义了该逻辑库的名称。
15. # 相关的定义如下：
16. <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">

复制代码

（2）在mysql11的后端数据库实例上插入10号部门的员工数据。

1. mysql> insert into emp values
2.        (7782,'CLARK','MANAGER',7839,'1981/6/9',2450,null,10),
3.        (7839,'KING','PRESIDENT',-1,'1981/11/17',5000,null,10),
4.        (7934,'MILLER','CLERK',7782,'1982/1/23',1300,null,10);

复制代码

（3）在mysql12的后端数据库实例上插入20号部门的员工数据。

1. mysql> insert into emp values
2.       (7369,'SMITH','CLERK',7902,'1980/12/17',800,null,20),
3.       (7566,'JONES','MANAGER',7839,'1981/4/2',2975,null,20),
4.       (7788,'SCOTT','ANALYST',7566,'1987/4/19',3000,null,20),
5.       (7876,'ADAMS','CLERK',7788,'1987/5/23',1100,null,20),
6.       (7902,'FORD','ANALYST',7566,'1981/12/3',3000,null,20);

复制代码

（4）在mysql13的后端数据库实例上插入30号部门的员工数据。

1. mysql> insert into emp values
2.       (7499,'ALLEN','SALESMAN',7698,'1981/2/20',1600,300,30),
3.       (7521,'WARD','SALESMAN',7698,'1981/2/22',1250,500,30),
4.       (7654,'MARTIN','SALESMAN',7698,'1981/9/28',1250,1400,30),
5.       (7698,'BLAKE','MANAGER',7839,'1981/5/1',2850,null,30),
6.       (7844,'TURNER','SALESMAN',7698,'1981/9/8',1500,null,30),
7.       (7900,'JAMES','CLERK',7698,'1981/12/3',950,null,30);

复制代码

（5）编辑文件“/root/training/mycat/conf/server.xml”的内容。

1. ......
9. <firewall>
10.    <whitehost>
11.           <host host="1*7.0.0.*" user="root"/>
12.       
13.           <host host="192.168.79.*" user="root"/>
14.    </whitehost>
15. <blacklist check="false">
16. </blacklist>
17. </firewall>
19. <user name="root" defaultAccount="true">
20.    
21.            <property name="password">123456</property>
22.    
23.            <property name="schemas">TESTDB</property>
24.            <property name="defaultSchema">TESTDB</property>
25.         ......
26. </user>
27. ......

复制代码

（6）编辑文件“/root/training/mycat/conf/schema.xml”的内容，设置逻辑库的表信息与分片的信息。

1. <?xml version="1.0"?>
2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
3. <mycat:schema xmlns:mycat="http://io.mycat/">
4.    
5.     <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
6.         
7.         
8.         
9.         <table name="emp" primaryKey="empno" autoIncrement="true"
10.                dataNode="dn1,dn2,dn3" rule="mod-long"/>
11.     </schema>
12.    
13.    
14.     <dataNode name="dn1" dataHost="mysql11" database="TESTDB"/>
15.     <dataNode name="dn2" dataHost="mysql12" database="TESTDB"/>
16.          <dataNode name="dn3" dataHost="mysql13" database="TESTDB"/>
18.    
19.     <dataHost name="mysql11" maxCon="1000" minCon="10" balance="0"
20.               writeType="0" dbType="mysql" dbDriver="native"
21.               switchType="1" slaveThreshold="100">
22.         <heartbeat>select user()</heartbeat>
23.         <writeHost host="mysql11" url="192.168.79.11:3306"
24.                    user="root" password="Welcome_1">
25.         </writeHost>
26.     </dataHost>
28.    
29.     <dataHost name="mysql12" maxCon="1000" minCon="10" balance="0"
30.               writeType="0" dbType="mysql" dbDriver="native"
31.               switchType="1" slaveThreshold="100">
32.         <heartbeat>select user()</heartbeat>
33.         <writeHost host="mysql12" url="192.168.79.12:3306"
34.                    user="root" password="Welcome_1">
35.         </writeHost>
36.     </dataHost>
38.    
39.     <dataHost name="mysql13" maxCon="1000" minCon="10" balance="0"
40.               writeType="0" dbType="mysql" dbDriver="native"
41.               switchType="1" slaveThreshold="100">
42.         <heartbeat>select user()</heartbeat>
43.         <writeHost host="mysql13" url="192.168.79.13:3306"
44.                    user="root" password="Welcome_1">
45.         </writeHost>
46.     </dataHost>
47. </mycat:schema>

复制代码

（7）修改文件“/root/training/mycat/conf/rule.xml”中界说分片路由规则。

1. ......
2. <tableRule name="mod-long">
3.    <rule>
4.       
5.       <columns>empno</columns>
6.       mod-long</algorithm>
7.    </rule>
8. </tableRule>
9. ......
10. <function name="mod-long" >
11.    
12.     <property name="count">3</property>
13. </function>
14. ......

复制代码

（8）重启Mycat。

1. mycat restart
3. # 输出的信息如下：
4. Stopping Mycat-server...
5. Stopped Mycat-server.
6. Starting Mycat-server...
8. # 如果在启动的过程在wrapper.log日志文件中出现了下面的错误：
9. Startup failed: Timed out waiting for a signal from the JVM.
11. # 可以在conf/wrapper.conf文件增加下面的参数：
12. wrapper.startup.timeout=300
14. # 然后进入mycat的bin目录执行重启操作。
15. ./mycat console 或者./mycat start

复制代码

（9）借助MySQL的图形客户端工具“Navicat for MySQL”毗连Mycat服务器。如下图所示。

（10）毗连乐成后，可以看到Mycat中存在一个逻辑库“TESTDB”和一张逻辑表“emp”，如上图所示。
（11）查询逻辑表“emp”中的数据，这里返回的14条数据分别来自于后端的mysq11、mysql12和mysql13上的MySQL数据库实例。如下图所示。

（12）往逻辑表“emp”中插入3条新的数据，如下图所示。

1. mysql> insert into emp(empno,ename,sal,deptno) values(1,'Tom1',1000,10);
2. mysql> insert into emp(empno,ename,sal,deptno) values(2,'Tom2',1000,10);
3. mysql> insert into emp(empno,ename,sal,deptno) values(3,'Tom3',1000,10);

复制代码

（13）直接在后端的mysq11、mysql12和mysql13上的MySQL数据库实例中查询员工表的数据。这时间会发现新插入的三条数据被存储在差异的节点上。如下图所示。

免责声明：如果侵犯了您的权益，请联系站长及时删除侵权内容，谢谢合作！qidao123.com:ToB企服之家，中国第一个企服评测及软件市场,开放入驻,技术点评得现金.