Sfrgttk

How are showroom/display vehicles prepared?

NASA's RS-25 Engines shut down time

Word for a person who has no opinion about whether god exists

How to write ı (i without dot) character in pgf-pie

Accountant/ lawyer will not return my call

How can I ensure my trip to the UK will not have to be cancelled because of Brexit?

How did Alan Turing break the enigma code using the hint given by the lady in the bar?

Intuition behind counterexample of Euler's sum of powers conjecture

Can one live in the U.S. and not use a credit card?

finite abelian groups tensor product.

Was Luke Skywalker the leader of the Rebel forces on Hoth?

How is the wildcard * interpreted as a command?

Vocabulary for giving just numbers, not a full answer

Plausibility of Mushroom Buildings

Difference on montgomery curve equation between EFD and RFC7748

What Happens when Passenger Refuses to Fly Boeing 737 Max?

What's the "normal" opposite of flautando?

weren't playing vs didn't play

List elements digit difference sort

Latex does not go to next line

PTIJ: wiping amalek’s memory?

When a wind turbine does not produce enough electricity how does the power company compensate for the loss?

Coax or bifilar choke

Find longest word in a string: are any of these algorithms good?

MySQL replication - independent data update on slave

Rebooting a MySQL Replication SlaveMySQL ignoring index, index cardinality is the same as number of records in the table (?!)Master Slave Replication in MysqlMySQL replication - slave updateMySQL slave replication permission errorTransactional Replication - Replication Order of Related TablesWhy can't I use a cname/alias in the permissions table?mysql replication master - slaveUTF8 Trouble while migrating from MSSQL to MySQL with MySQL Workbenchchanges on multimaster replication and activate GTID

0

I have an unusual situation where I am migrating websites and their corresponding databases from one server to another.

I have a database that is hosted on one server, A, where new records are created, and these are accessed and modified on the second server B (but no new records are created).

I have set up A as master, and B as slave to ensure that B has access to new records created by A, but A does not need access to the amended record data that are changed on B.

I know there are lots of warnings about not changing data on the slave, but it seems to me that this should work OK (until I finish the migration, at which point I will move the record creation facilities to server B and break the slave link).

Any thoughts?

mysql replication

share|improve this question

edited Sep 3 '14 at 11:39

Colin 't Hart

6,58682534

asked Sep 3 '14 at 11:34

Andy StokesAndy Stokes

1

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

0

I have an unusual situation where I am migrating websites and their corresponding databases from one server to another.

I have a database that is hosted on one server, A, where new records are created, and these are accessed and modified on the second server B (but no new records are created).

I have set up A as master, and B as slave to ensure that B has access to new records created by A, but A does not need access to the amended record data that are changed on B.

I know there are lots of warnings about not changing data on the slave, but it seems to me that this should work OK (until I finish the migration, at which point I will move the record creation facilities to server B and break the slave link).

Any thoughts?

mysql replication

share|improve this question

edited Sep 3 '14 at 11:39

Colin 't Hart

6,58682534

asked Sep 3 '14 at 11:34

Andy StokesAndy Stokes

1

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

I have an unusual situation where I am migrating websites and their corresponding databases from one server to another.

I have a database that is hosted on one server, A, where new records are created, and these are accessed and modified on the second server B (but no new records are created).

I have set up A as master, and B as slave to ensure that B has access to new records created by A, but A does not need access to the amended record data that are changed on B.

I know there are lots of warnings about not changing data on the slave, but it seems to me that this should work OK (until I finish the migration, at which point I will move the record creation facilities to server B and break the slave link).

Any thoughts?

mysql replication

share|improve this question

edited Sep 3 '14 at 11:39

Colin 't Hart

6,58682534

asked Sep 3 '14 at 11:34

Andy StokesAndy Stokes

1

share|improve this question

edited Sep 3 '14 at 11:39

Colin 't Hart

6,58682534

asked Sep 3 '14 at 11:34

Andy StokesAndy Stokes

1

share|improve this question

edited Sep 3 '14 at 11:39

Colin 't Hart

6,58682534

asked Sep 3 '14 at 11:34

Andy StokesAndy Stokes

1

edited Sep 3 '14 at 11:39

Colin 't Hart

6,58682534

edited Sep 3 '14 at 11:39

Colin 't Hart

6,58682534

asked Sep 3 '14 at 11:34

Andy StokesAndy Stokes

1

asked Sep 3 '14 at 11:34

Andy StokesAndy Stokes

1

2 Answers
2

active

oldest

votes

0

On this scenario replication should not face any issues.

MASTER (A): INSERT

SLAVE (B): UPDATE,DELETE

The only issue i can think is to change a unique constraint on B and try to insert the same record on A.

share|improve this answer

answered Sep 3 '14 at 12:19

AntoniosAntonios

1,057413

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You have it - Master A is only used to add new records... Slave B is used to access these records and update them - no deletes or changes to the primary key fields are made.
  
  – Andy Stokes
  Sep 4 '14 at 15:35
  
  
  

  
  
  
  

add a comment | 

0

So, if I understood correctly, you want to update records on a slave while the originals are still accessed on the master.

You are completely right in that it is possible, but very easy to get it broken:

• If you have a primary key or unique keys that can change, you may end up with duplicate keys, breaking replication


• Foreign keys, triggers, etc. may end up creating unexpected results


• Modifying values based on other rows (INSERT SELECT, IODKU, REPLACE, INSERT IGNORE, or just reading rows and then updating them will end up with different results)


• Autoincrement keys may end up being different because of the previous case (even if you do not insert on the slave)

