1 案例1：部署mycat服务

部署mycat服务器_mycat

1.1 问题

  1. 把主机mysql60 配置为 MySQL59 的从服务器
  2. 把主机mysql62 配置为 MySQL61 的从服务器
  3. 把主机mycat63 配置为mycat服务器

1.2 方案

部署mycat服务器_分库分表_02

准备5台虚拟机，具体配置如表-1

部署mycat服务器_mysql_03

表-1

准备虚拟机，在真机执行如下命令

ssh  root@192.168.88.59
 yum  -y  install  mysql-server  mysql
 systemctl  start mysqld
 exit
 
 ssh  root@192.168.88.60
 yum  -y  install  mysql-server  mysql
 systemctl  start mysqld
 exit
 
 ssh  root@192.168.88.61
 yum  -y  install  mysql-server  mysql
 systemctl  start mysqld
 exit
 
 ssh  root@192.168.88.62
 yum  -y  install  mysql-server  mysql
 systemctl  start mysqld
 exit
 
 ssh  root@192.168.88.63
 yum  -y  install  mysql-server  mysql
 systemctl  start mysqld
 exit

1.3 步骤

环境准备

步骤一：把MySQL60配置为MySQL59的从服务器

1）配置主服务器MySQL59

//启用binlog日志
  [root@mysql59 ~]# vim /etc/my.cnf.d/mysql-server.cnf
 [mysqld]
 server-id=59
 log-bin=mysql59
 :wq
 
 //重启mysqld服务
 [root@mysql59 ~]# systemctl  restart mysqld
 
 //用户授权
 [root@mysql59 ~]# mysql
 mysql> create user repluser@"%" identified by "123qqq...A";   创建用户
 mysql> grant replication slave on *.*  to repluser@"%";  授予权限
 
 //查看日志信息
 mysql> show master status;
 +----------------+----------+--------------+------------------+-------------------+
 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +----------------+----------+--------------+------------------+-------------------+
 | mysql59.000001 |      667 |              |                  |                   |
 +----------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)

2）配置slave服务器MySQL60

//指定server-id 
  [root@mysql60 ~]# vim /etc/my.cnf.d/mysql-server.cnf
 [mysqld]
 server-id=60
 :wq
 //重启mysqld服务
 [root@mysql60 ~]# systemctl  restart mysqld
 
 //指定主服务器信息
 [root@mysql60 ~]# mysql
 mysql> change master to  
 master_host="192.168.88.59",
 master_user="repluser",
 master_password="123qqq...A",
 master_log_file="mysql59.000001",
 master_log_pos=667;
 
 //启动slave进程
 mysql> start slave ; 
 
 //查看状态信息
 mysql> show slave status \G  
 *************************** 1. row ***************************
                Slave_IO_State: Waiting for source to send event
                   Master_Host: 192.168.88.59
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql59.000001
           Read_Master_Log_Pos: 667
                Relay_Log_File: mysql60-relay-bin.000002
                 Relay_Log_Pos: 322
         Relay_Master_Log_File: mysql59.000001
              Slave_IO_Running: Yes   //IO线程
             Slave_SQL_Running: Yes   //SQL线程
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 667
               Relay_Log_Space: 533
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 59
                   Master_UUID: 38c02165-005e-11ee-bd2d-525400007271
              Master_Info_File: mysql.slave_master_info
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
            Master_Retry_Count: 86400
                   Master_Bind: 
       Last_IO_Error_Timestamp: 
      Last_SQL_Error_Timestamp: 
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
            Retrieved_Gtid_Set: 
             Executed_Gtid_Set: 
                 Auto_Position: 0
          Replicate_Rewrite_DB: 
                  Channel_Name: 
            Master_TLS_Version: 
        Master_public_key_path: 
         Get_master_public_key: 0
             Network_Namespace: 
 1 row in set, 1 warning (0.00 sec)
 mysql>

步骤二：把MySQL62配置为MySQL61的从服务器

1）配置主服务器MySQL61

//启用binlog日志
 [root@mysql61 ~]# vim /etc/my.cnf.d/mysql-server.cnf
 [mysqld]
 server-id=61
 log-bin=mysql61
 :wq
 [root@mysql61 ~]# systemctl  restart mysqld
 
 //用户授权
 [root@mysql61 ~]# mysql
 mysql> create user repluser@"%" identified by "123qqq...A"; 创建用户
 mysql> grant replication slave on *.*  to repluser@"%"; 授予权限
 
 //查看日志信息
 mysql> show master status;
 +----------------+----------+--------------+------------------+-------------------+
 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +----------------+----------+--------------+------------------+-------------------+
 | mysql61.000001 |      667 |              |                  |                   |
 +----------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)

2）配置slave服务器MySQL62

