Echo editor, welcome to reprint, reprint please state the source of the article. Welcome to add echo wechat (wechat: t2421499075) for communication and learning Never lose in a hundred battles, never claim to win, never lose in a hundred defeats, and strive to move forward. ——This is really powerful!!!

Mycat quick start

• preparation:
• • Please install Mycat on the server now. Installation tutorial: https://blog.csdn.net/xlecho/article/details/102755484
• • Take a look at his core profile: Server.xml details,Details of schema.xml,Detailed explanation of rule.xml
• • To prepare three servers, you need to install mysql
• • Set up a MySQL read-write separation environment with two slaves: Mycat distributed database architecture solution -- build MySQL read-write separation environment -- one master and many slaves
• • Download a copy of Mycat's original code (it's not convenient to write on the server, local changes are good, and it's more convenient to replace the server configuration file) Mycat Download

Possible commands for Mycat configuration

• Enter the bin directory of mycat installation, and start the command:. / startup_nowrap.sh
• Enter the bin directory of mycat installation and stop the command:. / mycat stop
• See if there is a process running mycat: ps -ef
• View the running log of MYCAT and enter the logs Directory: more mycat.log
• Delete an entire line in the configuration file: vi open the configuration file, and double-click d to delete the line where the cursor is
• Delete a whole block in the configuration file: Click v, then move the left and right keys, the cursor will move to select the moving area, and then click x to delete

Configure Mycat

Using Mycat to achieve read-write separation is actually relatively simple, as long as you pay attention to its two configuration files schema.xml/server.xml.

• schema.xml is mainly responsible for connection configuration of database
• server.xml is mainly responsible for Mycat login connection configuration

schema

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="userDB" checkSQLschema="true" sqlMaxLimit="100">
        <table name="user" primaryKey="id" dataNode="dn1" />
    </schema>

    <dataNode name="dn1" dataHost="host1" database="test" />

    <!-- Read / write separation -->
    <dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM" url="192.168.222.132:3306" user="root" password="123456">
            <readHost host="hostS1" url="192.168.222.133:3306" user="root" password="123456" />
            <readHost host="hostS2" url="192.168.222.134:3306" user="root" password="123456" />
        </writeHost>
    </dataHost>
</mycat:schema>

server

<?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://io.mycat/">
    <!-- Mycat There are two important labels in user,system -->
    <!-- system Tags are related to system configuration -->
    <system>
        <!-- When you configure this property, you must ensure that mycat Character set and mysql The character set of is consistent.
        If you need to configure a special character set, such as: utf8mb4，Can be in index_to_charset.properties Configuration in, configuration format
        For " ID=Character set ",For example: "224=utf8mb4"-->
        <property name="charset">utf8</property>
        <!-- 1 To turn on real-time statistics, 0 to turn off -->
        <property name="useSqlStat">0</property>
        <!-- 0 For password login, 1 for no password login ,The default value is 0. If it is set to 1, you need to specify a default account-->
        <property name="nonePasswordLogin">0</property>
        <property name="useHandshakeV10">1</property>
        <!-- 1 To enable full overtime consistency detection, 0 to close -->
        <property name="useGlobleTableCheck">0</property>
        <!-- SQL Execution timeout unit:second-->
        <property name="sqlExecuteTimeout">300</property>
        <!-- Designated use Mycat The type of global sequence 0 is the local file mode, 1 is the database mode, 2 is the time stamp sequence mode, and 3 is the distributed mode ZK ID Generator, 4 for zk Increase progressively id Generation. -->
        <property name="sequnceHandlerType">5</property>
        <!-- Must have MYCATSEQ_perhaps mycatseq_Enter the sequence matching process Note MYCATSEQ_With spaces -->
        <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
        <!-- When there is an associated query in a subquery,Check whether there are fragment fields in the associated fields .default false -->
        <property name="subqueryRelationshipCheck">false</property>
        <property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
        <!-- Default is type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
        <property name="processorBufferPoolType">0</property>
        <!-- Distributed transaction switch, 0 for unfiltered distributed transactions, 1 for filtered distributed transactions (if only global tables are involved in distributed transactions, then no filtering), 2 for unfiltered distributed transactions,But log distributed transactions-->
        <property name="handleDistributedTransactions">0</property>
        <!-- off heap for merge/order/group/limit      1 On 0 off -->
        <property name="useOffHeapForMerge">0</property>
        <!-- Unit is m -->
        <property name="memoryPageSize">64k</property>
        <!-- Unit is k -->
        <property name="spillsFileBufferSize">1k</property>
        <property name="useStreamOutput">0</property>
        <!-- Unit is m -->
        <property name="systemReserveMemorySize">384m</property>
        <!--Whether to adopt zookeeper Coordinated switching  -->
        <property name="useZKSwitch">false</property>
        <!--If so true Strictly follow the isolation level,Not just select Switch connection in transaction when statement-->
        <property name="strictTxIsolation">false</property>
        <property name="useZKSwitch">true</property>
    </system>

        <!-- name="root" Define login user name -->
    <user name="root">
        <!-- Login password defined -->
        <property name="password">123456</property>
        <property name="schemas">userDB</property>
    </user>
</mycat:server>

After configuration, we can start Mycat:

Enter bin directory
./mycat start

Verify that read-write separation has been successfully implemented

First of all, we add two pieces of data to the master server. Since we implement mysql master-slave replication, the other two pieces of data will be added at the same time. The data are as follows:

Add a piece of data from server 192.168.222.134 as follows:

134 server has data that other servers don't have. Because we have configured two write function servers 132 and read 133 / 134 in the schema, when Mycat reads data, it should read one of 133 / 134 randomly. We log in to Mycat and open the user table, refresh several times, and you can see that sometimes there is no data with id 5

Log in to MYCAT and report an error. First check whether MYCAT has been successfully started. If not, you can enter the logs directory under MYCAT file and open wrapper.log to see the reason

Be careful:

• When configuring the login user, our server should correspond to the logical library in our schemas, otherwise an error will be reported.

• When unable to access Mycat, check port 8066 to see if the port is open

  Be a blogger with a bottom line