mycat部署

古城微笑少年丶 2023-07-08 09:07 18阅读 0赞

### mycat介绍 ###

mycat是阿里开源的一个分布式数据库中间层。

*  作用：

实现数据库的读写分离
    
    支持读负载均衡、后端mysql高可用
    
    数据库垂直拆分、水平拆分

*  应用场景：

需要读写分离
    
    需要分库分表
    
    多租户
    
    数据统计系统
    
    HBASE替代
    
    同样方式查询多种数据库

*  关键特性：

支持SQL92标准
    
    支持mysql集群
    
    支持jdbc连接数据库
    
    支持nosql数据库
    
    支持自动故障切换、高可用性
    
    支持全局表、全局序列号
    
    支持一致性hash分片、基于ER关系的分片策略

--------------------

### mycat部署 ###

*  环境：

192.168.1.231       mycat
    
    192.168.1.232       mysql master
    
    192.168.1.233       mysql slave

*  安装java：

mkdir /software && cd /software               #将安装包放到该目录下
    
    tar xf jdk-8u231-linux-x64.tar.gz && mv jdk1.8.0_231/ /usr/local/jdk

*  下载mycat：

wget http://dl.mycat.io/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
    
    tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
    
    mv mycat/ /usr/local/
    
    useradd mycat
    
    chown -R mycat:mycat /usr/local/mycat
    
    vim /etc/profile
    
    JAVA_HOME=/usr/local/jdk
    MYCAT_HOME=/usr/local/mycat
    PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$MYCAT_HOME/bin
    CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JAVA_HOME/jre/lib
    export JAVA_HOME MYCAT_HOME PATH CLASSPATH
    
    source !$
    
    java -version

*  启动mycat：

su - mycat
    
    cd /usr/local/mycat/
    
    startup_nowrap.sh
    -bash: /usr/local/mycat/bin/startup_nowrap.sh: /bin/sh^M: 坏的解释器: 没有那个文件或目录
    
    sed -i 's/\r$//' bin/startup_nowrap.sh
    
    startup_nowrap.sh 
    
    ps -aux |grep java |grep mycat
    
    cat logs/console.log
    MyCAT Server startup successfully. see logs in logs/mycat.log

*  mycat配置说明：

schema.xml      用于配置逻辑库表及数据节点
    
        <schema><table></table></schema>        定义逻辑库表
        
        <dataNode></dataNode>       定义数据节点
        
        <dataHost></dataHost>       定义数据节点的物理数据源
        
        
    rule.xml        用于配置表的分片规则
    
        <tableRule name=""></tableRule>        定义表使用的分片规则
        
        <function name=""></function>      定义分片算法
    
    server.xml      用于配置服务器权限
    
        <system><property name=""></property></system>      定义系统配置
        
        <user></user>       定义连接mycat的用户

*  配置读写分离：

首先mysql机器配置好mysql主从复制，并在mysql master上创建`user_db`库及`user_db.customer_login`表。

CREATE database user_db;
    
    USE user_db;
    
    CREATE TABLE customer_login(customer_id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,login_name VARCHAR(40),password VARCHAR(40),user_stats TINYINT UNSIGNED);

vim conf/schema.xml             #修改

<schema name="USERDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
            
            <dataNode name="dn1" dataHost="node1" database="user_db" />
    
            <dataHost name="node1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
                    <heartbeat>select user()</heartbeat>
                    <writeHost host="192.168.1.232" url="192.168.1.232:3306" user="root" password="123456789">
                            <readHost host="192.168.1.233" url="192.168.1.233:3306" user="root" password="123456789"/>
                    </writeHost>
                    <writeHost host="192.168.1.233" url="192.168.1.233:3306" user="root" password="123456789"/>
            </dataHost>

vim conf/server.xml             #修改，其余配置不变

<system>
                    <property name="serverPort">3306</property> <property name="managerPort">9066</property>
            </system>
    
            <user name="root" defaultAccount="true">
                    <property name="password">123456</property>
                    <property name="schemas">USERDB</property>
                    <property name="defaultSchema">USERDB</property>
            </user>
    
            <user name="user">
                    <property name="password">123456</property>
                    <property name="schemas">USERDB</property>
                    <property name="readOnly">true</property>
                    <property name="defaultSchema">USERDB</property>
            </user>

配置完成，重启mycat，

mycat restart

*  测试读写分离：

vim /software/init_data.py

#! /usr/bin/env python
    
    import MySQLdb
    from faker import Faker
    
    try:
        conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='123456', db='USERDB', charset="utf8")
        cursor= conn.cursor(MySQLdb.cursors.DictCursor)
    except MySQLdb.Error, e:
        print "Error %d: %s \n" % (e.args[0], e.args[1])
    
    try:
        faker=Faker()
        
        #write data
        for i in range(0,10):
            InSQL=""" insert into customer_login(login_name, password, user_stats) VALUES('%s',md5('%s'),1); """%(faker.name(), faker.ean13())
            print InSQL
            cursor.execute(InSQL)
            cursor.execute('commit')
        
        #read data
        for i in range(1,11):
            SQLstr=""" select login_name from customer_login where customer_id = %d; """%(i)
            cursor.execute(SQLstr)
            result = cursor.fetchall()
            print result
    except MySQLdb.Error, e:
        print "Error %d: %s \n" % (e.args[0], e.args[1])
    finally:
        cursor.close()
        conn.close()

