go语言实现mysql集群的简单负载均衡器

来源：这里教程网时间：2026-03-01 15:43:17 作者：

## 1.环境
* mysql集群：master01、master02组成双主，slave01、slave02、slave03是3个从节点，写操作分布到两个master上，读操作分布到3个slave上。所有mysql的ip，port等如下：
|   hostname   |   ip   |   port   |   dbname|
| :----: | :----: | :----: | :----: |
|   master01   |   192.168.122.5   |   3307   |   bmsql|
|   master02   |   192.168.122.5   |   3308   |  bmsql|
|   slave01   |   192.168.122.5   |   3309   |  bmsql|
|   slave02   |   192.168.122.5   |   3310   |  bmsql|
|   slave03   |   192.168.122.5   |   3311   |  bmsql|
* docekr:20.10.5
* go:1.14.6 
## 2.实现思路
在master02上不同步的数据库上建一张表储存所有的节点信息，其中，type表示该节点为读节点还是写节点，将type置为空字符串 "" 即可将该节点暂时移出集群
具体如下：
```
mysql> desc nodes;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| host   | char(28)            | NO   | PRI | NULL    |       |
| port   | int(10) unsigned    | NO   | PRI | NULL    |       |
| type   | set('READ','WRITE') | NO   |     |         |       |
| dbname | char(30)            | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> select * from nodes;
+---------------+------+-------+--------+
| host          | port | type  | dbname |
+---------------+------+-------+--------+
| 196.168.122.5 | 3307 | WRITE | bmsql  |
| 196.168.122.5 | 3308 | WRITE | bmsql  |
| 196.168.122.5 | 3309 | READ  | bmsql  |
| 196.168.122.5 | 3310 | READ  | bmsql  |
| 196.168.122.5 | 3311 | READ  | bmsql  |
+---------------+------+-------+--------+
5 rows in set (0.00 sec)
```
程序若要连接数据库，再根据是要读还是写查询这张表，使用 order by RAND() limit 1 来选择要连接的节点，实现负载均衡。
具体sql为：
``select host,port,type,dbname from nodes where type=? order by rand() limit 1``
## 3.代码实现：
首先，下载连接mysql需要的包
`go get github.com/go-sql-driver/mysql `
`go get github.com/jmoiron/sqlx`
完整代码如下：
```
package main
// 目的：实现一个简单的mysql集群的应用级别负载均衡器
// mysql集群:
//		master01、master02双主
//		slave01、slave02、slave03为master02的从节点
// 写操作分摊master01，master02
// 读操作分摊到slave01、slave02、slave03
// master01 192.158.122.5 3307
// master02 192.158.122.5 3308
// slave01 192.158.122.5 3309
// slave02 192.158.122.5 3310
// slave03 192.158.122.5 3311
import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"strconv"
	"time"
)

type Nodes struct {
	Host string `db:"host"`
	Port int `db:"port"`
	Dbname string `db:"dbname"`
	Type string `db:"type"`
}
// 由于所有的mysql在同一主机上，通过端口获取连接到的mysql名字，master01、master02等
func getHostname(port int){
	var hostName string
	switch port {
	case 3307:hostName="master01"
	case 3308:hostName="master02"
	case 3309:hostName="slave01"
	case 3310:hostName="slave02"
	case 3311:hostName="slave03"
	}
	fmt.Println(hostName)
}
// 通过信息连接mysql
func connectTo(node []Nodes) *sqlx.DB{
	var conString string
	conString="root:root@tcp("+node[0].Host+":"+strconv.Itoa(node[0].Port)+")/"+node[0].Dbname
	db,err := sqlx.Open("mysql",conString)
	if err != nil {
		fmt.Println("open mysql failed,", err)
		return nil
	}
	getHostname(node[0].Port)
	return db
}
// 读操作就连接slave，写操作就连接master，通过 order by RAND() 来实现负载均衡
func getNodes(Type string) []Nodes {
	master02,err := sqlx.Open("mysql","root:root@tcp(192.168.122.5:3308)/loadBalance")
	if err != nil {
		fmt.Println("open mysql failed,", err)
		return nil
	}
	var node []Nodes
	sql := "select host,port,type,dbname from nodes where type=? order by rand() limit 1"
	err2 := master02.Select(&node, sql, Type )
	if err2 != nil {
		fmt.Println("exec failed, ", err2)
		return nil
	}
	return node
}

func main(){
	var node []Nodes
//  写操作
	fmt.Println("write ten times")
	for i := 0; i < 10; i++ {
		node = getNodes("WRITE")
		connectTo(node)
	}
	time.Sleep(10000)
// 读操作
	fmt.Println("read ten times")
	for j := 0; j < 10; j++ {
		node = getNodes("READ")
		connectTo(node)
	}
}
```

## 不足： 
* 该mysql集群双主同时写数据可能会造成冲突
* 这个简单负载均衡器使用的是sql的RAND()来进行分配，不是一个好选择。
## 参考：
  《高可用MySQL_构建健壮的数据中心》