Prerequisites

• For MySQL master-slave setup, refer to: MySQL master-slave setup guide
• For MyCAT installation, refer to: Installing MyCAT 2

Network and Service Configuration

Creating Database Users

On master, slave, and slave01, create a user for MyCAT to use as a data source connection:

CREATE USER 'mycat'@'%' IDENTIFIED BY '123123';
GRANT ALL PRIVILEGES ON *.* TO 'mycat'@'%';
FLUSH PRIVILEGES;

Configuring Data Sources via MyCAT

Connect to MyCAT using Navicat (or any MySQL client) and execute the following commands in the query window. These commands will create the necessary data sources.

Creating Master Data Source

/*+ mycat:createDataSource{
    "dbType":"mysql",
    "idleTimeout":60000,
    "initSqls":[],
    "initSqlsGetConnection":true,
    "instanceType":"READ_WRITE",
    "maxCon":1000,
    "maxConnectTimeout":3000,
    "maxRetryCount":5,
    "minCon":1,
    "name":"master",
    "password":"123123",
    "type":"JDBC",
    "url":"jdbc:mysql://192.168.153.19:3307/mydb1?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
    "user":"mycat",
    "weight":0
} */;

Creating Slave Data Source

/*+ mycat:createDataSource{
    "dbType":"mysql",
    "idleTimeout":60000,
    "initSqls":[],
    "initSqlsGetConnection":true,
    "instanceType":"READ",
    "maxCon":1000,
    "maxConnectTimeout":3000,
    "maxRetryCount":5,
    "minCon":1,
    "name":"slave",
    "password":"123123",
    "type":"JDBC",
    "url":"jdbc:mysql://192.168.153.19:3308/mydb1?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
    "user":"mycat",
    "weight":0
} */;

Creating Standby Master Data Source

/*+ mycat:createDataSource{
    "dbType":"mysql",
    "idleTimeout":60000,
    "initSqls":[],
    "initSqlsGetConnection":true,
    "instanceType":"READ_WRITE",
    "maxCon":1000,
    "maxConnectTimeout":3000,
    "maxRetryCount":5,
    "minCon":1,
    "name":"master01",
    "password":"123123",
    "type":"JDBC",
    "url":"jdbc:mysql://192.168.153.19:3309/mydb1?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
    "user":"mycat",
    "weight":0
} */;

Creating Second Slave Data Source

/*+ mycat:createDataSource{
    "dbType":"mysql",
    "idleTimeout":60000,
    "initSqls":[],
    "initSqlsGetConnection":true,
    "instanceType":"READ",
    "maxCon":1000,
    "maxConnectTimeout":3000,
    "maxRetryCount":5,
    "minCon":1,
    "name":"slave01",
    "password":"123123",
    "type":"JDBC",
    "url":"jdbc:mysql://192.168.153.19:3310/mydb1?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
    "user":"mycat",
    "weight":0
} */;

After executing these commands, check the /usr/local/mycat/conf/datasources directory. The corresponding data source configuration files will have been generated.

Creating a Cluster Configuration

To create a master-slave cluster, execute the following command. Note that master01 serves both as a standby master and as a replica for read balancing.

/*! mycat:createCluster{
    "clusterType":"MASTER_SLAVE",
    "heartbeat":{
        "heartbeatTimeout":1000,
        "maxRetry":3,
        "minSwitchTimeInterval":300,
        "slaveThreshold":0
    },
    "masters":[
        "master","master01"
    ],
    "maxCon":200,
    "name":"master-slave",
    "readBalanceType":"BALANCE_ALL",
    "replicas":[
        "slave","slave","master01"
    ],
    "switchType":"SWITCH"
} */;

Creating a Schema

To associate the cluster with a physical database, create a schema using the following command. Replace mydb1 with your actual database name if different.

/*+ mycat:createSchema{
    "customTables":{},
    "globalTables":{},
    "normalTables":{},
    "schemaName":"mydb1",
    "shardingTables":{},
    "targetName":"master-slave"
} */;

Note: You can also direct edit the existing mysql.schema.json file. If you prefer to keep a backup, rename it first:

mv mysql.schema.json mysql.schema.bak

Starting MyCAT

For debugging purposes, it's recommended to start MyCAT in console mode:

/usr/local/mycat/bin/mycat console

Once started, the cluster will be active and MyCAT will route read/write operations according to the configured master-slave roles.