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

[Rick James]

That mysqldump procedure would probably work, but perhaps one fix is needed...

When INSERTing or SELECTing characters, mysql needs to know the encoding at both ends. In the table, the encoding is specified on the column. (latin1 in your case.) The other side is trickier to determine/specify. SET NAMES is often useful, but may not apply here.

The bytes you get out of mysqldump will (I think) be encoded as latin1, assuming there is nothing to say to change it to something else. Then, when you reload the data, you need to be sure to say that the bytes you have (in the dump) are latin1. I'm not talking about the declaration of the tables/columns, I'm talking about what the bytes look like. Properly done, the reload will notice that the bytes are latin1, but the destination column is to be utf8; then it will do the necessary conversion. Possibly --default-character-set=latin1 is needed on the 'mysql' command during the reload.

An alternative to modifying the large output file is to use two steps:
1. mysqldump --no-data -- this would give you the CREATE TABLE statements only, and would be easier to edit.
2. mysqldump --no-create-info -- this would give you just the data.
Then apply both dumps.