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.
Best Answer
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 hexE9
and utf8mb4 hexC3A9
.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.