Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
    
+1 for often overlooked reminder to make slaves read-only. –  RolandoMySQLDBA Dec 10 '12 at 23:19

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
    
so it is best practice to make slaves read-only? –  xref Dec 10 '12 at 23:14
3  
Yes it is best practice to do so –  RolandoMySQLDBA Dec 10 '12 at 23:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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