I have a master-slave setup of MySQL. If I make any changes in the slave database...
- Will it mess up the sync in any way ?
- Will the changes get overwritten from the master during the next replication event ?
- Can I make above (2) option happen ?
migrated from stackoverflow.com Jun 13 '13 at 6:01
This question came from our site for professional and enthusiast programmers.
Answers to your questions are as below:
1) Will it mess up the sync in any way
Answer: Yes it will mess up your replication process as slave reads from relay log and updates on Server. For example: If a row has been added to slave and which is not there in Master then "INSERT INTO ..." query will fail which can cause replication process being stopped.
2) Will the changes get overwritten from the master during the next replication event, Can I make above (2) option happen ?
Answer: NO. but there is a alternative you can use pt-table-sync tool
This tool changes data, so for maximum safety, you should back up your data before using it. When synchronizing a server that is a replication slave with the --replicate or --sync-to-master methods, it always makes the changes on the replication master, never the replication slave directly. This is in general the only safe way to bring a replica back in sync with its master;
Note: It is a good idea to set slaves as read_only This variable may help prevent some unsafe writes from occurring on slaves, but only if users are not needlessly granted the SUPER privilege.
Please find the answers inlined below.
1. Will it mess up the sync in any way -
On a high availability architectural view "YES". As long you have Master up and steady you might not end up problems.
2. Will the changes get overwritten from the master during the next replication event
The same data and structure you can see in slave also.
Now On slave do an update as
At this time Master is trying to update like this.
Now the sync is broken on Slave. Your SQL_SLAVE_THREAD stops.
Or anyother conflicting statements tries to append it will get broken, above is a better example for that.
But if you get updates like below on Master, for sure your Slave data is consistent with Master.
For this you need to be so sure about your application architecture, better to double check with application team. Probably you may check their code also.
3. Can I make above (2) option happen ?
Yes, you can overcome this, provided you should be meeting below points.
Unless you're using MySQL Cluster, replication is strictly a one-way operation. You should not be making changes on the slave copy.
As to what will happen if you do, it's hard to say, I've never been foolish enough to try :-)
I suspect it will do whatever software does when you break the contract - whatever it darn well pleases.
My advice is to not even think about the merest possibility of the thought of that action crossing your mind (paraphrased from Douglas Adams HHGTTG).
First I totally agree that you should avoid doing so unless under some extreme situations, such as the master server crashes and each slaves has different status to catch up before promoting a slave to be new master.
If you have to do so, I think it is important to understand the mechanism of mysql replication (or "sync") first.
On the master server side, whenever there is a change happens, it only send the corresponding binary log to the slaves, which contains sql queries in binary format.
On the slave server side, it uses an IO thread and keeps waiting for the master's event. When a new event happens, the slave save the binary log into its own relay log, and another sql thread start executing the content in the relay log. After all the contents are executed in current relay log, it would be deleted automatically by default (unless you set relay_log_purge=0 manually).
So, to make it simple, the process of replication or sync is just 2 steps: first transfer the same sql queries just happened on master to its slaves, second each slave execute the sql queries received.
Now I think it is quite straightforward what would happen in your case: