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 try using 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?
