Re: Replication and user privileges
- Date: Tue, 26 Feb 2019 13:57:11 -0500
- From: Jim <jim@xxxxxxxxxxxxxxxxxx>
- Subject: Re: Replication and user privileges
On 2/26/2019 9:44 AM, shawn l.green wrote:
On 2/25/2019 7:29 PM, Jim wrote:
On 2/25/2019 5:46 PM, shawn l.green wrote:
On 2/25/2019 5:04 PM, Jim wrote:
I have a question about mysql replication. I believe I understand most
of it, but have a question about user privileges.
I understand on the master, the replication user must have the
Repl_slave_priv privilege as described here:
My question is about what replication-related users and privileges
exist on the slave.
So, for example, if an insert on the master that is to be
performed by user 'abc' with proper insert permissions on the master,
does that same 'abc' user with same insert permissions need to
the slave as well?
In other words, what user is performing the replication operation
slave? I don't see any indication of users referenced in the bin logs
that I have examined on the master. Are user and privileges regarding
replicated queries irrelevant on the slave and that is handled all
internally via the replication thread with no regard to user
Your final supposition is correct. All privileges were checked and
verified on the master when the original command was executed. The
Replication system on the slave is going to repeat that change as well
as possible given the state of its copy of the data without regards to
"who originally performed this change" on the upstream master.
We do not store credentials in the Binary Log because they are not
important to either of the purposes of the Binary Log
* point-in-time recovery
* Replication (which is very much like an automated, continuous
That replication account you mentioned, on the master, is required to
give a slave (and you could have several) enough rights to read the
Binary Log and not much else. This allows you to create an account
that can login from a remote location with the "least privileges"
necessary to do its job. This minimizes your data's exposure should
that account become compromised.
Many other accounts could also have the REPL_SLAVE_PRIV privilege and
any of those could be used by a slave to do the same job. However
losing control over one of those more privileged accounts could pose a
higher risk to your data.
Thanks, Shawn. Your response confirms what I had assumed was happening.
So bottom line... what I plan to do is strip the various
insert/update/delete privileges from appropriate db users on my slaves.
I had placed them there originally because I thought they would be
needed for the replicated queries, but not true based on your response.
I only want the various mysql users used by my code to have select privs
on the slaves so that if somehow a slave was mistakenly written to via a
bug in my code, that write would fail and I would receive the error. The
slaves should only be used for selects and should never experience a
That would make sense based on our discussion, correct?
As masters and slaves can exchange "positions" or "roles" (it depends
on how you like to mentally visualize the relationship) within a
replication graph in a failover situation, adding time to re-establish
actual permissions using GRANT commands to reset user accounts to
their old privileges may not be time you want to spend.
A cleaner, simpler solution is to set the --super-read-only flag in
That way, you get the behavior you want (no writes to a read-only
slave) without forcing differences to the content of your privileges
tables within different nodes of your Replication setup. Each node
will remain a transactionally consistent copy of all the others
(within the temporal limits of replication being an asynchronous
super-read-only looks perfect for what I want. I can keep my slaves with
all the potential users needed to take over as master without risking
Given how you read:
"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|
One somewhat gets the impression that in order to enable
super_read_only, one must also enable read_only.
However, based on:
to |ON| implicitly forces |read_only|
to |ON|. "
So that reinforces that one only needs set super_read_only.
Is it appropriate to configure this in my.cnf, eg:
Thanks again for the incredibly helpful suggestion.