数据库中间件mycat的使用
一、环境 windows server 2008r2（8核 16G内存） 、 sqlserver 、 mysql 、 mycat1.5.2
二、测试前端代码（只提供C#，为了解决公司原有老项目 java的到mycat的github上面自行查找）
数据库操作如下
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
namespace ConsoleApplication1
{
public class MySQLHelper
{
private static string connectionString = "Server=10.10.3.234;Port=8066;Database=DataCenter_Odds;User=sa;Password=123456;";
private static string SET_ENCODING="utf8";
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
try
{
connection.Open();
}
catch(Exception exp)
{
}
finally
{
MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
command.Fill(ds);
}
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
return ds;
}
}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
{
try
{
int rows = 0;
try
{
connection.Open();
}
catch (Exception exp)
{
}
finally
{
rows = cmd.ExecuteNonQuery();
}
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string[] arrSql)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
try
{
connection.Open();
MySqlCommand cmdEncoding = new MySqlCommand(SET_ENCODING, connection);
cmdEncoding.ExecuteNonQuery();
int rows = 0;
foreach (string strN in arrSql)
{
using (MySqlCommand cmd = new MySqlCommand(strN, connection))
{
rows += cmd.ExecuteNonQuery();
}
}
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
finally
{
connection.Close();
}
}
}
}
}
测试代码如下
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using log4net;
using log4net.Config;
using System.IO;
using System.Data;
namespace ConsoleApplication1
{
public class Program
{
public static Guid executeFunctionMysql()
{
Guid str = new Guid();
DataSet dataset = MySQLHelper.Query("select * from T_TEST1 where age > 15");
//DataSet dataset = MySQLHelper.Query("select * from actor where first_name = 'PENELOPE' and last_name='GUINESS' limit 1");
DataTable dt = dataset.Tables[0];
//遍历行
foreach (DataRow dr in dt.Rows)
{
//遍历列
for (int i = 0; i < dt.Columns.Count; i++)
{
Console.Write(dr[i].ToString() + " - " );
}
Console.WriteLine();
}
return str;
}
public static int insertData()
{
DateTime start = DateTime.Now;
Console.WriteLine(" 开始时间 " + start.ToString("yyyy-MM-dd HH:mm:ss,sss"));
Random rand = new Random(10000);
for (int i=1; i<10000; i++)
{
MySQLHelper.ExecuteSql("insert into T_TEST1(name,age) values('aaa_"+i+"',"+rand.Next(10000)+")");
}
DateTime end = DateTime.Now;
Console.WriteLine(" 结束时间 " + end.ToString("yyyy-MM-dd HH:mm:ss,sss"));
Console.WriteLine("共花费：" + (end -start).TotalMilliseconds + " 毫秒");
return 0;
}
static void Main(string[] args)
{
XmlConfigurator.ConfigureAndWatch(new FileInfo(AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "log4net.config"));
Console.Write(insertData());
Console.Write(executeFunctionMysql());
Console.Read();
}
}
}
mycat1.5配置如下
（1）、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://org.opencloudb/">
<system>
<property name="defaultSqlParser">druidparser</property>
<property name="useCompression">1</property>
<property name="processorBufferChunk">40960</property>
<property name="processors">32</property>
<property name="processorExecutor">32</property>
<property name="maxStringLiteralLength">65535</property>
<property name="sequnceHandlerType">0</property>
<property name="backSocketNoDelay">1</property>
<property name="frontSocketNoDelay">1</property>
<property name="idleTimeout">300000</property>
<property name="frontWriteQueueSize">4096</property>
<!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</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="mutiNodeLimitType">1</property> 0：开启小数量级（默认） ；1：开启亿级数据排序
<property name="mutiNodePatchSize">100</property> 亿级数量排序批量
<property name="processors">32</property> <property name="processorExecutor">32</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> -->
</system>
<user name="sa">
<property name="password">jdd</property>
<property name="schemas">DataCenter_Odds</property>
</user>
<!-- <cluster> <node name="cobar1"> <property name="host">127.0.0.1</property>
<property name="weight">1</property> </node> </cluster> -->
<!-- <quarantine> <host name="1.2.3.4"> <property name="user">test</property>
</host> </quarantine> -->
</mycat:server>
（2）、schema.xml配置
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema name="DataCenter_Odds" checkSQLschema="false" sqlMaxLimit="100">
<table name="T_TEST1_1" dataNode="dn1"/>
<table name="T_TEST1" dataNode="dn1,dn2" rule="mod-long"/>
</schema>
<dataNode name="dn1" dataHost="localhost1" database="DataCenter_Odds" />
<dataNode name="dn2" dataHost="localhost2" database="DataCenter_Odds" />
<dataHost name="localhost1" maxCon="1000" minCon="1" balance="1" writeType="0" dbType="sqlserver" dbDriver="jdbc">
<heartbeat></heartbeat>
<connectionInitSql></connectionInitSql>
<writeHost host="hostM1" url="jdbc:sqlserver://10.10.97.232:1433" user="sa" password="123456">
<readHost host="hostS1" url="jdbc:sqlserver://10.10.97.232:1433" user="sa" password="123456"></readHost>
</writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="1" balance="1" writeType="0" dbType="sqlserver" dbDriver="jdbc">
<heartbeat></heartbeat>
<connectionInitSql></connectionInitSql>
<writeHost host="hostM2" url="jdbc:sqlserver://10.10.97.232:1433" user="sa" password="123456">
<readHost host="hostS2" url="jdbc:sqlserver://10.10.97.231:1433" user="sa" password="123456"></readHost>
</writeHost>
</dataHost>
</mycat:schema>
(3)、wrapper配置
#********************************************************************
# Wrapper Properties
#********************************************************************
# Java Application
wrapper.java.command=java
wrapper.working.dir=..
# Java Main class. This class must implement the WrapperListener interface
# or guarantee that the WrapperManager class is initialized. Helper
# classes are provided to do this for you. See the Integration section
# of the documentation for details.
wrapper.java.mainclass=org.tanukisoftware.wrapper.WrapperSimpleApp
set.default.REPO_DIR=lib
set.APP_BASE=.
# Java Classpath (include wrapper.jar) Add class path elements as
# needed starting from 1
wrapper.java.classpath.1=lib/wrapper.jar
wrapper.java.classpath.2=conf
wrapper.java.classpath.3=%REPO_DIR%/*
# Java Library Path (location of Wrapper.DLL or libwrapper.so)
wrapper.java.library.path.1=lib
# Java Additional Parameters
#wrapper.java.additional.1=
wrapper.java.additional.1=-DMYCAT_HOME=.
wrapper.java.additional.2=-server
wrapper.java.additional.3=-XX:MaxPermSize=512M
wrapper.java.additional.4=-XX:+AggressiveOpts
wrapper.java.additional.5=-XX:MaxDirectMemorySize=2G
wrapper.java.additional.6=-Dcom.sun.management.jmxremote
wrapper.java.additional.7=-Dcom.sun.management.jmxremote.port=1984
wrapper.java.additional.8=-Dcom.sun.management.jmxremote.authenticate=false
wrapper.java.additional.9=-Dcom.sun.management.jmxremote.ssl=false
wrapper.java.additional.10=-Xmx1G
wrapper.java.additional.11=-Xms1G
# Initial Java Heap Size (in MB)
#wrapper.java.initmemory=3
wrapper.java.initmemory=512
# Maximum Java Heap Size (in MB)
#wrapper.java.maxmemory=64
wrapper.java.maxmemory=512
# Application parameters. Add parameters as needed starting from 1
wrapper.app.parameter.1=org.opencloudb.MycatStartup
wrapper.app.parameter.2=start
#********************************************************************
# Wrapper Logging Properties
#********************************************************************
# Format of output for the console. (See docs for formats)
wrapper.console.format=PM
# Log Level for console output. (See docs for log levels)
wrapper.console.loglevel=INFO
# Log file to use for wrapper output logging.
wrapper.logfile=logs/wrapper.log
# Format of output for the log file. (See docs for formats)
wrapper.logfile.format=LPTM
# Log Level for log file output. (See docs for log levels)
wrapper.logfile.loglevel=INFO
# Maximum size that the log file will be allowed to grow to before
# the log is rolled. Size is specified in bytes. The default value
# of 0, disables log rolling. May abbreviate with the 'k' (kb) or
# 'm' (mb) suffix. For example: 10m = 10 megabytes.
wrapper.logfile.maxsize=0
# Maximum number of rolled log files which will be allowed before old
# files are deleted. The default value of 0 implies no limit.
wrapper.logfile.maxfiles=0
# Log Level for sys/event log output. (See docs for log levels)
wrapper.syslog.loglevel=NONE
#********************************************************************
# Wrapper Windows Properties
#********************************************************************
# Title to use when running as a console
wrapper.console.title=Mycat-server
#********************************************************************
# Wrapper Windows NT/2000/XP Service Properties
#********************************************************************
# WARNING - Do not modify any of these properties when an application
# using this configuration file has been installed as a service.
# Please uninstall the service before modifying this section. The
# service can then be reinstalled.
# Name of the service
wrapper.ntservice.name=mycat
# Display name of the service
wrapper.ntservice.displayname=Mycat-server
# Description of the service
wrapper.ntservice.description=The project of Mycat-server
# Service dependencies. Add dependencies as needed starting from 1
wrapper.ntservice.dependency.1=
# Mode in which the service is installed. AUTO_START or DEMAND_START
wrapper.ntservice.starttype=AUTO_START
# Allow the service to interact with the desktop.
wrapper.ntservice.interactive=false
wrapper.ping.timeout=120
configuration.directory.in.classpath.first=conf
（4）、rule.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:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/">
<tableRule name="rule1">
<rule>
<columns>id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="rule2">
<rule>
<columns>user_id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<tableRule name="mod-long">
<rule>
<columns>age</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-murmur">
<rule>
<columns>id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-month">
<rule>
<columns>create_date</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<tableRule name="latest-month-calldate">
<rule>
<columns>calldate</columns>
<algorithm>latestMonth</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-rang-mod">
<rule>
<columns>id</columns>
<algorithm>rang-mod</algorithm>
</rule>
</tableRule>
<tableRule name="jch">
<rule>
<columns>id</columns>
<algorithm>jump-consistent-hash</algorithm>
</rule>
</tableRule>
<function name="murmur"
class="org.opencloudb.route.function.PartitionByMurmurHash">
<property name="seed">0</property><!-- 默认是0 -->
<property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
<property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
<!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 -->
<!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 -->
</function>
<function name="hash-int"
class="org.opencloudb.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
<function name="rang-long"
class="org.opencloudb.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
<function name="mod-long" class="org.opencloudb.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
<function name="func1" class="org.opencloudb.route.function.PartitionByLong">
<property name="partitionCount">8</property>
<property name="partitionLength">128</property>
</function>
<function name="latestMonth"
class="org.opencloudb.route.function.LatestMonthPartion">
<property name="splitOneDay">24</property>
</function>
<function name="partbymonth"
class="org.opencloudb.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2015-01-01</property>
</function>
<function name="rang-mod" class="org.opencloudb.route.function.PartitionByRangeMod">
<property name="mapFile">partition-range-mod.txt</property>
</function>
<function name="jump-consistent-hash" class="org.opencloudb.route.function.PartitionByJumpConsistentHash">
<property name="totalBuckets">3</property>
</function>
</mycat:rule>
通过以上的操作 即可完成mycat的相关配置
备注：在使用mycat的时候 首先要知道 mycat是对mysql的封装 所以客队要以访问mysql的方式连接
同时需要注意不同的sql之间的不兼容问题
相关推荐
MyCAT 是一个彻底开源的，面向企业应用开发的“大数据库集群” 支持事务、ACID、可以替代Mysql的加强版数据库, 一个可以视为“Mysql”集群的企业级数据库，用来替代昂贵的Oracle集群,一个融合内存缓存技术、Nosql...
Mycat是一个广受好评的数据库中间件。支持MySQL、Oracle、DB2、SQL Server、PostgreSQL等DB的常见SQL语法。
支持MySQL、Oracle、DB2、SQL Server、PostgreSQL等DB的常见SQL语法 遵守Mysql原生协议，跨语言，跨平台，跨数据库的通用中间件代理。 基于心跳的自动故障切换，支持读写分离，支持MySQL主从，以及galera cluster...
Mycat发展到目前版本，已经不在是一个单纯的MySQL代理了，它的后端可以支持MySQL、SQL Server、Oracle、DB2、PostgreSQL等主流数据库，也支持MongoDB这种新型NOSQL方式的存储，未来还会支持更多类型的存储。...
Mycat发展到目前版本，已经不在是一个单纯的MySQL代理了，它的后端可以支持MySQL、SQL Server、Oracle、DB2、PostgreSQL等主流数据库，也支持MongoDB这种新型NOSQL方式的存储，未来还会支持更多类型的存储。...
mycat-server 1.6 源码包 可直接运行 1、需要自己配置 shcema.xml rule 等规则 2、启动mysql 3、网上查找入门搭建篇
MyCAT是一款由阿里Cobar演变而来...MyCAT原理MyCAT主要是通过对SQL的拦截，然后经过一定规则的分片解析、路由分析、读写分离分析、缓存分析等，然后将SQL发给后端真实的数据块，并将返回的结果做适当处理返回给客户端。
分库分表工具，支持mysql oracle， sqlserver win版本
支持JDBC连接ORACLE、DB2、SQL Server，将其模拟为MySQL Server使用； 支持galera for mysql集群，percona-cluster或者mariadb cluster，提供高可用性数据分片集群； 自动故障切换，高可用性； 支持读写分离，支持...
Mycat 发展到目前的版本，已经不是一个单纯的 MySQL 代理了，它的后端可以支持 MySQL、SQL Server、 Oracle、DB2、PostgreSQL 等主流数据库，也支持 MongoDB 这种新型 NoSQL 方式的存储，未来还会支持更 多类型的...
3、一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群 4、一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server 5、结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品 ...
一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品 一个新颖的数据库中间件产品 以上是官方说明。其实就是数据库的连接池。mysql proxy...
mycat window 安装包，博客中包含 使用 MyCAT实现MySQL的读写分离的文章 什么是MYCAT 一个彻底开源的，面向企业应用开发的大数据库集群 支持事务、ACID、可以替代MySQL的加强版数据库 一个可以视为MySQL集群的企业级...
从定义和分类来看，MyCat是一个开源的分布式数据库系统，是一个实现了MySQL协议的的Server，前端用户可以把它看作是一个数据库代理，用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生（Native）协议与多个...
Mycat发展到目前，已经不是一个单纯的MySQL代理了，它的后端可以支持MySQL、SQL Server、Oracle、DB2、PostgreSQL等主流数据库，也支持MongoDB这种新型NoSQL方式的存储，未来还会支持更多类型的存储。
并且作为一种新型的现代企业数据库产品，MyCAT结合了传统数据库和新型分布式数据仓库。 总之，MyCAT 是一个全新的数据库中间件。 Mycat 的目标是将当前的单机数据库和应用以低成本平滑迁移到云端，解决数据存储和...
mycat 1.6.7 最新版本，已调试好可用，来自于mycat官网 支持MySQL、Oracle、DB2、SQL Server、PostgreSQL等DB的常见SQL语法 遵守Mysql原生协议，跨语言，跨平台，跨数据库的通用中间件代理。
从定义和分类来看，MyCat是一个开源的分布式数据库系统，是一个实现了MySQL协议的的Server，前端用户可以把它看作是一个数据库代理，用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生（Native）协议与多个...
server-id修改，这是mysql服务唯一标识，使用自然数。 server-id Master 唯一标识数字必须小于Slave 唯一标识数字. 日志文件命名,开启日志功能。此日志是命令日志。就是记录主库中执行的所的SQL