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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