//指定server-id 
  [root@mysql62 ~]# vim /etc/my.cnf.d/mysql-server.cnf
 [mysqld]
 server-id=62
 :wq
 [root@mysql62 ~]# systemctl  restart mysqld
 //指定主服务器信息
 [root@mysql62 ~]# mysql
 mysql> change master to  
 master_host="192.168.88.61",
 master_user="repluser",
 master_password="123qqq...A",
 master_log_file="mysql61.000001", 
 master_log_pos=667;
 
 //启动slave进程
 mysql> start slave ; 
 
 //查看状态信息
 mysql> show slave status \G  
 *************************** 1. row ***************************
                Slave_IO_State: Waiting for source to send event
                   Master_Host: 192.168.88.61
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql61.000001
           Read_Master_Log_Pos: 667
                Relay_Log_File: mysql62-relay-bin.000002
                 Relay_Log_Pos: 322
         Relay_Master_Log_File: mysql61.000001
              Slave_IO_Running: Yes   //IO线程
             Slave_SQL_Running: Yes   //SQL线程
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 667
               Relay_Log_Space: 533
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 61
                   Master_UUID: 38c02165-005e-11ee-bd2d-525400007271
              Master_Info_File: mysql.slave_master_info
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
            Master_Retry_Count: 86400
                   Master_Bind: 
       Last_IO_Error_Timestamp: 
      Last_SQL_Error_Timestamp: 
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
            Retrieved_Gtid_Set: 
             Executed_Gtid_Set: 
                 Auto_Position: 0
          Replicate_Rewrite_DB: 
                  Channel_Name: 
            Master_TLS_Version: 
        Master_public_key_path: 
         Get_master_public_key: 0
             Network_Namespace: 
 1 row in set, 1 warning (0.00 sec)
 mysql>

步骤三：把主机mycat63配置为mycat服务器。

1)从真机拷贝软件到mycat63主机

[root@server1 ~]# scp /linux-soft/s3/mycat2-1.21-release-jar-with-dependencies.jar  root@192.168.88.63:/root/
 [root@server1 ~]# scp /linux-soft/s3/mycat2-install-template-1.21.zip  root@192.168.88.63:/root/

2)安装mycat软件

//安装jdk
 [root@mycat63 ~]# yum -y install java-1.8.0-openjdk.x86_64
 //安装解压命令
 [root@mycat63 ~]# which unzip || yum -y  install unzip
 //安装mycat
 [root@mycat63 ~]# unzip mycat2-install-template-1.21.zip
 [root@mycat63 ~]# mv mycat /usr/local/
 //安装依赖
 [root@mycat63 ~]# cp mycat2-1.21-release-jar-with-dependencies.jar  /usr/local/mycat/lib/
 //修改权限
 [root@mycat63 ~]# chmod -R 777 /usr/local/mycat/

3)定义客户端连接时使用的用户：

[root@mycat63 ~]# vim  /usr/local/mycat/conf/users/root.user.json
 {
         "dialect":"mysql",
         "ip":null,
         "password":"654321", 密码
         "transactionType":"proxy",
         "username":"mycat"  用户名
 }
 :wq

4）定义连接的数据库服务

[root@mycat63 ~]# vim  /usr/local/mycat/conf/datasources/prototypeDs.data.json
 {
         "dbType":"mysql",
         "idleTimeout":60000,
         "initSqls":[],
         "initSqlsGetConnection":true,
         "instanceType":"READ_WRITE",
         "maxCon":1000,
         "maxConnectTimeout":3000,
         "maxRetryCount":5,
         "minCon":1,
         "name":"prototypeDs",
         "password":"123456", 密码
         "type":"JDBC",
         "url":"jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimeznotallow=Asia/Shanghai&characterEncoding=UTF-8", 连接本机的数据库服务
         "user":"plj", 用户名
         "weight":0
 }
 :wq

5)在mycat63主机运行数据库服务

//创建plj用户
 [root@mycat63 ~]# mysql
 mysql> create user plj@"%" identified by "123456"; 创建用户
 mysql> grant all on *.* to plj@"%" ; 授予权限
 mysql> exit
 Bye
 [root@mycat63 ~]#

6)启动mycat服务

//查看命令帮助
 [root@mycat63 ~]# /usr/local/mycat/bin/mycat help
 Usage: /usr/local/mycat/bin/mycat { console | start | stop | restart | status | dump }
 //启动mycat服务
 [root@mycat63 ~]# /usr/local/mycat/bin/mycat start
 Starting mycat2...
 //半分钟左右 能看到端口
 [root@mycat63 ~]# netstat  -utnalp  | grep 8066
 tcp6       0      0 :::8066  :::*       LISTEN      57015/java          
 [root@mycat63 ~]#

