构建Mycat读写分离的数据库集群_mycat 集群读写-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_58410911/article/details/139218642

Mycat是什么

Mycat是一个开源数据库中间件，是一个实现了MySQL协议的的数据库中间件服务器，我们可以把它看作是一个数据库代理，用MySQL客户端工具和命令行访问Mycat，而Mycat再使用用MySQL原生（Native）协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，包括SQL Server、Oracle、DB2、PostgreSQL等主流数据库，也支持MongoDB这种新型NoSQL方式的存储，未来还会支持更多类型的存储。

为什么使用读写分离

从集中到分布，最基本的一个需求不是数据存储的瓶颈，而是在于计算的瓶颈，即 SQL 查询的瓶颈，我们知道，正常情况下，Insert SQL 就是几十个毫秒的时间内写入完成，而系统中的大多数 Select SQL 则要几秒到几分钟才能有结果，很多复杂的 SQL，其消耗服器 CPU 的能力超强，不亚于死循环的威力。

基础环境

IP	主机名	节点
192.168.169.208	mycat	Mycat中间件服务节点
192.168.169.209	db1	MariaDB数据库集群主节点
192.168.169.210	db2	MariaDB数据库集群从节点

[root@mycat ~]# hostnamectl set-hostname mycat
[root@mycat ~]# bash

[root@db1 ~]# hostnamectl set-hostname db1
[root@db1 ~]# bash

[root@db2 ~]# hostnamectl set-hostname db2
[root@db2 ~]# bash

mv /etc/yum.repos.d/CentOS-* /tmp/
systemctl disable firewalld && setenforce 0
curl -o /etc/yum.repos.d/centos.repo http://mirrors.aliyun.com/repo/Centos-7.repo
curl -o /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum install -y vim net-tools
iptables -F
iptables -X
iptables -Z
iptables-save

