How to configure my.cnf for multiple CHARACTER SET of database in one instance

Question

In a instance i have two databases: 1st databse -> my_db 2nd database -> sample_db

mysql> show global variables like 'char%';

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

mysql> show global variables like 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

mysql> use my_db;

SHOW CREATE DATABASE my_db ;
+-------------------+-------------------------------------------------------------------------------------------+
| Database          | Create Database                                                                       
+-------------------+-------------------------------------------------------------------------------------------+
| plum_production_1 | CREATE DATABASE `my_db` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */    |
+-------------------+-------------------------------------------------------------------------------------------+

1st database; my_db

mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_unicode_ci   |
| collation_server     | utf8_unicode_ci   |
+----------------------+-------------------+

mysql> show variables like '%char%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | utf8                                      |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8                                      |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.6.27.R1/share/charsets/ |
+--------------------------+-------------------------------------------+

2nd database:

mysql> use sample_db;

mysql> show create database sample_db;
+-----------------+----------------------------------------------------------------------------+
| Database        | Create Database                                                            
|
+-----------------+----------------------------------------------------------------------------+
| plum_production | CREATE DATABASE `plum_production` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+-----------------+----------------------------------------------------------------------------+

mysql> show variables like '%char%';
+--------------------------+-------------------------------------------+
| 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       | /rdsdbbin/mysql-5.6.27.R1/share/charsets/ |
+--------------------------+-------------------------------------------+

mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

How to configure my.cnf when we require multiple collation types of db's i.e., i need

my_db - character_set utf8 collate utf8_unicode_ci.

Sample_db - character_set latin1 collate latin1_swedish_ci.

With the above configuration am facing some issues like tables are locked when trying to insert records into multiple tables except 1st table of insert statement.And other queries are too slow.Temporarily i changed my_db -character_set latin1 collate latin1_swedish_ci,now it is working fine. But my requirement was not this.

For my_db table & columns: character set- Utf8,collation-utf8_unicode_ci --> To get this done i altered

Database :- Alter database my_db characterset utf8 collate utf8_unicode_ci,

Tables :- For all tables - ALTER TABLE table_names CHARACTER SET utf8 COLLATE utf8_unicode_ci;

To convert all Columns :- ALTER TABLE table_names CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; am i in right way ?.is there anything to change other than in my.cnf?

and in sample_db:Charcter set-latin1,collation-latin_swedish_ci.

We are using awsrds my.cnf looks like this :-

[mysqld]
character_set_client: utf8
character_set_database: utf8
character_set_results: utf8
character_set_connection: utf8
character_set_server: utf8
collation_connection: utf8_unicode_ci
collation_server: utf8_unicode_ci

and also how to configure local instance my.cnf(not in aws) ? for example:

[client]
[mysql]
[mysqld]

When connecting how can i set names utf8_mb4?is it required to mention always when connecting to that db? i asked many qstions coz am confused and scared of data lose..thanks in advance.

Answer 1

my.cnf is mostly defaults that can be overridden. If you have a mixture, don't worry about it; focus on the other settings.

Client

What client do you have? (All I see is mysql commandline tool.) Probably the client should be always utf8mb4 (mysql character set, equivalent to the outside world of UTF-8).

When connecting, use the connection parameters to establish CHARACTER SET utf8mb4, possibly by doing SET NAMES utf8m4;

Data in Columns

Each column can have a CHARACTER SET and COLLATION. If not specified, they default from the CREATE TABLE. If that does not specify, it defaults from the CREATE DATABASE. Etc.

So, be sure each column is the way they need to be. Use SHOW CREATE TABLE to verify.

Client to/from Columns

MySQL transcodes data as it goes between the client and the server. So, it is OK to have the client using utf8mb4, but INSERTing/SELECTing a column that is declared latin1. (Some combinations won't work.)

Corollary: There is no problem if one DB is latin1 and another is utf8.

Garbage

See "best practice" in http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored . If you get gibberish, see that link for further debugging/cures.