1.mycat文档:https://github.com/MyCATApache/Mycat-doc 官方网站:http://www.mycat.org.cn/ 2.mycat的优点:
- 配置简单,灵活
- 可实现读写分离
- 可利用多种规则实现分库分表
- 心跳机制,自动踢出故障机组
- 免费开源,长期维护,社区活跃
mycat的缺点:
- 主要是分片之后有一些限制,如只能2表join
3.架构图:可实现读写分离,分库分表
4.所需软件,机器配置与ip地址
名称 | 版本 | 下载地址 |
mysql server | mysql Ver 14.14 Distrib 5.6.27, for Linux (x86_64) | yum源安装,yum源地址:http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm |
mycat | 1.3.0.3 | https://github.com/MyCATApache/Mycat-download/blob/master/1.3.0.3-release/Mycat-server-1.3.0.3-release-20150527095523-linux.tar.gz |
keepalived | http://www.keepalived.org/download.html |
机器名 | ip地址 | 配置 | 用途 |
mycat1 | 192.168.2.155/192.168.10.30 | 4G,4C | mycat+keepalived (主) |
mycat2 | 192.168.2.156/192.168.10.31 | 4G,4C | mycat+keepalived (备) |
db1 | 192.168.10.155 | 8G,4C | mysql master1 |
db2 | 192.168.10.156 | 8G,4C | mysql master2 |
db3 | 192.168.10.157 | 8G,4C | mysql slave1 |
5.安装,配置mysql server
- 获取官方yum源: wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm
- 安装源:yum install mysql57-community-release-el6-7.noarch.rpm
- 默认yum安装的是5.7,我们修改yum源,下载5.6的mysql server
- 进入yum源配置文件:cd /etc/yum.repos.d/
- 找到并编辑:vim mysql-community.repo
- 找到下面这些内容:enabled=1就是可用,把[mysql56-community]段的enabled=0改为enabled=1,相应的把[mysql57-community]段的改为0,保存退出
# Enable to use MySQL 5.6 [mysql56-community] name=MySQL 5.6 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/ enabled=0 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql [mysql57-community] name=MySQL 5.7 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/ enabled=1 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
- 查找安装包(会更新源,时间几分钟,看你的网速咯):yum list |grep mysql
- 如果顺利就会看到这一行:mysql-community-server.x86_64 5.6.27-2.el6
- 没错就是它,安装:yum install -y mysql-community-server.x86_6
- 启动(这一步会自动初始化一些内容):service mysqld start
- 修改root密码:mysqladmin -uroot --password ‘xxxxx’
- 修改配置文件,我测试时候的配置文件:
- db1:
- db2:
- db3:
6.从最上面的架构图中可以看出,这里只有一个主主(db1,db2),一个主从(db1,db3),下面分别配置主主,主从
7.mysql主主复制配置
8.把db3(192.168.10.157)设置为db1的从
9.至此,mysql的设置全部完成。
10.下载,安装mycat
- wget https://github.com/MyCATApache/Mycat-download/blob/master/1.3.0.3-release/Mycat-server-1.3.0.3-release-20150527095523-linux.tar.gz
- tar -zxf Mycat-server-1.3.0.3-release-20150527095523-linux.tar.gz
- 解压完成就可以使用,建议把解压后的文件夹放在/opt下,并建立mycat的软连接(ln -s “解压出来的文件夹” mycat),方便以后升级,维护。
11.配置mycat/conf/schema.xml
<?xml version="1.0"?> < !DOCTYPE mycat:schema SYSTEM "schema.dtd"> < mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema name="33hao" checkSQLschema="false" dataNode="dn1"> </schema> <schema name="sbtest" checkSQLschema="false" dataNode="dn2"> </schema> <schema name="jaydb" checkSQLschema="false" dataNode="dn3"> </schema> <schema name="transdb" checkSQLschema="false"> <table name="users" primaryKey="id" type="global" dataNode="dn4,dn5,dn6" />
<!-- random sharding using mod sharind rule --> <table name="orders" primaryKey="id" rule="mod-long" dataNode="dn4,dn5,dn6" /> </schema> <schema name="shopnc" checkSQLschema="false" dataNode="dn7"> </schema> <dataNode name="dn1" dataHost="localhost1" database="33hao" /> <dataNode name="dn2" dataHost="localhost2" database="sbtest" /> <dataNode name="dn3" dataHost="localhost1" database="jaydb" /> <dataNode name="dn4" dataHost="localhost4" database="transdb" /> <dataNode name="dn5" dataHost="localhost5" database="transdb" /> <dataNode name="dn6" dataHost="localhost6" database="transdb" /> <dataNode name="dn7" dataHost="localhost1" database="shopnc" />
<dataHost name="localhost1" maxCon="100000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="db1:3306" user="root" password="123456"> <!-- can have multi read hosts --> <readHost host="hostS1" url="db3:3306" user="root" password="123456"/> <readHost host="hostS2" url="db2:3306" user="root" password="123456"/> </writeHost> <writeHost host="hostM2" url="db2:3306" user="root" password="123456"> <!-- can have multi read hosts --> </writeHost> <!-- <writeHost host="hostM2" url="db2:3306" user="root" password="123456"/> --> </dataHost> <dataHost name="localhost2" maxCon="100000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="db1:3306" user="root" password="123456"> </writeHost> </dataHost> <dataHost name="localhost4" maxCon="100000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="db1:3306" user="root" password="123456"></writeHost> </dataHost> <dataHost name="localhost5" maxCon="100000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM2" url="db2:3306" user="root" password="123456"></writeHost> </dataHost> <dataHost name="localhost6" maxCon="100000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostS1" url="db3:3306" user="root" password="123456"></writeHost> </dataHost>
12.设置mycat/conf/server.xml
- 这里是针对mycat的设置,如外部访问mycat的端口,用户名,密码,mycat的数据库有哪些等
<?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="processors">32</property> <property name="processorExecutor">32</property> <property name="serverPort">3306</property> <property name="managerPort">9066</property>
</system> <user name="root"> <property name="password">123456</property> <property name="schemas">33hao,sbtest,jaydb,transdb,shopnc</property> </user> < /mycat:server> 13.启动mycat
- /opt/mycat/bin/mycat start
14.连接mycat
- 和连接mysql方法一样,如果连接不上,看看是否在没个节点上都授予了mycat机器通过root/123456(在schema.xml中配置的)的方式访问:
- mysql -uroot -p123456 -h127.0.0.1 -P3306
15.测试mycat
- 我开起了general_log,数据库任何操作都会被记录,依次打开三个db1 db2 db3的日志:tail -f /var/log/mysql/mysql-row.log
- 透过mycat连接数据库 mysql -uroot -p123456 -h127.0.0.1 -P3306
- 在相应的数据库中执行操作,并观察日志。
- 比如对33hao的写总是在db1上,然后replication到db2,db3,如果db1宕机,写总是在db2上,db1恢复后,db2不会被抢占写的角色。读在db2,db3上随机执行。
- 而transdb的orders表被均匀地复制到db1,db2,db3的transdb的orders表中,users则在所有mysql(这里为db1,db2,db3)中保存副本(因为在schema.xml中设置为:type="global")。