利用mycat搭建mysql集群

最新推荐文章于 2023-08-19 10:11:36 发布

安迪爸爸

最新推荐文章于 2023-08-19 10:11:36 发布

阅读量442

点赞数 1

分类专栏：服务器文章标签： mycat mysql 读写分离主从复制分库分表

本文链接：https://blog.csdn.net/wzmde007/article/details/103277604

版权

服务器专栏收录该内容

25 篇文章 2 订阅

订阅专栏

说明

我的环境是centos7.7+mysql8，在不同版本主机及不同版本mysql命令不一样,如果想参考我的文章，尽量选择跟我一样的配置，这样可以省去一些麻烦，如果不一样，尽量理解思想，对于某个步骤的命令执行总是报错，或者找不到对应的文件，那就需要自己去网上搜索下对应版本的命令，不要死磕，会很浪费时间。

如果对于centos的安装和配置不熟悉的话，参考我的另外一篇文章：https://blog.csdn.net/wzmde007/article/details/103054341

安装mysql8

安装部分参考文章：https://blog.csdn.net/managementandjava/article/details/80039650，
该文章是利用mysql的在线安装方式。
也可以通过去官网下载tar包，离线安装rpm程序。
注意：用centos7离线安装mysql8需要glibc高版本，不太好弄，这种情况尽量选择在线下载安装方式。

在上边文章中，我没有使用iptables，而是使用的firewalld，

只需要通过命令

firewall-cmd --permanent --zone=public --add-port=3306/tcp

即可开通该端口。

如果用navicat连接报错，则需要在虚拟机中，进入mysql，进入mysql库，输入如下命令：

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'newpassword'; #更新一下用户的密码 root用户密码为newpassword

最后的newpassword是你设置的密码。

使用mycat

先单库分表练一练：https://blog.csdn.net/Trend_H/article/details/88711567

按照链接文章，注意两点

注意mysql的用户名密码；
如果用navicat连接mycat提示unknow chasetindex:255,是因为mycat不支持utf8mb4,需要去mycat的conf文件夹找到index_to_charset.properties在最后增加255=utf8mb4，重启服务即可。

搭建mysql主从复制

先实验一下1主1从，
我们测试的数据库命名为db1数据库。

注意：主机和从机现在都没有新建db1数据库，等配置完，在主机新建db1数据库及对应的表即可，当插入数据时，从机才开始同步。
首先配置主机的my.cnf

vi /etc/my.cnf

在【mysqld】下插入如下：

[mysqld]
#服务器唯一标示，一般取服务器的ip
server-id = 130
#启动MySQL二进制日志
log_bin = master-bin
#指定记录二进制日志的数据库
log_bin_index = master-bin.index
#指定记录二进制日志的数据库
binlog_do_db = db1
#指定不记录二进制日志的数据库
binlog_ignore_db = mysql

进入mysql命令行

mysql -u root -p

输入密码，登陆成功后，首先新创建一个用户backup，专门用于主从复制

#其中%表示任何ip都可以使用该用户
create user 'backup'@'%' identified by '你的密码';

创建成功后，给backup用户授权 FILE,REPLICATION SLAVE,REPLICATION CLIENT三个权力，主从复制基于这三个权力。

grant FILE,REPLICATION SLAVE,REPLICATION CLIENT ON *.* to 'backup'@'%';

授权成功后，刷新权力

 flush privileges;

然后查看刚才添加的用户

select user,host,plugin from user;

+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| backup           | %         | caching_sha2_password |
| root             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+

如果backup用户对应的plugin是caching_sha2_password的话，需要修改成native模式

 alter user 'backup'@'%' identified with mysql_native_password by '你刚才设置的密码';

重启服务

systemctl restart mysqld;

查看主节点的状态

show master status;

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 |      155 | db1          | mysql            |                   |
+-------------------+----------+--------------+------------------+-------------------+

上边的position要注意，一会设置slave时要用到。

设置slave节点，slave配置比较简单。

打开my.cnf，设置节点id即可。

vi /etc/my.cnf

[mysqld]
server-id=131

重启服务

systemctl restart mysqld

进入mysql命令行，配置主从关系，输入如下语句：

