How to set the default character set in MySQL and how to propagate it in a master-master replication scenario

In this post, we will see how to change the default character set from the default latin1_swedish_c to utf8_general_ci (or any other) and how to propagate charset changes in a master-master replication environment.

1. First determine the current default values, by issuing the MySQL command:

mysql> show variables like '%character_set_%';

This will return something like this:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Meaning if no other setup is done, the MySQL server will be using the default character set, latin1_swedish_ci.

2. In order to change this setting, please include in your MySQL configuration files the below information:

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

And restart your servers.

As a reminder, that Master-Master replication ( also know as a mirror) is not a recommended replication topology, as it’s not possible to assure ACID properties.

How to propagate the change in replication

We could do this using one of the three approaches:

1. Rebooting servers

change both servers configuration file and restart both server one at a time, and if the replication is properly configured the system would stay working

2. Without rebooting the servers.

Set the char set using a global SET using a MySQL client connection and issuing the below commands:

set global character_set_server = utf8;
set global character_set_database = utf8;
set global collation_server = utf8_general_ci;
set global character_set_client =utf8;
set global character_set_connection=utf8;
set global character_set_results=utf8;

3. A mix of the above

Changing the server’s configuration files, and set the global variables, so when, if, the servers are rebooted, they will not revert back to MySQL default values.