【MySQL】MyCat管理主从数据库(初体验)

前言

如果我们的系统和数据已经到达必须使用主从读写分离或者集群的地步了，那就面临这一个问题，我们程序的数据库连接池该怎么连接嘞？多数据源？还是主从配置？其实这样可能会出现硬编码，还要维护配置文件还更有其他的可能，所以怎么样才能统一管理这么多数据源？那就得用到数据库中间件--MyCat

准备

两台主从数据库、Mycat安装包 

关于主从数据库怎么配置安装，移驾到下面的链接！

【MySQL】主从搭建_阿小冰的博客-CSDN博客【MySQL】主从搭建https://blog.csdn.net/qq_38377525/article/details/125083088

MyCat的安装及配置

1、解压MyCat压缩包

[root@localhost home]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

 2、将解压好的文件夹拷贝到/usr/local目录下

[root@localhost home]# cp -rf mycat/ /usr/local/

3、切到/usr/local/mycat目录下

[root@localhost home]# cd /usr/local/mycat/

4、在master主数据库创建表格和自增ID函数

表格是随便创建的，脚本如下：

#用户表
CREATE TABLE `sec_user` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户表主键id',
  `user_name` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '用户名',
  `password` varchar(128) COLLATE utf8_bin NOT NULL COMMENT '密码',
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1357 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC;

