7.MyCAT实现MySQL读写分离

 

 

所有主机的系统环境：

cat /etc/centos-release

CentOS Linux release 8.5.2111

 

服务器共三台

mycat-server 10.0.0.8 #内存建议2G以上

mysql-master 10.0.0.18 MySQL 8.0 或者mariadb10.3.17

mysql-slave  10.0.0.28 MySQL 8.0 或者mariadb 10.3.17

 

关闭SELinux和防火墙

systemctl stop firewalld

setenforce 0

时间同步

 

1、创建 MySQL 主从数据库

mount /dev/sr0 /mnt

yum -y install mysql-server

[root@centos8 ~]#yum -y install mysql-server

 

1) 修改master和slave上的配置文件

#master上的my.cnf

[root@CentOS8 ~]# vim /etc/my.cnf.d/mysql-server.cnf

[mysqld]

server-id = 1

log-bin

 

#slave上的my.cnf

[mysqld]

server-id = 2

[root@CentOS8 ~]# systemctl start mysqld

 

2) Master上创建复制用户

[root@centos8 ~]#mysql -uroot -p

mysql> create user 'repluser'@'10.0.0.%' identified by 'Mmagedu0!';

grant all privileges on *.* to 'repluser'@'10.0.0.%';

 

mysql> flush privileges;

mysql> show master status;

+--------------------+----------+--------------+------------------+-------------------+

| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+--------------------+----------+--------------+------------------+-------------------+

| CentOS8-bin.000002 |     1137 |              |                  |                   |

+--------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

 

3) Slave上执行

[root@centos8 ~]#mysql -uroot -p

mysql> change master to

    -> MASTER_HOST='10.0.0.18',

    -> MASTER_USER='repluser',

    -> MASTER_PASSWORD='Mmagedu0!',

    -> MASTER_LOG_FILE='CentOS8-bin.000002',

    -> MASTER_LOG_POS=1137;

Query OK, 0 rows affected, 8 warnings (0.01 sec)

 

 

mysql> start slave;

 

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for source to send event

                  Master_Host: 10.0.0.18

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: CentOS8-bin.000002

          Read_Master_Log_Pos: 1137

               Relay_Log_File: CentOS8-relay-bin.000002

                Relay_Log_Pos: 326

        Relay_Master_Log_File: CentOS8-bin.000002

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

 

2、在MySQL代理服务器10.0.0.8安装mycat并启动

 

root@centos8 ~]#yum -y install java

 

#确认安装成功

[root@mycat ~]# java -version

openjdk version "1.8.0_312"

OpenJDK Runtime Environment (build 1.8.0_312-b07)

OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode)

 

#下载并安装Mycat

[root@centos8 ~]#wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-

1.6.7.6-release-20210303094759-linux.tar.gz

[root@centos8 ~]#mkdir /apps

[root@centos8 ~]#tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz

-C /apps/

 

#配置环境变量

