MYSQL-使用mycat工具实现mysql主从读写分离

秒速五厘米 2022-12-09 05:16 208阅读 0赞

### 文章目录 ###

*  MYSQL-使用mycat工具实现mysql主从读写分离
 *   *  1. 读写分离原理介绍
     *  2. mycat数据库分库分表中间件
     *   *  2.1 mycat介绍
         *  2.2 mycat中间件实现读写分离部署
         *  2.3 mycat读写分离测试

# MYSQL-使用mycat工具实现mysql主从读写分离 #

## 1. 读写分离原理介绍 ##

**生产环境架构**：使用主从复制的方式同步数据，再通过读写分离来提高数据库的并发负载能力。

**读写分离的优点：**  
①对于越来越大的访问压力，单台服务器的性能成为了瓶颈，需要分担负载。  
②主库负责写，从库负责读，缓解了X锁(排他锁)和S锁(共享锁)的占用。  
③从库可以使用myisam引擎，提升查询性能，节约系统开销。  
④增加冗余，提高可用性。

**读写分离的实现：**  
**①应用程序层实现**：在应用程序内部及连接器中实现读写分离。【内部实现读写分离、部署较易、访问能力不大时性能较好、架构调整代码也要调整、难以实现高级应用、无法适用大型应用场景】  
**②中间件实现**：在外部中间件程序实现读写分离。【mycat，其他不多介绍】

## 2. mycat数据库分库分表中间件 ##

### 2.1 mycat介绍 ###

mycat是一款开源的、面向企业应用开发的数据库集群。支持事务、ACID，是一款新型的数据库中间件产品。

**mysql实现读写分离拓扑图：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center]  
mycat可以在windows、linux、mac、solaris等系统上安装运行。

**mycat官网：** `http://www.mycat.org.cn/`

### 2.2 mycat中间件实现读写分离部署 ###

<table> 
 <thead> 
  <tr> 
   <th>主机名</th> 
   <th>IP地址</th> 
   <th>主从数据库类型</th> 
   <th>mysql版本</th> 
   <th>mycat安装主机</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>master</td> 
   <td>192.168.10.10</td> 
   <td>主库</td> 
   <td>mysql 5.7.31</td> 
   <td>192.168.10.10</td> 
  </tr> 
  <tr> 
   <td>slave</td> 
   <td>192.168.10.20</td> 
   <td>从库</td> 
   <td>mysql 5.7.31</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

**①下载并解压mycat：**

# 下载tar包并解压到/usr/local/mycat目录
    [root@master ~]# ll Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz 
    -rw-r--r--. 1 root root 21760812 9月  23 15:36 Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
    [root@master ~]# tar xzf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /usr/local/
    [root@master ~]# ll /usr/local/mycat/
    总用量 12
    drwxr-xr-x. 2 root root  190 9月  23 15:37 bin
    drwxrwxrwx. 2 root root    6 10月 22 2019 catlet
    drwxrwxrwx. 4 root root 4096 9月  23 15:37 conf
    drwxr-xr-x. 2 root root 4096 9月  23 15:37 lib
    drwxrwxrwx. 2 root root    6 1月   5 2020 logs
    -rwxrwxrwx. 1 root root  227 1月   5 2020 version.txt
    
    # 创建mycat用户，并设置密码
    [root@master ~]# useradd mycat
    [root@master ~]# echo 123456 | passwd --stdin mycat
    更改用户 mycat 的密码 。
    passwd：所有的身份验证令牌已经成功更新。
    
    # 设置/usr/local/mycat目录的所有者和所属组为mycat
    [root@master ~]# chown -R mycat:mycat /usr/local/mycat/
    [root@master ~]# ll /usr/local/mycat/
    总用量 12
    drwxr-xr-x. 2 mycat mycat  190 9月  23 15:37 bin
    drwxrwxrwx. 2 mycat mycat    6 10月 22 2019 catlet
    drwxrwxrwx. 4 mycat mycat 4096 9月  23 15:37 conf
    drwxr-xr-x. 2 mycat mycat 4096 9月  23 15:37 lib
    drwxrwxrwx. 2 mycat mycat    6 1月   5 2020 logs
    -rwxrwxrwx. 1 mycat mycat  227 1月   5 2020 version.txt
    
    bin程序目录
    conf配置文件目录
    lib目录主要存放mycat依赖的一些jar文件
    logs目录存放日志
    
    # 查看版本信息 1.6.7.4-release
    [root@master ~]# cd /usr/local/mycat/
    [root@master mycat]# cat version.txt
    BuildTime  2020-01-05 08:41:01
    GitVersion   f929f96a16852869bc9dc63f4c0f192ee02818e0
    MavenVersion 1.6.7.4-release
    GitUrl https://github.com/MyCATApache/Mycat-Server.git
    MyCatSite http://www.mycat.org.cn

