MyCat2介绍以及部署和读写分离/分库分表（MyCat2.0）

官网：http://www.mycat.org.cn/

[root@localhost ~]# tar -zxf jdk-8u171-linux-x64.tar.gz
[root@localhost ~]# ls
anaconda-ks.cfg  jdk-8u171-linux-x64.tar.gz        mysql80-community-release-el7-7.noarch.rpm
jdk1.8.0_171     mycat2-install-template-1.20.zip  original-ks.cfg
[root@localhost ~]# mv jdk1.8.0_171/ /usr/local/java
[root@localhost ~]# vi /etc/profile
PATH=$PATH:/usr/local/java/bin
[root@localhost ~]# source /etc/profile
[root@localhost ~]# java -version
java version "1.8.0_171"
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode)

tar（zip）包 ：
http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template1.20.zip
jar 包 ：
http://dl.mycat.org.cn/2.0/1.21-release/ (下载最新的 jar 包)

[root@localhost ~]# ls
anaconda-ks.cfg                                mycat2-install-template-1.20.zip            original-ks.cfg
mycat2-1.21-release-jar-with-dependencies.jar  mysql80-community-release-el7-7.noarch.rpm
[root@localhost ~]# unzip mycat2-install-template-1.20.zip
[root@localhost ~]# mv mycat /usr/local/
[root@localhost mycat]# ls
bin  conf  lib  logs
[root@localhost mycat]# mv /root/mycat2-1.21-release-jar-with-dependencies.jar ./lib/
[root@localhost mycat]# chmod 777 -R ./lib/

#直接将root改为所有地址可以登录，方便，但是在真实环境中需要根据权限来创建用户
mysql> 

[root@localhost mycat]# vi conf/datasources/prototypeDs.datasource.json
{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"prototypeDs",
        "password":"1234.Com",
        "type":"JDBC",
        "url":"jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"root",
        "weight":0
}

[root@localhost mycat]# cd bin/
[root@localhost bin]# chmod 555 *
[root@localhost bin]# ./mycat start

./mycat start #开启
./mycat stop  #关闭
./mycat restart   #重启
./mycat status  #查看状态
./mycat console #前台运行
./mycat pause #暂停

[root@localhost bin]# mysql -uroot -p1234.Com -P 8066 -h 192.168.2.1
选项：
-u：用户名
-p：密码
-P：端口
-h：IP地址
[root@localhost bin]# mysql -uroot -p1234.Com -P 9066 #后台管理端口

[root@localhost ~]# cd /usr/local/mycat/conf/
[root@localhost conf]# ll
总用量 32
drwxr-xr-x 2 root root   36 6月  28 2021 clusters
drwxr-xr-x 2 root root   41 5月  29 11:01 datasources
-rw-r--r-- 1 root root 3338 3月   5 2021 dbseq.sql
-rw-r--r-- 1 root root  316 11月  2 2021 logback.xml
-rw-r--r-- 1 root root    0 3月   5 2021 mycat.lock
drwxr-xr-x 2 root root   31 6月  28 2021 schemas
drwxr-xr-x 2 root root    6 6月  28 2021 sequences
-rw-r--r-- 1 root root  776 12月 28 2021 server.json
-rw-r--r-- 1 root root 1643 3月   5 2021 simplelogger.properties
drwxr-xr-x 2 root root  233 6月  28 2021 sql
drwxr-xr-x 2 root root    6 6月  28 2021 sqlcaches
-rw-r--r-- 1 root root   49 3月   5 2021 state.json
drwxr-xr-x 2 root root   28 6月  28 2021 users
-rw-r--r-- 1 root root  211 3月   5 2021 version.txt
-rw-r--r-- 1 root root 4165 1月  13 2022 wrapper.conf
clusters：集群
datasources：数据源
server.json：服务配置
user：用户目录

[root@localhost conf]# vi users/root.user.json
{
        "dialect":"mysql",
        "ip":null,
        "password":"123456",
        "transactionType":"xa",
        "username":"root"
}
#字段含义：
#username：用户名
#password：密码
#isolation：设置初始化的事务隔离级别
#transactionType：事务类型
可选值:
  proxy 本地事务,在涉及大于 1 个数据库的事务,commit 阶段失败会导致不一致,但是兼容性最好
