部署Mycat数据库中间件连接MySQL8.x高可用集群

主从复制搭建

创建容器

    # 创建自定义网络, 使得容器间能通过主机名访问
    docker network create sql-net
    
    # 创建master主机
    docker run -d -P --name mysql1 -p 4306:3306 --net=sql-net -v mysql1:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:latest
    # 创建slave从机1
    docker run -d -P --name mysql2 -p 4307:3306 --net=sql-net -v mysql2:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:latest
    # 创建slave从机2
    docker run -d -P --name mysql3 -p 4308:3306 --net=sql-net -v mysql3:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:latest

创建完成后如下

    [root@centos ~]# docker ps
    CONTAINER ID   IMAGE                   COMMAND                  CREATED       STATUS       PORTS                                                                                      NAMES
    60d89cfcb140   mysql:latest            "docker-entrypoint.s…"   3 hours ago   Up 3 hours   0.0.0.0:4308->3306/tcp, :::4308->3306/tcp, 0.0.0.0:49171->33060/tcp, :::49171->33060/tcp   mysql3
    ff3bdaaea587   mysql:latest            "docker-entrypoint.s…"   3 hours ago   Up 3 hours   0.0.0.0:4307->3306/tcp, :::4307->3306/tcp, 0.0.0.0:49170->33060/tcp, :::49170->33060/tcp   mysql2
    79289a84a5e4   mysql:latest            "docker-entrypoint.s…"   3 hours ago   Up 3 hours   0.0.0.0:4306->3306/tcp, :::4306->3306/tcp, 0.0.0.0:49160->33060/tcp, :::49160->33060/tcp   mysql1

修改容器配置

    # mysql1
    docker exec -it mysql1 bash
    #进入容器后运行以下命令
    cat >> /etc/mysql/my.cnf <<EOF
    server-id=1
    #主库开启binlog日志
    log-bin=/var/lib/mysql/mysql-bin
    EOF
    #退出该容器
    exit
    
    # mysql2
    docker exec -it mysql2 bash
    #进入容器后运行以下命令
    cat >> /etc/mysql/my.cnf <<EOF
    server-id=2
    EOF
    #退出该容器
    exit
    
    # mysql3
    docker exec -it mysql3 bash
    #进入容器后运行以下命令
    cat >> /etc/mysql/my.cnf <<EOF
    server-id=3
    EOF
    #退出该容器
    exit
    
    # 重启容器，使得配置生效
    docker restart mysql1 mysql2 mysql3

配置主从复制

    -- mysql1
    -- 创建从机访问主机所需要的用户
    create user 'slave'@'%' identified by '123456';
    -- 为该用户授权同步复制权限
    grant replication slave on *.* to 'slave'@'%';
    -- 刷新权限使其生效
    flush privileges;

查看主机详情并记下binary log二进制日志文件名以及位置 Position值

    show master status;

    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000001 |     1721 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

配置从机

    -- mysql2,mysql3
    change master to master_host='mysql1', master_port=3306, master_user='slave', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=1721;
    start slave ;
    
    -- 查看从机状态
    -- mysql8.x建议使用相关可视化工具执行，否则显示会十分混乱
    show slave status;

检查Slave_IO_Running 和 Slave_SQL_Running 是否为 Yes ，以及Slave_IO_State 为 Waiting for master to send event，若显示正确则证明主从复制成功。

    -- mysql1
    -- 创建数据库与表并赋值
    mysql> create database testdb;
    Query OK, 1 row affected (0.03 sec)
    
    mysql> use testdb;
    Database changed
    mysql> create table per(id int,name varchar(10));
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> insert into per values(1,'a'),(2,'b');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from per;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | a    |
    |    2 | b    |
    +------+------+
    2 rows in set (0.00 sec)
    
    -- mysql2,mysql3
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | testdb             |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> use testdb;
    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 per;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | a    |
    |    2 | b    |
    +------+------+
    2 rows in set (0.00 sec)

以上步骤完成了一主二从的相关配置。

Mycat-server搭建

配置mycat

lib库配置

将驱动包移动到mycat的lib目录下，并删除原本的5.x驱动包：

我是用的是mysql-connector-java-8.0.25.jar

使用chmod命令设置该驱动包的权限

schema.xml

    <?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    
    	<schema name="testdb" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
    </schema>
    	<schema name="information_schema" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn_schema"></schema>
    	<dataNode name="dn1" dataHost="host1" database="testdb" />
    	<dataNode name="dn_schema" dataHost="host1" database="information_schema" />
        <!-- dbDriver需修改为jdbc -->
    	<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
    			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
    		<heartbeat>select user()</heartbeat>
    		<!-- can have multi write hosts -->
    		<!-- url需修改为jdbc格式 -->
    		<writeHost host="hostM1" url="jdbc:mysql://localhost:4306" user="root"
     password="123456">
    		   <readHost host="hostS1" url="jdbc:mysql://localhost:4307" user="root" password="123456" />
    		   <readHost host="hostS2" url="jdbc:mysql://localhost:4308" user="root" password="123456" />
    		</writeHost>
    	</dataHost>
    </mycat:schema>

server.xml

    	<property name="useHandshakeV10">1</property>
    
    	<user name="mycat">
    		<property name="password">123456</property>
    		<property name="schemas">testdb,information_schema</property>
    	</user>

启动Mycat

    [root@centos opt]# /opt/mycat/bin/mycat console
    Running Mycat-server...
    wrapper  | --> Wrapper Started as Console
    wrapper  | Launching a JVM...
    jvm 1    | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
    jvm 1    |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
    jvm 1    | 
    jvm 1    | Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
    jvm 1    | MyCAT Server startup successfully. see logs in logs/mycat.log

使用任意mysql-client访问Mycat，默认访问端口为8066

    # mysql1容器内的mysql-client
    root@ff3bdaaea587:/# mysql -umycat -p123456 -h10.10.0.100 -P8066 --default-auth=mysql_native_password
    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 8
    Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
    
    Copyright (c) 2000, 2021, Oracle and/or its affiliates.
    
    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> exit
    Bye

相关问题

Mycat 目前还未全面支持MySQL 8以上的版本，可能会导致一些问题，例如：

• Mycat连接MySQL 8时可能会报密码错误，因为新版的密码加密方式与旧版不一样

  • 解决方案：登录时加上--default-auth=mysql_native_password

  • 注：在替换mysql驱动包后似乎不需要添加上面的参数就能访问了。。。

• 连接Mysql数据库时会出现数据库读取不正确的情况

  • 解决方案：将mycat的lib目录下原本的5.x驱动包替换为8.x版本

参考

https://blog.csdn.net/u013068184/article/details/107691389

https://blog.csdn.net/eyeofeagle/article/details/102841995

https://blog.csdn.net/eyeofeagle/article/details/117927398

https://blog.51cto.com/zero01/2466009

• 上一篇
• 下一篇