Best case scenario, replication will break. Worst case, slave and master will drift in a way that it is not intended. Row-based replication may reduce the drifting, and it should be reliable if you only update the table using the primary key. In practice it is very difficult to preview all possibilities, as databases and SQL is complex, unless you do very contained/controlled writes on both servers.

However, assuming that B is not in production, you can do regular checks and fix replication errors if they occur, making it possible.

share|improve this answer

answered Sep 3 '14 at 13:06

jynusjynus

11.1k11832

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f75577%2fmysql-replication-independent-data-update-on-slave%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

On this scenario replication should not face any issues.

MASTER (A): INSERT

SLAVE (B): UPDATE,DELETE

The only issue i can think is to change a unique constraint on B and try to insert the same record on A.

share|improve this answer

answered Sep 3 '14 at 12:19

AntoniosAntonios

1,057413

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You have it - Master A is only used to add new records... Slave B is used to access these records and update them - no deletes or changes to the primary key fields are made.
  
  – Andy Stokes
  Sep 4 '14 at 15:35
  
  
  

  
  
  
  

add a comment | 

0

On this scenario replication should not face any issues.

MASTER (A): INSERT

SLAVE (B): UPDATE,DELETE

The only issue i can think is to change a unique constraint on B and try to insert the same record on A.

share|improve this answer

answered Sep 3 '14 at 12:19

AntoniosAntonios

1,057413

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You have it - Master A is only used to add new records... Slave B is used to access these records and update them - no deletes or changes to the primary key fields are made.
  
  – Andy Stokes
  Sep 4 '14 at 15:35
  
  
  

  
  
  
  

add a comment | 

On this scenario replication should not face any issues.

MASTER (A): INSERT

SLAVE (B): UPDATE,DELETE

The only issue i can think is to change a unique constraint on B and try to insert the same record on A.

share|improve this answer

answered Sep 3 '14 at 12:19

AntoniosAntonios

1,057413

share|improve this answer

answered Sep 3 '14 at 12:19

AntoniosAntonios

1,057413

answered Sep 3 '14 at 12:19

AntoniosAntonios

1,057413

answered Sep 3 '14 at 12:19

AntoniosAntonios

1,057413

0

So, if I understood correctly, you want to update records on a slave while the originals are still accessed on the master.

You are completely right in that it is possible, but very easy to get it broken:

• If you have a primary key or unique keys that can change, you may end up with duplicate keys, breaking replication


• Foreign keys, triggers, etc. may end up creating unexpected results


• Modifying values based on other rows (INSERT SELECT, IODKU, REPLACE, INSERT IGNORE, or just reading rows and then updating them will end up with different results)


• Autoincrement keys may end up being different because of the previous case (even if you do not insert on the slave)

Best case scenario, replication will break. Worst case, slave and master will drift in a way that it is not intended. Row-based replication may reduce the drifting, and it should be reliable if you only update the table using the primary key. In practice it is very difficult to preview all possibilities, as databases and SQL is complex, unless you do very contained/controlled writes on both servers.

However, assuming that B is not in production, you can do regular checks and fix replication errors if they occur, making it possible.

share|improve this answer

answered Sep 3 '14 at 13:06

jynusjynus

11.1k11832

add a comment | 

0

So, if I understood correctly, you want to update records on a slave while the originals are still accessed on the master.

You are completely right in that it is possible, but very easy to get it broken:

• If you have a primary key or unique keys that can change, you may end up with duplicate keys, breaking replication


• Foreign keys, triggers, etc. may end up creating unexpected results


• Modifying values based on other rows (INSERT SELECT, IODKU, REPLACE, INSERT IGNORE, or just reading rows and then updating them will end up with different results)


• Autoincrement keys may end up being different because of the previous case (even if you do not insert on the slave)

Best case scenario, replication will break. Worst case, slave and master will drift in a way that it is not intended. Row-based replication may reduce the drifting, and it should be reliable if you only update the table using the primary key. In practice it is very difficult to preview all possibilities, as databases and SQL is complex, unless you do very contained/controlled writes on both servers.

However, assuming that B is not in production, you can do regular checks and fix replication errors if they occur, making it possible.

share|improve this answer

answered Sep 3 '14 at 13:06

jynusjynus

11.1k11832

add a comment | 

So, if I understood correctly, you want to update records on a slave while the originals are still accessed on the master.

You are completely right in that it is possible, but very easy to get it broken:

• If you have a primary key or unique keys that can change, you may end up with duplicate keys, breaking replication


• Foreign keys, triggers, etc. may end up creating unexpected results


• Modifying values based on other rows (INSERT SELECT, IODKU, REPLACE, INSERT IGNORE, or just reading rows and then updating them will end up with different results)


• Autoincrement keys may end up being different because of the previous case (even if you do not insert on the slave)

Best case scenario, replication will break. Worst case, slave and master will drift in a way that it is not intended. Row-based replication may reduce the drifting, and it should be reliable if you only update the table using the primary key. In practice it is very difficult to preview all possibilities, as databases and SQL is complex, unless you do very contained/controlled writes on both servers.

However, assuming that B is not in production, you can do regular checks and fix replication errors if they occur, making it possible.

share|improve this answer

answered Sep 3 '14 at 13:06

jynusjynus

11.1k11832

share|improve this answer

answered Sep 3 '14 at 13:06

jynusjynus

11.1k11832

answered Sep 3 '14 at 13:06

jynusjynus

11.1k11832

answered Sep 3 '14 at 13:06

jynusjynus

11.1k11832