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 ?
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 only takes a minute to sign up.Sign up to join this community
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
Depends upon your changes.
create table test (id int, name varchar(20)); insert into test values (12,'Mannoj'); ------------------------- |id | name | ------------------------- |12 | Mannoj | -------------------------
The same data and structure you can see in slave also.
Now On slave do an update as
update table test set name='Villain' where id=12;
At this time Master is trying to update like this.
update table test set id=14 where name='Mannoj';
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.
update table test set name='Hero' where id=12;
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.
WITH NO DOWN TIME ON HA
WITH DOWN TIME ON HA
To do this you should know your application in and out. Suppose your application does DMLs only for latest data like latest few secs only and not old data, then below one helps.
STOP SLAVE SQL_THREAD;Insight -> But IO_SLAVE_THREAD keeps getting its space added on RELAY_LOG than needs to be applied. This relay log will be purged only when they are executed by SQL thread until you have automatic purge enabled on relay logs and Master's binary log. By this you have information from Master site to Slave site of what are the transactions happened.
START SLAVE SQL_THREAD;Ofcourse it throws error under
Last_Error: and Seconds_Behind_Master: NULL
SET GLOBAL sql_slave_skip_counter = 1and
START SLAVE SQL_THREAD;-> Until you get this
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.
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:
It depends on what changes you make. For example if you inserted/updated some primary key value on slave manually and later on the master the same value is set, it will succeed on master but fail on the slave. You can see the error msg using "SHOW SLAVE STATUS" on slave.
It is totally possible if the overwriting event (I mean sql), after your manual change on slave, is issued on master. And also no conflict happens like the example above.
You have got the answer I guess.