当前位置：首页 > 数据库 > 正文

数据库集群

yunbo1
数据库
2026-08-26
148

分布式MySql 部署方案

解决方案

描述

1 2 3 4 1. 启动mysql主从备份 2. 通过使用Mycat中间件做分表以及路由 3. 使用haproxy代理MyCat做负载均衡 4. keepalived保证haproxy的高可用性，解决单点故障。

结构图

系统环境">系统环境

| system | ip | user | cpu | memory |
| ————- |:————-:| —–:|————- |:————-:| —–:|8G|
| centos7 | 192.168.100.95 | root |cpu: Intel(R) Pentium(R) CPU G3220 @ 3.00GHz 双核|8G|
| centos7 | 192.168.100.96 | root |cpu: Intel(R) Pentium(R) CPU G3220 @ 3.00GHz 双核|8G|
| centos7 | 192.168.100.97 | root |cpu: Intel(R) Pentium(R) CPU G3220 @ 3.00GHz 双核|8G|

mysql主从备份

修改配置文件(my.conf)

主库配置

1 2 3 4 5 6 7 8 9 10 11 Server-id = 1 #这是数据库ID,此ID是唯一的，主库默认为1，其他从库以此ID进行递增，ID值不能重复，否则会同步出错； log-bin = mysql-bin 二进制日志文件，此项为必填项，否则不能同步数据； binlog-do-db = dbTest1 #需要同步的数据库，如果需要同步多个数据库； 则继续添加此项。 binlog-do-db = dbTest2 binlog- ignore -db = mysql 不需要同步的数据库；

从库配置

1 2 3 4 5 6 log_bin = mysql-bin server_id = 2 relay_log = mysql-relay-bin log_slave_updates = 1 read_only = 1 user = mysql

重启数据库

为master数据库添加访问权限

1 2 3 create user repl; GRANT REPLICATION SLAVE ON *.* TO ‘repl‘ @ ‘192.168.100.%‘ IDENTIFIED BY ‘1234‘ ; #访问权限添加 SHOW MASTER STATUS; #显示主节点状态

slave 备份配置

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 change master to master_host= ‘192.168.100.96‘ , #master的host master_port=3306, #端口 master_user= ‘repl‘ , #用户 master_password= ‘1234‘ , #密码 master_log_file= ‘mysql-bin.000001‘ , #日志文件名 master_log_pos=3204; #开始位置将从这个位置开始备份 SHOW SLAVE STATUS; #查看slave状态 START SLAVE; #开启备份 STOP SLAVE; #停止备份 注意: 在开启备份后<show slave= "" status= "" >会看到： Slave_IO_Runing=Yes Slave_SQL_Runing=Yes 说明备份启动成功。</show>

MyCat中间件搭建

下载地址

https://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

配置文件

server.xml

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 <! --?xml version="1.0" encoding="UTF-8"?--> <mycat:server xmlns:mycat= "https://io.mycat/" > <system> <property name = "serverPort" >8066</property> <property name = "bindIp" >192.168.100.96</property> <property name = "managerPort" >9066</property> <property name = "systemReserveMemorySize" >384m</property> <property name = "defaultSqlParser" >druidparser</property> </system> < user name = "admin" > <property name = "password" >mypass</property> <property name = "schemas" >dbTest</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="admin"> <property name = "password" >mypass</property> <property name = "schemas" >db</property> <property name = "readOnly" > false </property> </ user > --> </mycat:server> 说明: 1. 结合文档很容易知道配置含义，不在说明。 2. 主要是对外用户配置，以及管理端口，服务端口配置，和其它一些配置。

