Mycat

来自linuxsa wiki
跳转到导航 跳转到搜索

introu

* docker mysql

Docker搭建MySQL主从 

这里未使用binlog方式，使用的是GTID方式

本机访问 请安装 mysql client  
yum install mysql -y  #client 
mysql -uroot -p -h127.0.0.1

* mycat

前提：mycat安装前需先实现主从复

** mycat ins

wget -c http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
tar xvf  Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

mv mycat/ /home/apps/
groupadd mycat
adduser -r -g mycat mycat
chown -R mycat.mycat /home/apps/mycat  #//修改mycat目录所属mycat用户

echo 'export PATH=/home/apps/mycat/bin/:$PATH' >> /etc/profile && source /etc/profile


#大小写不敏感 ？ 这个我不打开 
修改配置文件my.cnf，必须要修改才能使用mycat 
最后一行添加
lower_case_table_names = 1 

会自己创建logs目录了 不用手工的


** MyCat配置详解


目录 
bin:命令文件
catlet:空的,扩展
conf:配置文件(server.xml,schema.xml,rule.xml等)
lib:依赖的jar包


主要的配置文件设计有几个：
    schema.xml: 主要配置逻辑库、分片表、分片节点和节点主机等信息;
    server.xml: MyCat系统参数的配置文件;
    sequence: 全局序列的配置文件;
    log4j.xml：MyCat日志输出配置文件;
    rule.xml: MyCat分片规则相关配置文件;

** firewall

firewall-cmd --permanent --add-port=8066/tcpfirewall-cmd --permanent --add-port=8066/tcp


** config

*** 修改配置文件 mycat/conf/schema.xml

NOTE 
server.xm  schemas 和 schema.xml 的schemas name要一样 不然起不来的 

这里配置mysql的地址、用户名和密码

事先创建三个库  im_config  im_admin im_wallet 



cat schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
       
       <schema name="im_config" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema>
       <schema name="im_admin" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn2"></schema>
       <schema name="im_wallet" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn3"></schema>

<!-- 设置dataNode 对应的数据库,及 mycat 连接的地址dataHost -->
        <dataNode name="dn1" dataHost="node1" database="im_config" />
        <dataNode name="dn2" dataHost="node1" database="im_admin" />
        <dataNode name="dn3" dataHost="node1" database="im_wallet" />
<!-- mycat 逻辑主机dataHost对应的物理主机.其中也设置对应的mysql登陆信息 -->
        <dataHost name="node1" maxCon="10667" minCon="500" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
                <heartbeat>select user()</heartbeat>
                <writeHost host="master" url="192.168.10.211:3306" user="appuse" password="2pm5566">
 <readHost host="slave" url="192.168.10.212:3306" user="appuse" password="2pm556" />

</writeHost> 

        </dataHost>

</mycat:schema>


*** server.xml

这里配置mycat的用户名密码
note 最好是 root 和普通用户的 schemas 同一个  我这里是 im_config

用户可访问的逻辑数据库

放在开关 system 标签之后 不然mycat运行不起来 
 <property name="charset">utf8mb4</property>
<property name="charset">utf8mb4</property>


cat server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>

        <property name="charset">utf8mb4</property> <!-- add by evan -->
	<property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
	<property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
		<property name="sequnceHandlerType">2</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
	<!-- <property name="processorBufferChunk">40960</property> -->
	<!-- 
	<property name="processors">1</property> 
	<property name="processorExecutor">32</property> 
	 -->
		<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
		<property name="processorBufferPoolType">0</property>
		<!--默认是65535 64K 用于sql解析时最大文本长度 -->
		<!--<property name="maxStringLiteralLength">65535</property>-->
		<!--<property name="sequnceHandlerType">0</property>-->
		<!--<property name="backSocketNoDelay">1</property>-->
		<!--<property name="frontSocketNoDelay">1</property>-->
		<!--<property name="processorExecutor">16</property>-->
		<!--
			<property name="serverPort">8066</property> <property name="managerPort">9066</property> 
			<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
			<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
		<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
		<property name="handleDistributedTransactions">0</property>
		
			<!--
			off heap for merge/order/group/limit      1开启   0关闭
		-->
		<property name="useOffHeapForMerge">1</property>
		<!--
			单位为m
		-->
		<property name="memoryPageSize">1m</property>

		<!--
			单位为k
		-->
		<property name="spillsFileBufferSize">1k</property>

		<property name="useStreamOutput">0</property>

		<!--
			单位为m
		-->
		<property name="systemReserveMemorySize">384m</property>


		<!--是否采用zookeeper协调切换  -->
		<property name="useZKSwitch">true</property>


	</system>
	
	<!-- 全局SQL防火墙设置 -->
	<!-- 
	<firewall> 
	   <whitehost>
	      <host host="127.0.0.1" user="mycat"/>
	      <host host="127.0.0.2" user="mycat"/>
	   </whitehost>
       <blacklist check="false">
     </blacklist>
	</firewall>
	-->
	
	<user name="root">
		<property name="password">123456</property>
		<property name="schemas">im_config</property> <!-- add by evan-->
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>

<!-- add by evan  -->
<user name="appuse">
	<property name="password">2pm5566</property>
	<property name="schemas">im_config,im_admin,im_wallet</property>
</user>


</mycat:server>

*** grant

grant all privileges on *.* to  'appuse'@'192.168.10.*' IDENTIFIED BY '2pm5566';

'192.168.10.*'  这个写法有问题的  要不就直接 %

#以后授权用下面这个 精准些 
grant all privileges on *.* to  'aaaa'@'10.3.101.2' IDENTIFIED BY '66666';

*** mycat test

本地访问  就可以看到上面的三个库了 
 mysql -uappuse -p2pm5566 -h127.0.0.1 -P8066

远程访问

 mysql -uappuse -p2pm5566 -h192.168.10.211 -P8066

*** troubleshooting

重启mycat 得重连
mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query


关闭主mysql mycat 还是可以 show databases 等等 查


#这些操作主库得在 也就 wirte host
mysql> use im_admin;
Database changed
mysql> show tables;
ERROR 1184 (HY000): Connection refused
mysql> create table t1(name varchar(20),fa char(1));
ERROR 1184 (HY000): Connection refused
mysql>  insert into t1 values("e","m");
ERROR 1184 (HY000): Connection refused



关闭从 依然可以查看 show tablesStart services
除非你readonly ?

see also

Linux下Mycat安装配置和使用



http://www.mycat.io/document/mycat-definitive-guide.pdf 书下载





MyCat分库分表策略详解


MyCat简单使用与配置


数据库中间件Mycat+SpringBoot完成分库分表


Mysql中间件】Mycat安装部署+读写分离


配置文件详解

MyCat配置文件详解--server.xml

检索自“https://wiki.linuxsa.org/index.php?title=Mycat&oldid=677”