Are you using InnoDB? Xtrabackup will allow you to do "hot" or "online" copies of your running master.
Propagate the backup made via xtrabackup to the slave. There is no need to disrupt the relay (blackhole) server, just add the extra tables to be replicated to the relay server config before you "START SLAVE" on your slave.
Using replicate-wild-do-table
=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:
The replicate-*
options are restrictive by their presence. The *-do-*
options seem to be telling the server what to "do," but in fact they are telling the server what to only do.
The complete absence of any replicate-*
configuration variables means "replicate everything."
In the simplest case, when there are no --replicate-*
options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html
That, I think, is the point you are needing. It holds true for all MySQL 5.x.
Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ...
, and then START SLAVE
, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.
If you started out with identical data sets, everything should behave exactly as you would expect it to.
What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions. Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."
When you use the --replicate-*
options, the document cited above also offers this tip:
it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options
You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.
Best Answer
You can't.
Triggers do not fire on the slave in row-based replication, even if they are defined, because the rows in the other tables affected by the triggers on the master are replicated as row events, and if the triggers were allowed to fire on the slave, that would not work.
If you have entire tables that need to be eliminated on the slave, you can alter those tables to use the
BLACKHOLE
storage engine, which happily accepts the insert queries from replication and then discards the data, returning no data when youSELECT
from them... though this requires some attention to detail down the road since anALTER TABLE
on the master that specifies a storage engine will alter the table on the slave as well.If you are in a position to rearrange column order on tables, MySQL supports the slave having fewer columns than the master in row-based mode, as long as the columns they do have are all identical by definition and ordinal position in the table, as I discussed here. If moving the sensitive columns to the right side of the table is practical, you can then
ALTER TABLE ... DROP COLUMN ...
on the slave.But this also requires attention down the road.
Probably the simplest solution is to use permissions on views that you essentially create for the purpose of enforcing the access rights to the underlying tables. Views, by default, are executed with the permissions of the user who defined them, not the user who invokes them... so a view can allow a user to access columns in a table that aren't otherwise accessible to that user, if the table and columns are accessible to the
DEFINER
user.If you create a view with
ALGORITHM=MERGE
then queries written against that view will be handled by the server by rewriting the query internally based on the view definition, so there's no significant overhead. As long as you are simply selecting all or some of the columns from each table, and not using functions in the views that are incompatible withMERGE
then this seems like the optimum solution, since the analytics user would have no access at all to the underlying tables, and not necessarily even be aware of their presence on the server if the permissions are set correctly. Technically speaking, as long as there are no conflicts in naming, you could even create these views directly on the slave, since MySQL replication has no problem with spurious structures on the slave as long as the replication SQL thread never has any reason to notice them.If none of these are suitable alternatives, is one more alternative approach that can be used if part of the problem is that you can't even allow the sensitive data to be sent to or stored on the other server, because of its physical location or other security concerns... but it requires a third MySQL server in the middle (between existing master and slave) and is somewhat fragile because every
ALTER TABLE
operation occurring on the master will cause the intermediate machine's replication SQL thread to pause until you manually reconfigure it to understand the new table definition. On a stable schema, it works pretty much flawlessly (I've had one in production for over a year) but it's still a delicate setup, so I'll spare you the details on this option for the moment.