xa 事务,需要确认存储节点集群类型是否支持 XA
  可以通过语句实现切换
  set transaction_policy = 'xa'
  set transaction_policy = 'proxy' 可以通过语句查询
  SELECT @@transaction_policy

[root@localhost conf]# vi datasources/prototypeDs.datasource.json
{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"prototypeDs",
        "password":"1234.Com",
        "type":"JDBC",
        "url":"jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"root",
        "weight":0
}
字段含义：
dbtype：数据库类型，mysql
name：用户名
password：密码
type：数据源类型，默认JDBC
url：访问数据库地址
idletimeout：空闲连接超时时间
initsqls：初始化sql
initsqlsgetconnection：对于JDBC每次获取连接是否都执行initSqls
instanceType：配置实例只读还是读写 可选值:READ_WRITE,READ,WRITE

[root@localhost conf]# vi clusters/prototype.cluster.json
{
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetry":3,
                "minSwitchTimeInterval":300,
                "slaveThreshold":0
        },
        "masters":[  #配置多个节点，在主挂的时候会选一个检测存活的数据源作为主节点
                "prototypeDs"
        ],
        "maxCon":200,
        "name":"prototype",
        "readBalanceType":"BALANCE_ALL",
        "switchType":"SWITCH"
}
字段：
clusterType：集群类型
  可选值：
    single_node：单一节点
    master_slave：普通主从
    garela_cluster:garela cluster /PXC 集群
    MHA：MHA集群
    MGR：MGR集群
readBalanceType：查询负责均衡策略
  可选值：
    BALANCE_ALL（默认值）：获取集群中所有数据源
    BALANCE_ALL_READ：获取集群中允许读的数据源
    BALANCE_READ_WEITE：获取集群中允许读写的数据源，但允许读的数据源优先
    BALANCE——NODE：获取集群中允许写数据源，即主节点中选择
switchType：切换类型
  可选值：
    NOT_SWITCH：不进行主从切换
    SWITCH：进行主从切换

[root@localhost conf]# vi schemas/mysql.schema.json
#库配置
 "locality":{
                                "schemaName":"mysql",
                                "tableName":"spm_baseline",
                                "targetName":"prototype"
                        }
