• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

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

by admin

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.

please note that this will not modify any existing tables. (The restart can be done later on)

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.

Please note that, in approach 2 and 3, the existing session will be using the default values.

Filed Under: mysql, MySQL Cluster

Some more articles you might also be interested in …

  1. How to Rollback RPM Upgrade of the MySQL Server
  2. MySQL Backup stuck at “Starting to lock all the tables”
  3. Beginners Guide to MySQL Data Types
  4. “expect” script to provide password to mysql_config_editor
  5. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  6. Can MySQL Cluster Run Multiple Nodes On a Single Server
  7. How to Migrate from Oracle to MySQL
  8. How to Restrict MySQL User Creation with Blank Password
  9. How to Install MySQL Cluster on a single server for testing purposes
  10. How to take Logical Backups on a MySQL Replication Slave using shell script

You May Also Like

Primary Sidebar

Recent Posts

  • fprintd-delete Command Examples in Linux
  • fprintd-delete: command not found
  • foreman: command not found
  • foreman Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright