Set MySql read-only from K8s cluster

4/19/2021

I have a Kubernetes cluster running with MySql app (5.7.32 MySql version). I'm trying to make MySql app be in a consistency mode, using this command:

"FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON;"

When I run this command from inside the app - it works (getting error when trying to write when the DB is locked):

kubectl exec -ti mysql-0 -c mysql -- mysql

mysql> FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON; INSERT INTO mydb.mytable VALUES('hello');

But When I run this command using kubectl exec command - it doesn't work (this command doesn't fail, and it is able to write to the DB):

kubectl exec -ti mysql-0 -c mysql -- mysql -h mysql -e "FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON; INSERT INTO mydb.mytable VALUES('hello'); "

I also created a grant privileged user, it didn't help:

kubectl exec -ti mysql-0 -c mysql -- mysql -h mysql -e "CREATE USER $USER@'$HOST' IDENTIFIED BY '$PASS'; GRANT ALL PRIVILEGES ON . TO $USER@'$HOST';"

kubectl exec -ti mysql-0 -c mysql -- mysql -h mysql -u $USER -p$PASS -e "FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON; INSERT INTO mydb.mytable VALUES('hello'); "

How Can I bring my MySql app to be in a read-only state without running the command from inside the application?

Thanks!

-- Moranoy
consistency
kubernetes
mysql
readonly

0 Answers