1

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.

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
3
  • 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
    Commented Feb 19, 2019 at 19:33
  • 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
    Commented Feb 19, 2019 at 20:55
  • @RyanB - Great!
    – Rick James
    Commented Feb 19, 2019 at 22:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.