2024年最全Mysql读写分离，Mycat、ProxySQL（代理服务器）--多种问题(1)，泪目

最后

由于细节内容实在太多了，为了不影响文章的观赏性，只截出了一部分知识点大致的介绍一下，每个小节点里面都有更细化的内容！

小编准备了一份Java进阶学习路线图（Xmind）以及来年金三银四必备的一份《Java面试必备指南》

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

source /etc/profile.d/mycat.sh

mycat安装目录结构：

bin mycat命令，启动、重启、停止等

catlet catlet为Mycat的一个扩展功能

conf Mycat 配置信息,重点关注

lib Mycat引用的jar包，Mycat是java开发的

logs 日志文件，包括Mycat启动的日志和运行的日志

version.txt mycat版本说明

logs目录:

wrapper.log mycat启动日志

mycat.log mycat详细工作日志

Mycat的配置文件都在conf目录里面，这里介绍几个常用的文件：

server.xml Mycat软件本身相关的配置文件，设置账号、参数等

schema.xml Mycat对应的物理数据库和数据库表的配置,读写分离、高可用、分布式策略定制、节点控制

rule.xml Mycat分片（分库分表）规则配置文件,记录分片规则列表、使用方法等

启动和连接

#配置环境变量

vim /etc/profile.d/mycat.sh

PATH=/app/mycat/bin:$PATH

source /etc/profile.d/mycat.sh

#启动

mycat start

#查看日志，确定成功

cat /app/mycat/logs/wrapper.log

INFO | jvm 1 | 2019/11/01 21:41:02 | MyCAT Server startup successfully. see

logs in logs/mycat.log

#连接mycat：

mysql -uroot -p123456 -h 127.0.0.1 -P8066

Mycat 主要配置文件说明

user 用户配置节点

name 客户端登录MyCAT的用户名，也就是客户端用来连接Mycat的用户名。

password 客户端登录MyCAT的密码

schemas 数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如:db1,db2

privileges 配置用户针对表的增删改查的权限

readOnly mycat逻辑库所具有的权限。true为只读，false为读写都有，默认为false

用Mycat实现MySQL的读写分离

---

服务器共三台

mycat-server 192.168.100.10 内存建议2G以上

mysql-master 192.168.100.11

mysql-slave 192.168.100.12

关闭SELinux和防火墙

systemctl stop firewalld

setenforce 0

时间同步

创建 MySQL 主从数据库

yum -y install mariadb-server

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

#master上的my.cnf

