MySQL GTID replication: is it possible to make DDL changes to a slave and then catch it up to a master?mysql...

How exactly does an Ethernet collision happen in the cable, since nodes use different circuits for Tx and Rx?

In the late 1940’s to early 1950’s what technology was available that could melt ice?

Why is a very small peak with larger m/z not considered to be the molecular ion?

Can we track matter through time by looking at different depths in space?

Source permutation

From an axiomatic set theoric approach why can we take uncountable unions?

What will happen if my luggage gets delayed?

Dynamic Linkage of LocatorPane and InputField

Does an unused member variable take up memory?

(Codewars) Linked Lists - Remove Duplicates

Gaining more land

What materials can be used to make a humanoid skin warm?

Confusion about Complex Continued Fraction

Is a piano played in the same way as a harmonium?

Proving a statement about real numbers

How to write a chaotic neutral protagonist and prevent my readers from thinking they are evil?

Doesn't allowing a user mode program to access kernel space memory and execute the IN and OUT instructions defeat the purpose of having CPU modes?

Professor forcing me to attend a conference, I can't afford even with 50% funding

What do *foreign films* mean for an American?

Is it possible to find 2014 distinct positive integers whose sum is divisible by each of them?

Are small insurances worth it?

How do electrons receive energy when a body is heated?

Specifying a starting column with colortbl package and xcolor

Virginia employer terminated employee and wants signing bonus returned

MySQL GTID replication: is it possible to make DDL changes to a slave and then catch it up to a master?

mysql 5.6 GTID replication 'Got fatal error 1236 from master when reading data from binary log'Setup [master] - [master-slave] - [slave] replicationCopying MySQL Raw Data Files for Master-Slave Replication and GTIDsMySQL Replication: MASTER_LOG_POS never changesUpgrading Master-Slave MySQLchanges on multimaster replication and activate GTIDMySQL master slave replication on a per database basisMySQL replication with DRBD - Can we use Old master as slave after failover?Upgrading from single server to new GTID-replicated master and slave MySQL 5.7Upgrade MySQL Master Slave Replication Cluster from 5.7.22 to 5.7.24

Here's what I would like to do:
1. hang a slave off my production master.
2. stop replication
3. adjust some data types and create some indexes on the slave (no data mods)
4. restart replication
5. once the slave has caught up, lock the db & swap slave & master

-the goal, obv. is to have no significant downtime while making some changes that will take time (200G table getting modified)
-it should be possible, because the DDL changes I'm making have to do with shrinking data types & adding constraints - nothing that changes the actual values of data.
-since GTID replication is row-based, I don't know if replication will choke trying to insert, for example, 11 digit ids into a 5 digit id column, even if the values are in range.

asked Apr 28 '15 at 1:37

Eljuan

839

bumped to the homepage by Community♦ 1 min 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 |

asked Apr 28 '15 at 1:37

Eljuan

839

bumped to the homepage by Community♦ 1 min 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 |

asked Apr 28 '15 at 1:37

Eljuan

839

mysql replication

asked Apr 28 '15 at 1:37

Eljuan

839

asked Apr 28 '15 at 1:37

Eljuan

839

asked Apr 28 '15 at 1:37

Eljuan

839

asked Apr 28 '15 at 1:37

Eljuan

839

asked Apr 28 '15 at 1:37

Eljuan

839

bumped to the homepage by Community♦ 1 min ago

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

bumped to the homepage by Community♦ 1 min 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 |

1 Answer
1

active

oldest

votes

You have described a common way to make DDL changes. Yes, it should work. But... please describe the datatype change further.

If you are ALTERing a COLUMN that is current INT SIGNED to be a SMALLINT SIGNED, and if none of the values are bigger than 32767, there should be no problem. If you are changing from SIGNED to UNSIGNED, and none of the numbers are negative, then no problem. Etc. In general, if the old value will fit in the new datatype, then it is OK. If it won't fit, then something will be lost. 1 million into a SMALLINT UNSIGNED will probably become 65535.

As for the application, how does mysql know it is getting an 11-digit id? It sees a number of some number of digits and shoves it into the target size.

Do SHOW WARNINGS; after the DDL.

answered Apr 28 '15 at 2:22

Rick James

43.3k22259

Thank you, @rick; That's what I was wondering - specifically, if I have a table thus: color( id int(11) primary key not null autoincrement, color_name varchar(32)) in db1. Then I make db2 from a db1 backup, and make it a slave of db1 now I add (1, 'red') and (2, 'green') to db1, which get replicated into db2 then I stop replication, and in db2 alter table color so that id becomes tinyint, and then insert (3, 'blue') to db1.color, and turn replication back on now db2 would replicate the new row from db1 despite the fact that the id attribute takes less space.