**②安装高版本的JDK，至少高于JDK1.7版本：**

# 卸载原来的openjdk,安装oracle-jdk
    # oracle-jdk性能比openjdk好，兼容性大。
    [root@master ~]# java -version
    openjdk version "1.8.0_181"
    OpenJDK Runtime Environment (build 1.8.0_181-b13)
    OpenJDK 64-Bit Server VM (build 25.181-b13, mixed mode)
    [root@master ~]# yum remove -y java-* 
    
    # 下载oracle-jdk,解压到/usr/local/下
    [root@master ~]# tar xzvf /usr/local/src/jdk-8u191-linux-x64.tar.gz -C /usr/local/
    
    # 在/usr/local目录下做一个软连接
    [root@master ~]# cd /usr/local/
    oot@master local]# ln -s jdk1.8.0_191 jdk1.8
    
    # 配置环境变量
    [root@master ~]# vim /etc/profile.d/jdk8.sh
    [root@master ~]# cat /etc/profile.d/jdk8.sh 
    export JAVA_HOME=/usr/local/jdk1.8.0_191
    export CLASS_PATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/jar/tools.jar:$JAVA_HOME/jre/lib
    export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
    
    # 使环境变量生效
    [root@master ~]# chmod +x /etc/profile.d/jdk8.sh
    [root@master ~]# /etc/profile.d/jdk8.sh
    [root@master ~]# bash
    
    # 查看jdk版本
    # 不配置环境变量的话可以做一个软连接
    [root@master local]# ln -s /usr/local/jdk1.8/bin/java /usr/bin/java
    [root@master local]# java -version
    java version "1.8.0_191"
    Java(TM) SE Runtime Environment (build 1.8.0_191-b12)
    Java HotSpot(TM) 64-Bit Server VM (build 25.191-b12, mixed mode)

**③部署mycat启动：**

# 配置环境变量，在/etc/profile.d目录下创建mycat.sh脚本，添加环境变量
    [root@master ~]# vim /etc/profile.d/mycat.sh
    [root@master ~]# cat /etc/profile.d/mycat.sh 
    MYCAT_HOME=/usr/local/mycat
    PATH=$MYCAT_HOME/bin:$PATH
    
    # 使环境变量立即生效
    [root@master ~]# chmod +x /etc/profile.d/mycat.sh
    [root@master ~]# source /etc/profile.d/mycat.sh 
    
    # 定义mycat集群中各服务器的IP地址和主机名的映射关系
    [root@master ~]# vim /etc/hosts
    192.168.10.10 master
    192.168.10.20 slave
    
    # 修改mycat的用户信息和授权信息
    [root@master ~]# cd /usr/local/mycat/conf/
    [root@master conf]# cp server.xml server.xml.bak
    [root@master conf]# echo "" > server.xml
    [root@master conf]# vim server.xml
    # wabong用户用于写,rabong用户用于读
    [root@master conf]# cat server.xml
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mycat:server SYSTEM "server.dtd">
    <mycat:server xmlns:mycat="http://io.mycat/">
    	<system>
    	<property name="defaultSqlParser">druidparser</property>
    	</system>
    	
    	
    	<user name="wabong">
    	<property name="password">123456</property>
    	<property name="schemas">test</property>
    	</user>
    	
    	<user name="rabong">    # 读库账号
    	<property name="password">123456</property>
    	<property name="schemas">test</property>
    	<property name="readOnly">true</property>
    	</user>
    </mycat:server>
    
    # 配置mycat架构
    [root@master conf]# cp schema.xml schema.xml.bak
    [root@master conf]# echo "" > schema.xml
    [root@master conf]# vim schema.xml
    [root@master conf]# cat schema.xml
    <?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
     <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode='dn1'> </schema>
     <dataNode name="dn1" dataHost="dthost" database="test"/>
     <dataHost name="dthost" maxCon="500" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100">
     <heartbeat>select user()</heartbeat>
     
     <writeHost host="master" url="192.168.10.10:3306" user="mycat" password="Abong123.">
    	 
    	<readHost host="slave" url="192.168.10.20:3306" user="mycat" password="Abong123." />
     </writeHost>
     </dataHost>
    </mycat:schema>
    
    # 启动mycat
    [root@master ~]# mycat 
    Usage: /usr/local/mycat/bin/mycat {  console | start | stop | restart | status | dump }
    [root@master ~]# mycat start
    Starting Mycat-server...
    [root@master ~]# cat /usr/local/mycat/logs/wrapper.log # 看到这个日志记录，就说明已经启动成功了。
    INFO   | jvm 1    | 2020/09/23 16:23:27 | MyCAT Server startup successfully. see logs in logs/mycat.log
    [root@master ~]# mycat status;
    Mycat-server is running (9115).

