mycat的安装到简单使用hello world

我不是女神ヾ 2022-11-21 11:28 123阅读 0赞

## 写在前面 ##

环境：centos6，jdk8，mycat1.6，mysql5.6。

## 1：安装jdk ##

略

## 2：安装mysql ##

### 2.1：下载 ###

在[这里][Link 1]下载安装包。

### 2.2：安装 ###

*  创建mysql的用户和组

groupadd mysql
    useradd -g mysql mysql

*  解压到`/usr/local`  
    `tar -zxvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz -C /usr/local`
 *  将解压后的文件命名为`mysql`。
 *  在/etc下创建my.cnf

[mysql]
    #客户端默认字符集
    default-character-set=utf8
    socket=/var/lib/mysql/mysql.sock
    [mysqld]
    skip-name-resolve
    #设置3306端口
    port=3306
    socket=/var/lib/mysql/mysql.sock
    # 设置mysql的安装目录
    basedir=/usr/local/mysql
    # 设置mysql数据库的数据的存放目录
    datadir=/usr/local/mysql/data
    # 允许最大连接数
    max_connections=200
    # 服务端使用的字符集默认为8比特编码的latin1字符集
    character-set-server=utf8
    # 创建新表时将使用的默认存储引擎
    default-storage-engine=INNODB
    lower_case_table_names=1
    max_allowed_packet=16M

*  创建相关目录

mkdir /var/lib/mysql
    mkdir /var/lib/mysql/mysql
    chown -R mysql:mysql /var/lib/mysql
    chown -R mysql:mysql /var/lib/mysql/mysql

*  初始化数据

cd /usr/local/mysql
    chown -R mysql:mysql ./
    ./scripts/mysql_install_db --user=mysql --force
    chown -R mysql:mysql data

*  配置（包含开机启动）

chown 777 /etc/my.cnf 
    cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld # 拷贝开机启动脚本
    chmod +x /etc/rc.d/init.d/mysqld # 赋予启动脚本执行权限
    chkconfig --add mysqld # 加入到服务中
    chkconfig --list mysqld # 检查是否生效，类似如下即为生效：mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off

*  相关命令

service mysqld [start|stop|restart]

## 3：安装mycat ##

### 3.1：下载 ###

在[这里][Link 2]下载。

### 3.2：解压 ###

将文件解压到`/opt`目录下，解压命令为`tar -zxvf`。

### 3.3：尝试启动 ###

/opt/mycat/bin/mycat start

查看启动是否成功：

[root@localhost mycat]# tail -10 /opt/mycat/logs/wrapper.log 
    STATUS | wrapper  | 2020/10/31 20:44:34 | --> Wrapper Started as Daemon
    STATUS | wrapper  | 2020/10/31 20:44:35 | Launching a JVM...
    INFO   | jvm 1    | 2020/10/31 20:44:35 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
    INFO   | jvm 1    | 2020/10/31 20:44:36 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
    INFO   | jvm 1    | 2020/10/31 20:44:36 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
    INFO   | jvm 1    | 2020/10/31 20:44:36 | 
    INFO   | jvm 1    | 2020/10/31 20:44:39 | log4j:WARN No appenders could be found for logger (io.mycat.memory.MyCatMemory).
    INFO   | jvm 1    | 2020/10/31 20:44:39 | log4j:WARN Please initialize the log4j system properly.
    INFO   | jvm 1    | 2020/10/31 20:44:39 | log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
    INFO   | jvm 1    | 2020/10/31 20:44:40 | MyCAT Server startup successfully. see logs in logs/mycat.log

## 4：正式开始 ##

### 4.1：环境介绍 ###

两台mysql实例：
    	192.168.2.113:3306 root/12345678
    	192.168.2.114:3306 root/12345678
    一台mycat：
        192.168.2.113:8066

### 4.2：配置mycat用户 ###

编辑`/opt/mycat/conf/server.xml`，添加如下内容。

最后我们连接mycat使用的就是这个账号信息。

### 4.3：配置路由规则 ###

添加如下内容：

<tableRule name="mod-long-111">
             <rule>
                     <columns>id</columns>
                     <algorithm>mod-long-dongshidaddy</algorithm>
             </rule>
     </tableRule>
     <function name="mod-long-dongshidaddy" class="io.mycat.route.function.PartitionByMod">
            <property name="count">2</property>
    </function>

`io.mycat.route.function.PartitionByMod`配置路由规则类，`<property name="count">2</property>`代表一共是2个mysql实例。

### 4.4：配置schema信息 ###

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    	<schema name="test_db" checkSQLschema="false" sqlMaxLimit="100">
    	        <table name="t_user" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2" ruleRequired="true" rule="mod-long-111"></table>
    	</schema>
    	<dataNode name="dn1" dataHost="localhost1" database="test_db" />
    	<dataNode name="dn2" dataHost="localhost2" database="test_db" />
    	 
    	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    	        <heartbeat>select user()</heartbeat>
    	        
    	        <writeHost host="hostM1" url="192.168.2.114:3306" user="root" password="12345678"></writeHost>
    	</dataHost>
    	<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    	        <heartbeat>select user()</heartbeat>
    	        <writeHost host="hostS1" url="192.168.2.113:3306" user="root" password="12345678" />
    	</dataHost>
    </mycat:schema>

