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.
Yes
It's not going to be a problem but of course it depends on the problem itself. Lets use the term - incident. Problem is already a problem by definition. Solve incidents, analyze the cause , prevent the problem!
In case of an error, try to think what caused it. You are right about worrying, as it is a very common cause of issues.
Try to strictly separate the replicated objects from the unique ones in slave. Different DB
Use some constraints and "tips" for the replication process, like:
--replicate-wild-ignore-table=db_name.tbl_name
- include slave status verification in your backup procedures
- redirect all reads to the slave (of course the important reads should be done in master and ONLY they)
- backup the slave only
- include the GTID (you might need it)
Be more AGILE with it. Do it, start testing and you will see how it goes. It's easy and you sound competent enough to do it.
Always monitor your replication. Using GTID and row-based replication may help you a bit with the consistency.
Best Answer
Before you perform any mysqldump to fully restore a Slave, you should consult the output of
SHOW SLAVE STATUS\G
. Let's start with a sampleSHOW SLAVE STATUS\G
:Please notice that there are two sets of replication coordinates from the Master
There is a major difference between them
(Master_Log_File,Read_Master_Log_Pos)
tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs.(Relay_Master_Log_File,Exec_Master_Log_Pos)
tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs THAT IS NEXT TO BE EXECUTED ON THE SLAVE.The timestamps from these two coordinates helps you figure out
Seconds_Behind_Master
.Knowing these things, here is what you can do:
SHOW SLAVE STATUS\G
SHOW SLAVE STATUS\G
(In the sample, that would be (mysql-bin.000254,858190247)STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000254',master_log_pos=858190247;
START SLAVE;
SHOW SLAVE STATUS\G
and checkSeconds_Behind_Master
If the
Seconds_Behind_Master
is a number and eventaully drops to zero, replication is fully reesatablished.After doing all this, if replication breaks because of a corrupt binary log from the master, then you do the last resort:
RESET MASTER;
to erase all binary logs and start with a new oneThis create proper dump for the slave
Give it a Try !!!