I use master slave a lot for speeding up reads at remote offices at the end of slow (512 kbit) connections. My implementation and experience is as follows:
Applications are written to read from the slave and write to the master. Reads inside a transaction say for a last_insert_id() need to be done from the master.
In the event of an outage (say the broadband link goes down) reads continue from the slave transparently, but writes are not possible for that remote location. Other writes continue from other locations. The slaves keep updating as normal.
When the link is restored, the slave reconnects and downloads any updates and synchronises itself, usually transparently.
This has been pretty successful for me where I have lots of people reading and only a few updating, most of whome are at head office.
You can have multiple slaves updating from the master and, if I remember correctly, a slave can also update from another slave so you can have "layers" of slaves.
The following is a useful read.
http://dev.mysql.com/doc/refman/5.5/en/replication-solutions-switch.html
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.
Best Answer
You can safely write to a schema on the slave if that schema does not exist on the master or if you correctly use replication filters to prevent its replication to the slave.
"Correctly" is the important word in that sentence. Refer to this page:
https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html
Check the --replicate-do-db and other filter settings, and note that the behavior changes based on whether binlog events are formatted as rows or as statements. When statement-based replication is being used, replication filters only apply to the database in use as the current database. When row-based replication is used, the filters are applied regardless of current database.
Be sure you get this right, otherwise, you could have a situation like this:
Then you do this:
Guess what? tablex just got truncated on both master and slave. However, if you had this instead:
and did the same TRUNCATE on the master, monkey.tablex would not be truncated on the slave.
Note that this all applies according the binlog_format in use at the time the event is logged, and since binlog_format can be changed on a per-session basis, you can get some surprises unless you know exactly what's going on.
Having said all that, if the schema you're writing to does not even exist on the master, then you have nothing to worry about, other than the fact that you can't have read_only or super_read_only set on the slave if you plan on writing to it, so you can't count on it preventing write activity.