If you have three or more Slaves, I have a nice suggestion: Use one of the Slaves as a Volunteer to be cloned.
Here is a Topology
MAS --+--> SLV1
|
+--> SLV2
|
+--> SLV0
Say you want to Spawn SLV3
. You could use SLV0
as a Clone
- STEP01) Install MySQL Binaries on
SLV3
(Same version as all other Slaves)
- STEP02) On SLV0,
STOP SLAVE;
- STEP03) On SLV0,
service mysql stop
- STEP04)
scp -r SLV0:/etc/my.cnf SLV3:/etc/my.cnf
- STEP05)
scp -r SLV0:/var/lib/mysql SLV3:/var/lib/mysql
- STEP06) On SLV0,
service mysql start
- STEP07) On SLV3,
chown -R mysql:mysql /var/lib/mysql
- STEP08) On SLV3, change
server_id
in /etc/my.cnf
to be unique from all other Slaves
- STEP09) On SLV3,
service mysql start
That's it.
This is the same paradigm followed by Percona XtraDB Cluster (PXC). When it comes to PXC, introducing a New Slave in PXC is as simple as adding the MasterIP to my.cnf and starting MySQL. All of the above steps are executed internally by PXC using Quorom Selection to choose which Slave becomes the Donor (a.k.a. Volunteer to be Cloned) as well as one of three methods for copying data (xtrabackup, rsync, mysqldump) This copying method is known as SST
(State Snapshot Transfer).
If all of the Application-Level Data are stored in InnoDB only, you should look into using PXC. If you have a mix of InnoDB/MyISAM or all MyISAM, the above 9 steps are to be scripted by you.
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
Forgive me for sounding somewhat biased, but you can learn about setting up Master/Slave and Master/Master Replication right here in the DBA StackExchange.
Here are some of my posts
May 26, 2012
: Trying to understand SHOW SLAVE STATUS\GJan 05, 2012
: MySQL Replication ErrorDec 23, 2011
: How to make MySQL replication reliable?Dec 19, 2011
: Best way to setup master to multi master replicationFeb 23, 2011
: MySQL Replication - Introduce new Slave to replicationI also have posts concerning
Click here to see all MySQL Replication posts in the DBA StackExchange (not just mine)
There is only one thing I cannot give advice on : MySQL Cluster
While I have answered some questions, there are three MySQL Cluster Experts here
Feel free to post your MySQL Replication questions or simply search through the already posted questions. You may find that what you are looking for is already answered.