`<schema>`节点配置数据库信息，表信息，路由规则等信息。`<dataNode>`配置数据库节点信息，`<dataHost`配置具体的数据库信息，以及健康检查信息。

### 4.5：准备数据 ###

在两个mysql实例分别创建`test_db`数据库，并创建t\_user表，如下：

mysql> create table t_user (id int not null primary key,name varchar(100));
    Query OK, 0 rows affected (0.24 sec)
    
    mysql> insert into t_user(id,name) values(1,'hello mycat');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into t_user(id,name) values(2,'javacoder.cn');
    Query OK, 1 row affected (0.00 sec)

### 4.6：启动mycat ###

/opt/mycat/bin/mycat start

### 4.7：连接mycat ###

[root@localhost logs]# mysql -uroot -p -h192.168.2.113 -P8066
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.6.29-mycat-1.6.6.1-release-20181031195535 MyCat Server (OpenCloudDB)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql>

### 4.8：查询数据 ###

mysql> show databases;
    +----------+
    | DATABASE |
    +----------+
    | test_db  |
    +----------+
    1 row in set (0.03 sec)
    
    mysql> use test_db;
    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> select * from t_user where id=1;
    +----+-------------+
    | id | name        |
    +----+-------------+
    |  1 | hello mycat |
    +----+-------------+
    1 row in set (0.31 sec)

### 4.9：测试根据id分片 ###

*  在192.168.2.113执行如下SQL

insert into t_user(id,name) values(3,'id=3,192.168.2.113');
    insert into t_user(id,name) values(4,'id=4,192.168.2.113');

*  在192.168.2.114执行如下SQL

insert into t_user(id,name) values(3,'id=3,192.168.2.114');
    insert into t_user(id,name) values(4,'id=4,192.168.2.114');

*  连接mycat并查询

xbdeMacBook-Air:finalshell root# mysql -uroot -p -h192.168.2.113 -P8066
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 10
    Server version: 5.6.29-mycat-1.6.6.1-release-20181031195535 MyCat Server (OpenCloudDB)
    
    Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> use test_db;
    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> select * from t_user where id=3;
    +----+--------------------+
    | id | name               |
    +----+--------------------+
    |  3 | id=3,192.168.2.113 |
    +----+--------------------+
    1 row in set (0.01 sec)
    
    mysql> select * from t_user where id=4;
    +----+--------------------+
    | id | name               |
    +----+--------------------+
    |  4 | id=4,192.168.2.114 |
    +----+--------------------+
    1 row in set (0.01 sec)

可以看到`id=3`的数据从`192.168.2.113`查询到了，`id=4`从`192.168.2.114`查询到了。

### 4.10：测试插入数据分片 ###

*  通过mycat执行如下SQL

xbdeMacBook-Air:finalshell root# mysql -uroot -p -h192.168.2.113 -P8066
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 10
    ...
    mysql> insert into t_user(id,name) values(5,'id=5,insert by mycat middleware');
    Query OK, 1 row affected (0.10 sec)
    
    mysql> insert into t_user(id,name) values(6,'id=6,insert by mycat middleware');
    Query OK, 1 row affected (0.31 sec)

*  在`192.168.2.113`查询

xbdeMacBook-Air:finalshell xb$ mysql -uroot -p -h192.168.2.113 -P3306
    Enter password: 
    ...
    mysql> use test_db;
    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> select * from t_user where id=5;
    +----+---------------------------------+
    | id | name                            |
    +----+---------------------------------+
    |  5 | id=5,insert by mycat middleware |
    +----+---------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from t_user where id=6;
    Empty set (0.04 sec)

可以看到id为5的数据写入了，id为6的数据没有写入。

*  在`192.168.2.114`查询

xbdeMacBook-Air:finalshell xb$ mysql -uroot -p -h192.168.2.114 -P3306
    Enter password: 
    ...
    mysql> use test_db;
    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> select * from t_user where id=5;
    Empty set (0.01 sec)
    
    mysql> select * from t_user where id=6;
    +----+---------------------------------+
    | id | name                            |
    +----+---------------------------------+
    |  6 | id=6,insert by mycat middleware |
    +----+---------------------------------+
    1 row in set (0.01 sec)

可以看到id为5的数据没有写入，id为6的数据写入了。

## 最后：都让开，我要喝瑞幸 ##

![在这里插入图片描述][20200828090303329.png_pic_center]

[Link 1]: https://download.csdn.net/download/wang0907/13081633
[Link 2]: https://download.csdn.net/download/wang0907/13081646
[20200828090303329.png_pic_center]: /images/20221120/b98e36b952a54225bc23dc46303be25a.png