What is the proper approach to convert complete database from utf8mb3 (utf-8) to utf8mb4 with no downtime?

Question

Since MySQL 8.0 has depreciated utf8mb3 (and will subsequently completely remove utf8mb3 support in future), what is the proper approach to upgrade/convert complete database (in production) from utf8mb3 (utf-8) to utf8mb4 with no (or minimal) downtime?

The problem is not converting, I know there exist multiple scripts for this conversion. My major concern is downtime. Is it possible to achieve this with no downtime?

Possible duplicate of [UTF-8 all the way through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — Madhur Bhaiya, Jul 15 '19 at 10:46
@MadhurBhaiya No it is not a duplicate, I'm not asking how to, I'm asking how to in a production environment without downtime. — Rajat Goel, Jul 15 '19 at 10:48
Not that I've ever tried - Just guessed. Even if I knew exactly how to do that, I would probably hire a specialist for this task. — Paul Spiegel, Jul 17 '19 at 15:01
Replication solution... I don't know about `BINLOG_FORMAT=ROW`, but it sees like `STATEMENT` should not have any trouble. However, if you don't already have a Slave set up, that would incur downtime. — Rick James, Jul 17 '19 at 18:42
Do you have any Slaves now? Have you set up LVM? Is the server already running 8.0? Do you use Triggers now? — Rick James, Jul 17 '19 at 18:43

Rick James · Accepted Answer · 2019-07-17T19:19:39.237

Assuming no Slaves, 8.0, no Triggers, and a few other things, I would recommend

pt-online-schema-change

It will CREATE TABLE with the new schema (utf8mb4), then copy the rows from the existing table (utf8 aka utf8mb3), and use Triggers to keep things in sync. There is a brief lock on the table at the end to finish up and swap tables.

You would do that one table at a time.

Doc: https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

Also search around for it; sometimes abbreviated pt-osc.

With a Slave

Investigate the "failover" options and speed with RDS. If you can control the failover, then this method may be fast and 'simple' and possible. I assume it is really Master-Slave, not Master-Master?

With the Slave, leave it replicating from the Master. (Potential problem: RBR may squawk at utf8 replicating to utf8mb4. This needs investigating.)
Do ALTER TABLE ... CONVERT TO ... for each table on the Slave.
Failover.
Depending on what tools RDS has, it may be better to rebuild the Slave(s) from the new Master instead of doing ALTERs on the new Slave (which is the old Master).

Suggest you spin up some tiny instances with the same Master-Slave topology and version and charsets. Then try the steps. It does not need may rows, but it does need FOREIGN KEYs, Triggers, etc, in case any of them cause trouble. (Note how pt-osc has some issues with FKs.)

We do have a slave, we have RDS Multi-AZ as well. We are on 8.0 with GTID enabled, binlog format row. Not using any trigger as of now — Rajat Goel, Jul 17 '19 at 19:05
Yes, one by one. I added some tentative instructions for doing the conversion via replication and failover. — Rick James, Jul 17 '19 at 19:20
@RajatGoel - And you can generate all the `ALTERs` with a `SELECT ... FROM information_schema.tables ...` — Rick James, Jul 22 '19 at 21:01

score 0 · Answer 2 · answered Jul 15 '19 at 11:11

0

With phpmyadmin, Select the database, then you'll get a view of all table structures, Select all tables,then select Operations at the top menu, you'll find a Collation feature, at the end of the page, select the utf8mb4_unicode_ci,then mark the "Change all tables collations" & "Change all tables columns collations" checkboxes, then click go. The operation takes utmost 7 seconds, and you are done.

answered Jul 15 '19 at 11:11

Abdalla Maro

98
1
6

1

7 seconds? Is this independent of database size, in production we usually have data size in TBs – Rajat Goel Jul 15 '19 at 11:21
I suggest you have the live database in deployment, try making the modifications before performing the changes on production – Abdalla Maro Jul 15 '19 at 11:23
If you are changing the collation, at least go to a newer version: `utf8mb4_unicode_520_ci`. – Rick James Jul 22 '19 at 21:03

What is the proper approach to convert complete database from utf8mb3 (utf-8) to utf8mb4 with no downtime?

2 Answers2