MySQL replication - slave update

Question

I have a master-slave setup of MySQL. If I make any changes in the slave database...

1. Will it mess up the sync in any way ?
2. Will the changes get overwritten from the master during the next replication event ?
3. Can I make above (2) option happen ?

Answer 1

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. 

On Master

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

  • Have another new slave server (S2) from the current slave's (S1) backup. Point both the slaves to one Master(M). Do your stuffs on S2, even if it crashes you are least bothered. (OR)

• 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.

  • Keep slave log_slave_updates enabled on Slave.
  • Now issue 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.
  • After that you can do changes with slave.
  • Once changes on Slave is done, you may track the binary log of Slave and check each dml's corresponding value in Master with current live data and change the values in Slave too.
  • Now you may issue START SLAVE SQL_THREAD; Ofcourse it throws error under Last_Error: and Seconds_Behind_Master: NULL
  • Now do SET GLOBAL sql_slave_skip_counter = 1 and START SLAVE SQL_THREAD; -> Until you get this Seconds_Behind_Master: 0

Answer 2

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.

Answer 3

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).

Answer 4

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:

1. 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.

2. 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.

3. You have got the answer I guess.