[root@mycat ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.169.208 mycat
192.168.169.209 db1
192.168.169.210 db2

安装jdk

[root@mycat ~]# yum install -y java-1.8.0-*

[root@mycat ~]# java -version
openjdk version "1.8.0_392"
OpenJDK Runtime Environment (build 1.8.0_392-b08)
OpenJDK 64-Bit Server VM (build 25.392-b08, mixed mode)

安装mariadb

[root@db1 ~]# yum install -y mariadb-server mariadb

启动服务

[root@db1 ~]# systemctl enable mariadb --now
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

初始化数据库

[root@db1 ~]# mysql_secure_installation

修改配置文件

[mysqld]

log_bin = mysql-bin				### 记录操作日志
binlog_ignore_db = mysql	### 不同步mysql数据库
server_id = 209						### 数据库集群节点id一般使用IP

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

重启服务

[root@db1 ~]# systemctl restart mariadb

开放主节点数据库权限

[root@db1 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' identified by '000000';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant replication slave on *.* to 'user'@'db2' identified by '000000';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

配置从节点同步主节点

[root@db2 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> change master to master_host='db1',master_user='user',master_password='000000';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: db1
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 603
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 887
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              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: 603
              Relay_Log_Space: 1183
              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: 209
1 row in set (0.00 sec)

验证主从数据库的同步功能

### 主节点
[root@db1 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table company(id int not null primary key, name varchar(50),addr varchar(255));
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into company values(1,"facebook","usa");
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from company;
+----+----------+------+
| id | name     | addr |
+----+----------+------+
|  1 | facebook | usa  |
+----+----------+------+
1 row in set (0.00 sec)

### 从节点
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| company        |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> select * from company;
+----+----------+------+
| id | name     | addr |
+----+----------+------+
|  1 | facebook | usa  |
+----+----------+------+
1 row in set (0.00 sec)

部署Mycat读写分离中间件服务

schema：配置逻辑库,与server.xml中的逻辑库要对应
table：配置逻辑表，包含主键，自增在，位于哪个节点等
dataNode ：配置逻辑库与物理库的对应关系
dataHost ：配置连接数据库主机信息

[root@mycat ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat ~]# mv mycat /usr/local/
[root@mycat ~]# chmod -R 777 /usr/local/mycat/
[root@mycat ~]# vim /etc/profile
export MYCAT_HOME=/usr/local/mycat/
export PATH=$MYCAT_HOME/bin:$PATH
[root@mycat ~]# source /etc/profile

[root@mycat ~]# cat /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="USERDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"> </schema>
        <dataNode name="dn1" dataHost="localhost1" database="test" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" dbType="mysql" dbDriver="native" writeType="0" switchType="1" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.169.209:3306" user="root"
                                   password="000000">
                        <readHost host="hostS1" url="192.168.169.210:3306" user="root" password="000000" />
                </writeHost>
        </dataHost>
</mycat:schema>

sqlMaxLimit: 配置默认查询数量
database: 为真实数据库名
balance="0": 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上
balance=“1”: 全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单来说，当双主双从模式（ M1-> S1, M2 -> S2, 并且 M1 与 M2 互为主备），正常情况下，M2、S1、S2都参与 select 语句的负载均衡。
balance=“2”: 所以的读操作请求随机地发到 writeHost 对应的 readHost 执行，writeHost 不负担读压力，注意 balance=3 只在1.4及其以后有1.3版本没有
writeType=“0”: 所有写操作发送到配置的第一个 writeHost，第一个挂了需要切换到还生存的第二个 writeHost，重新启动后已切换后的为准，切换记录在配置文件 dnindex.properties 中
writeType=“1”: 所有写操作都随机的发送到配置的 writeHost

修改属组

[root@mycat ~]# chown root:root /usr/local/mycat/conf/schema.xml
[root@mycat ~]# ll /usr/local/mycat/conf/schema.xml
-rwxrwxrwx. 1 root root 678 Dec 26 16:39 /usr/local/mycat/conf/schema.xml

编辑mycat访问用户

### 修改root的访问密码和数据库
[root@mycat ~]# cat -n /usr/local/mycat/conf/server.xml | grep 000000
    81                  <property name="password">000000</property>
[root@mycat ~]# cat -n /usr/local/mycat/conf/server.xml | grep USERDB
    82                  <property name="schemas">USERDB</property>
### 删除末尾
        <user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user>


[root@mycat ~]# cat /usr/local/mycat/conf/server.xml 
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
        - you may not use this file except in compliance with the License. - You 
        may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
        - - Unless required by applicable law or agreed to in writing, software - 
        distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
        WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
        License for the specific language governing permissions and - limitations 
        under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
        <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->

                <property name="sequnceHandlerType">2</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
        <!-- <property name="processorBufferChunk">40960</property> -->
        <!-- 
        <property name="processors">1</property> 
        <property name="processorExecutor">32</property> 
         -->
                <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
                <property name="processorBufferPoolType">0</property>
                <!--默认是65535 64K 用于sql解析时最大文本长度 -->
                <!--<property name="maxStringLiteralLength">65535</property>-->
                <!--<property name="sequnceHandlerType">0</property>-->
                <!--<property name="backSocketNoDelay">1</property>-->
                <!--<property name="frontSocketNoDelay">1</property>-->
                <!--<property name="processorExecutor">16</property>-->
                <!--
                        <property name="serverPort">8066</property> <property name="managerPort">9066</property> 
                        <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
                        <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
                <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
                <property name="handleDistributedTransactions">0</property>

                        <!--
                        off heap for merge/order/group/limit      1开启   0关闭
                -->
                <property name="useOffHeapForMerge">1</property>

                <!--
                        单位为m
                -->
                <property name="memoryPageSize">1m</property>

                <!--
                        单位为k
                -->
                <property name="spillsFileBufferSize">1k</property>

                <property name="useStreamOutput">0</property>

                <!--
                        单位为m
                -->
                <property name="systemReserveMemorySize">384m</property>


                <!--是否采用zookeeper协调切换  -->
                <property name="useZKSwitch">true</property>


        </system>

        <!-- 全局SQL防火墙设置 -->
        <!-- 
        <firewall> 
           <whitehost>
              <host host="127.0.0.1" user="mycat"/>
              <host host="127.0.0.2" user="mycat"/>
           </whitehost>
       <blacklist check="false">
       </blacklist>
        </firewall>
        -->

        <user name="root">
                <property name="password">000000</property>
                <property name="schemas">USERDB</property>

                <!-- 表级 DML 权限设置 -->
                <!-- 
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>
                 -->
        </user>

</mycat:server>

启动服务

[root@mycat ~]# /bin/bash /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@mycat ~]# netstat -ntpl                             
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1032/sshd           
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1161/master         
tcp        0      0 127.0.0.1:32000         0.0.0.0:*               LISTEN      46458/java          
tcp6       0      0 :::8066                 :::*                    LISTEN      46458/java          
tcp6       0      0 :::9066                 :::*                    LISTEN      46458/java          
tcp6       0      0 :::43916                :::*                    LISTEN      46458/java          
tcp6       0      0 :::22                   :::*                    LISTEN      1032/sshd           
tcp6       0      0 :::46327                :::*                    LISTEN      46458/java          
tcp6       0      0 ::1:25                  :::*                    LISTEN      1161/master         
tcp6       0      0 :::1984                 :::*                    LISTEN      46458/java

安装MariaDB-client服务

[root@mycat ~]# cat /etc/yum.repos.d/mariadb.repo 
[mariadb]
name = MariaDB
baseurl = https://mirrors.cloud.tencent.com/mariadb/yum/10.4/centos7-amd64
gpgkey=https://mirrors.cloud.tencent.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@mycat ~]# yum install -y MariaDB-client
[root@mycat ~]# mysql -V
mysql  Ver 15.1 Distrib 10.4.32-MariaDB, for Linux (x86_64) using readline 5.1

查看Mycat服务的逻辑库USERDB

[root@mycat ~]# mysql -uroot -p000000 -h127.0.0.1 -P8066
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| USERDB   |
+----------+
1 row in set (0.004 sec)

MySQL [(none)]> use USERDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [USERDB]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| company        |
+----------------+
1 row in set (0.003 sec)

MySQL [USERDB]> select * from company;
+----+----------+------+
| id | name     | addr |
+----+----------+------+
|  1 | facebook | usa  |
+----+----------+------+
1 row in set (0.038 sec)

### 插入一条数据

MySQL [USERDB]> insert into company values(2,"bastetball","usa");
Query OK, 1 row affected (0.009 sec)

MySQL [USERDB]> select * from company;                           
+----+------------+------+
| id | name       | addr |
+----+------------+------+
|  1 | facebook   | usa  |
|  2 | bastetball | usa  |
+----+------------+------+
2 rows in set (0.004 sec)

验证Mycat服务对数据库读写操作读写分离

mycat节点

[root@mycat ~]# mysql -uroot -p000000 -h127.0.0.1 -P9066 -e "show @@datasource;"
+----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST            | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 192.168.169.209 | 3306 | W    |      0 |   10 | 1000 |      88 |         0 |          1 |
| dn1      | hostS1 | mysql | 192.168.169.210 | 3306 | R    |      0 |    7 | 1000 |      87 |         4 |          0 |
+----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+

db1节点

[root@db1 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;       
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use test;             
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> show tables;          
+----------------+
| Tables_in_test |
+----------------+
| company        |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> select * from company;
+----+------------+------+
| id | name       | addr |
+----+------------+------+
|  1 | facebook   | usa  |
|  2 | bastetball | usa  |
+----+------------+------+
2 rows in set (0.00 sec)

db2节点

[root@db2 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> select * from company;
+----+------------+------+
| id | name       | addr |
+----+------------+------+
|  1 | facebook   | usa  |
|  2 | bastetball | usa  |
+----+------------+------+
2 rows in set (0.00 sec)