Re: Replication of entire MySQL instance

[Gordon Burditt]

> I went through an exercise with a friend as we prepped him for an
> interview. We installed MySQL on two Centos machines and configured one
> to be the master and the other the slave. BUT, what I thought was true
> mirroring of an entire MySQL environment running on the master was NOT
> what was running on the slave. The exercise we setup only did
> replication of a single database. I didn't see a way to do EVERYTHING
> from master to slave.
>
> Possible or is that the MySQL cluster thingy?

With the replicate-do-db parameter in /etc/my.cnf (on the slave,
and possibly use it multiple times), I think you can do every
database *EXCEPT* the 'mysql' database, (which gets a bit messy
trying to replicate user permissions, so it refuses to try).
I forget whether you can do "--replicate-do-db=*".

Remember, you also need to set up permissions for each database on
the master for the replication user used by the slave.

[Axel Schwenke]

gordon...@burditt.org (Gordon Burditt) wrote:

>> ... The exercise we setup only did

>> replication of a single database. I didn't see a way to do EVERYTHING
>> from master to slave.
>>
>> Possible or is that the MySQL cluster thingy?
>
> With the replicate-do-db parameter in /etc/my.cnf (on the slave,
> and possibly use it multiple times), I think you can do every
> database

Nope. The default is to replicate *everything*
So if only a single database is replicated, it means somebody
configured this restriction.

Also don't let yourself be fooled by the naming of the various
--binlog-* and --replicate-* options. They don't always mean what
one would guess from the name. RTFM!

http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html
http://dev.mysql.com/doc/refman/5.6/en/replication-rules.html

> *EXCEPT* the 'mysql' database, (which gets a bit messy
> trying to replicate user permissions, so it refuses to try).

Replicating the `mysql` database is an edge case indeed. But it's
not impossible and there are situations, where it is required.

> Remember, you also need to set up permissions for each database on
> the master for the replication user used by the slave.

Nope. Replication requires exactly one user to be created on the
master(!) and that user needs only the "replication slave" privilege.
The slave side replication threads run with SUPER privilege and can
access anything on the slave.


XL