change master to master_host='主机ip地址',master_port=3306,master_user='backup',master_password='你的密码',master_log_file='master-bin.000002',master_log_pos=155;

其中master_log_file和master_log_pos分别对应master中status查出的file和position的值。

然后开启slave，

start slave;

我们可以查看slave的状态

show slave status;

因为状态表中的字段比较多，最好使用navicat查看，看下自己配置的主机ip，是否正确，看下其他状态是否有无异常。

现在就可以简单的测试了。在主机中，新建db1数据库，注意这块的db1一定要与主机my.cnf中的配置文件一样。随便新建一个表，这时，从机是没有复制的，只有当你插入、修改、删除数据时，才开始复制。接着我们插入一条数据，然后查看从机，这时，从机应该可以同步了。

如果没有同步成功，可以通过查看slave status，里边有个字段。last_sql_error，可以查看最后一次的报错信息，或者查看从机的log日志，对错误进行分析。

以上就是1主1从的配置过程。

使用mycat搭建读写分离

基于上边的主从复制。我们用mycat来实现读写分离。

mycat的安装和使用参见它的官网文档，因为是中国人开发的，写的很清楚。

稍微熟悉点mycat，配置还是非常简单的。

安装好mycat之后，我们只需要修改两个配置文件即可。

首先修改server.xml

	<user name="用户名[root]" defaultAccount="true">
		<property name="password">你配置的密码</property>
		<property name="schemas">schemas配置文件中的name属性[db1]</property>
	</user>

其他的不用动，然后修改schemas.xml的配置文件：

  <dataNode name="dn1" dataHost="localhost1" database="db1" />

  <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                         writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <heartbeat>show slave status</heartbeat>
    <!-- 主机用于写的 -->
    <writeHost host="hostM1" url="192.168.43.130:3306" user="数据库用户名" password="数据库密码">
      <!-- 从机用于读的 -->
      <readHost host="hostS1" url="192.168.43.131:3306" user="数据库用户名" password="数据库密码" />
    </writeHost>
  </dataHost>

然后重启mycat即可。使用navicat连接mycat，进行增删改查测试。

使用mycat插入批量数据测试

使用idea，新建空的maven工程，

导入jdbc和mysql依赖

<dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.1.9.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.17</version>
        </dependency>
    </dependencies>

新建插入sql工具类

package com.ming;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class SqlUtils {
    private static String driver = "com.mysql.jdbc.Driver";

    private static String url = "jdbc:mysql://192.168.43.129:8066/db1";

    private static String batch_url = "jdbc:mysql://192.168.43.129:8066/db1?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true";//要5.1.13以上版本的驱动包

    private static String user = "root";

    private static String password = "密码";

    /***
     * 向数据库插入单条语句，
     * @param id
     * @param name
     */
    public static void insert(int id,String name){
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            Class.forName(driver);
            conn = (Connection) DriverManager.getConnection(url, user, password);
            String sql = "INSERT INTO test (id,name) VALUES("+id+",'"+name+"')";
            System.out.println(sql);
            pstm = (PreparedStatement) conn.prepareStatement(sql);
            pstm.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (pstm != null) {
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

然后通过main方法调用即可，

package com.ming;

public class MycatTestMain {

    public static void main(String[] args){
        for(int i=100;i<10000000;i++){
            SqlUtils.insert(i,"abcdefg"+i);
            try {
                Thread.sleep(2);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }
}

main方法中插入间隔为2毫秒，也就是每秒插入500条，这种情况下，还会出现偶尔获取数据库连接失败的情况。

按照这种速度可以计算出插入1000万条数据需要5.5个小时。所以想很快插入上亿条数据，就不能每次打开和断开连接插入了，最好拼接一个大的sql，一次多插入些。或者直接用存储过程插入。

注意，在schema.xml中有配置sqlMaxLimit="100"这个字段，它的作用是，你使用mycat做查询时，只显示前100条。如果不想要这个限制，可以修改值，或者在使用查询语句时，最后加上limit+值的方式。

在每秒插入500条sql的同时，我们可以做一些查询的操作，因为读写分离的作用，可以看出，查询速度还是很快的。