Take the 2-minute tour ×
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.

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.

share|improve this question
    
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. –  BroknDodge Jan 11 '13 at 23:00

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
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. –  BroknDodge Jan 14 '13 at 15:31
    
After reading the link you provided along with this one and this one, I come to the conclusion that replication can not be trusted for any ALTER TABLE statements. Am I wrong in this thinking? –  BroknDodge Jan 14 '13 at 15:48
    
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 - sqlbot Jan 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 - sqlbot Jan 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 - sqlbot Jan 14 '13 at 16:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.