How to Connect Golang application to mysql statefulset in Kubernetes-CodePudding

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!

CodePudding user response：

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

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 .mysql from within any other Pod in the same Kubernetes cluster and namespace.

CodePudding user response：

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/