MyCat实现mysql读写分离
一、环境准备1.1 确认当前环境角色IP用途Master192.168.10.110写Slave1192.168.10.111读Slave2192.168.10.112读MyCat192.168.10.113中间件1.2 确保主从复制已配置好在 Master 上创建测试库和表CREATE DATABASE ha; USE ha; CREATE TABLE test ( id INT, name VARCHAR(20), address VARCHAR(50) ); INSERT INTO test VALUES(1, master, from_master);在 Slave1/Slave2 上确认数据已同步USE ha; SELECT * FROM test;1.3 授权 MyCat 访问后端 MySQL在所有 MySQL 服务器Master、Slave1、Slave2上执行GRANT ALL PRIVILEGES ON *.* TO mycat% IDENTIFIED BY 123456; FLUSH PRIVILEGES;二、安装 JDKMyCat 依赖在 MyCat 服务器192.168.10.113上操作# 下载 JDK如果已有 JDK 可跳过 cd /opt wget https://mirrors.aliyun.com/centos/7/os/x86_64/Packages/java-1.8.0-openjdk-headless-1.8.0.262.b10-1.el7.x86_64.rpm rpm -ivh java-1.8.0-openjdk-headless-1.8.0.262.b10-1.el7.x86_64.rpm java -version配置环境变量vim /etc/profile.d/java.sh添加export JAVA_HOME/usr/local/jdk1.8.0 export PATH$JAVA_HOME/bin:$PATH export CLASSPATH$JAVA_HOME/jre/lib/ext:$JAVA_HOME/lib/tools.jar生效source /etc/profile.d/java.sh java -version # 确认显示 1.8.x三、下载安装 MyCatcd /opt tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/ cd /usr/local创建 MyCat 用户useradd mycat chown -R mycat:mycat /usr/local/mycat配置 MyCat 环境变量vim /etc/profile.d/mycat.sh添加export MYCAT_HOME/usr/local/mycat export PATH$MYCAT_HOME/bin:$PATH生效source /etc/profile.d/mycat.sh四、配置 MyCat4.1 配置server.xml用户认证vim /usr/local/mycat/conf/server.xml找到user nameroot部分替换或修改为user namemycat property namepassword123456/property property nameschemasha/property /user user nameuser property namepassworduser/property property nameschemasha/property property namereadonlytrue/property /user4.2 配置schema.xml读写分离核心先备份原文件mv /usr/local/mycat/conf/schema.xml /usr/local/mycat/conf/schema.xml.bak创建新配置vim /usr/local/mycat/conf/schema.xml写入以下内容?xml version1.0? !DOCTYPE mycat:schema SYSTEM schema.dtd mycat:schema xmlns:mycathttp://io.mycat/ !-- 逻辑库 ha -- schema nameha checkSQLschemafalse sqlMaxLimit100 dataNodedn1 /schema !-- 数据节点 -- dataNode namedn1 dataHostdthost databaseha / !-- 数据主机 -- dataHost namedthost maxCon500 minCon10 balance1 writeType0 dbTypemysql dbDrivernative switchType-1 slaveThreshold100 heartbeatselect user()/heartbeat !-- 主库写 -- writeHost hostmaster url192.168.10.110:3306 usermycat password123456 !-- 从库1读 -- readHost hostslave1 url192.168.10.111:3306 usermycat password123456 / !-- 从库2读 -- readHost hostslave2 url192.168.10.112:3306 usermycat password123456 / /writeHost /dataHost /mycat:schema五、启动 MyCat# 切换到 mycat 用户启动 su - mycat /usr/local/mycat/bin/mycat start exit查看启动日志cat /usr/local/mycat/logs/wrapper.log查看端口MyCat 默认端口 8066netstat -antulp | grep 8066六、验证读写分离6.1 连接 MyCatmysql -u mycat -p123456 -h 192.168.10.113 -P 80666.2 查看逻辑库和表SHOW DATABASES; USE ha; SHOW TABLES; SELECT * FROM test;应该能看到id1, namemaster, addressfrom_master。6.3 验证读写分离步骤1停止从库复制制造主从不一致在Slave1和Slave2上分别执行STOP SLAVE;步骤2在从库插入标记数据Slave1USE ha; INSERT INTO test VALUES(10, slave1, from_slave1);Slave2USE ha; INSERT INTO test VALUES(20, slave2, from_slave2);步骤3通过 MyCat 多次查询USE ha; SELECT * FROM test;执行 3-5 次观察结果第1次可能看到id10, 1第2次可能看到id20, 1第3次可能看到id10, 1轮询如果两次结果不同说明读写分离配置成功步骤4验证写走主库通过 MyCat 插入INSERT INTO test VALUES(88, write_test, from_mycat);然后直接连Master查询 → 能看到id88直接连Slave1/Slave2查询STOP SLAVE未恢复→ 看不到id88步骤5恢复主从同步在 Slave1 和 Slave2 上START SLAVE;七、配置参数说明参数值含义balance1读请求在 readHost 间轮询writeHost 不负担读writeType0只写第一个 writeHostmasterswitchType-1不自动切换主库sqlMaxLimit100查询默认返回最大行数
)
