On 2/26/2019 1:57 PM, Jim wrote:
On 2/26/2019 9:44 AM, shawn l.green wrote:
Hello Jim,
On 2/25/2019 7:29 PM, Jim wrote:
On 2/25/2019 5:46 PM, shawn l.green wrote:
Hello Jim,
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:
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave
My question is about what replication-related users and privileges
must
exist on the slave.
So, for example, if an insert on the master that is to be
replicated is
performed by user 'abc' with proper insert permissions on the master,
does that same 'abc' user with same insert permissions need to
exist on
the slave as well?
In other words, what user is performing the replication operation
on the
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
privileges?
Thank you.
Jim
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
or
* Replication (which is very much like an automated, continuous
point-in-time recovery)
===
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
write.
That would make sense based on our discussion, correct?
Thanks again.
Jim
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
the server:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only
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
process).
Yours,
Thanks, Shawn.
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 unwanted writes.
Given how you read:
"If the |read_only|
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only>
system variable is enabled, the server permits client updates only
from users who have the |SUPER|
<https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super>
privilege. If the |super_read_only|
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only>
system variable is also enabled, the server prohibits client updates
even from users who have |SUPER|
<https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super>."
One somewhat gets the impression that in order to enable
super_read_only, one must also enable read_only.
However, based on:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only
I see:
"Setting |super_read_only|
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only>
to |ON| implicitly forces |read_only|
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_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:
super-read-only=ON
Thanks again for the incredibly helpful suggestion.
Jim
Shawn,
We tested your super-read-only suggestion today. It worked perfectly. It
allowed replication while blocking any direct writes to the slave.
Thanks again for the help.
Jim
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