**配置mysql主从同步复制：**

# 配置主库/etc/my.cnf
    [root@master ~]# vim /etc/my.cnf
    log-bin=mysql-bin-master
    server-id=1
    binlog-do-db=test
    binlog-ignore-db=mysql
    [root@master ~]# systemctl restart mysqld
    
    # 在主库上创建用户mycat，并授予all privileges的权限，允许在所有主机的客户端上使用mycat用户进行登录。
    [root@master ~]# mysql -uroot -p
    Enter password: 
    
    mysql> grant all privileges on *.* to mycat@'%' identified by 'Abong123.';   # 用于读写分离的用户
    Query OK, 0 rows affected, 1 warning (0.04 sec)
    
    mysql> grant replication slave on *.* to slave@192.168.10.20 identified by 'Abong123.';   # 用于主从复制的用户
    Query OK, 0 rows affected, 1 warning (0.03 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> exit
    Bye
    
    # 主库上导出test数据库，并导入到从库。保持主从数据一致性。
    [root@master ~]# mysqldump -uroot -p test > test.sql
    Enter password: 
    [root@master ~]# ll test.sql 
    -rw-r--r--. 1 root root 6815 9月  24 11:24 test.sql
    [root@master ~]# scp test.sql root@192.168.10.20:/root/
    
    [root@slave ~]# mysql -uroot -p # 导入test到从库前的检测
    Enter password: 
    mysql> show databases;    # 从库上没有test数据库，要先创建。
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.00 sec)
    
    mysql> create database test;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> exit
    Bye
    [root@slave ~]# mysql -uroot -p test < test.sql
    Enter password: 
    
    # 配置从库上的/etc/my.cnf
    [root@slave ~]# vim /etc/my.cnf
    [mysqld]
    server-id=2
    [root@slave ~]# systemctl restart mysqld
    
    # 在从库上创建用户mycat，并授予all privileges的权限，允许在所有主机的客户端上使用mycat用户进行登录。
    [root@slave ~]# mysql -uroot -p
    Enter password: 
    
    mysql> grant all privileges on *.* to mycat@'%' identified by 'Abong123.';
    Query OK, 0 rows affected, 1 warning (0.04 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> exit
    Bye
    
    # 开放主库上的3306端口号
    [root@master ~]# firewall-cmd --permanent --zone=public --add-port=3306/tcp
    success
    [root@master ~]# firewall-cmd --reload 
    success
    
    # 建立主从关系
    mysql> show master status\G;    # 主库信息
    *************************** 1. row ***************************
                 File: mysql-bin-master.000001
             Position: 154
         Binlog_Do_DB: test
     Binlog_Ignore_DB: mysql
    Executed_Gtid_Set: 
    1 row in set (0.00 sec)
    
    ERROR: 
    No query specified
    
    # 配置从库对应主库信息：
    mysql> stop slave;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> change master to master_host='192.168.10.10',master_port=3306,master_user='slave',master_password='Abong123.',master_log_file='mysql-bin-master.000001',master_log_pos= 154;
    Query OK, 0 rows affected, 2 warnings (0.06 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> show slave status\G;    # 可以看到主从同步复制的关系已经建立起来。
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

### 2.3 mycat读写分离测试 ###

**登录格式：**`mysql -urabong -p123456 -h 192.168.10.10 -P8066`  
**mycat是装在192.168.10.10上的，所以-h也是192.168.10.10**  
**对mycat来说，8066是数据端口，9066是管理端口**  
**客户端登录mysql数据库，用8066端口。**  
**客户端管理mycat，用9066端口。**  
**①只读用户rabong通过mycat登录mysql数据库：**

[root@master ~]# mysql -urabong -p123456 -h 192.168.10.10 -P8066
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 10
    Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)   # 可以看到是通过中间件mycat登录的
    
    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> 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
    mysql> desc emp;     # 可以看到rabong是没有写权限的，即只能执行select语句。
    ERROR 1495 (HY000): User readonly
    mysql> select * from emp;
    +----+------+--------+
    | id | name | deptno |
    +----+------+--------+
    |  1 | haha |     20 |
    |  2 | xixi |     20 |
    |  3 | ohoh |     20 |
    |  5 | yeye |     20 |
    +----+------+--------+
    4 rows in set (0.10 sec)

**②可写用户wabong通过mycat登录mysql数据库：**

[root@master ~]# mysql -uwabong -p123456 -h 192.168.10.10 -P8066
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 11
    Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)   # 可以看到是通过中间件mycat登录的
    
    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> 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
    mysql> desc emp;      # 可以看到用户wabong是具有读写权限的
    +--------+-------------+------+-----+---------+-------+
    | Field  | Type        | Null | Key | Default | Extra |
    +--------+-------------+------+-----+---------+-------+
    | id     | int(11)     | NO   | PRI | NULL    |       |
    | name   | varchar(10) | YES  |     | NULL    |       |
    | deptno | int(11)     | YES  | MUL | NULL    |       |
    +--------+-------------+------+-----+---------+-------+
    3 rows in set (0.40 sec)
    
    mysql> select  * from emp;
    +----+------+--------+
    | id | name | deptno |
    +----+------+--------+
    |  1 | haha |     20 |
    |  2 | xixi |     20 |
    |  3 | ohoh |     20 |
    |  5 | yeye |     20 |
    +----+------+--------+
    4 rows in set (0.01 sec)

