MySQL读写分离服务配置方式
目录
- mysql读写分离
- 原理
- 前提准备步骤
- 启动mycat之前需改变数据库用户的访问权限和设置新密码
- 启动mycat
- 最后一步
- 途中遇到的问题
- 总结
MySQL读写分离
使用mycat实现
原理
前提准备步骤
(都在代理服务器上执行)
安装jdk1.8(代理服务器上执行)
1.解压缩包:
tar -xzvf jdk-8u161-linux-x64.tar.gz -C /usr/local/Java
2.环境变量文件/etc/profile配置
export JAVA_HOME=/usr/local/java/jdk1.8.0_161 export JRE_HOME=${JAVA_HOME}/jre export CLASSPATH=.:${JAVA_HOME}/lib:${JRE_HOME}/lib:$CLASSPATH export JAVA_PATH=${JAVA_HOME}/bin:${JRE_HOME}/bin export PATH=$PATH:${JAVA_PATH}
注意:配置环境变量时,中间不能有空格
3. 使用source命令使修改即时生效,无需重启服务器
source /etc/profile
4. 解压缩包:
tar -zxvf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
(任意目录都可以)
配置mycat
server.XML
把下面 user name ="usr"等删掉
schema.xml
schema.xml是最主要的配置项,首先看我的配置文件database=“mytest” 必须是主从库都存在的数据库。
vim schema.xml (全部删完)
重新加进去
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"/> <dataNode name="dn1" dataHost="auth" database="mytest"/> <dataHost name=php"auth" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat> select user() </heartbeat> <writeHost host="hostM" url="192.168.202.145:3306" user="root" password="Nebula@编程客栈123"> <readHost host="hostS1" url="192.168.202.148:3306" user="root" password="Nebula@123" /> </writeHost> </dataHost> </mycat:schema>
启动mycat之前需改变数据库用户的访问权限和设置新密码
查询用户密码命令:select host,user,authentication_string from mysql.user
问题一: 增加远程访问
use mysql; update user set host='%' where user='root'; flush privileges; select host,user from user ;
问题二:密码编码方式不同,改
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY
对比
启动mycat
需进入bin目录下
- 启动:./mycat start(后台启动) ./mycat console(前台启动)
- 停止:./mycat stop
- 重启:./mycat restart
如果在启动时发现异常,在logs目录中查看日志。
- wrapper.log 为程序启动的日志,启动时的问题看这个
- mycat.log 为脚本执行时的日志,SQL脚本执行报错后的具体错误内容,查看这个文件。
- mycat.log是最新的错误日志,历史日志会根据时间生成目录保存。
成功的mycat.log
2021-11-26 23:24:44.684 INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionAcquired(PhysicalDatasource.java:514)) - connection id is 14
2021-11-26 23:24:44.685 INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.NewConnectionRespHandler.connectionAcquired(NewConnectionRespHandler.java:44)) - connectionAcquired MySQLConnection [id=14, lastTime=1637940284685, user=root,www.devze.com schema=test, old shema=test, borrowed=true, fromSlaveDB=true, threadId=18, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.202.151, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]2021-11-26 23:24:44.685 INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionAcquired(PhysicalDatasource.java:514)) - connection id is 152021-11-26 23:24:44.685 INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.NewConnectionRespHandler.connectionAcquired(NewConnectionRespHandler.java:44)) - connectionAcquired MySQLConnection [id=15, lastTime=1637940284685, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=true, threadId=19, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.202.151, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]2021-11-26 23:29:44.676 INFO [Timer0] (io.mycat.backend.datasource.PhysicalDatasource.createByIdleLitte(PhysicalDatasource.java:397)) - create connections ,because idle connection not enough ,cur is 5, minCon is 10 for hostS12021-11-26 23:29:44.685 INFO [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionAcquired(PhysicalDatasource.java:514)) - connection id is 16
成功的wrapper.log
vim wrapper.log
STATUS | wrapper | 2021/11/26 23:19:39 | TERM trapped. Shutting down. STATUS | wrapper | 2021/11/26 23:19:40 | <-- Wrapper Stopped STATUS | wrapper | 2021/11/26 23:19:41 | --> Wrapper Started as Daemon STATUS | wrapper | 2021/11/26 23:19:41 | Launching a JVM... INFO | jvm 1 | 2021/11/26 23:19:41 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0 INFO | jvm 1 | 2021/11/26 23:19:43 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org INFO | jvm 1 | 2021/11/26 23:19:43 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved. INFO | jvm 1 | 2021/11/26 23:19:43 | INFO | jvm 1 | 2021/11/26 23:19:44 | MyCAT Server startup successfully. see logs in logs/mycat.log
查看端口:netstat -anultp
8066业务端口 9066管理端口
最后一步
show @@help; #查看帮助
show @@heartbeat;#查看心跳RS_CODE 1 表示正常show @@datasource; #查看读写配置情况W 写 R 读
8066端口 在此进行业务操作 端口都在代理机上
途中遇到的问题
1.堆内存jvm不够
JVM exited while loading the application.
启动mycat后异常次 JVM exited while loading the application.
原因:初始化堆指定过小
INFO | jvm 1 | 2018/10/12 17:12:25 | # There is insufficient memory for the Java Runtime Environment to continujavascripte. INFO | jvm 1 | 2018/10/12 17:12:25 | # Native memory allocation (mmap) failed to map 715849728 bytes for committing reserved memory. INFO | jvm 1 | 2018/10/12 17:12:25 | # An error report file with more information is saved as: INFO | jvm 1 | 2018/10/12 17:12:25 | # /usr/local/software/mycat/hs_err_pid8956.log ERROR | wrapper | 2018/10/12 17:12:26 | JVM exited while loading the application. STATUS | wrapper | 2018/10/12 17:12:31 | Launching a JVM... INFO | jvm 2 | 2018/10/12 17:12:32 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0 INFO | jvm 2 | 2018/10/12 17:12:32 | Java HotSpot(TM) 64-Bit Server VM warning: INFO: os::commit_memory(0x0000000715550000, 715849728, 0) failed; error='Cannot alwww.devze.comlocate memory' (errno=12)
修改mycat/conf/wrapper.conf 第40行 wrapper.java.initmemory=30 将3改为30 ./mycat start 再次启动 ./mycat status 查看状态
问题我自己虚拟机本来就是30,并且加##默认
去除井号改30为40 成功
2.自己配置的问题
例如:没有添加好数据库test等。
总结
以上为个人经验,希望能给大家一个参考,也希望大家多多支持编程客栈(www.devze.com)。
精彩评论