3

I have a replication in place with a Master server and a slave server.

From time to time I connect to the slave server mostly to get info or generate statistics on my user's usage.

But I'm afraid that at some point I will run a write command (INSERT/UPDATE/DELETE) to that slave, thinking I'm on the master server.

Is there a way I can tell MariaDB: "Don't accept the INSERT/UPDATE/DELETE commands" in the slave server?

Thank you for your help!

Improve this question

1 Answer 1

Reset to default
5

When I have managed replicas, I always set this on the replica:

SET GLOBAL read_only=ON;

Of course the replication thread gets to ignore this. But it ensures that all user requests (even superuser requests) cannot make out-of-band changes.

A user with SUPER privilege can still write changes on the replica even if read_only is enabled. Don't grant SUPER privilege to users.

On MySQL, you can solve this with:

SET GLOBAL read_only=ON, super_read_only=ON

But a user with SUPER privilege could get around that because they can change the setting.

The larger problem is if you can't trust the users to whom you grant SUPER privilege.

Improve this answer
6
  • Thanks! That seems to be what I'm looking for. Does this command sustain after a logout ? a restart ?
    – Cyril N.
    Commented Mar 2, 2023 at 14:08
  • SET GLOBAL only lasts until the next restart of MySQL Server. Read dev.mysql.com/doc/refman/8.0/en/set-variable.html for details. Make sure you are reading the documentation for the version you are using (SELECT VERSION();).
    – Bill Karwin
    Commented Mar 2, 2023 at 15:13
  • Thank you for the link. When running your command, I get the following "unknown system variable 'super_read_only'". (version: 10.5.18-MariaDB)
    – Cyril N.
    Commented Mar 2, 2023 at 15:52
  • Remove the reference to super_read_only, and in the future please be clear that you're using MariaDB, not MySQL. These are different products.
    – Bill Karwin
    Commented Mar 2, 2023 at 15:59
  • 1
    mariadb.com/kb/en/read-only-replicas: "When the variable read_only is set to 1, no updates are permitted except from users with the SUPER privilege"
    – Bill Karwin
    Commented Mar 3, 2023 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.