CentOS7下的Mysql8集群操作实践（四）-分库分表

绝地灬酷狼 2021-11-23 05:38 360阅读 0赞

业务场景：一张订单表order\_info分割成多张表分配到三个数据库db1,db2,db3（同一主机）

实现方式：Mycat数据库中间件实现分库分表。

实现逻辑：客户端发起一个数据操作请求，Mycat拦截首先对SQL语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL发往后端的真实数

据库，并将返回的结果做适当的处理，最终再返回给用户。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdmFfY3hycw_size_16_color_FFFFFF_t_70][]

在实现前我们下看下服务器规划

<table style="width:460.45pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:80.9pt;"> 主机名 </td> 
 <td style="vertical-align:top;width:70.7pt;"> OS </td> 
 <td style="vertical-align:top;width:94.5pt;"> IP </td> 
 <td style="vertical-align:top;width:82.75pt;"> 用途 </td> 
 <td style="vertical-align:top;width:131.6pt;"> 备注 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:80.9pt;"> CentOS 7_107 </td> 
 <td style="vertical-align:top;width:70.7pt;"> CentOS7 </td> 
 <td style="vertical-align:top;width:94.5pt;"> 192.168.1.107 </td> 
 <td style="vertical-align:top;width:82.75pt;"> Master/写 </td> 
 <td style="vertical-align:top;width:131.6pt;"> Mysql8/mysql-proxy </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:80.9pt;"> CentOS 7_108 </td> 
 <td style="vertical-align:top;width:70.7pt;"> CentOS7 </td> 
 <td style="vertical-align:top;width:94.5pt;"> 192.168.1.108 </td> 
 <td style="vertical-align:top;width:82.75pt;"> Slave/读 </td> 
 <td style="vertical-align:top;width:131.6pt;"> Mysql8 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:80.9pt;"> CentOS 7_109 </td> 
 <td style="vertical-align:top;width:70.7pt;"> CentOS7 </td> 
 <td style="vertical-align:top;width:94.5pt;"> 192.168.1.109 </td> 
 <td style="vertical-align:top;width:82.75pt;"> Slave/读 </td> 
 <td style="vertical-align:top;width:131.6pt;"> Mysql8 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:80.9pt;"> CentOS 7_110 </td> 
 <td style="vertical-align:top;width:70.7pt;"> CentOS7 </td> 
 <td style="vertical-align:top;width:94.5pt;"> 192.168.1.110 </td> 
 <td style="vertical-align:top;width:82.75pt;"> Mycat </td> 
 <td style="vertical-align:top;width:131.6pt;"> Mycat </td> 
 </tr> 
 </tbody> 
</table>

从表格我们可以看出来，Mycat是单独部署到一台服务器上，通过安装配置Mycat来完成分库分表功能

具体实现步骤：

*  下载安装jdk1.8

上传到/usr/local目录下并解压

tar -xvf jdk-8u181-linux-x64.tar.gz

配置jdk环境变量

vi /etc/profile

添加如下内容：

export JAVA_HOME=/usr/local/jdk1.8.0_181
    
    export PATH=$JAVA_HOME/bin:$PATH

wq保存退出后：

source /etc/profile

输入命令：\[root@localhost ~\]\#

java -version

Java(TM) SE Runtime Environment (build 1.8.0\_181-b13)

Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)

显示如上信息表示jdk安装配置完成

*  下载 Mycat

