Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have some MySQL database servers set up with row-based replication, for performance. The software writes to the master, and reads from either the master or the slave. Everything's working great, for the most part.

It's my understanding that MySQL will allow writes to the slave, even though it knows it's a MySQL slave. Ideally, I'd kind of like to close this, so even if somebody writes some bad code that gets a read-connection and does an UPDATE, it will throw an error rather than put data on the slave.

Is there a way to do this in MySQL? Obviously we'd like to make this impossible from our software, as well, but like a firewall on our servers, I'd like to be as defensive as possible.

Thanks!

share|improve this question

4 Answers 4

up vote 3 down vote accepted

Enable the read-only option in my.cnf. It can also be specified as a flag on the command line using --read-only with mysqld.

share|improve this answer
2  
Note that this will not work for superusers (ie: root user in MySQL) as it doesn't obey read-only. –  vmfarms Aug 30 '10 at 21:25

As an alternative to setting read_only=1 (e.g. when there are other scratchpad/reporting/development databases on the slave instance), I sometimes strip all privileges other than SELECT from all users to the DB that I am replicating.

That is, after running the GRANT command on the master, I run the REVOKE command on the slave.

share|improve this answer

As the first post somewhat suggests, you do it with permissions. Read-only option does not work for super users as an FYI and its also not really a workable solution for a slave where you want to prevent writes. You need to prevent writes with user/database/table permissions. For one, the replication user still must be able to write to the slave to keep it in sync with the master. Better way to control writes is you need to revoke options that allow writes (ie- inserts, creates, etc.) for the user in question that should be doing reads only on the slave.

share|improve this answer

Only give replication related rights to the users on the slave. You still have the issue of root user rights, but you can remove remote root access to the DB server.

share|improve this answer

Your Answer

 
discard

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