mysql代理、中间件技术

mysql代理、中间件技术

代理简介

名词

DB proxy 数据库中间件

功能

读写分离:读写分离导致处理速度迅速,一般情况下是主服务器进行写操作而从服务器进行读操作
负载均衡
支持数据的分片自动路由和聚合

本文主要围绕Mycat实现、且在完成MM-SS集群的条件下

实验步骤

1.配置五台虚拟机的域名解析(在之前完成的集群技术的条件下)

[root@mysql01 ~]# vim /etc/hosts
192.168.70.33 master1
192.168.70.34 master2
192.168.70.35 slave1
192.168.70.36 slave2
192.168.70.37 mycat

2.在mycat机器上准备好jdk

jdk官网下载地址

根据上面提供的官方下载地址下载好解压
解压
[root@mysql05 ~]# tar -zxf jdk-8u221-linux-x64.tar.gz -C /usr/local/

软链接
[root@mysql05 ~]# ln -s /usr/local/jdk1.8.0_221 /usr/local/java

配置环境变量
[root@mysql05 ~]# vim /etc/profile
JAVA_HOME=/usr/local/java
CLASSPATH=.:$JAVA_HOME/lib.tools.jar
PATH=$JAVA_HOME/bin:$PATH
export JAVA_HOME CLASSPATH PATH

[root@mysql05 ~]# source /etc/profile
[root@mysql05 ~]# which java
/usr/local/java/bin/java

[root@mysql05 ~]# java -version
java version "1.8.0_221"
Java(TM) SE Runtime Environment (build 1.8.0_221-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.221-b11, mixed mode)

3.配置Mycat

[root@mysql05 ~]# wget http://dl.mycat.org.cn/1.6.7.1/Mycat-server-1.6.7.1-release-20200209222254-linux.tar.gz

解压
[root@mysql05 ~]# tar -zxvf Mycat-server-1.6.7.6-release-20210730131311-linux.tar.gz -C /usr/local/

[root@mysql05 ~]# ls /usr/local/mycat/
bin  catlet  conf  lib  logs  version.txt

3.1 配置mycat前端

备份一下xml文件 以防我们修改错误
[root@mysql05 ~]# cp /usr/local/mycat/conf/server.xml .

[root@mysql05 ~]# vim /usr/local/mycat/conf/server.xml

​ 最下面 如下修改

3.2 配置mycat后端

同样备份一下schema文件
[root@mysql05 ~]# cp /usr/local/mycat/conf/schema.xml .
[root@mysql05 ~]# vim /usr/local/mycat/conf/schema.xml

balance为0:读写不分离
balance为1:读写分离
writeType=”0“,所有的写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个writeHost,重新启动后以切换后的为准
writeType=”1“,均衡发送到两个服务器上
switchType='-1' 表示不自动切换
switchType='1' 默认值,表示根据延时自动切换
switchType='2' 根据MySQL主从同步的状态决定是否切换,心跳语句为show slave status

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="mycatDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="mycatDB" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="master1" url="master1:3306" user="mycatproxy"
                                   password="Xuxin_0709">
                        <readHost host="slave1" url="slave1:3306" user="mycatproxy" password="Xuxin_0709" />
                        <readHost host="slave2" url="slave2:3306" user="mycatproxy" password="Xuxin_0709" />
                </writeHost>

                <writeHost host="master2" url="master2:3306" user="mycatproxy"
                                   password="Xuxin_0709">
                        <readHost host="slave1" url="slave1:3306" user="mycatproxy" password="Xuxin_0709" />
                        <readHost host="slave2" url="slave2:3306" user="mycatproxy" password="Xuxin_0709" />
                </writeHost>
        </dataHost>
</mycat:schema>

4.配置mysql群

M-M-S-S 准备Mycat连接的用户及权限 (这里不要填详细ip 填域名mycat!)

在master1上
mysql> create user 'mycatproxy'@'mycat' identified by 'Xuxin_0709';

mysql> grant all privileges on *.* to 'mycatproxy'@'mycat';

mysql> flush privileges;

5.启动Mycat

[root@mysql05 ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...

查看日志 看是否存在错误
[root@mysql05 logs]# tail -f /usr/local/mycat/logs/wrapper.log 

如果看到如下错误
Startup failed: Timed out waiting for a signal from the JVM.
JVM did not exit on request, terminated
可以试试编辑wrapper.conf文件
vim /usr/local/mycat/conf/wrapper.conf 
添加如下语句,增加启动时间 
wrapper.startup.timeout=300

日志无异常的话查看端口 8066 表示已经成功启动mycat了
[root@mysql05 ~]# netstat -tunlp
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      949/sshd            
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1366/master         
tcp        0      0 127.0.0.1:32000         0.0.0.0:*               LISTEN      10085/java          
tcp6       0      0 :::8066                 :::*                    LISTEN      10085/java          
tcp6       0      0 :::33060                :::*                    LISTEN      1004/mysqld         
tcp6       0      0 :::9066                 :::*                    LISTEN      10085/java          
tcp6       0      0 :::3306                 :::*                    LISTEN      1004/mysqld         
tcp6       0      0 :::40747                :::*                    LISTEN      10085/java          
tcp6       0      0 :::22                   :::*                    LISTEN      949/sshd            
tcp6       0      0 ::1:25                  :::*                    LISTEN      1366/master         
tcp6       0      0 :::37215                :::*                    LISTEN      10085/java          
tcp6       0      0 :::1984                 :::*                    LISTEN      10085/java          
udp        0      0 127.0.0.1:323           0.0.0.0:*                           672/chronyd         
udp        0      0 0.0.0.0:68              0.0.0.0:*                           746/dhclient        
udp6       0      0 ::1:323                 :::*                                672/chronyd         

6.安装mariadb(mysql客户端)

注意:如果已经安装mysql服务端 可以不安装mariadb 关闭mysql服务端即可。

yum install mariadb -y

7.登录mycat账号

[root@mysql05 ~]# mysql -hmycat -uroot -p'Xuxin_0709' -P8066;

mysql> show databases;  //虚拟的数据库 先不要在此数据库做任何操作!
+----------+
| DATABASE |
+----------+
| mycatDB  |
+----------+
1 row in set (0.02 sec)

8.先去集群创建mycatDB和表(master1)

mysql> create database mycatDB;

mysql> create table mycatDB.t1(id int(10));

mysql> insert into mycatDB.t1 values(1);

9.再回到mycat机器 (如果执行sql语句卡住,试着检查授权问题,配置文件是否正确)

MySQL [(none)]> use mycatDB;
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 [mycatDB]> select * from mycatDB.t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

热门相关:流鱼无恙   买妻种田:山野夫君,强势宠!   买妻种田:山野夫君,强势宠!   不科学御兽   拒嫁豪门,前妻太抢手