Return to Answer

added 37 characters in body

Source Link

edited Jun 13, 2013 at 9:13

1.6k
3
15
36

On a high availability architectural view "YES". As long you have Master up and steady you might not end up problems.

On a high availability architectural view "YES". As long you have Master up and steady you might not end up problems.

update table test set name='Villain' where id=12;

update table test set name='Villain' where id=12;

update table test set id=14 where name='Mannoj';

update table test set id=14 where  name='Mannoj';

update table test set name='Hero' where id=12;

update table test set name='Hero' where id=12;

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

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; InsightSTOP 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;START SLAVE SQL_THREAD; Ofcourse it throws error under Last_Error: and Seconds_Behind_Master: NULLLast_Error: and Seconds_Behind_Master: NULL
- Now do SET GLOBAL sql_slave_skip_counter = 1 and START SLAVE SQL_THREAD;START SLAVE SQL_THREAD; -> Until you get this Seconds_Behind_Master: 0.Seconds_Behind_Master: 0

On a high availability architectural view "YES". As long you have Master up and steady you might not end up problems.

update table test set name='Villain' where id=12;

update table test set id=14 where name='Mannoj';

update table test set name='Hero' where id=12;

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.

On a high availability architectural view "YES". As long you have Master up and steady you might not end up problems.

update table test set name='Villain' where id=12;

update table test set id=14 where  name='Mannoj';

update table test set name='Hero' where id=12;

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

Source Link

created Jun 13, 2013 at 8:29

Mannoj

1.6k
3
15
36

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.