yum install -y python-pip mysql-devel python-devel
    
    pip install --upgrade pip
    
    pip install MySQL-python faker
    
    python /software/init_data.py

执行结果，

insert into customer_login(login_name, password, user_stats) VALUES('Angela Hernandez',md5('4151032118150'),1);
            
    
                insert into customer_login(login_name, password, user_stats) VALUES('Cathy Howell',md5('8122034728520'),1);
            
    
                insert into customer_login(login_name, password, user_stats) VALUES('Gregory Taylor',md5('2661518375233'),1);
            
    
                insert into customer_login(login_name, password, user_stats) VALUES('Edward Gilmore',md5('8421803476830'),1);
            
    
                insert into customer_login(login_name, password, user_stats) VALUES('Jeremy Dawson',md5('2803656252087'),1);
            
    
                insert into customer_login(login_name, password, user_stats) VALUES('Michael Walton',md5('2220300773833'),1);
            
    
                insert into customer_login(login_name, password, user_stats) VALUES('Debra Williams',md5('0101458681817'),1);
            
    
                insert into customer_login(login_name, password, user_stats) VALUES('Kimberly Cooper',md5('9468150847615'),1);
            
    
                insert into customer_login(login_name, password, user_stats) VALUES('Andrew Taylor',md5('4304499804351'),1);
            
    
                insert into customer_login(login_name, password, user_stats) VALUES('Teresa Edwards',md5('6814811117352'),1);
            
    ({'login_name': u'Angela Hernandez'},)
    ({'login_name': u'Cathy Howell'},)
    ({'login_name': u'Gregory Taylor'},)
    ({'login_name': u'Edward Gilmore'},)
    ({'login_name': u'Jeremy Dawson'},)
    ({'login_name': u'Michael Walton'},)
    ({'login_name': u'Debra Williams'},)
    ({'login_name': u'Kimberly Cooper'},)
    ({'login_name': u'Andrew Taylor'},)
    ({'login_name': u'Teresa Edwards'},)

在mycat主机上登录mysql，故意插入一条错误数据，

mysql -uroot -p -h192.168.1.231 -P3306
    
    use USERDB;
    
    insert into customer_login(login_name, password, user_stats) VALUES('%s',md5('%s'));
    ERROR 1136 (HY000): Column count doesn't match value count at row 1

tail -f logs/mycat.log
    
    2020-01-20 17:27:53.000  WARN [$_NIOREACTOR-1-RW] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.backConnectionErr(SingleNodeHandler.java:284)) - execute  sql err : errno:1136 Column count doesn't match value count at row 1 con:MySQLConnection@762087023 [id=7, lastTime=1579512472990, user=root, schema=user_db, old shema=user_db, borrowed=true, fromSlaveDB=false, threadId=63, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into customer_login(login_name, password, user_stats) VALUES('%s',md5('%s'))}, respHandler=SingleNodeHandler [node=dn1{insert into customer_login(login_name, password, user_stats) VALUES('%s',md5('%s'))}, packetId=1], host=192.168.1.232, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true] frontend host:192.168.1.231/45118/root 2020-01-20 17:27:53.003 ERROR [$_NIOREACTOR-1-RW] (io.mycat.net.FrontendConnection.writeErrMessage(FrontendConnection.java:210)) - ServerConnection [id=2, schema=USERDB, host=192.168.1.231, user=root,txIsolation=3, autocommit=true, schema=USERDB, executeSql=insert into customer_login(login_name, password, user_stats) VALUES('%s',md5('%s'))]Column count doesn't match value count at row 1java.lang.Thread .getStackTrace1559
    io.mycat.net.FrontendConnection .getStack224
    io.mycat.net.FrontendConnection .writeErrMessage210
    io.mycat.backend.mysql.nio.handler.SingleNodeHandler .backConnectionErr311
    io.mycat.backend.mysql.nio.handler.SingleNodeHandler .errorResponse272
    io.mycat.backend.mysql.nio.MySQLConnectionHandler .handleErrorPacket168
    io.mycat.backend.mysql.nio.MySQLConnectionHandler .handleData97
    io.mycat.net.handler.BackendAsyncHandler .offerData36
    io.mycat.backend.mysql.nio.MySQLConnectionHandler .handle79
    io.mycat.net.AbstractConnection .handle269
    io.mycat.net.AbstractConnection .onReadData327
    io.mycat.net.NIOSocketWR .asynRead216
    io.mycat.net.AbstractConnection .asynRead279
    io.mycat.net.NIOReactor$RW .run113
    java.lang.Thread .run748
     write errorMsg:{ } error

