Mysql读写分离，Mycat、ProxySQL（代理服务器）--多种问题

Mysql读写分离，Mycat、ProxySQL（代理服务器）--多种问题

2024-12-26 14:57

Mycat关键特性

支持SQL92标准

遵守MySQL 原生协议，跨语言，跨平台，跨数据库的通用中间件代理

基于心跳的自动故障切换，支持读写分离，支持MySQL主从，以及galera cluster集群

支持Galera for MySQL集群，Percona Cluster或者MariaDB cluster

基于Nio实现，有效管理线程，高并发问题

支持数据的多片自动路由与聚合，支持sum,count,max等常用的聚合函数,支持跨库分页

支持单库内部任意join，支持跨库2表join，甚至基于caltlet的多表join

支持通过全局表，ER关系的分片策略，实现了高效的多表join查询

支持多租户方案

支持分布式事务（弱xa）

支持全局序列号，解决分布式下的主键生成问题

分片规则丰富，插件化开发，易于扩展

强大的web，命令行监控

支持前端作为mysq通用代理，后端JDBC方式支持Oracle、DB2、SQL Server 、 mongodb 、巨杉

支持密码加密

支持服务降级

支持IP白名单

支持SQL黑名单、sql注入攻击拦截

支持分表（1.6）

集群基于ZooKeeper管理，在线升级，扩容，智能优化，大数据处理（2.0开发版）

Mycat工作原理

Mycat的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的SQL语句，首先对SQL语句做了一些特定的分析：等，然后将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户

MyCat的高可用性：

需要注意: 在生产环境中, Mycat节点最好使用双节点, 即双机热备环境, 防止Mycat这一层出现单点故障.

可以使用的高可用集群方式有:

Keepalived+Mycat+Mysql

Keepalived+LVS+Mycat+Mysql

Keepalived+Haproxy+Mycat+Mysql

例如：lvs+mysq实现读写分离：参考

例二：keepalived+LVS+mysql 实现读写分离+高可用

Mycat安装

下载安装JDK

yum -y install java

#确认安装成功

java -version

下载安装mycat

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安装目录结构：

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 主要配置文件说明

用户配置节点

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

客户端登录MyCAT的密码

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

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

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

修改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

Master上创建复制用户

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

FLUSH PRIVILEGES;

show master status;

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 statusG

安装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 服务器上修改文件配置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

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

启动ProxySQL：service proxysql start

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

6032：ProxySQL 的管理端口

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

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

通过sql命令来配置管理

mysql -uadmin -padmin -P6032 -h127.0.0.1

数据库说明：

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

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

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

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

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

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

1、JAVA面试核心知识整理（PDF）：包含JVM，JAVA集合，JAVA多线程并发，JAVA基础，Spring原理，微服务，Netty与RPC，网络，日志，Zookeeper，Kafka，RabbitMQ，Hbase，MongoDB，Cassandra，设计模式，负载均衡，数据库，一致性哈希，JAVA算法，数据结构，加密算法，分布式缓存，Hadoop，Spark，Storm，YARN，机器学习，云计算共30个章节。

2、Redis学习笔记及学习思维脑图

3、数据面试必备20题+数据库性能优化的21个最佳实践

[外链图片转存中…(img-lL3Xcmcy-1711803618610)]

[外链图片转存中…(img-lzeWMMzS-1711803618611)]

2、Redis学习笔记及学习思维脑图

[外链图片转存中…(img-mB9zAlX8-1711803618611)]