– Eljuan
Apr 28 '15 at 5:19

If you are using binlog_format = STATEMENT, I am sure it will work. What gets replicated is the statement INSERT ... (1, 'red') or INSERT ... ('red'). For ROW format, I am less certain. Give this a try: create another table on both machines; INT on Master, TINYINT on slave. Then insert something. If it hangs, use sql_slave_skip_counter to un-stick replication.

– Rick James
Apr 28 '15 at 16:07

Since I'm working on a fabric setup, I need row-based replication, and that's why I was worried about how binary the internal representation of a row-based transaction is, but apparently it works fine. I have done just that - reducing the data type on the target server while leaving it unmolested on the source, and it works on inserts without problem. Once I have a little free time I'll see how it breaks when the target server range is exceeded by the source update...

– Eljuan
Apr 29 '15 at 18:04

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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f99003%2fmysql-gtid-replication-is-it-possible-to-make-ddl-changes-to-a-slave-and-then-c%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

You have described a common way to make DDL changes. Yes, it should work. But... please describe the datatype change further.

As for the application, how does mysql know it is getting an 11-digit id? It sees a number of some number of digits and shoves it into the target size.

Do SHOW WARNINGS; after the DDL.

answered Apr 28 '15 at 2:22

Rick James

43.3k22259

Thank you, @rick; That's what I was wondering - specifically, if I have a table thus: color( id int(11) primary key not null autoincrement, color_name varchar(32)) in db1. Then I make db2 from a db1 backup, and make it a slave of db1 now I add (1, 'red') and (2, 'green') to db1, which get replicated into db2 then I stop replication, and in db2 alter table color so that id becomes tinyint, and then insert (3, 'blue') to db1.color, and turn replication back on now db2 would replicate the new row from db1 despite the fact that the id attribute takes less space.

– Eljuan
Apr 28 '15 at 5:19

If you are using binlog_format = STATEMENT, I am sure it will work. What gets replicated is the statement INSERT ... (1, 'red') or INSERT ... ('red'). For ROW format, I am less certain. Give this a try: create another table on both machines; INT on Master, TINYINT on slave. Then insert something. If it hangs, use sql_slave_skip_counter to un-stick replication.

– Rick James
Apr 28 '15 at 16:07

Since I'm working on a fabric setup, I need row-based replication, and that's why I was worried about how binary the internal representation of a row-based transaction is, but apparently it works fine. I have done just that - reducing the data type on the target server while leaving it unmolested on the source, and it works on inserts without problem. Once I have a little free time I'll see how it breaks when the target server range is exceeded by the source update...

– Eljuan
Apr 29 '15 at 18:04

add a comment |

You have described a common way to make DDL changes. Yes, it should work. But... please describe the datatype change further.

As for the application, how does mysql know it is getting an 11-digit id? It sees a number of some number of digits and shoves it into the target size.

Do SHOW WARNINGS; after the DDL.

answered Apr 28 '15 at 2:22

Rick James

43.3k22259

Thank you, @rick; That's what I was wondering - specifically, if I have a table thus: color( id int(11) primary key not null autoincrement, color_name varchar(32)) in db1. Then I make db2 from a db1 backup, and make it a slave of db1 now I add (1, 'red') and (2, 'green') to db1, which get replicated into db2 then I stop replication, and in db2 alter table color so that id becomes tinyint, and then insert (3, 'blue') to db1.color, and turn replication back on now db2 would replicate the new row from db1 despite the fact that the id attribute takes less space.

– Eljuan
Apr 28 '15 at 5:19

If you are using binlog_format = STATEMENT, I am sure it will work. What gets replicated is the statement INSERT ... (1, 'red') or INSERT ... ('red'). For ROW format, I am less certain. Give this a try: create another table on both machines; INT on Master, TINYINT on slave. Then insert something. If it hangs, use sql_slave_skip_counter to un-stick replication.

– Rick James
Apr 28 '15 at 16:07

Since I'm working on a fabric setup, I need row-based replication, and that's why I was worried about how binary the internal representation of a row-based transaction is, but apparently it works fine. I have done just that - reducing the data type on the target server while leaving it unmolested on the source, and it works on inserts without problem. Once I have a little free time I'll see how it breaks when the target server range is exceeded by the source update...

– Eljuan
Apr 29 '15 at 18:04

add a comment |

You have described a common way to make DDL changes. Yes, it should work. But... please describe the datatype change further.

As for the application, how does mysql know it is getting an 11-digit id? It sees a number of some number of digits and shoves it into the target size.

Do SHOW WARNINGS; after the DDL.

answered Apr 28 '15 at 2:22

