Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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

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.

Sorry for the lack of hyperlinks, since this is my first post on dba I was unable to post so many links in my question. I think I might have found the answer, but I would like to hear from some more experienced dba's.
–
BroknDodgeJan 11 '13 at 23:00

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.

I posted my.cnf for both servers. I have not yet used any replicate-* options. As I said in my question this section says anything after a USE statement isn't replicated by default. So I am looking for the right options to ensure everything is replicated. If my current setup is as good as it gets then I will mark this question answered and change my procedures for modifying the database. Database level changes in 2 tables are what broke the replication for me.
–
BroknDodgeJan 14 '13 at 15:31

The section you cited is in the docs for --replicate-do-db which is why I assumed you were using that. The docs are stating that if you use that option, and then USE a database not included in the "do" rules, subsequent statements won't replicate, or won't replicate consistently (depending on whether you're using statement/mixed/row logging). If you don't use any replicate-* options, then absolutely everything should replicate including database structure changes (DDL).
–
Michael - sqlbotJan 14 '13 at 15:53

Replication can be trusted to do exactly what you configure it to do... the confusion comes in understanding what you've configured it to do, if you use replicate-* options. If you don't you should be good. One exception is a bug, fixed mid-stream in 5.1 (and possible others) involving TRUNCATE TABLE. Hitting this will cause your slaves to become inconsistent. I'll look for that.
–
Michael - sqlbotJan 14 '13 at 15:55

False alarm, on that... the bug I was thinking of wouldn't be a problem in 5.5.28. It was fixed in 5.1.32. Prior to this, truncate table didn't replicate properly and on inserts after a truncate, you'd see the "Duplicate entry..." replication errors like the one you posted. dev.mysql.com/doc/refman/5.1/en/…
–
Michael - sqlbotJan 14 '13 at 16:01