MySQL-proxy实现读写分离详细步骤

待我称王封你为后i 2023-10-08 11:02 12阅读 0赞

> MySQL读写分离介绍:
> 
> 官方提供了一种mysql-proxy的技术用于解决读写分离, 但不是GA, 官方不推荐在生产环境使用这种方式.

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk_size_16_color_FFFFFF_t_70][]

> 环境配置:

①准备3台服务器:

![20191223114310681.png][]

②在master和slave上配置主从复制

③进行proxy的相关配置:

上传安装包并解压:

![20191223114716851.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk_size_16_color_FFFFFF_t_70 3][]

启动mysql-proxy:

![20191223135004418.png][]

如果报文件权限不足错误, 就需要执行如下命令:

![201912231351452.png][]

重新启动mysql-proxy, 并验证是否启动成功:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk_size_16_color_FFFFFF_t_70 4][]

> 从其他机器上进行连接:

![20191223135606827.png][]

如果使用命令行会出现无法连接或者连接卡死的情况, 可以使用navicat等客户端工具进行连接:

![20191223135725283.png][]

停止MySQL服务, 验证从客户端查询数据到底是获取的哪台机器上的数据:

![20191223140048339.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk_size_16_color_FFFFFF_t_70 5][]

按照配置文件上的配置, 主节点既可以读也可以写, 从节点只可以读, 但是如果在连接proxy节点进行insert操作时, 主节点断掉, 仍然可以insert成功, 虽然会报错, 但是会将数据insert到从节点里, 跟我们的配置相悖.

**生产环境中一般不适用mysql-proxy的方式实现读写分离.**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019122311391939.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk=,size_16,color_FFFFFF,t_70
[20191223114310681.png]: https://img-blog.csdnimg.cn/20191223114310681.png
[20191223114716851.png]: https://img-blog.csdnimg.cn/20191223114716851.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191223114825139.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191223115151143.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191223115410714.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk=,size_16,color_FFFFFF,t_70
[20191223135004418.png]: https://img-blog.csdnimg.cn/20191223135004418.png
[201912231351452.png]: https://img-blog.csdnimg.cn/201912231351452.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191223135331921.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk=,size_16,color_FFFFFF,t_70
[20191223135606827.png]: https://img-blog.csdnimg.cn/20191223135606827.png
[20191223135725283.png]: https://img-blog.csdnimg.cn/20191223135725283.png
[20191223140048339.png]: https://img-blog.csdnimg.cn/20191223140048339.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20191223140154636.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2E3NzIzMDQ0MTk=,size_16,color_FFFFFF,t_70