英文:
How to Connect Golang application to mysql statefulset in Kubernetes
问题
我按照官方的步骤部署了MySQL作为一个有状态集合,步骤在这里:https://kubernetes.io/docs/tasks/run-application/run-replicated-stateful-application/
我已经成功地启动并运行了它,但是指南上说:
> 客户端服务称为mysql-read,它是一个普通的服务,具有自己的集群IP,可以将连接分发到所有报告为就绪状态的MySQL Pod。潜在的终端点集包括主MySQL服务器和所有副本。
请注意,只有读取查询可以使用负载均衡的客户端服务。由于只有一个主MySQL服务器,客户端应该直接连接到主MySQL Pod(通过Headless服务中的DNS条目)来执行写操作。
这是我的连接代码:
func NewMysqlClient() *sqlx.DB {
//username:password@protocol(address)/dbname?param=value
dataSourceName := fmt.Sprintf("%s:%s@tcp(%s)/%s?parseTime=true&multiStatements=true",
username, password, host, schema,
)
log.Println(dataSourceName)
var mysqlClient *sqlx.DB
var err error
connected := false
log.Println("trying to connect to db")
for i:=0; i<7; i++{
mysqlClient, err = sqlx.Connect("mysql", dataSourceName)
if err == nil {
connected = true
break
} else {
log.Println(err)
log.Println("failed will try again in 30 secs!")
time.Sleep(30*time.Second)
}
}
if (!connected){
log.Println(err)
log.Println("Couldn't connect to db will exit")
os.Exit(1)
}
log.Println("database successfully configured")
return mysqlClient
}
当我将应用程序连接到无头MySQL服务时,我得到以下错误:
Error 1290: The MySQL server is running with the --super-read-only option so it cannot execute this statement"
我猜想它连接到了其中一个从节点,当我连接到mysql-0.mysql主机时,一切正常,这是预期的,因为这是主节点。
我的问题是,当我们只连接到主节点时,我的应用程序如何能够从从节点读取数据,因为应用程序需要能够写入数据。
我尝试使用mysql-0.mysql,mysql-1.mysql,mysql-2.mysql
,但是我得到以下错误:
dial tcp: lookup mysql-0.mysql;mysql-1.mysql,mysql-2.mysql: no such host
所以我想知道是否有办法将这三个副本连接在一起,以便我们可以向主节点写入并从任何一个节点读取,就像其他数据库(如Mongo等)一样。
如果没有办法连接到所有副本,你会如何建议我从从节点读取并向主节点写入。
谢谢!
英文:
I followed the official walkthrough on how to deploy MySQL as a statefulset here https://kubernetes.io/docs/tasks/run-application/run-replicated-stateful-application/
I have it up and running well but the guide says:
> The Client Service, called mysql-read, is a normal Service with its own cluster IP that distributes connections across all MySQL Pods that report being Ready. The set of potential endpoints includes the primary MySQL server and all replicas.
Note that only read queries can use the load-balanced Client Service. Because there is only one primary MySQL server, clients should connect directly to the primary MySQL Pod (through its DNS entry within the Headless Service) to execute writes.
this is my connection code:
func NewMysqlClient() *sqlx.DB {
//username:password@protocol(address)/dbname?param=value
dataSourceName := fmt.Sprintf("%s:%s@tcp(%s)/%s?parseTime=true&multiStatements=true",
username, password, host, schema,
)
log.Println(dataSourceName)
var mysqlClient *sqlx.DB
var err error
connected := false
log.Println("trying to connect to db")
for i:=0; i<7; i++{
mysqlClient, err = sqlx.Connect("mysql", dataSourceName)
if err == nil {
connected = true
break
} else {
log.Println(err)
log.Println("failed will try again in 30 secs!")
time.Sleep(30*time.Second)
}
}
if (!connected){
log.Println(err)
log.Println("Couldn't connect to db will exit")
os.Exit(1)
}
log.Println("database successfully configured")
return mysqlClient
}
when I connect the app to the headless MySQL service, I get:
Error 1290: The MySQL server is running with the --super-read-only option so it cannot execute this statement"
I am guessing it is connecting to one of the slave replicas, when I connect to mysql-0.mysql host, everything works fine which is what is expected as this the master node.
My question is how will my application be able to read from the slave nodes when we are only connecting to the master as the application needs to be able to write data.
I tried using mysql-0.mysql,mysql-1.mysql,mysql-2.mysql
but then I get:
dial tcp: lookup mysql-0.mysql;mysql-1.mysql,mysql-2.mysql: no such host
So I want to know if there is anyway to connect to the three replicas together so that we write to the master and read from any as with other databases like mongo etc.
If there is no way to connect to all the replicas, how would you suggest that I read from the slaves and write to the master.
Thank you!
答案1
得分: 1
你必须使用服务名称来连接Go应用程序中的MySQL。
所以你的流量流向如下:
Go应用程序POD在同一个K8s集群中运行,与容器内的POD相同
发送请求到MySQL服务 -> MySQL服务将流量转发到MySQL有状态副本集(POD或其他合并副本)
所以如果你在你的情况下创建了服务,主机名将是服务名称:mysql
例如,你可以参考这个链接:https://kubernetes.io/docs/tutorials/stateful-application/mysql-wordpress-persistent-volume/
如果你注意到WordPress是如何连接到mysql的
containers:
- image: wordpress:4.8-apache
name: wordpress
env:
- name: WORDPRESS_DB_HOST
value: wordpress-mysql
它使用MySQL服务名称wordpress-mysql
作为主机名进行连接。
如果你只想连接到读取副本,你可以使用服务名称mysql-read
或者你也可以尝试连接到
kubectl run mysql-client --image=mysql:5.7 -i --rm --restart=Never --\ mysql -h mysql-0.mysql
选项-2
如果你只想连接到特定的POD或写入副本,你可以使用
<pod-name>.mysql
无头服务为StatefulSet控制器为集合中的每个Pod创建的DNS条目提供了一个主机。由于无头服务的名称为mysql,所以可以通过在同一Kubernetes集群和命名空间中的任何其他Pod中解析
.mysql来访问Pods。
英文:
You have to use the service name for connecting with the MySQL from Go application.
So your traffic flow like
Go appliction POD running inside same K8s cluster as POD inside the container
send a request to MySQL service -> MySQL service forward traffic to MySQL stateful sets (PODs or in other merge replicas)
So if you have created the service in your case host name will be service name : mysql
For example you can refer this : https://kubernetes.io/docs/tutorials/stateful-application/mysql-wordpress-persistent-volume/
If you notice how WordPress is connceting to mysql
containers:
- image: wordpress:4.8-apache
name: wordpress
env:
- name: WORDPRESS_DB_HOST
value: wordpress-mysql
it's using the MySQL service name wordpress-mysql
as hostname to connect.
If you just want to connect with Read Replica you can use the service name mysql-read
OR
you can also use try connecting with
kubectl run mysql-client --image=mysql:5.7 -i --rm --restart=Never --\
mysql -h mysql-0.mysql
Option -2
if you just to connect with specific POD or write a replica you can use the
<pod-name>.mysql
> The Headless Service provides a home for the DNS entries that the
> StatefulSet controller creates for each Pod that's part of the set.
> Because the Headless Service is named mysql, the Pods are accessible
> by resolving <pod-name>.mysql from within any other Pod in the same
> Kubernetes cluster and namespace.
答案2
得分: 0
另一种适当的方法是,你的应用程序代码忽略主实例、副本实例等,就像它连接到单个主实例一样运行,并且读写查询拆分在一个功能强大的代理中进行抽象化。而这个代理负责将写查询路由到主实例,将读查询路由到副本实例。
示例代理 - https://proxysql.com/
英文:
Another appropriate approach could be your application code ignores master, replica instance, etc and operates like it's connected to a single master instance and read, write query splitting is abstracted in a capable proxy. And that proxy is responsible for routing the write queries to the master instance and read queries to the replica instances.
Example proxy - https://proxysql.com/
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论