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.

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?

share|improve this question

migrated from stackoverflow.com Nov 1 '10 at 8:02

This question came from our site for professional and enthusiast programmers.

1  
belongs to superuser –  Svisstack Oct 30 '10 at 20:02

4 Answers 4

up vote 0 down vote accepted

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 )

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
you know, read-only mode can be set for entire server in /etc/my.cnf. The question was about why isn't it the default behaviour because writing to slave (if it isn't in readonly mode) causes the data desync and replications become not working from this moment –  Andrey Pesoshin Nov 1 '10 at 9:28

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. :)

share|improve this answer

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.