I'm just going through the documentation on replication and i'm confused about one thing.
The documentation says the slave can use any username as long as REPLICATION SLAVE is granted, and that is all that's needed.
The documentation also says that:
"Tell the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database."
Am i reading the documentation correctly when i say that as soon as a user has REPLICATION SLAVE any slave using that user will replicate all databases on the master server ?
I am in a situation whereby i want to replicate a specific database to a slave server on someone else's PC but i don't want that person to be able to replicate any other DB's i got on the same master server.
Does the user with REPLICATION SLAVE also need access to specific DB's in order for the replication to work ? As that would allow me to restrict replication to specific users and specific databases.
Ok, from reading the documentation i understand that if a slave can replicate a master it can replicate any database on the master providing that database is in the bin-log. ( since REPLICATION SLAVE is a global setting )
So it appears there is no way to say slave A can only replicate database A and slave B can only replicate Database B. Both slaves can replicate both databases from the same master.
on the master you replicate all the db's.
and on each slave you set --replicate-do-db=db_name
If you need cross-database updates to work, make sure that you have MySQL 3.23.28 or later, and use --replicate-wild-do-table=db_name.% instead
yes, i got that but,
if the slave is controlled by someone else then there really is nothing to stop that person from replicating all databases right ?
Assuming that's a yes, at least that's how i understand the documentation, how plausable would this set-up be:
A master database, (controlled by me on a server) replicating all its DB's.
A master-assistent database, (also controlled by me, under a different mysql process. Would probably achieve this by using 2 different VPS accounts )
replicating only one specific database from the master.
Any number of slaves, controlled by other people, servers or PC's, using the master-assistant for replication. As the master-assistent only has one DB replicated the slaves can only replicate that single one.
This way i can control who gets access to what.
Sounds like a reasonable solution, is it worth trying it out ?
Or did i miss something somewhere ?