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.
mysql replication
asked Apr 28 '15 at 1:37
EljuanEljuan
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 |
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.
mysql replication
asked Apr 28 '15 at 1:37
EljuanEljuan
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 |
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.
mysql replication
asked Apr 28 '15 at 1:37
EljuanEljuan
839
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.
mysql replication
mysql replication
asked Apr 28 '15 at 1:37
EljuanEljuan
839
asked Apr 28 '15 at 1:37
EljuanEljuan
839
asked Apr 28 '15 at 1:37
EljuanEljuan
839
asked Apr 28 '15 at 1:37
EljuanEljuan
839
asked Apr 28 '15 at 1:37
EljuanEljuan
839
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 |
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 JamesRick 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 usingbinlog_format = STATEMENT, I am sure it will work. What gets replicated is the statementINSERT ... (1, 'red')orINSERT ... ('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, usesql_slave_skip_counterto 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
});
}
});
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
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 JamesRick 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 usingbinlog_format = STATEMENT, I am sure it will work. What gets replicated is the statementINSERT ... (1, 'red')orINSERT ... ('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, usesql_slave_skip_counterto 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.
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 JamesRick 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 usingbinlog_format = STATEMENT, I am sure it will work. What gets replicated is the statementINSERT ... (1, 'red')orINSERT ... ('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, usesql_slave_skip_counterto 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.
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 JamesRick James
43.3k22259
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 JamesRick James
43.3k22259
answered Apr 28 '15 at 2:22
Rick JamesRick James
43.3k22259
answered Apr 28 '15 at 2:22
Rick JamesRick James
43.3k22259
answered Apr 28 '15 at 2:22
Rick JamesRick James
43.3k22259
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 usingbinlog_format = STATEMENT, I am sure it will work. What gets replicated is the statementINSERT ... (1, 'red')orINSERT ... ('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, usesql_slave_skip_counterto 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 usingbinlog_format = STATEMENT, I am sure it will work. What gets replicated is the statementINSERT ... (1, 'red')orINSERT ... ('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, usesql_slave_skip_counterto 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
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
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
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 |
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.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
