After setting up mysql replications with master/slave db scheme, I noticed that the slave db server is not read-only. Of cause, after that I configured it manually in /etc/my.cnf file.
I just can't understand: isn't it the default behavior that the slaves are always running in the read-only mode or it should be always configured by hand?
No it is not the default behavior, and yes if you want slaves to be read only you need to do so "by hand" (or script).
The reason for this is mostly just because thats how it started and it hasn't been changed since. However there are plenty of ways in which this is the desired behavior, for instance if your slaves have some tables that your master does not (like reporting summary tables). Or, if you want to run an ALTER you can run it on your slave first, failover, then run it on your master so as not to impact the live site.
I think that the read-only mode is only per user basis, not globally for the slave So make sure you are not replicating the users (database 'mysql'), and if necessary create users with only USAGE grant option - that way those users have read-only.
Although many of our slaves are set to read_only, a few of them are not.
In these instances, it is usually because we house other development/scratchpad/reporting databases on these servers. In these cases, users are only granted SELECT permissions to the primary db, but SELECT/UPDATE permissions to the secondary db's.
Moreover, if multi-master replication was being used, having the slaves be read_only by default would be a serious problem. :)
It is quite common to not have the slave in read_only mode..
in fact, if you do not set it yourself, then it never is enabled..
A good question, is why you would actually want to set it...???
Are you trying to protect mistakes? or errors made my admin users??? or the application?
Considering MANY admins are using MySQL as root anyways, where is the protection here??
You cant exactly protect your data as you would imagine ( as everything replicated into the slave anyway )
You do know that any user with super privileges and the replication thread do not abide by the read_only??
A strong structured approach to users and privileges is the correct way to deal with replication issues, not a read_only configuration on the slave
In my honest opinion, it actually does not protect very much...or useful for anything besides stopping a few users not write.. ( which on production systems - should never happen anyways )
As an example, In Mysql-Multi-Master ( read_only is set by the scripts ) This in theory is only to simply decide which server is the "True Master" in the master-master configuration..
All in all.. Forget about read_only ( its really unneccesary, and probably wont protect you from what you imagine it will )
