mysql读写分离+mycat集群
2.优势 基于阿里开源的 Cobar 产品而研发，Cobar 的稳定性、可靠性、优秀的架构和性能以及众 多成熟的使用案例使得 MYCAT 一开始就拥有一个很好的起点，站在巨人的肩膀上，我们能 看到更远。业界优秀的开源项目和创新思路被广泛融入到 MYCAT 的基因中，使得 MYCAT 在很多方面都领先于目前其他一些同类的开源项目，甚至超越某些商业产品。 MYCAT 背后有一支强大的技术团队，其参与者都是 5 年以上软件工程师、架构师、DBA 等，优秀的技术团队保证了 MYCAT 的产品质量。 MYCAT 并不依托于任何一个商业公司，因此不像某些开源项目，将一些重要的特性封闭在 其商业产品中，使得开源项目成了一个摆设。
server.xml:
<?xml version=1.0 encoding=UTF-8?> <!DOCTYPE mycat:server SYSTEM server.dtd> <mycat:server xmlns:mycat=http://io.mycat/> <system> <property name=nonePasswordLogin>0</property> <property name=useHandshakeV10>1</property> <property name=useSqlStat>0</property> <property name=useGlobleTableCheck>0</property> <property name=sequnceHandlerType>2</property> <property name=subqueryRelationshipCheck>false</property> <property name=processorBufferPoolType>0</property> <property name=handleDistributedTransactions>0</property> <property name=useOffHeapForMerge>1</property> <property name=memoryPageSize>64k</property> <property name=spillsFileBufferSize>1k</property> <property name=useStreamOutput>0</property> <property name=systemReserveMemorySize>384m</property> <property name=useZKSwitch>false</property> </system> <!--这里是设置的 kgc 用户和虚拟逻辑库--> <user name=kgc defaultAccount=true> <property name=password>kgc123</property> <property name=schemas>kgc</property> </user> </mycat:server>
schema.xml:
<?xml version=1.0?> <!DOCTYPE mycat:schema SYSTEM schema.dtd> <mycat:schema xmlns:mycat=http://io.mycat/> <schema name=db_zhu checkSQLschema=false sqlMaxLimit=100> <table name=t_user dataNode=dn1,dn2 rule=mod-long /> </schema> <dataNode name=dn1 dataHost=cluster1 database=db_zhu /> <dataNode name=dn2 dataHost=cluster2 database=db_zhu /> mysql集群之基于Mycat实现读写分离 <dataHost name=cluster1 maxCon=1000 minCon=10 balance=3 writeType=1 dbType=mysql dbDriver=native switchType=1 slaveThreshold=100> <heartbeat>select user()</heartbeat> <writeHost host=W1 url=192.168.115.188:3306 user=root password=root> <readHost host=W1R1 url=192.168.115.188:3307 user=root password=root /> </writeHost> </dataHost> <dataHost name=cluster2 maxCon=1000 minCon=10 balance=3 writeType=1 dbType=mysql dbDriver=native switchType=1 slaveThreshold=100> <heartbeat>select user()</heartbeat> <writeHost host=W2 url=192.168.115.188:3316 user=root password=root> <readHost host=W2R1 url=192.168.115.188:3317 user=root password=root /> </writeHost> </dataHost> </mycat:schema>
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://io.mycat/> <tableRule name=mod-long> mysql集群之基于Mycat实现读写分离 <rule> <columns>id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <function name=mod-long class=io.mycat.route.function.PartitionByMod> <property name=count>2</property> </function> </mycat:rule>
balance 属性说明： 负载均衡类型，目前的取值有 3 种： 1. balance=0, 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。 2. balance=1，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的 说，当双 主 双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负 载均衡。 3. balance=2，所有读操作都随机的在 writeHost、readhost 上分发。 4. balance=3，所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不 负担读压 力， 注意 balance=3 只在 1.4 及其以后版本有，1.3 没有。
测试: --创建表，数据 CREATE TABLE `t_user` ( `id` int(32) NOT NULL AUTO_INCREMENT, `name` varchar(32) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; --测试结果：主库有写入数据，从库会同步数
6.配置 master
#搭建 master #创建目录 mkdir /data/mysql/master02 cd /data/mysql/master02 mkdir conf data chmod 777 * -R #创建配置文件 cd /data/mysql/master02/conf vim my.cnf #输入如下内容 [mysqld] log-bin=mysql-bin #开启二进制日志 server-id=1 #服务 id，不可重复 sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_B Y_ZERO ,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' #创建容器 docker create --name percona-master02 -v /data/mysql/master02/data:/var/lib/mysql -v /data/mysql/master02/conf:/etc/my.cnf.d -p 3316:3306 -e MYSQL_ROOT_PASSWORD=root percona:5.7.23 #启动 docker start percona-master02 && docker logs -f percona-master02 #创建同步账户以及授权 create user 'kgc'@'%' identified by 'kgc'; grant replication slave on *.* to 'kgc'@'%'; flush privileges; #查看 master 状态 show master status
7.配置 slave
#搭建从库 #创建目录 mkdir /data/mysql/slave02 cd /data/mysql/slave02 mkdir conf data chmod 777 * -R #创建配置文件 cd /data/mysql/slave02/conf vim my.cnf #输入如下内容 mysql集群之基于Mycat实现读写分离 [mysqld] server-id=2 #服务 id，不可重复 sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_B Y_ZERO ,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' #创建容器 docker create --name percona-slave02 -v /data/mysql/slave02/data:/var/lib/mysql -v /data/mysql/slave02/conf:/etc/my.cnf.d -p 3317:3306 -e MYSQL_ROOT_PASSWORD=root percona:5.7.23 #启动 docker start percona-slave02 && docker logs -f percona-slave02 #设置 master 相关信息 CHANGE MASTER TO master_host='192.168.115.211', master_user='kgc', master_password='kgc', master_port=3316, master_log_file='mysql-bin.000003', master_log_pos=737; #启动同步 start slave; #查看 master 状态 show slave status
--创建表，数据 CREATE TABLE `t_user` ( `id` int(32) NOT NULL AUTO_INCREMENT, `name` varchar(32) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
8.配置 mycat
(1) 安装 jdk 环境 (1) sudo mkdir /usr/lib/jvm (2) 并修改权限 (3)tar -zxvf jdk-8u141-linux-x64.tar.gz -C /usr/lib/jvm (4)sudo vim ~/.bashrc 文件末尾追加如下内容 export JAVA_HOME=/usr/lib/jvm/jdk1.8.0_141 export PATH=$JAVA_HOME/bin:$PATH mysql集群之基于Mycat实现读写分离 export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar (5)使环境变量生效 source ~/.bashrc (6)设置默认 jdk sudo update-alternatives --install /usr/bin/java java /usr/lib/jvm/jdk1.8.0_141/bin/java 300 sudo update-alternatives --install /usr/bin/javac javac /usr/lib/jvm/jdk1.8.0_141/bin/javac 300 sudo update-alternatives --install /usr/bin/jar jar /usr/lib/jvm/jdk1.8.0_141/bin/jar 300 sudo update-alternatives --install /usr/bin/javah javah /usr/lib/jvm/jdk1.8.0_141/bin/javah 300 sudo update-alternatives --install /usr/bin/javap javap /usr/lib/jvm/jdk1.8.0_141/bin/javap 300 (7)sudo update-alternatives --config java (8)测试是否安装成功 java -version javac -version (2) cd /data mkdir mycat cd mycat 通过 ftp 方式讲 mycat 安装包放到对应目录中 tar -xvf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz mv mycat mycat01 #重命名 cd mycat01 cd conf 修改配置三个配置
schema.xml
<?xml version=1.0?> <!DOCTYPE mycat:schema SYSTEM schema.dtd> <mycat:schema xmlns:mycat=http://io.mycat/> <schema name=db_zhu checkSQLschema=false sqlMaxLimit=100> <table name=t_user dataNode=dn1,dn2 rule=mod-long /> </schema> <dataNode name=dn1 dataHost=cluster1 database=db_zhu /> <dataNode name=dn2 dataHost=cluster2 database=db_zhu /> <dataHost name=cluster1 maxCon=1000 minCon=10 balance=3 writeType=1 dbType=mysql dbDriver=native switchType=1 slaveThreshold=100> <heartbeat>select user()</heartbeat> <writeHost host=W1 url=192.168.115.188:3306 user=root password=root> mysql集群之基于Mycat实现读写分离 <readHost host=W1R1 url=192.168.115.188:3307 user=root password=root /> </writeHost> </dataHost> <dataHost name=cluster2 maxCon=1000 minCon=10 balance=3 writeType=1 dbType=mysql dbDriver=native switchType=1 slaveThreshold=100> <heartbeat>select user()</heartbeat> <writeHost host=W2 url=192.168.115.188:3316 user=root password=root> <readHost host=W2R1 url=192.168.115.188:3317 user=root password=root /> </writeHost> </dataHost> </mycat:schema>
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://io.mycat/> <tableRule name=mod-long> <rule> <columns>id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <function name=mod-long class=io.mycat.route.function.PartitionByMod> <property name=count>2</property> </function> </mycat:rule>
server.xml
<?xml version=1.0 encoding=UTF-8?> mysql集群之基于Mycat实现读写分离 <!-- - - 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=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> mysql集群之基于Mycat实现读写分离 <!-- 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>db_zhu</property> mysql集群之基于Mycat实现读写分离 <!-- 表级 DML 权限设置 --> <!-- <privileges check=false> <schema name=TESTDB dml=0110 > <table name=tb01 dml=0000></table> <table name=tb02 dml=1111></table> </schema> </privileges> --> </user> </mycat:server>
重新启动 mycat 进行测试： ./startup_nowrap.sh && tail -f ../logs/mycat.log 测试 添加数据测试结果(主库，从库，逻辑库) 可以看到，数据已经从 2 个分片中进行了汇总。 9.Mycat 集群 mycat 做了数据库的代理，在高并发的情况下，会遇到单节点性能问题，所以需要部署多个 mycat 节点。 架构： mysql集
搭建多节点 mycat： cp mycat mycat2 -R vim wrapper.conf #设置 jmx 端口 wrapper.java.additional.7=-Dcom.sun.management.jmxremote.port=1985 vim server.xml #设置服务端口以及管理端口 <property name=serverPort>8067</property> <property name=managerPort>9067</property> #重新启动服务 ./startup_nowrap.sh tail -f ../logs/mycat.log 多节点的 mycat
