MySQL utf8mb4 Migration - Replication ConsiderationsMySQL replication: slave not updatedMySQL Replication Lag...
Why does RAM (any type) access time decrease so slowly?
Which was the first story to feature space elevators?
Does limiting the number of sources help simplify the game for a new DM with new and experienced players?
For US ESTA, should I mention a visa denial from before I got UK citizenship?
Why is Bernie Sanders maximum accepted donation on actblue $5600?
How does a single engine tail wheel landing gear airplane turn when it is on the ground?
Why does finding small effects in large studies indicate publication bias?
Run a command that requires sudo after a time has passed
Why did Tywin never remarry?
How to write pow math?
Contribution form
GPL - Is it required to post source code to the Public - when is a software released?
Buying a "Used" Router
Are there any rules or guidelines about the order of saving throws?
Is layered encryption more secure than long passwords?
How to draw these kind of adjacent ovals with arrows in latex?
Would Refreshing a Sandbox Wipe Out Certain Metadata?
How to know if I am a 'Real Developer'
What happens if you declare more than $10,000 at the US border?
How to explain one side of Super Earth is smoother than the other side?
Taking an academic pseudonym?
Identical projects by students at two different colleges: still plagiarism?
How to run a binary file from crontab?
Is there a technology capable of disabling the whole of Earth's satellitle network?
MySQL utf8mb4 Migration - Replication Considerations
MySQL replication: slave not updatedMySQL Replication Lag Almost 23 HoursMySQL - Migrating from Master-Slave replication to Master-MasterMySQL Replication - Accidentally Dropped Database on SlaveMySQL 5.5 to 5.7 doesn't work, apparently this is a feature?MySQL replication with 5.7 master and 5.5 slaveMySQL master slave replication on a per database basisMaster_ master replication on MariadbRe-sync MySQL Server ReplicationUpgrade MySQL Master Slave Replication Cluster from 5.7.22 to 5.7.24
We're planning to migrate a MySQL database with a mix utf8 and latin1 tables to the new utf8mb4 character set per this article: https://mathiasbynens.be/notes/mysql-utf8mb4.
Our server is 5.7.22 and the live database has several hundred InnoDB tables, is several GB large and is setup in a master/slave replication. I've successfully ran the conversion scripts on a copy of the database on a test server. It takes about 3 hours to complete.
When this is done live, should replication be disabled and the conversion scripts be ran against the master and slave individually or is it OK to run against master with replication active?
We are planning a maintenance window for this update - all updates/queries from our applications will be suspended during this process.
I'm unsure about this because I've been reading about the MySQL 8 Upgrade (a separate, future project for us) and MySQL mentioned performing large operations requiring table rebuilds with replication disabled:
https://dev.mysql.com/doc/refman/8.0/en/replication-upgrade.html. However, this may be relevant only in the context of the version 8 upgrade, which we aren't doing simultaneously. Thanks.
mysql replication mysql-5.7 character-set
asked 3 hours ago
Ryan BRyan B
1083
New contributor
Ryan B is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
We're planning to migrate a MySQL database with a mix utf8 and latin1 tables to the new utf8mb4 character set per this article: https://mathiasbynens.be/notes/mysql-utf8mb4.
Our server is 5.7.22 and the live database has several hundred InnoDB tables, is several GB large and is setup in a master/slave replication. I've successfully ran the conversion scripts on a copy of the database on a test server. It takes about 3 hours to complete.
When this is done live, should replication be disabled and the conversion scripts be ran against the master and slave individually or is it OK to run against master with replication active?
We are planning a maintenance window for this update - all updates/queries from our applications will be suspended during this process.
I'm unsure about this because I've been reading about the MySQL 8 Upgrade (a separate, future project for us) and MySQL mentioned performing large operations requiring table rebuilds with replication disabled:
https://dev.mysql.com/doc/refman/8.0/en/replication-upgrade.html. However, this may be relevant only in the context of the version 8 upgrade, which we aren't doing simultaneously. Thanks.
mysql replication mysql-5.7 character-set
asked 3 hours ago
Ryan BRyan B
1083
New contributor
Ryan B is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
We're planning to migrate a MySQL database with a mix utf8 and latin1 tables to the new utf8mb4 character set per this article: https://mathiasbynens.be/notes/mysql-utf8mb4.
Our server is 5.7.22 and the live database has several hundred InnoDB tables, is several GB large and is setup in a master/slave replication. I've successfully ran the conversion scripts on a copy of the database on a test server. It takes about 3 hours to complete.
When this is done live, should replication be disabled and the conversion scripts be ran against the master and slave individually or is it OK to run against master with replication active?
We are planning a maintenance window for this update - all updates/queries from our applications will be suspended during this process.
I'm unsure about this because I've been reading about the MySQL 8 Upgrade (a separate, future project for us) and MySQL mentioned performing large operations requiring table rebuilds with replication disabled:
https://dev.mysql.com/doc/refman/8.0/en/replication-upgrade.html. However, this may be relevant only in the context of the version 8 upgrade, which we aren't doing simultaneously. Thanks.
mysql replication mysql-5.7 character-set
asked 3 hours ago
Ryan BRyan B
1083
New contributor
Ryan B is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
We're planning to migrate a MySQL database with a mix utf8 and latin1 tables to the new utf8mb4 character set per this article: https://mathiasbynens.be/notes/mysql-utf8mb4.
Our server is 5.7.22 and the live database has several hundred InnoDB tables, is several GB large and is setup in a master/slave replication. I've successfully ran the conversion scripts on a copy of the database on a test server. It takes about 3 hours to complete.
When this is done live, should replication be disabled and the conversion scripts be ran against the master and slave individually or is it OK to run against master with replication active?
We are planning a maintenance window for this update - all updates/queries from our applications will be suspended during this process.
I'm unsure about this because I've been reading about the MySQL 8 Upgrade (a separate, future project for us) and MySQL mentioned performing large operations requiring table rebuilds with replication disabled:
https://dev.mysql.com/doc/refman/8.0/en/replication-upgrade.html. However, this may be relevant only in the context of the version 8 upgrade, which we aren't doing simultaneously. Thanks.
mysql replication mysql-5.7 character-set
mysql replication mysql-5.7 character-set
asked 3 hours ago
Ryan BRyan B
1083
New contributor
Ryan B is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 3 hours ago
Ryan BRyan B
1083
New contributor
Ryan B is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 3 hours ago
Ryan BRyan B
1083
New contributor
Ryan B is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 3 hours ago
Ryan BRyan B
1083
asked 3 hours ago
Ryan BRyan B
1083
1083
New contributor
Ryan B is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Ryan B is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Ryan B is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
First make sure any latin1 columns have not been messed up. Find an accented letter and do SELECT HEX(col) ... -- any accented letter should show as 2 bytes. For example é is latin1 hex E9 and utf8mb4 hex C3A9.
If you see C383C2A9, you have "double encoding" and you need to worry about that now.
As for Master/Slave,...
If it is Master-Master (for ease of failing over), then consider doing them one at a time, thereby avoiding more than a brief downtime (to failover). Be sure to use the SET to disable replication for the session.
Are you using ALTER TABLE ... CONVERT TO CHARACTER SET ...? (That is probably the only valid way to be doing the conversion.)
With Master-Slave and plenty of downtime, that ALTER can simple go through replication.
answered 3 hours ago
Rick JamesRick James
42.9k22258
Thanks, I'll check into the latin1 tables. Yes, we're using ALTER TABLE CONVERT TO CHARACTER SET. Also, our setup is Master/Slave so good to hear about replication.
– Ryan B
1 hour ago
I think thelatin1data converted toutf8mb4correctly. For example, "Vélez" came out correctly on both the upgraded and original databases. UsingHEX(col)the output was56C3A96C657Avs.56E96C657A, withC3A9andE9being the only difference.
– Ryan B
36 mins ago
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
});
}
});
Ryan B is a new contributor. Be nice, and check out our Code of Conduct.
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%2f230161%2fmysql-utf8mb4-migration-replication-considerations%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
First make sure any latin1 columns have not been messed up. Find an accented letter and do SELECT HEX(col) ... -- any accented letter should show as 2 bytes. For example é is latin1 hex E9 and utf8mb4 hex C3A9.
If you see C383C2A9, you have "double encoding" and you need to worry about that now.
As for Master/Slave,...
If it is Master-Master (for ease of failing over), then consider doing them one at a time, thereby avoiding more than a brief downtime (to failover). Be sure to use the SET to disable replication for the session.
Are you using ALTER TABLE ... CONVERT TO CHARACTER SET ...? (That is probably the only valid way to be doing the conversion.)
With Master-Slave and plenty of downtime, that ALTER can simple go through replication.
answered 3 hours ago
Rick JamesRick James
42.9k22258
Thanks, I'll check into the latin1 tables. Yes, we're using ALTER TABLE CONVERT TO CHARACTER SET. Also, our setup is Master/Slave so good to hear about replication.
– Ryan B
1 hour ago
I think thelatin1data converted toutf8mb4correctly. For example, "Vélez" came out correctly on both the upgraded and original databases. UsingHEX(col)the output was56C3A96C657Avs.56E96C657A, withC3A9andE9being the only difference.
– Ryan B
36 mins ago
add a comment |
First make sure any latin1 columns have not been messed up. Find an accented letter and do SELECT HEX(col) ... -- any accented letter should show as 2 bytes. For example é is latin1 hex E9 and utf8mb4 hex C3A9.
If you see C383C2A9, you have "double encoding" and you need to worry about that now.
As for Master/Slave,...
If it is Master-Master (for ease of failing over), then consider doing them one at a time, thereby avoiding more than a brief downtime (to failover). Be sure to use the SET to disable replication for the session.
Are you using ALTER TABLE ... CONVERT TO CHARACTER SET ...? (That is probably the only valid way to be doing the conversion.)
With Master-Slave and plenty of downtime, that ALTER can simple go through replication.
answered 3 hours ago
Rick JamesRick James
42.9k22258
Thanks, I'll check into the latin1 tables. Yes, we're using ALTER TABLE CONVERT TO CHARACTER SET. Also, our setup is Master/Slave so good to hear about replication.
– Ryan B
1 hour ago
I think thelatin1data converted toutf8mb4correctly. For example, "Vélez" came out correctly on both the upgraded and original databases. UsingHEX(col)the output was56C3A96C657Avs.56E96C657A, withC3A9andE9being the only difference.
– Ryan B
36 mins ago
add a comment |
First make sure any latin1 columns have not been messed up. Find an accented letter and do SELECT HEX(col) ... -- any accented letter should show as 2 bytes. For example é is latin1 hex E9 and utf8mb4 hex C3A9.
If you see C383C2A9, you have "double encoding" and you need to worry about that now.
As for Master/Slave,...
If it is Master-Master (for ease of failing over), then consider doing them one at a time, thereby avoiding more than a brief downtime (to failover). Be sure to use the SET to disable replication for the session.
Are you using ALTER TABLE ... CONVERT TO CHARACTER SET ...? (That is probably the only valid way to be doing the conversion.)
With Master-Slave and plenty of downtime, that ALTER can simple go through replication.
answered 3 hours ago
Rick JamesRick James
42.9k22258
First make sure any latin1 columns have not been messed up. Find an accented letter and do SELECT HEX(col) ... -- any accented letter should show as 2 bytes. For example é is latin1 hex E9 and utf8mb4 hex C3A9.
If you see C383C2A9, you have "double encoding" and you need to worry about that now.
As for Master/Slave,...
If it is Master-Master (for ease of failing over), then consider doing them one at a time, thereby avoiding more than a brief downtime (to failover). Be sure to use the SET to disable replication for the session.
Are you using ALTER TABLE ... CONVERT TO CHARACTER SET ...? (That is probably the only valid way to be doing the conversion.)
With Master-Slave and plenty of downtime, that ALTER can simple go through replication.
answered 3 hours ago
Rick JamesRick James
42.9k22258
answered 3 hours ago
Rick JamesRick James
42.9k22258
answered 3 hours ago
Rick JamesRick James
42.9k22258
answered 3 hours ago
Rick JamesRick James
42.9k22258
42.9k22258
Thanks, I'll check into the latin1 tables. Yes, we're using ALTER TABLE CONVERT TO CHARACTER SET. Also, our setup is Master/Slave so good to hear about replication.
– Ryan B
1 hour ago
I think thelatin1data converted toutf8mb4correctly. For example, "Vélez" came out correctly on both the upgraded and original databases. UsingHEX(col)the output was56C3A96C657Avs.56E96C657A, withC3A9andE9being the only difference.
– Ryan B
36 mins ago
add a comment |
Thanks, I'll check into the latin1 tables. Yes, we're using ALTER TABLE CONVERT TO CHARACTER SET. Also, our setup is Master/Slave so good to hear about replication.
– Ryan B
1 hour ago
I think thelatin1data converted toutf8mb4correctly. For example, "Vélez" came out correctly on both the upgraded and original databases. UsingHEX(col)the output was56C3A96C657Avs.56E96C657A, withC3A9andE9being the only difference.
– Ryan B
36 mins ago
Thanks, I'll check into the latin1 tables. Yes, we're using ALTER TABLE CONVERT TO CHARACTER SET. Also, our setup is Master/Slave so good to hear about replication.
– Ryan B
1 hour ago
Thanks, I'll check into the latin1 tables. Yes, we're using ALTER TABLE CONVERT TO CHARACTER SET. Also, our setup is Master/Slave so good to hear about replication.
– Ryan B
1 hour ago
I think the
latin1 data converted to utf8mb4 correctly. For example, "Vélez" came out correctly on both the upgraded and original databases. Using HEX(col) the output was 56C3A96C657A vs. 56E96C657A, with C3A9 and E9 being the only difference.– Ryan B
36 mins ago
I think the
latin1 data converted to utf8mb4 correctly. For example, "Vélez" came out correctly on both the upgraded and original databases. Using HEX(col) the output was 56C3A96C657A vs. 56E96C657A, with C3A9 and E9 being the only difference.– Ryan B
36 mins ago
add a comment |
Ryan B is a new contributor. Be nice, and check out our Code of Conduct.
Ryan B is a new contributor. Be nice, and check out our Code of Conduct.
Ryan B is a new contributor. Be nice, and check out our Code of Conduct.
Ryan B is a new contributor. Be nice, and check out our Code of Conduct.
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%2f230161%2fmysql-utf8mb4-migration-replication-considerations%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
