部署MyCAT实现读写分离

pekeka

于 2022-12-09 11:44:37 发布

阅读量316

点赞数

文章标签： java 服务器数据库 linux 运维

本文链接：https://blog.csdn.net/p12361465/article/details/128250532

版权

部署MyCAT实现读写分离

主机名	IP	系统	角色
MyCAT	192.168.88.67	RHEL7.3	MyCAT服务器
server69	192.168.88.69	RHEL7.3	mysql5.7主
server70	192.168.88.70	RHEL7.3	mysql5.7从

MyCAT服务安装与配置

[root@mycat ~]# cd /usr/local/
[root@mycat local]# tar -xvf /root/Mycat-server-1.6.7.3-release-20190927161129-linux.tar.gz -C .   
[root@mycat local]# cd mycat/
[root@mycat mycat]# useradd mycat   ##需要用mycat用户进行启动mycat
[root@mycat mycat]# passwd mycat
更改用户 mycat 的密码 。
新的 密码：
无效的密码： 密码少于 8 个字符
重新输入新的 密码：
passwd：所有的身份验证令牌已经成功更新。
[root@mycat mycat]# chown -R mycat.mycat /usr/local/mycat  修改文件所属主和所属组为mycat
[root@mycat mycat]# ll
总用量 12
drwxr-xr-x 2 mycat mycat  190 12月  9 09:38 bin
drwxrwxrwx 2 mycat mycat    6 7月  18 2019 catlet
drwxrwxrwx 4 mycat mycat 4096 12月  9 09:38 conf
drwxr-xr-x 2 mycat mycat 4096 12月  9 09:38 lib
drwxrwxrwx 2 mycat mycat    6 9月   9 2019 logs
-rwxrwxrwx 1 mycat mycat  227 9月  27 2019 version.txt
####  mycat的安装需要jdk环境
[root@mycat ~]# tar -xvf jdk-8u201-linux-x64.tar.gz  -C /usr/java/
[root@mycat ~]# vim /etc/profile  ##添加环境变量
JAVA_HOME=/usr/java/jdk1.8.0_201
PATH=$JAVA_HOME/bin:$PATH 
CLASSPATH=$JAVA_HOME/jre/lib/ext:$JAVA_HOME/lib/tools.jar  
export PATH JAVA_HOME CLASSPATH 
MYCAT_HOME=/usr/local/mycat  
PATH=$MYCAT_HOME/bin:$PATH  
[root@mycat ~]# java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[root@mycat ~]# mycat
Usage: /usr/local/mycat/bin/mycat { console | start | stop | restart | status |
### 在所有的主机添加映射关系
# vim /etc/hosts
192.168.88.67 mycat
192.168.88.69 server69
192.168.88.70 server70

[root@mycat mycat]# cd /usr/local/mycat/conf/
[root@mycat conf]# vim server.xml  ##只需修改文件最下面的数据库表的信息
        <user name="root" defaultAccount="true">
                <property name="password">GuangZhou_123</property>
                <property name="schemas">pekeka</property>

                <!-- 表级 DML 权限设置 -->
                <!--            
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           
                 -->
        </user>

        <user name="user">
                <property name="password">user</property>
                <property name="schemas">pekeka</property>
                <property name="readOnly">true</property>
        </user>

</mycat:server>

在server69上配置mysql5.7数据库

[root@server69 ~]# ll
总用量 50484
-rw-------.  1 root root      1455 11月 22 04:51 anaconda-ks.cfg
drwxr-xr-x  35 7161 31415     4096 6月  22 2017 mysql-5.7.19
-rw-r--r--   1 root root  51686763 9月   4 10:54 mysql-5.7.19.tar.gz
[root@server69 ~]# cd mysql-5.7.19
[root@server69 mysql-5.7.19]# ll   ###准备好这些包并进行解压
-rw-r--r--  1 root root   25090196 9月   4 10:54 mysql-community-client-5.7.20-1.el7.x86_64.rpm
-rw-r--r--  1 root root     278300 9月   4 10:54 mysql-community-common-5.7.20-1.el7.x86_64.rpm
-rw-r--r--  1 root root    3840412 9月   7 09:46 mysql-community-devel-5.7.24-1.el7.x86_64.rpm
-rw-r--r--  1 root root    2238604 9月   4 10:54 mysql-community-libs-5.7.20-1.el7.x86_64.rpm
-rw-r--r--  1 root root  171597916 9月   4 10:59 mysql-community-server-5.7.20-1.el7.x86_64.rpm
[root@server69 mysql-5.7.19]# rpm -ivh mysql-community-common-5.7.24-1.el7.x86_64.rpm
[root@server69 mysql-5.7.19]# rpm -ivh mysql-community-libs-5.7.24-1.el7.x86_64.rpm
[root@server69 mysql-5.7.19]# rpm -ivh mysql-community-client-5.7.24-1.el7.x86_64.rpm
[root@server69 mysql-5.7.19]#rpm -ivh mysql-community-devel-5.7.24-1.el7.x86_64.rpm
[root@server69 mysql-5.7.19]# rpm -ivh mysql-community-server-5.7.24-1.el7.x86_64.rpm
[root@server69 mysql-5.7.19]# systemctl enable mysqld
[root@server69 mysql-5.7.19]# systemctl start mysqld
[root@server69 mysql-5.7.19]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 四 2022-12-08 17:25:46 CST; 4ms ago
     Docs: man:mysqld(8)
[root@server69 mysql-5.7.19]# cat /var/log/mysqld.log | grep "password"   ###查找初始密码
2022-12-08T09:25:44.126928Z 1 [Note] A temporary password is generated for root@localhost: qvB.wjoyx2qu
[root@server69 mysql-5.7.19]# mysql -uroot -p
Enter password: qvB.wjoyx2qu
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20
Copyright (c) 2000, 2017, 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> ALTER USER 'root'@'localhost' identified by "GuangZhou_123";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to pekeka@'%' identified by "GuangZhou_123";  ###增加用户并为其添加权限
mysql> use mysql
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 user, host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| pekeka        | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
mysql> create database pekeka;  ##创建一个数据库用于测试读写分离
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
[root@server69 mysql-5.7.19]# > /etc/my.cnf
[root@server69 mysql-5.7.19]# vim /etc/my.cnf  
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
#主从复制配置 
sync_binlog=1
#需要备份的数据库 
binlog-do-db=pekeka
#不需要备份的数据库 
binlog-ignore-db=mysql
#启动二进制文件 
log-bin=mysql-bin
#服务器ID 
server-id=1
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@server69 mysql-5.7.19]# systemctl restart mysqld
[root@server69 mysql-5.7.19]# mysql -uroot -pGuangZhou_123  ##配置主从设置
mysql> grant replication slave on *.* to 'pekeka'@'%' identified by 'GuangZhou_123';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      591 | pekeka       | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> grant all privileges on *.* to root@"%" identified by "GuangZhou_123";