Rick James

43.3k22259

You have described a common way to make DDL changes. Yes, it should work. But... please describe the datatype change further.

As for the application, how does mysql know it is getting an 11-digit id? It sees a number of some number of digits and shoves it into the target size.

Do SHOW WARNINGS; after the DDL.

answered Apr 28 '15 at 2:22

Rick James

43.3k22259

answered Apr 28 '15 at 2:22

Rick James

43.3k22259

answered Apr 28 '15 at 2:22

Rick James

43.3k22259

answered Apr 28 '15 at 2:22

Rick James

43.3k22259

Thank you, @rick; That's what I was wondering - specifically, if I have a table thus: color( id int(11) primary key not null autoincrement, color_name varchar(32)) in db1. Then I make db2 from a db1 backup, and make it a slave of db1 now I add (1, 'red') and (2, 'green') to db1, which get replicated into db2 then I stop replication, and in db2 alter table color so that id becomes tinyint, and then insert (3, 'blue') to db1.color, and turn replication back on now db2 would replicate the new row from db1 despite the fact that the id attribute takes less space.

– Eljuan
Apr 28 '15 at 5:19

If you are using binlog_format = STATEMENT, I am sure it will work. What gets replicated is the statement INSERT ... (1, 'red') or INSERT ... ('red'). For ROW format, I am less certain. Give this a try: create another table on both machines; INT on Master, TINYINT on slave. Then insert something. If it hangs, use sql_slave_skip_counter to un-stick replication.

– Rick James
Apr 28 '15 at 16:07

Since I'm working on a fabric setup, I need row-based replication, and that's why I was worried about how binary the internal representation of a row-based transaction is, but apparently it works fine. I have done just that - reducing the data type on the target server while leaving it unmolested on the source, and it works on inserts without problem. Once I have a little free time I'll see how it breaks when the target server range is exceeded by the source update...

– Eljuan
Apr 29 '15 at 18:04

add a comment |

Thank you, @rick; That's what I was wondering - specifically, if I have a table thus: color( id int(11) primary key not null autoincrement, color_name varchar(32)) in db1. Then I make db2 from a db1 backup, and make it a slave of db1 now I add (1, 'red') and (2, 'green') to db1, which get replicated into db2 then I stop replication, and in db2 alter table color so that id becomes tinyint, and then insert (3, 'blue') to db1.color, and turn replication back on now db2 would replicate the new row from db1 despite the fact that the id attribute takes less space.

– Eljuan
Apr 28 '15 at 5:19

If you are using binlog_format = STATEMENT, I am sure it will work. What gets replicated is the statement INSERT ... (1, 'red') or INSERT ... ('red'). For ROW format, I am less certain. Give this a try: create another table on both machines; INT on Master, TINYINT on slave. Then insert something. If it hangs, use sql_slave_skip_counter to un-stick replication.

– Rick James
Apr 28 '15 at 16:07

Since I'm working on a fabric setup, I need row-based replication, and that's why I was worried about how binary the internal representation of a row-based transaction is, but apparently it works fine. I have done just that - reducing the data type on the target server while leaving it unmolested on the source, and it works on inserts without problem. Once I have a little free time I'll see how it breaks when the target server range is exceeded by the source update...

– Eljuan
Apr 29 '15 at 18:04

Thank you, @rick; That's what I was wondering - specifically, if I have a table thus: color( id int(11) primary key not null autoincrement, color_name varchar(32)) in db1. Then I make db2 from a db1 backup, and make it a slave of db1 now I add (1, 'red') and (2, 'green') to db1, which get replicated into db2 then I stop replication, and in db2 alter table color so that id becomes tinyint, and then insert (3, 'blue') to db1.color, and turn replication back on now db2 would replicate the new row from db1 despite the fact that the id attribute takes less space.

– Eljuan
Apr 28 '15 at 5:19

If you are using binlog_format = STATEMENT, I am sure it will work. What gets replicated is the statement INSERT ... (1, 'red') or INSERT ... ('red'). For ROW format, I am less certain. Give this a try: create another table on both machines; INT on Master, TINYINT on slave. Then insert something. If it hangs, use sql_slave_skip_counter to un-stick replication.

– Rick James
Apr 28 '15 at 16:07

Since I'm working on a fabric setup, I need row-based replication, and that's why I was worried about how binary the internal representation of a row-based transaction is, but apparently it works fine. I have done just that - reducing the data type on the target server while leaving it unmolested on the source, and it works on inserts without problem. Once I have a little free time I'll see how it breaks when the target server range is exceeded by the source update...

– Eljuan
Apr 29 '15 at 18:04

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Database Administrators Stack Exchange!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Sfrgttk