步骤四：连接mycat服务器

1）连接本机的mycat服务

[root@mycat63 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321

步骤五：添加数据源

//添加MySQL59主机
 MySQL>/*+ mycat:createdatasource{
 "name":"dw0",
 "url":"jdbc:mysql://192.168.88.59:3306",
 "user":"plj",
 "password":"123456"
 }*/;
 //添加MySQL60主机
 Mysql>/*+ mycat:createdatasource{
 "name":"dr0",
 "url":"jdbc:mysql://192.168.88.60:3306",
 "user":"plj",
 "password":"123456"
 }*/;
 //添加MySQL61主机
 Mysql>/*+ mycat:createdatasource{
 "name":"dw1", 
 "url":"jdbc:mysql://192.168.88.61:3306",
 "user":"plj",
 "password":"123456"
 }*/;
 //添加MySQL62主机
 Mysql>/*+ mycat:createdatasource{
 "name":"dr1", 
 "url":"jdbc:mysql://192.168.88.62:3306",
 "user":"plj",
 "password":"123456"
 }*/;
 Mysql>

2）查看数据信息

mysql> /*+mycat:showDataSources{}*/ \G
 *************************** 1. row ***************************
                    NAME: dw0
                USERNAME: plj
                PASSWORD: 123456
                 MAX_CON: 1000
                 MIN_CON: 1
               EXIST_CON: 0
                 USE_CON: 0
         MAX_RETRY_COUNT: 5
     MAX_CONNECT_TIMEOUT: 30000
                 DB_TYPE: mysql
                     URL: jdbc:mysql://192.168.88.59:3306?serverTimeznotallow=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoRecnotallow=true
                  WEIGHT: 0
                INIT_SQL: 
 INIT_SQL_GET_CONNECTION: true
           INSTANCE_TYPE: READ_WRITE
            IDLE_TIMEOUT: 60000
                  DRIVER: {
         CreateTime:"2023-05-08 16:10:26",
         ActiveCount:0,
         PoolingCount:0,
         CreateCount:0,
         DestroyCount:0,
         CloseCount:0,
         ConnectCount:0,
         Connections:[
         ]
 }
                    TYPE: JDBC
                IS_MYSQL: true
 *************************** 2. row ***************************
                    NAME: dw1
                USERNAME: plj
                PASSWORD: 123456
                 MAX_CON: 1000
                 MIN_CON: 1
               EXIST_CON: 0
                 USE_CON: 0
         MAX_RETRY_COUNT: 5
     MAX_CONNECT_TIMEOUT: 30000
                 DB_TYPE: mysql
                     URL: jdbc:mysql://192.168.88.61:3306?serverTimeznotallow=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoRecnotallow=true
                  WEIGHT: 0
                INIT_SQL: 
 INIT_SQL_GET_CONNECTION: true
           INSTANCE_TYPE: READ_WRITE
            IDLE_TIMEOUT: 60000
                  DRIVER: {
         CreateTime:"2023-05-08 16:10:26",
         ActiveCount:0,
         PoolingCount:0,
         CreateCount:0,
         DestroyCount:0,
         CloseCount:0,
         ConnectCount:0,
         Connections:[
         ]
 }
                    TYPE: JDBC
                IS_MYSQL: true
 *************************** 3. row ***************************
                    NAME: dr0
                USERNAME: plj
                PASSWORD: 123456
                 MAX_CON: 1000
                 MIN_CON: 1
               EXIST_CON: 0
                 USE_CON: 0
         MAX_RETRY_COUNT: 5
     MAX_CONNECT_TIMEOUT: 30000
                 DB_TYPE: mysql
                     URL: jdbc:mysql://192.168.88.61:3306?serverTimeznotallow=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoRecnotallow=true
                  WEIGHT: 0
                INIT_SQL: 
 INIT_SQL_GET_CONNECTION: true
           INSTANCE_TYPE: READ_WRITE
            IDLE_TIMEOUT: 60000
                  DRIVER: {
         CreateTime:"2023-05-08 16:10:26",
         ActiveCount:0,
         PoolingCount:0,
         CreateCount:0,
         DestroyCount:0,
         CloseCount:0,
         ConnectCount:0,
         Connections:[
         ]
 }
                    TYPE: JDBC
                IS_MYSQL: true
 *************************** 4. row ***************************
                    NAME: dr1
                USERNAME: plj
                PASSWORD: 123456
                 MAX_CON: 1000
                 MIN_CON: 1
               EXIST_CON: 0
                 USE_CON: 0
         MAX_RETRY_COUNT: 5
     MAX_CONNECT_TIMEOUT: 30000
                 DB_TYPE: mysql
                     URL: jdbc:mysql://192.168.88.62:3306?useUnicode=true&serverTimeznotallow=Asia/Shanghai&characterEncoding=UTF-8&autoRecnotallow=true
                  WEIGHT: 0
                INIT_SQL: 
 INIT_SQL_GET_CONNECTION: true
           INSTANCE_TYPE: READ_WRITE
            IDLE_TIMEOUT: 60000
                  DRIVER: {
         CreateTime:"2023-05-08 16:10:26",
         ActiveCount:0,
         PoolingCount:0,
         CreateCount:0,
         DestroyCount:0,
         CloseCount:0,
         ConnectCount:0,
         Connections:[
         ]
 }
                    TYPE: JDBC
                IS_MYSQL: true
 *************************** 5. row ***************************
                    NAME: prototypeDs
                USERNAME: plj
                PASSWORD: 123456
                 MAX_CON: 1000
                 MIN_CON: 1
               EXIST_CON: 0
                 USE_CON: 0
         MAX_RETRY_COUNT: 5
     MAX_CONNECT_TIMEOUT: 3000
                 DB_TYPE: mysql
                     URL: jdbc:mysql://localhost:3306/mysql?serverTimeznotallow=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoRecnotallow=true
                  WEIGHT: 0
                INIT_SQL: 
 INIT_SQL_GET_CONNECTION: true
           INSTANCE_TYPE: READ_WRITE
            IDLE_TIMEOUT: 60000
                  DRIVER: {
         CreateTime:"2023-05-08 16:10:26",
         ActiveCount:0,
         PoolingCount:0,
         CreateCount:0,
         DestroyCount:0,
         CloseCount:0,
         ConnectCount:0,
         Connections:[
         ]
 }
                    TYPE: JDBC
                IS_MYSQL: true
 5 rows in set (0.07 sec)
 
 mysql>

3）查看存放目录

[root@mycat63 ~]# ls /usr/local/mycat/conf/datasources/
 dr0.datasource.json  dr1.datasource.json  dw0.datasource.json  dw1.datasource.json  prototypeDs.datasource.json

步骤六：配置数据库服务器

//在主服务器 MySQL59 主机添加plj用户

[root@mysql59 ~]# mysql 
 mysql> create user plj@"%" identified by "123456";
 Mysql> grant all on *.*  to plj@"%";

//在主服务器 MySQL61 主机添加plj用户

[root@mysql61 ~]# mysql 
 mysql> create user plj@"%" identified by "123456";
 Mysql> grant all on *.*  to plj@"%";

//在从服务器MySQL60 查看用户

[root@mysql60 ~]# mysql -e 'select user from mysql.user where user="plj"'
 
 
 | user |
 +------+
 | plj  |
 +------+
 [root@mysql60 ~]#

//在从服务器MySQL62 查看用户

[root@mysql62 ~]# mysql -e 'select user from mysql.user where user="plj"'
 +------+
 | user |
 +------+
 | plj  |
 +------+
 [root@host62 ~]#

步骤七：创建集群

1）连接mycat服务

[root@mycat63 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321

2）创建第1个集群

mysql>/*!mycat:createcluster{
 "name":"c0",
 "masters":["dw0"],
 "replicas":["dr0"]
 }*/;

3）创建第2个集群

mysql>/*!mycat:createcluster{
 "name":"c1",
 "masters":["dw1"],
 "replicas":["dr1"]
 }*/;
 Mysql>

4）查看集群信息

mysql> /*+ mycat:showClusters{}*/ \G
 *************************** 1. row ***************************
              NAME: prototype
       SWITCH_TYPE: SWITCH
 MAX_REQUEST_COUNT: 200
              TYPE: BALANCE_ALL
          WRITE_DS: prototypeDs
           READ_DS: prototypeDs
           WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
            READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
         AVAILABLE: true
 *************************** 2. row ***************************
              NAME: c0
       SWITCH_TYPE: SWITCH
 MAX_REQUEST_COUNT: 2000
              TYPE: BALANCE_ALL
          WRITE_DS: dw0
           READ_DS: dw0,dr0
           WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
            READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
         AVAILABLE: true
 *************************** 3. row ***************************
              NAME: c1
       SWITCH_TYPE: SWITCH
 MAX_REQUEST_COUNT: 2000
              TYPE: BALANCE_ALL
          WRITE_DS: dw1
           READ_DS: dw1,dr1
           WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
            READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
         AVAILABLE: true
 3 rows in set (0.03 sec)
 
 mysql>

5）创建的集群保存在mycat安装目录下

[root@MySQL63 ~]# ls /usr/local/mycat/conf/clusters/
 c0.cluster.json  c1.cluster.json  prototype.cluster.json
 [root@mycat63 ~]#