Mycat+MySQL 主从复制

一、主从复制搭建(新环境5.6.33)
1、设置复制Master配置信息

[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搭建
Mycat、Mycat-eye依赖jdk1.7+环境
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添加值

;%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

pause

如果执行报java命令错误，可修改批处理中的变量为实例对象；内存不充足时可适当调整Xms、Xmx的大小。这种方式一般是在程序运行有问题时选用。
前面已经将Mycat添加到环境变量(不然要切换到对应目录)，可以在cmd下直接执行mycat

装载、启动服务

#装载成服务

mycat install

#启动服务

mycat start

首次启动

STATUS | wrapper  | // :: | Starting the Mycat-server service...

STATUS | wrapper  | // :: | --> Wrapper Started as Service

STATUS | wrapper  | // :: | Launching a JVM...

INFO   | jvm     | // :: | Error: missing `server' JVM at `D:\Java\jre7\bin\server\jvm.dll'.

INFO   | jvm     | // :: | Please install or use the JRE or JDK that contains these missing components.

ERROR  | wrapper  | // :: | JVM exited while loading the application.

STATUS | wrapper  | // :: | Launching a JVM...

INFO   | jvm     | // :: | Error: missing `server' JVM at `D:\Java\jre7\bin\server\jvm.dll'.

INFO   | jvm     | // :: | Please install or use the JRE or JDK that contains these missing components.

ERROR  | wrapper  | // :: | JVM exited while loading the application.

INFO   | wrapper  | // :: | Waiting to start...

STATUS | wrapper  | // :: | Launching a JVM...

INFO   | jvm     | // :: | Error: missing `server' JVM at `D:\Java\jre7\bin\server\jvm.dll'.

INFO   | jvm     | // :: | Please install or use the JRE or JDK that contains these missing components.

ERROR  | wrapper  | // :: | JVM exited while loading the application.

INFO   | wrapper  | // :: | Waiting to start...

STATUS | wrapper  | // :: | Launching a JVM...

INFO   | jvm     | // :: | Error: missing `server' JVM at `D:\Java\jre7\bin\server\jvm.dll'.

INFO   | jvm     | // :: | Please install or use the JRE or JDK that contains these missing components.

ERROR  | wrapper  | // :: | JVM exited while loading the application.

INFO   | wrapper  | // :: | Waiting to start...

STATUS | wrapper  | // :: | Launching a JVM...

INFO   | jvm     | // :: | Error: missing `server' JVM at `D:\Java\jre7\bin\server\jvm.dll'.

INFO   | jvm     | // :: | Please install or use the JRE or JDK that contains these missing components.

ERROR  | wrapper  | // :: | JVM exited while loading the application.

FATAL  | wrapper  | // :: | There were  failed launches in a row, each lasting less than  seconds.  Giving up.

FATAL  | wrapper  | // :: |   There may be a configuration problem: please check the logs.

STATUS | wrapper  | // :: | <-- Wrapper Stopped

ERROR  | wrapper  | // :: | The Mycat-server service was launched, but failed to start.

将mycat\conf\wrapper.conf中的wrapper.java.command=java
修改为wrapper.java.command=D:\Java\jdk1.7.0_80\bin\java.exe
再次启动

STATUS | wrapper  | // :: | Starting the Mycat-server service...

STATUS | wrapper  | // :: | --> Wrapper Started as Service

STATUS | wrapper  | // :: | Launching a JVM...

INFO   | jvm     | // :: | Error: Could not create the Java Virtual Machine.

INFO   | jvm     | // :: | Error: A fatal exception has occurred. Program will exit.

INFO   | jvm     | // :: | Invalid maximum heap size: -Xmx4G

INFO   | jvm     | // :: | The specified size exceeds the maximum representable size.

ERROR  | wrapper  | // :: | JVM exited while loading the application.

INFO   | wrapper  | // :: | Waiting to start...

STATUS | wrapper  | // :: | Launching a JVM...

INFO   | jvm     | // :: | Error: Could not create the Java Virtual Machine.

INFO   | jvm     | // :: | Error: A fatal exception has occurred. Program will exit.

INFO   | jvm     | // :: | Invalid maximum heap size: -Xmx4G

INFO   | jvm     | // :: | The specified size exceeds the maximum representable size.

ERROR  | wrapper  | // :: | JVM exited while loading the application.

INFO   | wrapper  | // :: | Waiting to start...

STATUS | wrapper  | // :: | Launching a JVM...

INFO   | jvm     | // :: | Error: Could not create the Java Virtual Machine.

INFO   | jvm     | // :: | Error: A fatal exception has occurred. Program will exit.

INFO   | jvm     | // :: | Invalid maximum heap size: -Xmx4G

INFO   | jvm     | // :: | The specified size exceeds the maximum representable size.

ERROR  | wrapper  | // :: | JVM exited while loading the application.

INFO   | wrapper  | // :: | Waiting to start...

STATUS | wrapper  | // :: | Launching a JVM...

INFO   | jvm     | // :: | Error: Could not create the Java Virtual Machine.

INFO   | jvm     | // :: | Error: A fatal exception has occurred. Program will exit.

INFO   | jvm     | // :: | Invalid maximum heap size: -Xmx4G

INFO   | jvm     | // :: | The specified size exceeds the maximum representable size.

ERROR  | wrapper  | // :: | JVM exited while loading the application.

STATUS | wrapper  | // :: | Launching a JVM...

INFO   | jvm     | // :: | Error: Could not create the Java Virtual Machine.

INFO   | jvm     | // :: | Error: A fatal exception has occurred. Program will exit.

INFO   | jvm     | // :: | Invalid maximum heap size: -Xmx4G

INFO   | jvm     | // :: | The specified size exceeds the maximum representable size.

ERROR  | wrapper  | // :: | JVM exited while loading the application.

INFO   | wrapper  | // :: | Waiting to start...

FATAL  | wrapper  | // :: | There were  failed launches in a row, each lasting less than  seconds.  Giving up.

FATAL  | wrapper  | // :: |   There may be a configuration problem: please check the logs.

STATUS | wrapper  | // :: | <-- Wrapper Stopped

ERROR  | wrapper  | // :: | The Mycat-server service was launched, but failed to start.

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>

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>

三、Mycat-Web
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.\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 主从复制

最新文章

热门文章