**③测试主库或从库故障，对用户访问数据库的影响：**

**主库正常运行，停止从库：**

[root@slave ~]# systemctl stop mysqld
    
    [root@master ~]# mysql -uwabong -p123456 -h 192.168.10.10 -P8066
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 12
    Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 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> 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
    mysql> desc emp;
    +--------+-------------+------+-----+---------+-------+
    | Field  | Type        | Null | Key | Default | Extra |
    +--------+-------------+------+-----+---------+-------+
    | id     | int(11)     | NO   | PRI | NULL    |       |
    | name   | varchar(10) | YES  |     | NULL    |       |
    | deptno | int(11)     | YES  | MUL | NULL    |       |
    +--------+-------------+------+-----+---------+-------+
    3 rows in set (1.66 sec)
    
    mysql> select * from emp;
    +----+------+--------+
    | id | name | deptno |
    +----+------+--------+
    |  1 | haha |     20 |
    |  2 | xixi |     20 |
    |  3 | ohoh |     20 |
    |  5 | yeye |     20 |
    +----+------+--------+
    4 rows in set (0.32 sec)
    
    mysql> insert into emp values(4,'lala',20);
    Query OK, 1 row affected (0.08 sec)
    
    mysql> select * from emp;
    +----+------+--------+
    | id | name | deptno |
    +----+------+--------+
    |  1 | haha |     20 |
    |  2 | xixi |     20 |
    |  3 | ohoh |     20 |
    |  4 | lala |     20 |
    |  5 | yeye |     20 |
    +----+------+--------+
    5 rows in set (0.00 sec)
    
    测试结果：主库上仍然可以进行读写操作。对主库的写操作会在从库恢复后再次进行同步复制，保证主从数据的一致性。

**主库挂了，从库正常运行：**

[root@master ~]# systemctl stop mysqld
    
    [root@master ~]# mysql -uwabong -p123456 -h 192.168.10.10 -P8066
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 13
    Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 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> 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
    mysql> desc emp;
    ERROR 1184 (HY000): java.net.ConnectException: 拒绝连接
    mysql> select * from emp;
    +----+------+--------+
    | id | name | deptno |
    +----+------+--------+
    |  1 | haha |     20 |
    |  2 | xixi |     20 |
    |  3 | ohoh |     20 |
    |  4 | lala |     20 |
    |  5 | yeye |     20 |
    +----+------+--------+
    5 rows in set (0.00 sec)
    
    测试结果：由于主库挂了，故不再对主库进行写操作，为保持主从数据库的一致性，从库上也不能进行写操作，但是还可以进行读操作。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/fed12d8ab1f54adaadde5877937c6368.png