MySQL master/slave replication… structure not being replicated

MySQLreplication

Edit: I might have answered my own question while going back thru the links for this post. I would still like to get insight from more experienced DBA's and perhaps make the information easier to find for anyone that might need it in the future.

I have 2 MySQL servers. One sitting right beside me, the other in a remote data center. Both servers are identical. Both are dedicated. Both are running Ubuntu 12.04 LTS, MySQL 5.5.28. These servers power our in house web application, 2 web sites, virtual mail for 9 domains and backend for MS Access. The slave is not replicating structure changes in the master.

I have reviewed:
dev.mysql.com/doc/refman/5.0/en/replication-howto-slavebaseconfig.html
dev.mysql.com/doc/refman/5.0/en/replication-howto-existingdata.html

And tracked down other issues with my configuration thanks to this question: serverfault.com/q/336888/143841.
Where I posted an answer that solved both mine and his issues at that time. However, I am having trouble finding a real solution to ensure that structure queries are replicated right along with data queries.

Here is my configuration

Master:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |  6547573 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

my.cnf on master:

#master/slave replication
server-id=172868
log-bin=mysql-bin
log-error=mysql-bin.err

Slave:

mysql> show slave status\G; | pastebin

my.cnf on slave:

# master/slave configuration
server-id=1357749477

I'm not highly concerned about the error listed in the slave status report. I believe that issue was triggered by changes I made to the structure on the master. I'm trying to configure the slave to apply any structural changes that occur on the Master.

I found a reference to some slave settings in a mysql bug report: bugs.mysql.com/bug.php?id=956

> replicate-do-db=rate
> replicate-ignore-table=rate.votes
> replicate-ignore-table=rate.clicks
> replicate-wild-ignore-table=rate.%cache

But I'm having trouble finding clear documentation on their usage and repercussions…. Well, on this page I found some information that says it won't replicate any statement made after a USE db; statement. My ALTER statement was preceeded by a USE statement. But, I don't have replicate-do-db set. Then further down it states that database-level changes are applied if replicate-wild-do-table=% is set.

So, is "replicate-wild-do-table=%" the correct way to get complete replication? What are the ramifications of using this setting? What is the correct way to configure the slave for complete replication of all databases.

Best Answer

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.

Related Question