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.