[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf

[mysqld]

server-id = 1

log-bin

#slave上的my.cnf

[mysqld]

server-id = 2

[root@centos8 ~]#systemctl start mariadb

2. Master上创建复制用户

GRANT REPLICATION SLAVE ON . TO ‘repluser’@‘192.168.100.12’ IDENTIFIED BY ‘123456’;

FLUSH PRIVILEGES;

show master status;

3. Slave上执行

mysql -uroot -p

CHANGE MASTER TO

MASTER_HOST=‘192.168.100.11’,

MASTER_USER=‘repluser’,

MASTER_PASSWORD=‘replpass’,

MASTER_LOG_FILE=‘mariadb-bin.000001’,

MASTER_LOG_POS=403;

start slave;

show slave status\G

安装mycat并启动

yum -y install java mariadb

#确认安装成功

java -version

wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

mkdir /app

tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /app

配置环境变量

echo ‘PATH=/app/mycat/bin:$PATH’ > /etc/profile.d/mycat.sh

source /etc/profile.d/mycat.sh

启动mycat

mycat start

ss -ntlp

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

tail /app/mycat/logs/wrapper.log

用默认密码123456来连接mycat

mysql -uroot -p123456 -h 192.168.100.10 -P8066

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

vim /app/mycat/conf/server.xml

#连接Mycat的用户名

#连接Mycat的密码

123456

#数据库名要和schema.xml相对应

testdb

</mycat:server>

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

vim /app/mycat/conf/schema.xml

<mycat:schema xmlns:mycat=“http://io.mycat/”>

<schema name=“TESTDB” checkSQLschema=“false” sqlMaxLimit=“100”

dataNode=“dn1”>

<dataHost name=“localhost1” maxCon=“1000” minCon=“10” balance=“1”

writeType=“0” dbType=“mysql” dbDriver=“native” switchType=“1”

slaveThreshold=“100”>

select user()

***<writeHost host=“host1” url=“192.168.100.11:3306” user=“root”

password=“123456”>***

***<readHost host=“host2” url=“192.168.100.12:3306” user=“root”

password=“123456” />***

</mycat:schema>

#重新启动mycat

[root@centos8 ~]#mycat restart

balance改为1，表示读写分离。以上配置达到的效果就是192.168.100.11为主库，192.168.100.12为从库

要保证192.168.100.11和192.168.100.12机器能使用root/123456权限成功登录mysql数据库。

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

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

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

mysql> create database mycat;

mysql>GRANT ALL ON . TO ‘root’@‘192.168.100.10’ IDENTIFIED BY ‘123456’ WITH GRANT OPTION;

mysql> flush privileges;

在Mycat服务器上连接并测试

mysql -uroot -pmagedu -h127.0.0.1 -P8066 -DTESTDB

mysql> show databases;

±---------+

| DATABASE |

±---------+

| TESTDB | //只能看一个虚拟数据库

±---------+

mysql> use TESTDB;

mysql> create table t1(id int);

MySQL> select @@server_id;

MySQL> select @@hostname;

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

在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’; #设置日志文件保存位置

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

vim /etc/my.cnf.d/mariadb-server.cnf

[mysqld]

general_log=ON

systemctl restart mariadb

tail -f /var/lib/mysql/centos8.log

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

systemctl stop mariadb

mysql -uroot -pmagedu -h192.168.100.10 -P8066

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

±------------+

| @@server_id |

±------------+

| 1 |

±------------+ 1 row in set (0.00 sec)

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

二. ProxySQL（读写分离 代理服务器）

==========================================================================================

https://proxysql.com/

ProxySQL： MySQL中间件 两个版本：官方版和percona版，percona版是基于官方版基础上修改C++语言开发，轻量级但性能优异(支持处理千亿级数据)具有中间件所需的绝大多数功能，包括：

多种方式的读/写分离

定制基于用户、基于schema、基于语句的规则对SQL语句进行路由

缓存查询结果

后端节点监控

官方站点：https://proxysql.com/

官方手册：https://github.com/sysown/proxysql/wiki

**proxySQL的判断标准

从节点的read-only**

ProxySQL安装

---

准备：

实现读写分离前，先实现主从复制

注意：slave节点需要设置read_only=1

基于YUM仓库安装

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo

[proxysql_repo]

name= ProxySQL YUM repository

baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasever

gpgcheck=1

gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

EOF

基于RPM下载安装：https://github.com/sysown/proxysql/releases

ProxySQL组成

服务脚本：/etc/init.d/proxysql

配置文件：/etc/proxysql.cnf

主程序：/usr/bin/proxysql

基于SQLITE的数据库文件：/var/lib/proxysql/

启动ProxySQL：service proxysql start

启动后会监听两个默认端口

6032：ProxySQL 的管理端口

6033：ProxySQL 对外提供服务的端口

使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都是admin：

通过sql命令来配置管理

mysql -uadmin -padmin -P6032 -h127.0.0.1

数据库说明：

main 是默认的”数据库”名，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效，SAVE 使其存到硬盘以供下次重启加载

disk 是持久化到硬盘的配置，sqlite数据文件

stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间，等等

monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查

在main和monitor数据库中的表， runtime_开头的是运行时的配置，不能修改，只能修改非runtime_表

修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效

执行save … to disk 才将配置持久化保存到磁盘，即保存在proxysql.db文件中

global_variables 有许多变量可以设置，其中就包括监听的端口、管理账号等

参考: https://github.com/sysown/proxysql/wiki/Global-variables

向ProxySQL中添加MySQL节点

---

以下操作不需要use main也可成功

MySQL> show tables;

MySQL > select * from sqlite_master where name=‘mysql_servers’\G

MySQL > select * from mysql_servers;

MySQL > insert into mysql_servers(hostgroup_id,hostname,port)

values(10,‘192.168.8.17’,3306);

MySQL > insert into mysql_servers(hostgroup_id,hostname,port)

values(10,‘192.168.8.27’,3306);

MySQL > load mysql servers to runtime;

MySQL > save mysql servers to disk;

添加监控后端节点的用户。ProxySQL通过每个节点的read_only值来自动调整它们是属于读组还是写组

在master上执行

MySQL> grant replication client on . to monitor@‘192.168.8.%’ identified by ‘magedu’;

ProxySQL上配置监控

MySQL [(none)]> set mysql-monitor_username=‘monitor’;

MySQL [(none)]> set mysql-monitor_password=‘magedu’;

加载到RUNTIME，并保存到disk

MySQL [(none)]> load mysql variables to runtime;

MySQL [(none)]> save mysql variables to disk;

查看

监控模块的指标保存在monitor库的log表中

查看监控连接是否正常的 (对connect指标的监控)：(如果connect_error的结果为NULL则表示正常)

MySQL> select * from mysql_server_connect_log;

查看监控心跳信息 (对ping指标的监控)：

MySQL> select * from mysql_server_ping_log;

查看read_only和replication_lag的监控日志

MySQL> select * from mysql_server_read_only_log;

MySQL> select * from mysql_server_replication_lag_log;

设置分组信息

---

需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：writer_hostgroup，reader_hostgroup，comment, 指定写组的id为10，读组的id为20

MySQL> insert into mysql_replication_hostgroups values(10,20,“test”);

将mysql_replication_hostgroups表的修改加载到RUNTIME生效

MySQL> load mysql servers to runtime;

MySQL> save mysql servers to disk;

Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组

MySQL> select hostgroup_id,hostname,port,status,weight from mysql_servers;

±-------------±-------------±-----±-------±-------+

| hostgroup_id | hostname | port | status | weight |

±-------------±-------------±-----±-------±-------+

| 10 | 192.168.8.17 | 3306 | ONLINE | 1 |

面试题总结

其它面试题（springboot、mybatis、并发、java中高级面试总结等）

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

载到RUNTIME生效

MySQL> load mysql servers to runtime;

MySQL> save mysql servers to disk;

Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组

MySQL> select hostgroup_id,hostname,port,status,weight from mysql_servers;

±-------------±-------------±-----±-------±-------+

| hostgroup_id | hostname | port | status | weight |

±-------------±-------------±-----±-------±-------+

| 10 | 192.168.8.17 | 3306 | ONLINE | 1 |

面试题总结

其它面试题（springboot、mybatis、并发、java中高级面试总结等）

[外链图片转存中…(img-hGoXE1Rm-1715088034604)]

[外链图片转存中…(img-L1ZXnmmV-1715088034604)]

[外链图片转存中…(img-VU6XKIWu-1715088034605)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取