schema.xml

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 <! --?xml version="1.0"?--> <mycat: schema xmlns:mycat= "https://io.mycat/" > < schema checksqlschema= "true" name = "dbTest" sqlmaxlimit= "100" > <! -- auto sharding by id (long) --><table datanode="dn1,dn2" name="t_user" primarykey="id" rule="rule1"><tbody><tr><td> <! -- global table is auto cloned to all defined data nodes ,so can join with any table whose sharding node is in the same data node --></td></tr></tbody></table><datanode database="dbTest1" datahost="localhost1" name="dn1"> <datanode database = "dbTest2" datahost= "localhost1" name = "dn2" > <datahost balance= "0" dbdriver= "native" dbtype= "mysql" maxcon= "1000" mincon= "10" name = "localhost1" slavethreshold= "100" switchtype= "1" writetype= "0" > <heartbeat>show status like ‘wsrep%‘ </heartbeat> <! -- can have multi write hosts --> <writehost host= "hostM1" password = "mypass" url= "192.168.100.96:3306" user = "admin" > <! -- can have multi read hosts --> <readhost host= "hostS2" password = "mypass" url= "192.168.100.97:3306" user = "admin" > </readhost></writehost> <! -- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --> </datahost> 说明: 1. 数据库对应表分表配置,其中 rule 对应 rule .xml中分表的类型。 2. datanode 所分的数据库名字以及datahost名字。 3. datahost 连接配置，主数据库配置，以及从数据库配置。<h4 id= "rulexml" > rule .xml</h4><pre class= "brush:sql;" ><! --?xml version="1.0" encoding="UTF-8"?--> <mycat: rule xmlns:mycat= "https://io.mycat/" > <tablerule name = "rule1" > < rule > <columns>id</columns> func1</algorithm> </ rule > </tablerule> < function class= "io.mycat.route.function.PartitionByLong" name = "func1" > <property name = "partitionCount" >8</property> <property name = "partitionLength" >128</property> </ function > </mycat: rule > 说明: 1. 默认分表规则有很多种，可以酌情选择使用。</pre> <h3 id= "启动" >启动</h3> <pre class= "brush:sql;" >1. ./bin/mycat start #启动mycat 2. tail -n1000 -f ./logs/wrapper.log #查看启动日志 3. tail -n1000 -f ./logs/mycat.log #查看mycat.log服务日志</pre> <h2 id= "haproxy-负载代理" >haproxy 负载代理</h2> <h3 id= "下载地址-1" >下载地址</h3> <p>https://www.haproxy.org/download/1.7/src/haproxy-1.7.3.tar.gz</p> <h3 id= "参考文档" >参考文档</h3> <p>https://blog.csdn.net/zzhongcy/article/details/46443765</p> <h3 id= "安装" >安装</h3> <pre class= "brush:sql;" >　uname -a //查看Linux内核版本, TARGET是内核版本，2.6就写作26 　　make TARGET=linux26 PREFIX=/usr/ local /haproxy 　　make install PREFIX=/usr/ local /haproxy 　　</pre> <h3 id= "配置" >配置</h3> <pre class= "brush:sql;" > 1. mkdir /etc/haproxy/conf 2. vim /etc/haproxy/conf/haproxy.cfg global log 127.0.0.1 local0 ##记日志的功能 maxconn 4096 chroot /usr/ local /haproxy user haproxy group haproxy daemon ########默认配置############ defaults log global mode tcp #默认的模式mode { tcp|http|health }，tcp是4层，http是7层，health只会返回OK retries 3 #两次连接失败就认为是服务器不可用，也可以通过后面设置 option redispatch #当serverId对应的服务器挂掉后，强制定向到其他健康的服务器 option abortonclose #当服务器负载很高的时候，自动结束掉当前队列处理比较久的链接 maxconn 32000 #默认的最大连接数 timeout connect 5000ms #连接超时 timeout client 30000ms #客户端超时 timeout server 30000ms #服务器超时 timeout check 2000 #心跳检测超时 #log 127.0.0.1 local0 err #[err warning info debug] ########test1配置################# listen mycat_1 bind 0.0.0.0:8076 mode tcp balance roundrobin server s1 192.168.100.95:8066 weight 1 maxconn 10000 check inter 10s server s2 192.168.100.96:8066 weight 1 maxconn 10000 check inter 10s listen mycat_1_manage bind 0.0.0.0:9076 mode tcp balance roundrobin server s1 192.168.100.95:9066 weight 1 maxconn 10000 check inter 10s server s2 192.168.100.96:9066 weight 1 maxconn 10000 check inter 10s</pre> <p>### 启动<br> <br> /usr/ local /haproxy/sbin/haproxy -f /usr/ local /haproxy/haproxy.cfg</p> <h2 id= "keepalived-解决单点故障" >keepalived 解决单点故障</h2> <h3 id= "下载地址-2" >下载地址</h3> <p>https://www.keepalived.org/software/keepalived-1.3.5.tar.gz</p> <h3 id= "文档-1" >文档</h3> <p>https://www.keepalived.org/documentation.html</p> <h3 id= "安装-1" >安装</h3> <pre class= "brush:sql;" >./configure && make</pre> <h3 id= "配置-1" >配置</h3> <pre class= "brush:sql;" >1. mkdir -p /usr/ local /etc/keepalived/ 2. vim /usr/ local /etc/keepalived/keepalived.conf global_defs { router_id NodeB } vrrp_instance VI_1 { state BACKUP #设置为主服务器 interface enp3s0 #监测网络接口 virtual_router_id 51 #主、备必须一样 priority 90 #(主、备机取不同的优先级，主机值较大，备份机值较小,值越大优先级越高) advert_int 1 #VRRP Multicast广播周期秒数 authentication { auth_type PASS #VRRP认证方式，主备必须一致 auth_pass 1111 #(密码) } virtual_ipaddress { 192.168.200.100/24 #VRRP HA虚拟地址 } </pre> <h3 id= "启动-1" >启动</h3> <pre class= "brush:sql;" >./bin/keepalived -D -f /usr/ local /etc/keepalived/keepalived.conf</pre> <h2 id= "mycat-eye-监控web" >mycat-eye 监控web</h2> <h3 id= "下载地址-3" >下载地址</h3> <pre class= "brush:sql;" >https://dl.mycat.io/mycat-web-1.0/Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz</pre> <h3 id= "安装zookeeper" >安装zookeeper</h3> <pre class= "brush:sql;" >docker run -d \ -e MYID=1 \ --name=zookeeper --net=host --restart=always sdvdxl/zookeeper</pre> <h3 id= "配置-2" >配置</h3> <pre class= "brush:sql;" >修改zookeeper地址： 1. cd /mycat-web/WEB-INF/classes 2. vim mycat.properties 3. zookeeper=127.0.0.1:2181 </pre> <h3 id= "启动-2" >启动</h3> <pre class= "brush:sql;" >1. cd /mycat-web/ 2. ./start.sh & </pre> <h2 id= "实验环境整体结构图" >实验环境整体结构图</h2> <p><img alt= "这里写图片描述" src= "/uploadfile/Collfiles/20170818/20170818103343280.jpg" title= "\" style=" width: 630px; height: 311.85px; "></p> <h2 id=" 补充 ">补充</h2> <h3 id=" mycat-密码明文加密 ">MyCat 密码明文加密</h3> <pre class=" brush:sql; "> 1. java -cp Mycat-server-1.6-RELEASE.jar io.mycat.util.DecryptUtil 1:userB:root:321 2. 修改配置 <property name=" usingDecrypt ">1</property> #使用加密 说明: 1. 0 为对外提供密码加密，1.是后端也就是数据库连接密码加密 2. userB 用户名 3. 321 明文密码</pre> </datanode></datanode> <table datanode=" dn1,dn2 " name=" t_company " primarykey=" id " rule=" rule1 " type=" global "></table> </ schema ></mycat: schema >