MySQL数据库集群项目实战（主从架构、mycat、haproxy负载均衡、PXC集群）

傷城~ 2023-10-07 08:23 18阅读 0赞

**目录**

--------------------

架构

部署PXC集群

部署主从复制集群

master

slave

部署mycat

节点一

sever.xml

schema.xml

rule.xml

设置端口以及启动

测试

节点二

部署HAProxy

修改配置文件

启动docker容器

--------------------

## ![20200119102043257.png][] ##

## 架构 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1][]

## 部署PXC集群 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 5][]

## 部署主从复制集群 ##

### master ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 6][]

### slave ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 7][]

> 测试：在主节点创建库和表，然后看从节点是否同步了。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 8][]

## 部署mycat ##

### 节点一 ###

![20200119110347720.png][]

#### sever.xml ####

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 9][]

#### schema.xml ####

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 10][]

#### rule.xml ####

![20200119111509201.png][]

#### 设置端口以及启动 ####

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 11][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 12][]

#### 测试 ####

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 13][]

**测试插入数据：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 14][]

**测试结果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 15][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 16][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 17][]

### 节点二 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 18][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 19][]

## 部署HAProxy ##

### 修改配置文件 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 20][]

### 启动docker容器 ###

![20200119113008651.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 21][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 22][]

[20200119102043257.png]: https://img-blog.csdnimg.cn/20200119102043257.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200119101755449.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200119101955838.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200119102146647.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200119102446757.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200119104745773.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200119105147625.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200119105450678.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200119105842370.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200119110131804.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[20200119110347720.png]: https://img-blog.csdnimg.cn/20200119110347720.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200119110445790.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/2020011911140047.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[20200119111509201.png]: https://img-blog.csdnimg.cn/20200119111509201.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20200119111912356.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20200119112038890.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/20200119112133461.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 14]: https://img-blog.csdnimg.cn/20200119112212360.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 15]: https://img-blog.csdnimg.cn/20200119112334778.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 16]: https://img-blog.csdnimg.cn/20200119112400890.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 17]: https://img-blog.csdnimg.cn/20200119112408378.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 18]: https://img-blog.csdnimg.cn/20200119112502916.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 19]: https://img-blog.csdnimg.cn/20200119112646926.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 20]: https://img-blog.csdnimg.cn/20200119113139334.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[20200119113008651.png]: https://img-blog.csdnimg.cn/20200119113008651.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 21]: https://img-blog.csdnimg.cn/20200119113241439.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 22]: https://img-blog.csdnimg.cn/20200119113314807.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly96aGFuZ3h1ZWxpYW5nLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70