在server70上配置mysql5.7数据库

按如上步骤进行安装mysql5.7后
[root@server70 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
server-id=2
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-name-resolve
[root@server70 ~]# mysql -uroot -pGuangZhou_123
mysql> change master to master_host='192.168.88.69',
    ->      master_user='pekeka',
    ->      master_password='GuangZhou_123',
    ->      master_port=3306,
    ->      master_log_file='mysql-bin.000001',
    ->      master_log_pos=591,
    ->      master_connect_retry=10;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.88.69
                  Master_User: pekeka
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 591
               Relay_Log_File: server70-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 591
              Relay_Log_Space: 530
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 4a623bc9-76da-11ed-813d-00505632aa47
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
mysql> grant all privileges on *.* to root@"%" identified by "GuangZhou_123";

测试MySQL读写分离

[root@mycat conf]# mysql -uroot -pGuangZhou_123 -h 192.168.88.67 -P8066  ##利用8066端口进行读写操作
MySQL [(none)]>  show databases;
+----------+
| DATABASE |
+----------+
| pekeka   |
+----------+
1 row in set (0.00 sec)
MySQL [(none)]> use pekeka;
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 [pekeka]> select * from book;
+------------------+--------+-------+
| name             | price  | pages |
+------------------+--------+-------+
| Linux            |     66 |   666 |
| pekeka           |     77 |   666 |
| Linux            |     30 |   666 |
| Cloud Computing  |     60 |   666 |
| Operation System |     80 |   666 |
| Linux            |     66 |   666 |
| pekeka           |     77 |   666 |
| Linux            |     30 |   666 |
| Cloud Computing  |     60 |   666 |
| Operation System |     80 |   666 |
| Artifgence       | 111111 |     1 |
| nginx            |     66 |   666 |
| pekeka           |     66 |   666 |
| pekeka           |    888 |   666 |
| pekeka           |    888 |   666 |
+------------------+--------+-------+
15 rows in set (0.00 sec)
MySQL [pekeka]> INSERT INTO book(name,price,pages) VALUES('pekeka','999','666');  
Query OK, 1 row affected (0.01 sec)
[root@mycat ~]# tail -f /usr/local/mycat/logs/mycat.log  ##实时查看mycat日志内容 可以得知读写分离配置成功
2022-12-09 11:39:31.755 DEBUG [$_NIOREACTOR-1-RW] (io.mycat.backend.datasource.PhysicalDatasource.releaseChannel(PhysicalDatasource.java:633)) - release channel MySQLConnection [id=772, lastTime=1670557171748, user=root, schema=pekeka, old shema=pekeka, borrowed=true, fromSlaveDB=false, threadId=122, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.88.69, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2022-12-09 11:39:31.755 DEBUG [$_NIOREACTOR-3-RW] (io.mycat.backend.datasource.PhysicalDatasource.releaseChannel(PhysicalDatasource.java:633)) - release channel MySQLConnection [id=763, lastTime=1670557171748, user=root, schema=pekeka, old shema=pekeka, borrowed=true, fromSlaveDB=true, threadId=55, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.88.70, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]