mycat的使用---sqlserver和mysql

作者：Cpp五条 | 2024-04-11 20:09:56

踩

mycat怎么连接sqlserver数据库

数据库中间件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"?>

<!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>
     









</system>
<user name="sa">
<property name="password">jdd</property>
<property name="schemas">DataCenter_Odds</property>
</user>



</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"?>

<!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>
<property name="count">2</property>
<property name="virtualBucketTimes">160</property>


</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">

<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之间的不兼容问题

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Cpp五条/article/detail/407153