#自增记录表
CREATE TABLE `mycat_sequence` (
  `NAME` varchar(50) NOT NULL,
  `current_value` int(11) NOT NULL,
  `increment` int(11) NOT NULL DEFAULT '100',
  PRIMARY KEY (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建函数，可以直接复制粘贴：

-- 插入表数据，第一个入参要填写需要管理表的大写表名即可，后面俩参数直接用即可
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('SEC_USER', 0, 1);
--创建行数 mycat_seq_currval
DROP FUNCTION IF EXISTS `mycat_seq_currval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) 
RETURNS VARCHAR(64) CHARSET utf8
    DETERMINISTIC
BEGIN DECLARE retval VARCHAR(64);
        SET retval="-999999999,null";  
        SELECT CONCAT(CAST(current_value AS CHAR),",",CAST(increment AS CHAR) ) INTO retval 
          FROM MYCAT_SEQUENCE WHERE NAME = seq_name;  
        RETURN retval ; 
END
;;
DELIMITER ;
-- 创建函数 mycat_seq_nextval
DROP FUNCTION IF EXISTS `mycat_seq_nextval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
 CHARSET utf8
    DETERMINISTIC
BEGIN UPDATE MYCAT_SEQUENCE  
                 SET current_value = current_value + increment 
                  WHERE NAME = seq_name;  
         RETURN mycat_seq_currval(seq_name);  
END
;;
DELIMITER ;
-- 创建函数  mycat_seq_setval
DROP FUNCTION IF EXISTS `mycat_seq_setval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), VALUE INTEGER) 
RETURNS VARCHAR(64) CHARSET utf8
    DETERMINISTIC
BEGIN UPDATE MYCAT_SEQUENCE  
                   SET current_value = VALUE  
                   WHERE NAME = seq_name;  
         RETURN mycat_seq_currval(seq_name);  
END
;;
DELIMITER ;

5、查看Slave从数据库是否已经同步

 

现在来看是已经同步了，那我们继续配置MyCat来管理这个sec_user表

6、来到/usr/local/mycat/config

分别编辑rule.xml、server.xml、schema.xml

1. rule.xml其实要调整的，然后我这里暂时不用，因为rule原本的配置有我想要的，如果没有大家想要的，可以自行查一下怎么调整
2. server.xml，这个文件配置比较多，这里我只标注需要编辑的几个标签

   <!--0 表⽰是表⽰使⽤本地⽂件⽅式
   1 表⽰的是根据数据库来⽣成，就是我们⾃⼰配置的自增
   2 表⽰时间戳的⽅式-->
   <property name="sequnceHandlerType">1</property>
   
   <user name="root">
       <property name="password">123456</property>
       <property name="schemas">mycat_user</property>
   
       <!-- 表级 DML 权限设置 -->
       <!--
           <privileges check="false">
               <schema name="TESTDB" dml="0110" >
                   <table name="tb01" dml="0000"></table>
                   <table name="tb02" dml="1111"></table>
               </schema>
           </privileges>
       -->
   </user>
   
   <user name="user">
       <property name="password">123456</property>
       <property name="schemas">mycat_user</property>
       <property name="readOnly">true</property>
   </user>

3. schema.xml，这个我就直接贴出来整个的配置

   
   <?xml version="1.0"?>
   <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
   <mycat:schema xmlns:mycat="http://io.mycat/">
       <!--name要与上面的server.xml中定义的schemas保持一致-->
       <schema name="mycat_user" checkSQLschema="false" sqlMaxLimit="100">
           <!--配置指定表，name是实际表名，dataNode是下面定义的标签，primartKey和autoIncrement是自增ID，type是全局自增-->
           <table name="sec_user"  dataNode="dn1" primaryKey="user_id" autoIncrement="true" type="global"/>
       </schema>
       <!--配置数据库，name是配置别名，dataHost是下面的标签name，database是实际数据库库名-->
       <dataNode name="dn1" dataHost="localhost1" database="mycat_user" />
       <!--配置数据库信息，name是配置别名-->
       <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
           <!--心跳，定时执行这个sql，只要返回就没问题，这个不用管-->
           <heartbeat>select user()</heartbeat>
           <!--writeHost是写数据库，readHost是负责读的数据库-->
           <writeHost host="hostM1" url="192.168.80.200:3306" user="root" password="123456">
               <readHost host="hostS2" url="192.168.80.201:3306" user="root" password="123456" />
           </writeHost>
       </dataHost>
   </mycat:schema>
   

   |-<schema/> name属性逻辑库名称

   |-----------------checkSQLschema是否自动检查模式(模式名.表名)

   |-----------------sqlMaxLimit 如果sql语句中没有limit平时测试时自动添加limit 100

   |--<table/> 配置表信息

   |-----------------name配置逻辑库名称

   |-----------------dataNode 值来源于<dataNode>的name属性

   |-----------------rule 设置切分规则,值来源于rule.xml<tableRule>name属性

   |-<dataNode/> 配置数据节点

   |-----------------dataHost值来源于<dataHost>的name属性

   |-<dataHost/> 配置数据库连接信息

   |--<writeHost/> 负责写的主机信息

   |--<readHost/>负责读的主机信息

7、编辑sequence_db_conf.properties

[root@localhost conf]# vi sequence_db_conf.properties
#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1
#需要管理的数据库大写表名=上面schema中 <dataNode name="dn1"/>中的name
SEC_USER=dn1

8、启动mycat

进入bin目录，使用命令

[root@localhost bin]# ./mycat start

查看日志，进入logs目录

[root@localhost logs]# tail -f wrapper.log

只要没有Exception其实就启动成功了，或者看日志，出现这句话，也是成功的

 

9、开放Mycat的端口8066

10、使用Navicat连接MyCat

ip是安装的这台机器的公网ip，端口是8066，账号密码是上面server.xml中<user name="root">和下面的password密码，连接之后就会发现，Mycat已经把主数据库的表、函数都同步过来了

11、操作Mycat

进入Mycat命令行客户端，其实是基于MySQL使用的，命令如下：

[root@localhost conf]# mysql -h 127.0.0.1 -P 8066 -u root -p
Enter password:

密码还是上面server.xml中的配置，进入之后，看一下和原生的MySQL有什么区别

如果是MySQL，进入客户端之后是这样的

如果是MyCat，进入之后是：

 

然后执行insert，通过Mycat命令客户端，对主从数据库进行添加数据，因为自增规则被MyCat托管了，所以添加的时候不需要列出自增ID的字段和赋值，同时MyCat的insert必须要声明对应的列

12、观察主从数据库的sec_user数据

不出意外是有上面insert的这条数据的

到这里，MyCat托管MySQL就配置完成了

总结

然后我们程序直接数据库连接这个MyCat就可以了，一个数据源，不需要多数据源配置，我们不需要管MyCat是怎么做转发的，只要规则正确，它会根据我们实现配置的分区规则，ID自增规则等等我们配置的来进行处理数据