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.
Your question already has the key to your answer. It does depend on row based or statement based replication.
STATEMENT-BASED REPLICATION
If you run DELETE FROM tblname WHERE blahblahblah;
and the rows matching blahblahblah
do not exist, no big deal. Replication will just carry on. It will just take you a lot longer to realize your data draft on the Slave (or in your case, either Master) if there were slight differences at all.
ROW-BASED REPLICATION
If you run DELETE FROM tblname WHERE blahblahblah;
and the rows matching blahblahblah
do not exist, that can break replication because the exact row info is embedded in the relay logs on slave. That row is expected to exist for the delete to happen. There is an error code for it:
sh-4.1# perror 1032
MySQL error code 1032 (ER_KEY_NOT_FOUND): Can't find record in '%-.192s'
You will likely see error log messages like these:
2014-02-27 22:03:00 4070 [ERROR] Slave SQL: Could not execute Update_rows event on table mydb.mytable; Can't find record in 'mytable', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000166, end_log_pos 93114177, Error_code: 1032
2014-03-17 10:50:15 11596 [ERROR] Slave SQL: Could not execute Delete_rows event on table mydb.mytable; Can't find record in 'mytable', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000337, end_log_pos 427881, Error_code: 1032
Here are other links that discuss this
For more insights, please read Advantages and Disadvantages of Statement-Based and Row-Based Replication from the MySQL Documentation.
Best Answer
There are multiple reason why you wouldn't want to do multimaster replication in a circular fashion, but most of them can be summarized in one: You will have 3 different single points of failure. Check this article (which is exactly what you asked for, but probably not what you want). Standard replication is very prone to drifts in data, as it is asynchronous, producing -if you are lucky- the replication to stop for all nodes or -if you are unlucky- replication to continue with different data between nodes.
While 5.6 GTID and other features minimized those consistency problems, you still have the 3 single points of failure, as replication is single-master (multi-source replication is available only in MariaDB and MySQL 5.7, negating the need for circular replication).
If you want a setup that is multimaster (write anywhere), I would highly recommend going for a different technology that manages conflicts between nodes. Galera (you may also find it under the names of Percona XtraDB Cluster or MariaDB Cluster) is probably the way to go. It works on WANs, "resolves" conflicts (rollbacks them and retries the transaction) is multithreaded, and can be used to substitute regular replication and/or for clustering. Very recommended if your goal is HA or read scaling. It is free and open source, very widespread (I've helped several banks and hosting companies using it), compatible with standard replication and uses standard InnoDB -not a different engine- for storage.
The biggest cons, of course, are that it is a different technology which may take some time to understand (although probably easier than other clustering technologies), and it has its small quirks. But, in my very own opinion it is worth the time learning about it to make things "work properly".
Can you setup circular replication? Sure, in the article I mentioned above you have the typical recommendations of
log-slave-updates
,auto_increment_increment
andauto_increment_offset
for each node. However, the few people that may be running this have to still either avoid multi-master writes or run it in a very controlled environment, where you cannot execute updates and deletes to the same tables in concurrency. Some people also over-engineer solutions involving GTID and semi-sync replication, etc., but generally not all companies have the dedication and knowledge to put patches over a protocol that is not natively prepared for this.