Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I've got replication running on Percona Server 5.5 by following this guide and wondered if I should add read-only=1 to my slave's my.cnf to make it read only?
The guide sets up replication for the mysql table so users are replicated but I am primarily using the slave to take mysqldumps, an in an emergency reconfiguring it to be master, so I don't believe we need (or should have) write enabled on it constantly?
When a Slave is read-only, it is not 100% shielded from the world.
According to MySQL Documentation on read-only
This variable is off by default. When it is enabled, the server permits no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. In replication setups, it can be useful to enable read_only on slave servers to ensure that slaves accept updates only from the master server and not from clients.
Thus, anyone with SUPER privilege can read and write at will to such a Slave...
Make sure all non-privileged users do not have the SUPER Privilege.
If you want to revoke all SUPER privileges in one shot, please run this on Master and Slave:
UPDATE mysql.user SET super_priv='N' WHERE user<>'root';
FLUSH PRIVILEGES;
With reference to the Slave, this will reserve SUPER privilege to just root and prevent non-privileged from doing writes they would otherwise be restricted from.
I just learned recently that MySQL 5.7 will introduce super_read_only.
This will stop SUPER users in their tracks because the 5.7 Docs say
If the read_only system variable is enabled, the server permits client updates only from users who have the SUPER privilege. If the super_read_only system variable is also enabled, the server prohibits client updates even from users who have SUPER. See the description of the read_only system variable for a description of read-only mode and information about how read_only and super_read_only interact.
Changes to super_read_only on a master server are not replicated to slave servers. The value can be set on a slave server independent of the setting on the master.
super_read_only was added in MySQL 5.7.8.
Should a MySQL replication slave be set to read only?
Yes, you'd better run slave servers with R/O modes available. Previously privileged users could modify such a slave's data anyways but later then they also got theirs R/O restriction "knob".
Why it's important — it's better fail with write than regret with an improper writes that could effectively render slave unusable due to simple data muck up or things like keys conflict that would break replication (no more actual slave again).
There's also software used for orchestration/load split that would take into consideration R/O status of the servers in pools they have configured for proper requests routing among those servers.
This is safety. Use it.
The problem here, included MySQL 8.0, is that MySQL don't force you to set read_only = ON when you execute the start slave command.
Why is a problem? Because almost all MySQL DBAs use their slaves on RO, to prevent data corruption, and always exists one dba who accidentally run wrongly an SQL that modify data on a slave, or an app that won't change his conf.
If I want write on slaves, because it have several schemas and I need do writes on this schema, so the read_only command must be more intelligent, and let us do a read_only for schema.
That could be very useful on multi-master servers and slaves that do a Replicate_Ignore_DB.
So, for now, you must do the control manually and be very carefull.
Enjoy.
