MySQL Replication Master Slave Architecture – Web app that is based on the SLAVE DB

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.

Can I simply point my office web server to the Slave MySQL server? Will they be able to write to the slave?

Short answer: Yes, but they probably shouldn't.

Depending on how your application works, it is possible (even likely) that writing to the slave will break replication. Even if it doesn't, over time it's likely that your databases will develop split brain - for example, someone could change some data on your slave that remains undetected for a long period of time.

If you are able to maintain two MySQL servers in your office, I would suggest the following:

• Master server #1 on your trailer
• Replicant server #2 in your office, acting as slave to #1
• "Working" server #3 in your office

Server #3 would be the server that your staff connect to. Set up a job - maybe one that runs each night - which takes a backup of server #2 and then restores it over the top of server #3. This way staff can work with fairly recent data without the risk of affecting replication.

You could even make server #3 a slave to server #2 if it looked like that might work, though I suspect this might cause more problems than it solves.

Public cell and wifi networks are unlikely to be as secure as you like - I'm assuming that you will secure the connection to your office via VPN. If not, MySQL can secure communications via SSL and you may want to consider that.