[root@centos8 ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh

[root@centos8 ~]#source /etc/profile.d/mycat.sh

 

#查看端口

[root@centos8 ~]#ss -ntl

State         Recv-Q       Send-Q     Local Address:Port     Peer Address:Port   

              

LISTEN        0             128              0.0.0.0:22            0.0.0.0:*     

                

LISTEN        0             128                 [::]:22               [::]:*   

 

#启动mycat

[root@centos8 ~]#mycat start

Starting Mycat-server...

#可以看到打开多个端口,其中8066端口用于连接MyCAT

[root@mycat bin]# ss -ntlp

State       Recv-Q      Send-Q           Local Address:Port             Peer Address:Port      Process                                                                                        

LISTEN      0           5                    127.0.0.1:631                   0.0.0.0:*          

LISTEN      0           128                  127.0.0.1:6012                  0.0.0.0:*          users:(("sshd",pid=38534,fd=15))                                                              

LISTEN      0           1                    127.0.0.1:32000                 0.0.0.0:*          users:(("java",pid=38625,fd=4))                                                               

LISTEN      0           128                    0.0.0.0:111                   0.0.0.0:*          users:(("rpcbind",pid=750,fd=4),("systemd",pid=1,fd=25))                                      

LISTEN      0           128                    0.0.0.0:22                    0.0.0.0:*          users:(("sshd",pid=840,fd=4))                                                                 

LISTEN      0           5                        [::1]:631                      [::]:*          users:(("cupsd",pid=1024,fd=9))                                                               

LISTEN      0           128                      [::1]:6010                     [::]:*          users:(("sshd",pid=2885,fd=14))                                                               

LISTEN      0           128                      [::1]:6011                     [::]:*          users:(("sshd",pid=2179,fd=14))                                                               

LISTEN      0           128                      [::1]:6012                     [::]:*        

 users:(("sshd",pid=38534,fd=14))                                                              

LISTEN      0           50                           *:1984                        *:*          users:(("java",pid=38625,fd=67))                                                              

LISTEN      0           100                          *:8066                        *:*          users:(("java",pid=38625,fd=88))                                                              

LISTEN      0           100                          *:9066                        *:*          users:(("java",pid=38625,fd=84))                                                              

LISTEN      0           50                           *:37423                       *:*          users:(("java",pid=38625,fd=66))                                                              

 

 

#查看日志，确定成功，可能需要等一会儿才能看到成功的提示

[root@mycat bin]# tail /apps/mycat/logs/wrapper.log

STATUS | wrapper  | 2022/07/29 21:57:03 | --> Wrapper Started as Daemon

STATUS | wrapper  | 2022/07/29 21:57:04 | Launching a JVM...

INFO   | jvm 1    | 2022/07/29 21:57:07 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org

INFO   | jvm 1    | 2022/07/29 21:57:07 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.

INFO   | jvm 1    | 2022/07/29 21:57:07 |

INFO   | jvm 1    | 2022/07/29 21:57:16 | MyCAT Server startup successfully. see logs in logs/mycat.log

 

#用默认密码123456来连接mycat

[root@mycat bin]# yum install mysql-server -y

[root@mycat bin]# mysql -uroot -p123456 -h 10.0.0.8 -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 2

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> show databases;

+----------+

| DATABASE |

+----------+

| TESTDB   |

+----------+

1 row in set (0.00 sec)

 

mysql> use TESTDB;

mysql> show tables;

+------------------+

| Tables in TESTDB |

+------------------+

| address          |

| travelrecord     |

+------------------+

2 rows in set (0.00 sec)

 

mysql> select * from travelrecord;

ERROR 1105 (HY000): backend connect: java.lang.IllegalArgumentException: Invalid DataSource:0

mysql> quit;

Bye

 

4、在mycat 服务器上修改server.xml文件配置Mycat的连接信息

[root@centos8 ~]#vim /apps/mycat/conf/server.xml

...省略...

#修改下面行的8066改为3306 ，顺序不要乱

<property name="serverPort">3306</property>                                                                                                                    

<property name="handleDistributedTransactions">0</property>

...........

  <user name="root" defaultAccount="true">

        <property name="password">Mmagedu0!</property>                                                                                                                 

        <property name="schemas">TESTDB</property>

        <property name="defaultSchema">TESTDB</property>

 

这里使用的是root，密码为Mmagedu0!,逻辑数据库为TESTDB，这些信息都可以自己随意定义,读写权限都有，没有针对表做任何特殊的权限。重点关注上面这段配置，其他默认即可。

 

5、修改schema.xml实现读写分离策略

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

    <!-- <schema name="park" checkSQLschema="false" sqlMaxLimit="100">

        <table name="part" dataNode="dn1,dn2,dn3,dn4" rule="sharding-by-month" />

    </schema> -->

    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

    </schema>

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

    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="5">

    <heartbeat>show slave status</heartbeat>

    <!-- can have multi write hosts -->

    <writeHost host="hostM1" url="10.0.0.18:3306" user="root" password="123456">

        <!-- can have multi read hosts -->

    <readHost host="hostS1" url="10.0.0.28:3306" user="root" password="123456" />

    </writeHost>

    <writeHost host="hostM2" url="localhost:3308" user="root" password="root"></writeHost>

    </dataHost>

</mycat:schema>                                             

 

#重新启动mycat

[root@centos8 ~]#mycat restart

 

上面配置中，balance改为1，表示读写分离。以上配置达到的效果就是10.0.0.18为主库，10.0.0.28为

从库 .

注意：要保证能使用root/123456权限成功登录10.0.0.18和10.0.0.28机器上面的mysql数据库。同时，

也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，否则会导致登录mycat后，对库和表操作失败！

 

 

 

6、在后端主服务器创建用户并对mycat授权

[root@centos8 ~]#mysql -uroot -p

mysql> create database mycat;

mysql>ceate user root@'10.0.0.%’identified by ‘123456’

mysql>grant all on *.* to 'root'@'10.0.0.%';

mysql> flush privileges;

 

7、在Mycat服务器上连接并测试

[root@centos8 ~]#

mysql> show databases;

+----------+

| DATABASE |

+----------+

| TESTDB   | 

+----------+

mysql> use TESTDB;

mysql> create table t1(id int);

MySQL> select @@server_id;

MySQL> select @@hostname;

 

8、通过通用日志确认实现读写分离

在mysql中查看通用日志

show variables like 'general_log';

set global general_log=on;

show variables like 'general_log_file';

set global general_log_file='tmp/general.log';

 

在主和从服务器分别启用通用日志，查看读写分离

[root@CentOS8 ~]# vim /etc/my.cnf.d/mysql-server.cnf

[mysqld]

general_log=ON

 

[root@centos8 ~]#systemctl restart mysqld

[root@centos8 ~]#tail -f /var/lib/mysql/centos8.log

 

 

 

9、停止从节点，MyCAT自动调度读请求至主节点

[root@slave ~]#systemctl stop mysqld

[root@client ~]#mysql -uroot -pMmagedu0! -h10.0.0.8 -P3306

MySQL [(none)]> select @@server_id;

 

#停止主节点，MyCAT不会自动调度写请求至从节点

MySQL [TESTDB]> insert teachers values(5,'wang',30,'M');

ERROR 1184 (HY000): java.net.ConnectException: Connection refused

 

10、MyCAT对后端服务器的健康性检查方法select user()

#开启通用日志

[root@master ~]#mysql -uroot -p

mysql> set global  general_log=1;

 

[root@slave ~]#mysql -uroot -p

mysql> set global  general_log=1;

 

#查看通用日志

[root@master ~]#tail -f /var/lib/mysql/master.log

 

[root@slave ~]#tail -f /var/lib/mysql/slave.log