List:General Discussion« Previous MessageNext Message »
From:shawn green Date:March 25 2013 8:51pm
Subject:Re: Possible issues with replication filters
View as plain text  
Hello Dimitre,

On 3/24/2013 5:23 PM, Radoulov, Dimitre wrote:
> Hi all,
> we need to move a few databases from one host to another.
> During the process we need to use replication filters (to replicate
> only the databases to be moved).
> The current MySQL version is 5.5, the new one will be 5.6.
> We're using "mixed" binlog_format.
>
> We're planing to use:
>
> replicate-wild-do-table=db_name1.%
> replicate-wild-do-table=db_name2.%
> ...
>
> The documentation states the following about the replicate-wild-do-table
> option:
>
> ==
> This option applies to tables, views, and triggers. It does not apply to
> stored procedures and functions,
> or events. To filter statements operating on the latter objects, use one
> or more of the |--replicate-*-db| options.
> ==
>
> Does anybody know how exactly this option doesn't apply for stored
> procedures, functions or events?
> Is the creation DDL "skipped" or their execution isn't replicated?
>
> Anyway, I assume that with the following configuration (i.e. by adding
> replicate-do-db),
> I'll be able to replicate all statements related to db_name1 and db_name2
> (excluding possible grant/revoke statements, because the mysql system
> database
> won't be replicated).
>
> Is that correct?
> Is there a better (more robust) way to replicate a subset of databases?
>
> replicate-wild-do-table=db_name1.%
> replicate-wild-do-table=db_name2.%
> ...
> replicate-do-db=db_name1
> replicate-do-db=db_name2
> ...
>
>
> Thanks
> Dimitre
>

Stored procedures and Functions do not exist at the table level. They 
only exist at the global (system) level. Therefore there is no way to 
filter statements like CREATE PROCEDURE or DROP FUNCTION based on 
table-level substring matches.

The key to notice is 'operating on' in the text you quoted. If you limit 
execution of those DDL statements to just a few databases, then any user 
with enough privileges that start the DDL command from the context of 
the permitted database will be able to affect those objects on the slave 
via replication. If you change one of them on the master and you do it 
from the context of a database that is not on the 'do list', then that 
change will not be applied to the slave via replication.

Based on your example, a DBA starting in the db_name1 database or 
db_name2 database would be able to affect a PROCEDURE or FUNCTION on the 
master and through replication, the same command would execute on the 
slave.

Controlling this behavior is one of the uses of the 'principle of least 
privileges'. In short, it means you give each user just enough rights to 
do what it is they are supposed to do. For example, you want very few 
users to have the 'super' privilege or the 'with create option' option 
on their accounts. In my position I see many servers operating where 
every user (including applications) are operating with root privileges. 
  This is as bad for databases as it is for operating systems.

Yours,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Possible issues with replication filtersDimitre Radoulov24 Mar
  • Re: Possible issues with replication filtersshawn green25 Mar
    • Re: Possible issues with replication filtersDimitre Radoulov26 Mar