Does super_read_only works with XtraDB?

MySQL & MariaDB Percona XtraDB Cluster 8.x
,
1

Hello,

We have this asyncronous replication configured between “standalone” database and XtraDB cluster. Is it possible to use “super_read_only” with XtraDB cluster to prevent accidental writes on XtraDB?

Thanks.

1 Like
2

Hi @katajistok , thanks for posting to the Percona forums!

Usage of super_read_only is an additional protection above read_only so that even if you have client accounts granted the SUPER privilege, you will be able to block them from accidentally writing to the Replica.

Replication from your asynchronous standalone + PXC will still be applied, the replication thread processes are not blocked by read_only or super_read_only.

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only

1 Like
3

Yes. As Michael said, only replication will be able to write to your XtraDB cluster.

1 Like
4

Hi,
Could you advice how to get either super_read_only or read_only to be enabled on XtraDB cluster?
I have now on cr.yaml:

    configuration: |
      [mysqld]
      super_read_only = ON

But:

[katajistok@dbaasjump002 mysql_production_configuration]$ kubectl exec testreadonly-pxc-0 -c pxc -- mysql -uroot -proot_password -e'SHOW VARIABLES LIKE "read_only"';
mysql: [Warning] Using a password on the command line interface can be insecure.
Variable_name   Value
read_only       OFF
[katajistok@dbaasjump002 mysql_production_configuration]$ kubectl exec testreadonly-pxc-0 -c pxc -- mysql -uroot -proot_password -e'SHOW VARIABLES LIKE "super_read_only"';
mysql: [Warning] Using a password on the command line interface can be insecure.
Variable_name   Value
super_read_only OFF

I also tried to enable “read_only” from ConfigMap and from executing “SET GLOBAL read_only = ON” on MySQL, but nothing have helped so far. For the record, this cluster does not have replication configured.

1 Like
5

Did you re-deploy the cr.yaml and restart the cluster (all pods)?

1 Like
6

Yes. I tested all the things that came in to my mind. First I just edited cr.yaml and applied the changes, I think that Pod were restarted, but no effect. Then I tried to change ConfigMap - the same. And after this I removed cluster (kubectl delete -f cr.yaml) and re-created cluster (I didn’t touch on PVC’s).
Only thing which helped (for a minute) was that I logged in to db node and executed that SET GLOBAL read_only = ON. Then the setting was there for a minute or so.

1 Like
7

If the value was reset after you changed it, then there’s probably something our operator is doing which is not honoring the value in my.cnf. I’ll see if I can get an Operator dev to comment here.

1 Like
8

Hi this is the cr.yaml which I’m now using: cr - Pastebin.com.

Some commands:

[katajistok@dbaasjump002 mysql_production_configuration]$ kubectl get pods
NAME                                               READY   STATUS    RESTARTS   AGE
percona-xtradb-cluster-operator-566848cf48-nzxrp   1/1     Running   0          35h
testreadonly-haproxy-0                             2/2     Running   0          95s
testreadonly-haproxy-1                             2/2     Running   0          60s
testreadonly-haproxy-2                             2/2     Running   0          40s
testreadonly-pxc-0                                 3/3     Running   0          95s
testreadonly-pxc-1                                 3/3     Running   0          65s
testreadonly-pxc-2                                 3/3     Running   0          35s
[katajistok@dbaasjump002 mysql_production_configuration]$ kubectl get pxc
NAME           ENDPOINT                                        STATUS   PXC   PROXYSQL   HAPROXY   AGE
testreadonly   testreadonly-haproxy.dbaas-mysql-testreadonly   ready    3                3         100s
[katajistok@dbaasjump002 mysql_production_configuration]$ kubectl exec testreadonly-pxc-0 -c pxc -- mysql -uroot -proot_password -e'SHOW VARIABLES LIKE "read_only"';
mysql: [Warning] Using a password on the command line interface can be insecure.
Variable_name   Value
read_only       OFF
[katajistok@dbaasjump002 mysql_production_configuration]$ kubectl exec testreadonly-pxc-0 -c pxc -- mysql -uroot -proot_password -e'SHOW VARIABLES LIKE "super_read_only"';
mysql: [Warning] Using a password on the command line interface can be insecure.
Variable_name   Value
super_read_only OFF