MyCat 数据库中间件配置读写分离

读写分离

准备工作

• 创建mycat用户

mysql> create user mycat@'localhost' identified by '123456';
mysql> grant all on *.* to mycat@'localhost';

• 配置文件

/usr/local/mycat/conf/
├── clusters   # 后端数据库集群
│   └── prototype.cluster.json
├── datasources    # 数据源
│   └── prototypeDs.datasource.json   # 关联后端mysql账号
├── schemas    # 逻辑库
│   ├── information_schema.schema.json
│   └── mysql.schema.json
├── users    # 登录mycat用的账号，不是后端mysql账号
│   └── root.user.json

• 启动服务

chmod +x /usr/local/mycat/bin/*
/usr/local/mycat/bin/mycat start

读写分离配置

• 数据源

1. 连接mycat

mysql -h127.0.0.1 -P8066 -umycatuser -p'654321'

2. 新增三个数据源

/*+ mycat:createDataSource {
    "instanceType": "WRITE",
    "name": "mysql21",
    "url": "jdbc:mysql://192.168.88.21:3306/",
    "user": "mycat",
    "password": "123456"
} */;
/*+ mycat:createDataSource {
    "instanceType": "READ",
    "name": "mysql22",
    "url": "jdbc:mysql://192.168.88.22:3306/",
    "user": "mycat",
    "password": "123456"
} */;
/*+ mycat:createDataSource {
    "instanceType": "READ",
    "name": "mysql23",
    "url": "jdbc:mysql://192.168.88.23:3306/",
    "user": "mycat",
    "password": "123456"
} */;

3. 查看创建好的数据源

/usr/local/mycat/conf/datasources/
├── mysql21.datasource.json
├── mysql22.datasource.json
├── mysql23.datasource.json
└── prototypeDs.datasource.json

4. 每个数据库节点都单独创建用户

mysql -e "create user mycat@'%' identified by '123456';"
mysql -e "grant all on *.* to mycat@'%';"

• 集群

1. 新增一个集群

/*! mycat:createCluster {
    "clusterType": "MASTER_SLAVE",    # 一主多从
    "masters": ["mysql21"],
    "replicas": ["mysql22", "mysql23"],
    "name": "rwcluster",
    "readBalanceType": "BALANCE_ALL_READ"   # 读请求分发到所有READ节点
} */;

2. 查看创建好的集群

ls /usr/local/mycat/conf/clusters/
 prototype.cluster.json  rwcluster.cluster.json

• 逻辑库

1. 新增一个逻辑库

/*+ mycat:createSchema {
    "schemaName": "mydb",   # 关联到已有数据库
    "targetName": "rwcluster"
} */;

2. 查看创建好的逻辑库

mysql> /*+ mycat:showSchemas{} */\G
*************************** 1. row ***************************
        SCHEMA_NAME: mydb
DEFAULT_TARGET_NAME: rwcluster
        TABLE_NAMES: user

注意事项

1. 读写分离建立在已有主从复制关系的节点上 - 验证show slave status\G
2. 实际操作建议写节点采用READ_WRITE
3. 实际操作建议集群采用BALANCE_ALL_READ_WRITE