首页 / MYSQL / Mycat+MySQL 主从复制
Mycat+MySQL 主从复制
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mycat+MySQL 主从复制，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含14744字，纯文字阅读大概需要22分钟。
内容图文
mysqld]
#repl master库
server-id=6
log-bin=E:\MySQL\4306\logbin\mysql-bin
max_binlog_size=100M
binlog-format=MIXED
2、启动Master实例
3、Master实例上创建一个用于复制的帐户
mysql> use `mysql`; mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘localhost‘ IDENTIFIED BY ‘repl‘;
4、获取Master状态信息
#在session1锁表 mysql> FLUSH TABLES WITH READ LOCK; #在session2查看状态 mysql > SHOW MASTER STATUS;
5、在Master上释放读锁
mysql> UNLOCK TABLES;
6、在Slave上编辑配置信息
[mysqld] #repl slave库 server-id=7 relay_log=E:\MySQL\4307\logbin\mysql-relay-bin
7、启动Slave实例
8、在Slave上设置Master配置
mysql> change master to master_host=‘127.0.0.1‘, master_port=4306, master_user=‘repl‘, master_password=‘repl‘, master_log_file=‘mysql-bin.000001‘, master_log_pos=331;
二、Mycat搭建
1、JDK下载安装(jdk-7u80-windows-i586.exe)，并配置环境变量
新创建环境变量 JAVA_HOME D:\Java\jdk1.7.0_80 Classpath .;%JAVA_HOME%\lib;%JAVA_HOME%\lib\tools.jar MYCAT_HOME D:\MySQLSoftware\mycat PATH添加值 Path ;%JAVA_HOME%\bin;%JAVA_HOME%\jre\bin;%MYCAT_HOME%\bin
设置环境变量后cmd运行
java -version
2、Mycat解压(Mycat-server-1.6-RELEASE-20161028204710-win.tar.gz)，运行startup_nowrap.bat
rem startup_nowrap.bat REM check JAVA_HOME & java set "JAVA_CMD=%JAVA_HOME%\bin\java" if "%JAVA_HOME%" == "" goto noJavaHome if exist "%JAVA_HOME%\bin\java.exe" goto mainEntry :noJavaHome echo --------------------------------------------------- echo WARN: JAVA_HOME environment variable is not set. echo --------------------------------------------------- set "JAVA_CMD=java" :mainEntry REM set HOME_DIR set "CURR_DIR=%cd%" cd .. set "MYCAT_HOME=%cd%" cd %CURR_DIR% "%JAVA_CMD%" -server -Xms512M -Xmx1024M -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=1G -DMYCAT_HOME=%MYCAT_HOME% -cp "..\conf;..\lib\*" io.mycat.MycatStartup pauseView Code
如果执行报jave命令错误，可修改批处理中的变量为实例对象；内存不充足时可适当调整Xms、Xmx的大小。这种方式一般是在程序运行有问题时选用。
前面已经将Mycat添加到环境变量(不然要切换到对应目录)，可以在cmd下直接执行mycat
装载、启动服务
#装载成服务 mycat install #启动服务 mycat start
首次启动
STATUS | wrapper | 2017/03/02 14:09:26 | Starting the Mycat-server service... STATUS | wrapper | 2017/03/02 14:09:26 | --> Wrapper Started as Service STATUS | wrapper | 2017/03/02 14:09:27 | Launching a JVM... INFO | jvm 1 | 2017/03/02 14:09:27 | Error: missing `server‘ JVM at `D:\Java\jre7\bin\server\jvm.dll‘. INFO | jvm 1 | 2017/03/02 14:09:27 | Please install or use the JRE or JDK that contains these missing components. ERROR | wrapper | 2017/03/02 14:09:27 | JVM exited while loading the application. STATUS | wrapper | 2017/03/02 14:09:31 | Launching a JVM... INFO | jvm 2 | 2017/03/02 14:09:31 | Error: missing `server‘ JVM at `D:\Java\jre7\bin\server\jvm.dll‘. INFO | jvm 2 | 2017/03/02 14:09:31 | Please install or use the JRE or JDK that contains these missing components. ERROR | wrapper | 2017/03/02 14:09:31 | JVM exited while loading the application. INFO | wrapper | 2017/03/02 14:09:31 | Waiting to start... STATUS | wrapper | 2017/03/02 14:09:35 | Launching a JVM... INFO | jvm 3 | 2017/03/02 14:09:36 | Error: missing `server‘ JVM at `D:\Java\jre7\bin\server\jvm.dll‘. INFO | jvm 3 | 2017/03/02 14:09:36 | Please install or use the JRE or JDK that contains these missing components. ERROR | wrapper | 2017/03/02 14:09:36 | JVM exited while loading the application. INFO | wrapper | 2017/03/02 14:09:36 | Waiting to start... STATUS | wrapper | 2017/03/02 14:09:40 | Launching a JVM... INFO | jvm 4 | 2017/03/02 14:09:40 | Error: missing `server‘ JVM at `D:\Java\jre7\bin\server\jvm.dll‘. INFO | jvm 4 | 2017/03/02 14:09:40 | Please install or use the JRE or JDK that contains these missing components. ERROR | wrapper | 2017/03/02 14:09:40 | JVM exited while loading the application. INFO | wrapper | 2017/03/02 14:09:41 | Waiting to start... STATUS | wrapper | 2017/03/02 14:09:44 | Launching a JVM... INFO | jvm 5 | 2017/03/02 14:09:44 | Error: missing `server‘ JVM at `D:\Java\jre7\bin\server\jvm.dll‘. INFO | jvm 5 | 2017/03/02 14:09:44 | Please install or use the JRE or JDK that contains these missing components. ERROR | wrapper | 2017/03/02 14:09:44 | JVM exited while loading the application. FATAL | wrapper | 2017/03/02 14:09:44 | There were 5 failed launches in a row, each lasting less than 300 seconds. Giving up. FATAL | wrapper | 2017/03/02 14:09:44 | There may be a configuration problem: please check the logs. STATUS | wrapper | 2017/03/02 14:09:44 | <-- Wrapper Stopped ERROR | wrapper | 2017/03/02 14:09:45 | The Mycat-server service was launched, but failed to start.View Code
将mycat\conf\wrapper.conf中的wrapper.java.command=java
修改为wrapper.java.command=D:\Java\jdk1.7.0_80\bin\java.exe
再次启动
STATUS | wrapper | 2017/03/02 14:11:08 | Starting the Mycat-server service... STATUS | wrapper | 2017/03/02 14:11:08 | --> Wrapper Started as Service STATUS | wrapper | 2017/03/02 14:11:09 | Launching a JVM... INFO | jvm 1 | 2017/03/02 14:11:11 | Error: Could not create the Java Virtual Machine. INFO | jvm 1 | 2017/03/02 14:11:11 | Error: A fatal exception has occurred. Program will exit. INFO | jvm 1 | 2017/03/02 14:11:11 | Invalid maximum heap size: -Xmx4G INFO | jvm 1 | 2017/03/02 14:11:11 | The specified size exceeds the maximum representable size. ERROR | wrapper | 2017/03/02 14:11:11 | JVM exited while loading the application. INFO | wrapper | 2017/03/02 14:11:13 | Waiting to start... STATUS | wrapper | 2017/03/02 14:11:15 | Launching a JVM... INFO | jvm 2 | 2017/03/02 14:11:15 | Error: Could not create the Java Virtual Machine. INFO | jvm 2 | 2017/03/02 14:11:15 | Error: A fatal exception has occurred. Program will exit. INFO | jvm 2 | 2017/03/02 14:11:15 | Invalid maximum heap size: -Xmx4G INFO | jvm 2 | 2017/03/02 14:11:15 | The specified size exceeds the maximum representable size. ERROR | wrapper | 2017/03/02 14:11:15 | JVM exited while loading the application. INFO | wrapper | 2017/03/02 14:11:18 | Waiting to start... STATUS | wrapper | 2017/03/02 14:11:19 | Launching a JVM... INFO | jvm 3 | 2017/03/02 14:11:20 | Error: Could not create the Java Virtual Machine. INFO | jvm 3 | 2017/03/02 14:11:20 | Error: A fatal exception has occurred. Program will exit. INFO | jvm 3 | 2017/03/02 14:11:20 | Invalid maximum heap size: -Xmx4G INFO | jvm 3 | 2017/03/02 14:11:20 | The specified size exceeds the maximum representable size. ERROR | wrapper | 2017/03/02 14:11:20 | JVM exited while loading the application. INFO | wrapper | 2017/03/02 14:11:23 | Waiting to start... STATUS | wrapper | 2017/03/02 14:11:24 | Launching a JVM... INFO | jvm 4 | 2017/03/02 14:11:24 | Error: Could not create the Java Virtual Machine. INFO | jvm 4 | 2017/03/02 14:11:24 | Error: A fatal exception has occurred. Program will exit. INFO | jvm 4 | 2017/03/02 14:11:24 | Invalid maximum heap size: -Xmx4G INFO | jvm 4 | 2017/03/02 14:11:24 | The specified size exceeds the maximum representable size. ERROR | wrapper | 2017/03/02 14:11:24 | JVM exited while loading the application. STATUS | wrapper | 2017/03/02 14:11:28 | Launching a JVM... INFO | jvm 5 | 2017/03/02 14:11:28 | Error: Could not create the Java Virtual Machine. INFO | jvm 5 | 2017/03/02 14:11:28 | Error: A fatal exception has occurred. Program will exit. INFO | jvm 5 | 2017/03/02 14:11:28 | Invalid maximum heap size: -Xmx4G INFO | jvm 5 | 2017/03/02 14:11:28 | The specified size exceeds the maximum representable size. ERROR | wrapper | 2017/03/02 14:11:28 | JVM exited while loading the application. INFO | wrapper | 2017/03/02 14:11:28 | Waiting to start... FATAL | wrapper | 2017/03/02 14:11:28 | There were 5 failed launches in a row, each lasting less than 300 seconds. Giving up. FATAL | wrapper | 2017/03/02 14:11:28 | There may be a configuration problem: please check the logs. STATUS | wrapper | 2017/03/02 14:11:28 | <-- Wrapper Stopped ERROR | wrapper | 2017/03/02 14:11:30 | The Mycat-server service was launched, but failed to start.View Code
Invalid maximum heap size: -Xmx4G，将-Xmx4G数值适当调低些，再次启动成功。
mycat console界面类似mycat\bin\startup_nowrap.bat，不过后者不会创建JVM。
适当修改schema.xml、server.xml、rule.xml，然后就可以启动Mycat啦。下面的schema.xml(注意server.xml中的</user>栏中的schemas属性要保持一致)是针对MySQL主从复制，开启Mycat的读写分离功能。4306写入，4307读取
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="sakila" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate" /> --> </schema> <dataNode name="dn1" dataHost="localhost1" database="sakila" /> <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" /> --> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100"> <heartbeat>show slave status</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="localhost:4306" user="root" password="mysql4306"> <!-- can have multi read hosts --> <!--<readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" />--> </writeHost> <writeHost host="hostS1" url="localhost:4307" user="root" password="mysql4307" /> <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --> </dataHost> </mycat:schema>View Code
4306结点宕机后不会切换(switchType="-1")到4307结点，但4307能继续提供读取功能。如果4307结点宕机，读写将全部在4306结点。
下面这个schema.xml使用Mycat进行分片
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> <!-- auto sharding by id (long) --> <table name="travelrecord" dataNode="dn$1-3" rule="auto-sharding-long" /> <!-- global table is auto cloned to all defined data nodes ,so can join with any table whose sharding node is in the same data node --> <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" /> <table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" /> <!-- random sharding using mod sharind rule --> <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" /> <!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global" needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3" rule="mod-long" /> --> <table name="employee" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile" /> <table name="customer" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile"> <childTable name="orders" primaryKey="ID" joinKey="customer_id" parentKey="id"> <childTable name="order_items" joinKey="order_id" parentKey="id" /> </childTable> <childTable name="customer_addr" primaryKey="ID" joinKey="customer_id" parentKey="id" /> </table> <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate" /> --> </schema> <schema name="sakila" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn4"> <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate" /> --> </schema> <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" /> --> <dataNode name="dn$1-3" dataHost="localhost1" database="mycatdb$1-3" /> <dataNode name="dn4" dataHost="localhost1" database="sakila" /> <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" /> <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" /> <dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" /> <dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> --> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="localhost:4306" user="root" password="mysql4306"> <!-- can have multi read hosts --> <!--<readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" />--> </writeHost> <writeHost host="hostS1" url="localhost:4307" user="root" password="mysql4307" /> <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --> </dataHost> <!-- <dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc"> <heartbeat> </heartbeat> <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng" password="jifeng"></writeHost> </dataHost> <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat> <connectionInitSql>alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss‘</connectionInitSql> <writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base" password="123456" > </writeHost> </dataHost> <dataHost name="jdbchost" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost> <dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc"> <heartbeat> </heartbeat> <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng" password="jifeng"></writeHost> </dataHost> --> <!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql" dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost> </dataHost> --> </mycat:schema>View Code
三、Mycat-Web
Mycat、Mycat-eye依赖jdk1.7+环境
1、Zookeeper配置
Mycat-eye需要Zookeeper作为配置中心
1.1、解压zookeeper-3.4.8.tar.gz
1.2、zookeeper-3.4.8\conf目录下把zoo_sample.cfg修改为zoo.cfg
1.3、启动zookeeper
Windowns版本：zookeeper-3.4.6\bin\zkServer.bat
2、Mycat-eye部署
2.1、解压Mycat-web-1.0-SNAPSHOT-20160617163048-win.zip
2.2、先启动Zookeeper
2.3、启动Mycat-eye
Windowns版本：mycat-web\start.bat
2.4、访问Mycat-eye
http://localhost:8082/mycat/
mycat.properties配置：mycat-web\mycat-web\WEB-INF\classes\ mycat.properties
Mycat+MySQL 主从复制
标签：datanode str false tor 分离 font sql line admin
本文系统来源：http://www.cnblogs.com/ShanFish/p/6485226.html
内容总结
以上是互联网集市为您收集整理的Mycat+MySQL 主从复制全部内容，希望文章能够帮你解决Mycat+MySQL 主从复制所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】