#schemaName：逻辑库名
#targetName：目的数据源或集群
targetName自动从prototype目标加载test库下的物理表或者视图作为单表，prototype必须是MySQL服务器
#单表配置
{
"schemaName": "mysql-test",
"normalTables": {
"role_edges": {
"createTableSQL":null,//可选
"locality": {
"schemaName": "mysql",//物理库,可选
"tableName": "role_edges",//物理表,可选
"targetName": "prototype"//指向集群,或者数据源
}
}

[root@localhost ~]# vi /etc/my.cnf
server-id=1
log-bin=mysql-bin
[root@localhost ~]# systemctl restart mysqld

#设置不要复制的数据库（可以设置为多个）
binlog-ignore-db=dbname
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=dbname
#设置binlog格式
binlog_format=statement

[root@localhost ~]# vi /etc/my.cnf
server-id=2
log-bin=mysql-bin
[root@localhost ~]# systemctl restart mysqld

mysql> grant replication slave on *.* to slave@'%' identified by '1234.Com';
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      438 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> change master to master_host='192.168.2.1',master_user='slave',master_password='1234.Com',master_log_pos=438,master_log_file='mysql-bin.000002';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
      Slave_IO_Running: Yes   #数据传输
            Slave_SQL_Running: Yes    #SQL执行

mysql> create database mydb1;
Query OK, 1 row affected (0.00 sec)
mysql> use mydb1;
Database changed
mysql> create table mytb1(id int,name varchar(50));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into
    -> mytb1 values(1,'zhangsan');
Query OK, 1 row affected (0.03 sec)
mysql> insert into  mytb1 values(2,'lisi');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mytb1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

[root@localhost mycat]# mysql -uroot -p123456 -P8066 -h192.168.2.1
mysql> create database mydb1;
[root@localhost mycat]# vi conf/schemas/mydb1.schema.json
{
        "customTables":{},
        "globalTables":{},
        "normalProcedures":{},
        "normalTables":{},
        "schemaName":"mydb1",
        "targetName": "prototype",
        "shardingTables":{},
        "views":{}
}

[root@localhost mycat]# mysql -uroot -p123456 -P8066 -h192.168.2.1
mysql> /*+ mycat:createDataSource{ "name":"rwSepw","url":"jdbc:mysql://192.168.2.1:3306/mydb1?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root","password":"1234.Com" } */;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> /*+ mycat:createDataSource{ "name":"rwSepr","url":"jdbc:mysql://192.168.2.2:3306/mydb1?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root","password":"1234.Com" } */;
Query OK, 0 rows affected (0.03 sec)
#查询配置数据源结果
mysql> /*+ mycat:showDataSources{} */\G

mysql> /*!mycat:createCluster{"name":"prototype","masters":["rwSepw"],"replicas":["rwSepr"]} */;
Query OK, 0 rows affected (0.03 sec)
mysql> /*+ mycat:showClusters{} */;   #查看集群配置文件
+-----------+-------------+-------------------+-------------+----------+---------------+-------------------------------------------+-------------------------------------------+-----------+
| NAME      | SWITCH_TYPE | MAX_REQUEST_COUNT | TYPE        | WRITE_DS | READ_DS       | WRITE_L                                   | READ_L                                    | AVAILABLE |
+-----------+-------------+-------------------+-------------+----------+---------------+-------------------------------------------+-------------------------------------------+-----------+
| prototype | SWITCH      | 2000              | BALANCE_ALL | rwSepw   | rwSepw,rwSepr | io.mycat.plug.loadBalance.BalanceRandom$1 | io.mycat.plug.loadBalance.BalanceRandom$1 | true      |
+-----------+-------------+-------------------+-------------+----------+---------------+-------------------------------------------+-------------------------------------------+-----------+
1 row in set (0.01 sec)
ava.lang.RuntimeException: java.lang.IllegalArgumentException: ignored rw
Sepr

[root@localhost mycat]# vi /usr/local/mycat/conf/clusters/prototype.cluster.json
{
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetryCount":3,
                "minSwitchTimeInterval":300,
                "showLog":false,
                "slaveThreshold":0.0
        },
        "masters":[
                "rwSepw"
        ],
        "replicas":[
                "rwSepr"
        ],
        "maxCon":2000,
        "name":"prototype",
        "readBalanceType":"BALANCE_ALL",
        "switchType":"SWITCH"
}
readBalanceType
查询负载均衡策略
  可选值:
  BALANCE_ALL(默认值)
  获取集群中所有数据源
  BALANCE_ALL_READ
  获取集群中允许读的数据源
  BALANCE_READ_WRITE
  获取集群中允许读写的数据源,但允许读的数据源优先
  BALANCE_NONE
  获取集群中允许写数据源,即主节点中选择
switchType
NOT_SWITCH:不进行主从切换
SWITCH:进行主从切换

[root@localhost mycat]# ./bin/mycat restart
Stopping mycat2...
Stopped mycat2.
Starting mycat2...

在从MySQL上修改数据
mysql> update mytb1 set name='wangwu' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
然后使用mycat登录
查询来查看数据不同

mysql> change master to master_host='192.168.2.3',master_user='slave',master_password='1234.Com',master_log_pos=438,master_log_file='mysql-bin.000001';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='192.168.2.1',master_user='slave',master_password='1234.Com',master_log_pos=6394,master_log_file='mysql-bin.000001';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看是否master1创建库，四台同步

*m1：主机
*m2：备机，也负责读
*s1，s2：从机

/*+ mycat:createDataSource{ "name":"rwSepw2","url":"jdbc:mysql://192.168.2.3:3306/mydb1？useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root","password":"1234.Com" } */;
/*+ mycat:createDataSource{ "name":"rwSepr2","url":"jdbc:mysql://192.168.2.4:3306/mydb1？useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root","password":"1234.Com" } */;

[root@localhost mycat]# vi /usr/local/mycat/conf/clusters/prototype.cluster.json
{
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetryCount":3,
                "minSwitchTimeInterval":300,
                "showLog":false,
                "slaveThreshold":0.0
        },
        "masters":[
                "rwSepw","rwSepw2"
        ],
         "replicas":[
                "rwSepr","rwSepr2","rwSepw2"
        ],
        "maxCon":2000,
        "name":"prototype",
        "readBalanceType":"BALANCE_ALL",
        "switchType":"SWITCH"
}

