Re: Require suggestion for changing character set in Master master replication setup

[Rick James]

Until you do an ALTER, you have latin1 stuff in your tables.

You have to do at least one ALTER TABLE on each table with latin1 columns. Do SHOW CREATE TABLE to see whether there are CHAR/TEXT columns and to see what charset has been assigned.

In some cases (GUID, IP-address, postal codes, etc) the characters in the column are always ascii, so it does not matter what charset is used (ascii being a subset of both latin1 and utf8). If none of the columns in a table need converting, you can avoid the ALTER for that table.

I am not clear on the details of converting the data. And it probably depends on whether utf8 characters are already in the table.

TRY THESE BEFORE MODIFYING YOUR PRODUCTION TABLES!...

If the there are no 8-bit bytes in any column in a table, but you want to allow utf8 in the future, this might be the ticket:
ALTER TABLE foo MODIFY COLUMN col1 ... utf8 ..., MODIFY COLUMN col2 ... utf8 ...;

If the utf8 bytes correctly converted to latin1 on INSERT (should you be so lucky), the about ALTER might work.

If you already have a mess (utf8 character turned into 2 or 3 latin 1 characters), then see the docs about how to do two ALTERs. It recommends something like altering to BLOB or VARBINARY as an intermediate step.