MySQL MYCAT installation configuration


  • Linux MYCAT installation configuration to realize MySQL master-slave replication + read-write separation.
  • In this case, you have installed MySQL master-slave replication by default. Please refer to my related articles for details.
  • Mycat has a single server and MySQL has a master server and a slave server. There are three servers in total.
    • MySQL master server:
    • MySQL slave server:
    • Mycat server:

Operation steps

>Environmental preparation

  • One master-slave MySQL server configured
  • Another server is used to install Mycat. Mycat is a Java project. It is required that this server must install JDK. For details, please refer to my related articles.

>Install Mycat

  • Download:

  • Downloaded installation package:

    [root@192 Mycat]# ll
    total 21512
    -rw-r--r--. 1 501 games 21760812 Jan 28 11:52 Mycat-server-
  • Unzip:

    [root@192 Mycat]# tar -zxvf Mycat-server-
    [root@192 Mycat]# mv mycat /usr/local/
    [root@192 Mycat]# cd /usr/local/mycat/
    [root@192 mycat]# ll
    total 12
    drwxr-xr-x. 2 501 games  190 Jan 29 00:36 bin
    drwxr-xr-x. 2 501 games    6 Oct 22  2019 catlet
    drwxr-xr-x. 4 501 games 4096 Jan 29 00:36 conf
    drwxr-xr-x. 2 501 games 4096 Jan 29 00:36 lib
    drwxr-xr-x. 2 501 games    6 Jan  5  2020 logs
    -rwxr-xr-x. 1 501 games  227 Jan  5  2020 version.txt

>Configure Mycat

  • The configuration file is located in the: conf directory

  • Modify the configuration file server xml:

    [root@192 conf]# vi server.xml
    <user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">master_slave_001</property>
        <property name="defaultSchema">master_slave_001</property>
        <!--No MyCAT Database selected Try to use this before making an error schema As schema,Otherwise null,report errors -->
        <!-- Table level DML Permission setting -->
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
    • Configuration Description:
      • root: the user name of connecting Mycat, which is equivalent to the user name of MySQL
      • Password: the password to connect to Mycat, which is equivalent to the password of MySQL
      • schemas and defaultSchema: the virtual library name of Mycat, corresponding to the real library name of MySQL.
        • The two sides should be consistent, otherwise some MySQL clients (such as Navicat) will report an error when connecting: table 'testdb tb001' doesn't exist.
  • Modify the configuration file schema XML (this file has changed a lot, so back it up first):

    [root@192 conf]# cp schema.xml schema.xml.default
    [root@192 conf]# vi schema.xml
    <?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    	schema:           Mapping between virtual library and real library 
    	-name:           The name of the virtual library, corresponding to server.xml Set in schemas
    	-sqlMaxLimit:    Maximum number of query records allowed
    	-checkSQLschema: Check whether to automatically delete "virtual library name"
    	-dataNode:       Virtual library corresponding real database,corresponding<dataNode>Tagged name
    	<schema name="master_slave_001" checkSQLschema="false" sqlMaxLimit="100" dataNode="dataNode001">
    		table:      Virtual table configuration
    		-name:     Table name
    		-dataNode: The partition corresponding to the table. If slicing is done, configure multiple, separated by commas; Or use db$0-99,representative db0 reach db99 of database
    		-rule:     Sharding rule. If not, delete it
    		<table name="tb_item" dataNode="dn1" rule="sharding-by-mod2"/> 
    	dataNode:   Database fragmentation, each dataNode It is a database partition
    	-name:     Slice name
        -dataHost: Host information of real library, corresponding to<dataHost>Tagged name
    	-database: real MySQL Real physical database name in
    	<dataNode name="dataNode001" dataHost="dataHost001" database="master_slave_001" />
    	dataHost:     Host information of real library
    	-name:       host name
    	-maxCon:     Maximum connection
    	-minCon:     Minimum connection
    	-balance:    Load balancing mode, 0 do not enable read / write separation. one~3 All open. See the details below
    	-writeType:  Write load balancing. Always set 0
    	-dbDriver:   Drive type, recommended native,Optional jdbc
    	-switchType: Automatic switching between master and slave, 1=Automatic switching,-1=No automatic switching, 2=be based on MySQL The status of master-slave synchronization determines whether to switch. The default is 1
    	<dataHost name="dataHost001" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    		<heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
    		<writeHost host="writeHost001" url="" user="root" password="rootroot">
    			<readHost host="readHost001" url="" user="root" password="rootroot" />
    • (Virtual Library name):
      • Scheme 1: the virtual database name is consistent with the real database name in MySQL, so you can access all tables in the real database without defining accessible tables.
      • Scheme 2: the virtual database name is different from the real database name in MySQL, and then define the accessible table in the schema tag.
      • If the second scheme is selected, when the accessible table is not defined, the query will show table 'testdb Tb001 'doesn't exist error.
    • dataHost. Detailed description of balance:
      • balance = "0": the read-write separation mechanism is not enabled, and all read operations are sent to the currently available writeHost.
      • balance = "1": all readhosts and stand by writeHost participate in the load balancing of the select statement. In short, it is applicable to dual master and dual slave mode (M1 - > S1, M2 - > S2, and M1 and M2 are primary and standby to each other). Under normal circumstances, M2, S1 and S2 all participate in the load balancing of select statements.
      • balance = "2": all read operations are randomly distributed on writeHost and readhost.
      • balance = "3": all read requests are randomly distributed to readhost for execution. writerHost does not bear the reading pressure. Corresponding to master order and slave order.

>Start Mycat

  • Console startup:

    [root@192 mycat]# ./bin/mycat console
    Running Mycat-server...
    wrapper  | --> Wrapper Started as Console
    wrapper  | Launching a JVM...
    jvm 1    | Wrapper (Version 3.2.3)
    jvm 1    |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
    jvm 1    |
    jvm 1    | MyCAT Server startup successfully. see logs in logs/mycat.log
  • View log file location:

    [root@192 logs]# pwd
    [root@192 logs]# ll
    total 28
    -rw-r--r--. 1 root root 20067 Jan 29 10:55 mycat.log
    -rw-r--r--. 1 root root     6 Jan 29 10:49
    -rw-r--r--. 1 root root  2568 Jan 29 10:49 wrapper.log
  • Background start:

    [root@192 mycat]# ./bin/mycat start
    [root@192 mycat]# ./bin/mycat stop
    [root@192 mycat]# ./bin/mycat restart
    [root@192 mycat]# ./bin/mycat status

>Remote connection test

  • Mycat default port: 8066

  • Here I use Navicat client to create a new MySQL connection method. Configuration parameters:

    • Parameter Description:
      • Host: the IP address of the server where Mycat is located
      • Port: the external access port when Mycat is used as MySQL springboard (mapped to 3306 of MySQL)
      • UserName: Mycat configuration file server "root" in the user element defined in XML“
      • Password: Mycat configuration file server "Password" in the user element defined in XML
  • After the connection is successful, you will see the database named "master_slave_001", which is the table in MySQL. Try to add, delete, modify, check and view the data of the master-slave database. Everything is normal.

  • So far, Mycat+MySQL master-slave replication + read-write separation has been realized.

>Dual master and dual slave (dual machine hot standby)

  • The core operation is to configure another master-slave copy, and then the two master services copy each other. Please pay attention to my [manual dog head] for specific operations~

Keywords: Linux Database MySQL mycat

Added by PhillNeedsHelp on Wed, 23 Feb 2022 04:53:32 +0200