2

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!

Gasser A
  • 21
  • 3

2 Answers2

1

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

Harsh Manvar
  • 27,020
  • 6
  • 48
  • 102
  • Hi! Thanks for taking the time to reply! Yes I did use what you are labeling as option 1 and it was working perfectly when I had one stateful pod but now that I am using a statefulset it doesn't, I get an error saying that I am connecting with read-only when I use the headless service name as specified in the guide. As you are suggesting in option 2, when I use mysql-0.mysql, everything works, but this means I am only connected to the master and not using the other two slaves! Am I mistaken ? – Gasser A Dec 29 '21 at 06:26
  • you are right in terms of understanding. when you are connecting with specifc replica you will be able to do a write or read operation. `mysql-0.mysql` is master here so if you want to use read replicas also in code you have to use `mysql-1.mysql` or `mysql-2.mysql`. – Harsh Manvar Dec 29 '21 at 06:38
  • i am not sure about MySQL behavior when any of the pod crash it change master replica automatic or not. With the Redis database, if one pod crash Master get changed to replicas number 2. so you need to check this scenario before using fix `mysql-0.mysql` for just write and `mysql-1.mysql` just read. – Harsh Manvar Dec 29 '21 at 06:42
  • I understand what you mean! But when I try to mention more than one replica in the connection string the DNS look up fails, if i connect ti mysql-1.mysql then i wont be able to write data! If I make two clients one that connects to master the other to slave its gonna be very confusing in code i feel to use sometimes a client then another, do you agree? – Gasser A Dec 29 '21 at 06:50
  • yes you are absolutely right on that part. that's the one way. i am again sorry not sure with MySQL but in Redis there is concept called sentinel, what it do is return the IP of each availble replicas with write and read IP. consider it like app when you hit it return the master and slave ip, you can use that further with method. example : https://github.com/redis/redis-py#sentinel-support – Harsh Manvar Dec 29 '21 at 06:56
  • there could be some way using MySQL service name as single connection and use it into the application, checking for best practice. – Harsh Manvar Dec 29 '21 at 06:58
0

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/

shahin mahmud
  • 945
  • 4
  • 11