#客户表 rows:20万
CREATE TABLE customer(
  id INT AUTO_INCREMENT
  NAME VARCHAR(200),
  PRIMARY KEY(id)
);
#订单表 rows:600万
CREATE TABLE orders(
  id INT AUTO_INCREMENT,
  order_type INT,
  customer_id INT,
  amount DECIMAL(10,2),
  PRIMARY KEY(id)
);
#订单详细表 rows:600万
CREATE TABLE orders_detail(
  id INT AUTO_INCREMENT,
  detail VARCHAR(2000),
  order_id INT,
  PRIMARY KEY(id)
);
#订单状态字典表 rows:20
CREATE TABLE dict_order_type(
  id INT AUTO_INCREMENT,
  order_type VARCHAR(200),
  PRIMARY KEY(id)       
);

/*+ mycat:createDataSource{
"name":"dw0",
"url":"jdbc:mysql://192.168.2.1:3306",
"user":"root",
"password":"1234.Com"
} */;
/*+ mycat:createDataSource{
"name":"dr0",
"url":"jdbc:mysql://192.168.2.1:3306",
"user":"root",
"password":"1234.Com"
} */;
/*+ mycat:createDataSource{
"name":"dw1",
"url":"jdbc:mysql://192.168.2.2:3306",
"user":"root",
"password":"1234.Com"
} */;
/*+ mycat:createDataSource{
"name":"dr1",
"url":"jdbc:mysql://192.168.2.2:3306",
"user":"root",
"password":"1234.Com"
} */;

/*!mycat:createCluster{"name":"c0","masters":["dw0"],"replicas":["dr0"]} */;
/*!
{mycat:createCluster{"name":"c1","masters":["dw1"],"replicas":["dr1"]}
*/;

#添加数据库db1
CREATE DATABASE db1;
#在建表语句中加上关键字 BROADCAST（广播，即为全局表）
CREATE TABLE db1.`travelrecord` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` varchar(100) DEFAULT NULL,
`traveldate` date DEFAULT NULL,
`fee` decimal(10,0) DEFAULT NULL,
`days` int DEFAULT NULL,
`blob` longblob,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST

#在 Mycat 终端直接运行建表语句进行数据分片
CREATE TABLE db1.orders(
id BIGINT NOT NULL AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id),
KEY `id` (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
dbpartition BY mod_hash(customer_id) tbpartition BY mod_hash(customer_id)
tbpartitions 1 dbpartitions 2;
#数据库分片规则，表分片规则，以及各分多少片
INSERT INTO orders(id,order_type,customer_id,amount)
VALUES(1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount)
VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount)
VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount)
VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount)
VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount)
VALUES(6,102,100,100020);
SELECT * FROM orders;
#同样可以查看生成的配置信息
#进入相关目录查看 schema 配置
vim /usr/local/mycat/conf/schemas/db1.schema.json

####  4.创建分片表（分库分表）
```sql
#在 Mycat 终端直接运行建表语句进行数据分片
CREATE TABLE db1.orders(
id BIGINT NOT NULL AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id),
KEY `id` (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
dbpartition BY mod_hash(customer_id) tbpartition BY mod_hash(customer_id)
tbpartitions 1 dbpartitions 2;
#数据库分片规则，表分片规则，以及各分多少片
INSERT INTO orders(id,order_type,customer_id,amount)
VALUES(1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount)
VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount)
VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount)
VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount)
VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount)
VALUES(6,102,100,100020);
SELECT * FROM orders;
#同样可以查看生成的配置信息
#进入相关目录查看 schema 配置
vim /usr/local/mycat/conf/schemas/db1.schema.jso