可以看到是在mysql master（192.168.1.232）上写数据。接着使用mycat查看一条数据，

tail -f logs/mycat.log
    
    2020-01-20 17:28:37.204  INFO [$_NIOREACTOR-1-RW] (io.mycat.backend.mysql.nio.handler.NewConnectionRespHandler.connectionAcquired(NewConnectionRespHandler.java:44)) - connectionAcquired MySQLConnection@28406540 [id=15, lastTime=1579512517204, user=root, schema=user_db, old shema=user_db, borrowed=true, fromSlaveDB=true, threadId=147, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.1.233, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

可以看到读数据是在mysql slave（192.168.1.233）上操作的。这就是mycat完成的读写分离。

--------------------

### mycat管理 ###

可以使用mysql客户端对mycat进行管理。

*  登录mycat：

mysql -uroot -p -h192.168.1.231 -P9066

*  常用命令：

show @@help;        查看管理命令
    
    reload @@config;        动态加载配置
    
    show @@datanode;        查看数据节点
    
    show @@datasource;      查看物理节点
    
    show @@backend;     查看连接情况

命令示例：

reload @@config;
    Query OK, 1 row affected (0.08 sec)
    Reload config success
    
    show @@datanode;
    +------+---------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
    | NAME | DATHOST       | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
    +------+---------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
    | dn1  | node1/user_db |     0 | mysql |      0 |    9 | 1000 |     129 |          0 |        0 |       0 |            -1 |
    +------+---------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
    1 row in set (0.00 sec)
    
    show @@datasource;
    +----------+---------------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
    | DATANODE | NAME          | TYPE  | HOST          | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
    +----------+---------------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
    | dn1      | 192.168.1.232 | mysql | 192.168.1.232 | 3306 | W    |      0 |    9 | 1000 |     137 |         0 |         22 |
    | dn1      | 192.168.1.233 | mysql | 192.168.1.233 | 3306 | W    |      0 |    1 | 1000 |     107 |         2 |          0 |
    | dn1      | 192.168.1.233 | mysql | 192.168.1.233 | 3306 | R    |      0 |    7 | 1000 |     112 |         0 |          0 |
    +----------+---------------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
    3 rows in set (0.00 sec)
    
    show @@backend;
    +------------+------+---------+---------------+------+--------+--------+---------+------+--------+----------+------------+---------+---------+---------+------------+
    | processor  | id   | mysqlId | host          | port | l_port | net_in | net_out | life | closed | borrowed | SEND_QUEUE | schema  | charset | txlevel | autocommit |
    +------------+------+---------+---------------+------+--------+--------+---------+------+--------+----------+------------+---------+---------+---------+------------+
    | Processor0 |   16 |     148 | 192.168.1.233 | 3306 |  54458 |   1337 |     358 |  921 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor0 |    2 |      65 | 192.168.1.232 | 3306 |  37152 |   1308 |     387 | 1221 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor0 |   19 |     151 | 192.168.1.233 | 3306 |  54466 |     89 |      70 |   21 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor0 |    4 |      66 | 192.168.1.232 | 3306 |  37154 |   1308 |     387 | 1221 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor0 |    6 |      64 | 192.168.1.232 | 3306 |  37150 |   1427 |     838 | 1221 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor0 |    8 |      67 | 192.168.1.232 | 3306 |  37156 |   1308 |     387 | 1221 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor0 |   10 |      62 | 192.168.1.232 | 3306 |  37146 |   1231 |     369 | 1221 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor0 |   12 |     144 | 192.168.1.233 | 3306 |  54446 |   2117 |     538 | 1221 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor0 |   14 |     146 | 192.168.1.233 | 3306 |  54450 |  10219 |    2353 | 1221 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor1 |    1 |      68 | 192.168.1.232 | 3306 |  37158 |   1230 |     370 | 1221 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor1 |   17 |     149 | 192.168.1.233 | 3306 |  54460 |    791 |     232 |  621 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor1 |   18 |     150 | 192.168.1.233 | 3306 |  54464 |    401 |     142 |  321 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor1 |    5 |      60 | 192.168.1.232 | 3306 |  37142 |   1308 |     387 | 1221 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor1 |    7 |      63 | 192.168.1.232 | 3306 |  37148 |   1211 |     439 | 1221 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor1 |    9 |      61 | 192.168.1.232 | 3306 |  37144 |   1485 |    1369 | 1221 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor1 |   11 |     143 | 192.168.1.233 | 3306 |  54444 |   2195 |     556 | 1221 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor1 |   13 |     145 | 192.168.1.233 | 3306 |  54448 |   2117 |     538 | 1221 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    | Processor1 |   15 |     147 | 192.168.1.233 | 3306 |  54456 |   1259 |     340 |  921 | false  | false    |          0 | user_db | utf8:33 | 3       | true       |
    +------------+------+---------+---------------+------+--------+--------+---------+------+--------+----------+------------+---------+---------+---------+------------+
    18 rows in set (0.00 sec)

--------------------