设计联盟网站,上海网站建设乐云seo模板中心,单位邮箱一般用什么邮箱,渭南是哪个省1、mycat介绍
Mycat 是一个开源的分布式数据库系统#xff0c;但是由于真正的数据库需要存储引擎#xff0c;而 Mycat 并没有存 储引擎#xff0c;所以并不是完全意义的分布式数据库系统。MyCat是目前最流行的基于Java语言编写的数据库中间件#xff0c;也可以理解为是数据…1、mycat介绍
Mycat 是一个开源的分布式数据库系统但是由于真正的数据库需要存储引擎而 Mycat 并没有存 储引擎所以并不是完全意义的分布式数据库系统。MyCat是目前最流行的基于Java语言编写的数据库中间件也可以理解为是数据库代理。在架构体系中是位于数据库和应用层之间的一个组件并且对于应用层是透明的即数据库 感受不到mycat的存在认为是直接连接的mysql数据库实际上是连接的mycat,mycat实现了mysql的原生协议。MyCat是基于阿里开源的Cobar产品而研发Cobar的稳定性、可靠性、优秀的架构和性能以及众多成熟的使用案例使得MyCat变得非常的强大。mycat的三大功能分库分表、读写分离、主从切换
2、mycat配置
Mycat conf目录下有三个重要配置文件分别是
schema.xml定义逻辑库表分片节点等内容rule.xml定义分片规则server.xml定义用户以及系统相关变量
schema.xml
?xml version1.0?
!DOCTYPE mycat:schema SYSTEM schema.dtd
mycat:schema xmlns:mycathttp://io.mycat/!-- --schema nameTESTDB checkSQLschematrue sqlMaxLimit100 randomDataNodedn1!-- auto sharding by id (long) --!--splitTableNames 启用table name 属性使用逗号分割配置多个表,即多个表使用这个配置--table nametravelrecord,address dataNodedn1,dn2,dn3 ruleauto-sharding-long splitTableNames true/!-- table nameoc_call primaryKeyID dataNodedn1$0-743 rulelatest-month-calldate/ --/schema!-- 数据库节点 --dataNode namedn1 dataHostlocalhost1 databasedb1 /dataNode namedn2 dataHostlocalhost1 databasedb2 /dataNode namedn3 dataHostlocalhost1 databasedb3 /!--dataNode namedn4 dataHostsequoiadb1 databaseSAMPLE /dataNode namejdbc_dn1 dataHostjdbchost databasedb1 /dataNode namejdbc_dn2 dataHostjdbchost databasedb2 /dataNode namejdbc_dn3 dataHostjdbchost databasedb3 / --dataHost namelocalhost1 maxCon1000 minCon10 balance0writeType0 dbTypemysql dbDrivernative switchType1 slaveThreshold100heartbeatselect user()/heartbeat!-- 配置的写库 --writeHost hosthostM1 urllocalhost:3306 userrootpassword123456/writeHost!-- writeHost hosthostM2 urllocalhost:3316 userroot password123456/ --/dataHost!--dataHost namesequoiadb1 maxCon1000 minCon1 balance0 dbTypesequoiadb dbDriverjdbcheartbeat /heartbeatwriteHost hosthostM1 urlsequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE userjifeng passwordjifeng/writeHost/dataHostdataHost nameoracle1 maxCon1000 minCon1 balance0 writeType0 dbTypeoracle dbDriverjdbc heartbeatselect 1 from dual/heartbeatconnectionInitSqlalter session set nls_date_formatyyyy-mm-dd hh24:mi:ss/connectionInitSqlwriteHost hosthostM1 urljdbc:oracle:thin:127.0.0.1:1521:nange userbase password123456 /writeHost /dataHostdataHost namejdbchost maxCon1000 minCon1 balance0 writeType0 dbTypemongodb dbDriverjdbcheartbeatselect user()/heartbeatwriteHost hosthostM urlmongodb://192.168.0.99/test useradmin password123456 /writeHost /dataHostdataHost namesparksql maxCon1000 minCon1 balance0 dbTypespark dbDriverjdbcheartbeat /heartbeatwriteHost hosthostM1 urljdbc:hive2://feng01:10000 userjifeng passwordjifeng/writeHost /dataHost --!-- dataHost namejdbchost maxCon1000 minCon10 balance0 dbTypemysqldbDriverjdbc heartbeatselect user()/heartbeat writeHost hosthostM1urljdbc:mysql://localhost:3306 userroot password123456 /writeHost/dataHost --
/mycat:schemarule.xml
?xml version1.0 encodingUTF-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:mycathttp://io.mycat/tableRule namerule1rulecolumnsid/columnsalgorithmfunc1/algorithm/rule/tableRuletableRule namesharding-by-daterulecolumnscreateTime/columnsalgorithmpartbyday/algorithm/rule/tableRuletableRule namerule2rulecolumnsuser_id/columnsalgorithmfunc1/algorithm/rule/tableRuletableRule namesharding-by-intfilerulecolumnssharding_id/columnsalgorithmhash-int/algorithm/rule/tableRuletableRule nameauto-sharding-longrulecolumnsid/columnsalgorithmrang-long/algorithm/rule/tableRuletableRule namemod-longrulecolumnsid/columnsalgorithmmod-long/algorithm/rule/tableRuletableRule namesharding-by-murmurrulecolumnsid/columnsalgorithmmurmur/algorithm/rule/tableRuletableRule namecrc32slotrulecolumnsid/columnsalgorithmcrc32slot/algorithm/rule/tableRuletableRule namesharding-by-monthrulecolumnscreate_time/columnsalgorithmpartbymonth/algorithm/rule/tableRuletableRule namelatest-month-calldaterulecolumnscalldate/columnsalgorithmlatestMonth/algorithm/rule/tableRuletableRule nameauto-sharding-rang-modrulecolumnsid/columnsalgorithmrang-mod/algorithm/rule/tableRuletableRule namejchrulecolumnsid/columnsalgorithmjump-consistent-hash/algorithm/rule/tableRulefunction namemurmurclassio.mycat.route.function.PartitionByMurmurHashproperty nameseed0/property!-- 默认是0 --property namecount2/property!-- 要分片的数据库节点数量必须指定否则没法分片 --property namevirtualBucketTimes160/property!-- 一个实际的数据库节点被映射为这么多虚拟节点默认是160倍也就是虚拟节点数是物理节点数的160倍 --!-- property nameweightMapFileweightMapFile/property 节点的权重没有指定权重的节点默认是1。以properties文件的格式填写以从0开始到count-1的整数值也就是节点索引为key以节点权重值为值。所有权重值必须是正整数否则以1代替 --!-- property namebucketMapPath/etc/mycat/bucketMapPath/property用于测试时观察各物理节点与虚拟节点的分布情况如果指定了这个属性会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件没有默认值如果不指定就不会输出任何东西 --/functionfunction namecrc32slotclassio.mycat.route.function.PartitionByCRC32PreSlotproperty namecount2/property!-- 要分片的数据库节点数量必须指定否则没法分片 --/functionfunction namehash-intclassio.mycat.route.function.PartitionByFileMapproperty namemapFilepartition-hash-int.txt/property/functionfunction namerang-longclassio.mycat.route.function.AutoPartitionByLongproperty namemapFileautopartition-long.txt/property/functionfunction namemod-long classio.mycat.route.function.PartitionByMod!-- how many data nodes --property namecount3/property/functionfunction namefunc1 classio.mycat.route.function.PartitionByLongproperty namepartitionCount8/propertyproperty namepartitionLength128/property/functionfunction namelatestMonthclassio.mycat.route.function.LatestMonthPartionproperty namesplitOneDay24/property/functionfunction namepartbymonthclassio.mycat.route.function.PartitionByMonthproperty namedateFormatyyyy-MM-dd/propertyproperty namesBeginDate2015-01-01/property/functionfunction namepartbydayclassio.mycat.route.function.PartitionByDateproperty namedateFormatyyyy-MM-dd/propertyproperty namesNaturalDay0/propertyproperty namesBeginDate2014-01-01/propertyproperty namesEndDate2014-01-31/propertyproperty namesPartionDay10/property/functionfunction namerang-mod classio.mycat.route.function.PartitionByRangeModproperty namemapFilepartition-range-mod.txt/property/functionfunction namejump-consistent-hash classio.mycat.route.function.PartitionByJumpConsistentHashproperty nametotalBuckets3/property/function
/mycat:ruleserver.xml
?xml version1.0 encodingUTF-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:mycathttp://io.mycat/systemproperty namenonePasswordLogin0/property !-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0设置为1则需要指定默认账户--property nameignoreUnknownCommand0/property!-- 0遇上没有实现的报文(Unknown command:),就会报错、1为忽略该报文返回ok报文。在某些mysql客户端存在客户端已经登录的时候还会继续发送登录报文,mycat会报错,该设置可以绕过这个错误--property nameuseHandshakeV101/propertyproperty nameremoveGraveAccent1/propertyproperty nameuseSqlStat0/property !-- 1为开启实时统计、0为关闭 --property nameuseGlobleTableCheck0/property !-- 1为开启全加班一致性检测、0为关闭 --property namesqlExecuteTimeout300/property !-- SQL 执行超时 单位:秒--property namesequnceHandlerType1/property!--property namesequnceHandlerPattern(?:(\s*next\svalue\sfor\s*MYCATSEQ_(\w))(,|\)|\s)*)/propertyINSERT INTO travelrecord (id,user_id) VALUES (next value for MYCATSEQ_GLOBAL,xxx);--!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况--property namesequnceHandlerPattern(?:(\s*next\svalue\sfor\s*MYCATSEQ_(\w))(,|\)|\s)*)/propertyproperty namesubqueryRelationshipCheckfalse/property !-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false --property namesequenceHanlderClassio.mycat.route.sequence.handler.HttpIncrSequenceHandler/property!-- property nameuseCompression1/property-- !--1为开启mysql压缩协议--!-- property namefakeMySQLVersion5.6.20/property-- !--设置模拟的MySQL版本号--!-- property nameprocessorBufferChunk40960/property --!-- property nameprocessors1/property property nameprocessorExecutor32/property --!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool --property nameprocessorBufferPoolType0/property!--默认是65535 64K 用于sql解析时最大文本长度 --!--property namemaxStringLiteralLength65535/property--!--property namesequnceHandlerType0/property--!--property namebackSocketNoDelay1/property--!--property namefrontSocketNoDelay1/property--!--property nameprocessorExecutor16/property--!--property nameserverPort8066/property property namemanagerPort9066/property property nameidleTimeout300000/property property namebindIp0.0.0.0/propertyproperty namedataNodeIdleCheckPeriod300000/property 5 * 60 * 1000L; //连接空闲检查property namefrontWriteQueueSize4096/property property nameprocessors32/property --!--分布式事务开关0为不过滤分布式事务1为过滤分布式事务如果分布式事务内只涉及全局表则不过滤2为不过滤分布式事务,但是记录分布式事务日志--property namehandleDistributedTransactions0/property!--off heap for merge/order/group/limit 1开启 0关闭--property nameuseOffHeapForMerge0/property!--单位为m--property namememoryPageSize64k/property!--单位为k--property namespillsFileBufferSize1k/propertyproperty nameuseStreamOutput0/property!--单位为m--property namesystemReserveMemorySize384m/property!--是否采用zookeeper协调切换 --property nameuseZKSwitchfalse/property!-- XA Recovery Log日志路径 --!--property nameXARecoveryLogBaseDir.//property--!-- XA Recovery Log日志名称 --!--property nameXARecoveryLogBaseNametmlog/property--!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接--property namestrictTxIsolationfalse/propertyproperty nameuseZKSwitchtrue/property!--如果为0的话,涉及多个DataNode的catlet任务不会跨线程执行--property nameparallExecute0/property/system!-- 全局SQL防火墙设置 --!--白名单可以使用通配符%或着*--!--例如host host127.0.0.* userroot/--!--例如host host127.0.* userroot/--!--例如host host127.* userroot/--!--例如host host1*7.* userroot/--!--这些配置情况下对于127.0.0.1都能以root账户登录--!--firewallwhitehosthost host1*7.0.0.* userroot//whitehostblacklist checkfalse/blacklist/firewall--user nameroot defaultAccounttrueproperty namepassword123456/property!-- schemas配置连接的逻辑库 --property nameschemasTESTDB/propertyproperty namedefaultSchemaTESTDB/property!--No MyCAT Database selected 错误前会尝试使用该schema作为schema不设置则为null,报错 --!-- 表级 DML 权限设置 --!-- privileges checkfalseschema nameTESTDB dml0110 table nametb01 dml0000/tabletable nametb02 dml1111/table/schema/privileges --/useruser nameuserproperty namepassworduser/propertyproperty nameschemasTESTDB/propertyproperty namereadOnlytrue/propertyproperty namedefaultSchemaTESTDB/property/user/mycat:server
既然是直接当做数据库系统用所以直接用navicat连接mycat即可。
3、mysql主从复制的原理
mysql自带的功能主从复制是把一台主机的数据复制到其他一台或多台主机上
主从复制原理
主从同步过程中主服务器有一个工作线程I/O dump thread从服务器有两个工作线程I/O thread和SQL thread。主库把外界接收的SQL请求记录到自己的binlog日志中从库的I/O thread去请求主库的binlog日志并将binlog日志写到中继日志中然后从库重做中继日志的SQL语句。主库通过I/O dump thread给从库I/O thread传送binlog日志。