官网地址: [http://www.mycat.io/][http_www.mycat.io]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdmFfY3hycw_size_16_color_FFFFFF_t_70 1][]

*  上传至/usr/local/目录下并解压

tar -xvf  Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz

*  修改mycat相关配置文件

**配置****server.xml**

vi /usr/local/mycat/conf/server.xml

该配置主要修改用户信息

修改内容如下

<?xml version="1.0" encoding="UTF-8"?>
 	......
 		<user name="root" defaultAccount="true">
 			<property name="password">123456</property>
 			<property name="schemas">tDB</property>
 
 			
 			
 		</user>
 	
 
 	</mycat:server>

**配置****schema.xml**

此文件关联mysql读写分离策略！读写分离、分库分表策略、分片节点都是在此文件中配置的！

首先备份schema.xml文件

cp schema.xml schema.xml_bak

编辑schema.xml文件

vi /usr/local/mycat/conf/schema.xml

xml version="1.0"?>
 <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
 <mycat:schema xmlns:mycat="http://io.mycat/">
 <!—mycat逻辑库的名称-->
 <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
 
 <!—物理表的名称-->
 <table name="order_info" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" /> 
 </schema>
 <dataNode name="dn1" dataHost="localhost1" database="db1" />
 <dataNode name="dn2" dataHost="localhost1" database="db2" />
 <dataNode name="dn3" dataHost="localhost1" database="db3" />
 <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
 writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
 <heartbeat>select user()</heartbeat>
 
 <writeHost host="hostM1" url="192.168.1.107:3306" user="root"
 password="root">
 
 
 </writeHost>
 </dataHost>
 </mycat:schema>

wq保存退出;

*  mycat启动

cd /usr/local/mycat/bin目录下

./mycat start

查看进程是否启动

ps -ef | grep mycat

![20190725225455701.png][]

*  创建数据库和表

在master主机上创建物理数据库db1、db2、db3

create database db1；
    
    create database db2；
    
    create database db3；

分别在三个库中执行以下sql创建orde\_info表

DROP TABLE IF EXISTS `order_info`;
    CREATE TABLE `order_info`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `order_no` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `total_count` int(11)  NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    SET FOREIGN_KEY_CHECKS = 1;

![20190725225540284.png][]

*  客户端登录Mycat数据库

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdmFfY3hycw_size_16_color_FFFFFF_t_70 2][]

*  插入数据，测试分库分表

在mycat客户端下插入数据

insert into order_info(id,order_no,total_count) VALUES (1,'100001',11);
    insert into order_info(id,order_no,total_count) VALUES (2,'200001',22);
    insert into order_info(id,order_no,total_count) VALUES (3,'300001',33);
    insert into order_info(id,order_no,total_count) VALUES (4,'400001',44);
    insert into order_info(id,order_no,total_count) VALUES (5,'500001',55);
    insert into order_info(id,order_no,total_count) VALUES (6,'600001',66);
    insert into order_info(id,order_no,total_count) VALUES (7,'700001',77);

*  在master主机和slave从机客户端查看mycat端写入的数据

select \* from db1.order\_info

![20190725225743226.png][]

select \* from db2.order\_info

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdmFfY3hycw_size_16_color_FFFFFF_t_70 3][]

select \* from db3.order\_info

![20190725225805926.png][]

从上面可以看出：

在mycat里做了sharding分片策略的order\_info表里写入的数据，已经分片到db1、db2、db3三个库中了，即成功实现了分库分表功能！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdmFfY3hycw_size_16_color_FFFFFF_t_70]: /images/20211122/b057e9ddc3f24aa8a6ad5aa8eaffebda.png
[http_www.mycat.io]: http://www.mycat.io/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdmFfY3hycw_size_16_color_FFFFFF_t_70 1]: /images/20211122/7c7c1d6d353e431490fd7710b8448b91.png
[20190725225455701.png]: /images/20211122/c027e594ceb44a4389017c56c6150556.png
[20190725225540284.png]: /images/20211122/0b58884536a549128de28a6048c04d4d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdmFfY3hycw_size_16_color_FFFFFF_t_70 2]: /images/20211122/9d7198bac0f046c480c87fa154ba1b89.png
[20190725225743226.png]: /images/20211122/d6e0478c5116459f8816fa9779f8f761.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdmFfY3hycw_size_16_color_FFFFFF_t_70 3]: /images/20211122/e8a69ad6ecb2426188d19ff5a10629a6.png
[20190725225805926.png]: /images/20211122/c53438f1e9e24638881d5519d2eb7e4e.png