title: How to configure my.cnf for multiple CHARACTER SET of database in one instance
body: <p>In a instance i have two databases:
      1st databse  -> <strong>my_db</strong>
      2nd database -> <strong>sample_db</strong></p>
  
  <pre><code>mysql&gt; 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&gt; show global variables like 'coll%';
  +----------------------+-------------------+
  | Variable_name        | Value             |
  +----------------------+-------------------+
  | collation_connection | latin1_swedish_ci |
  | collation_database   | latin1_swedish_ci |
  | collation_server     | latin1_swedish_ci |
  +----------------------+-------------------+
  
  mysql&gt; 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 */    |
  +-------------------+-------------------------------------------------------------------------------------------+
  </code></pre>
  
  <p>1st database; <strong>my_db</strong></p>
  
  <pre><code>mysql&gt; show variables like '%coll%';
  +----------------------+-------------------+
  | Variable_name        | Value             |
  +----------------------+-------------------+
  | collation_connection | utf8_general_ci   |
  | collation_database   | utf8_unicode_ci   |
  | collation_server     | utf8_unicode_ci   |
  +----------------------+-------------------+
  
  mysql&gt; 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/ |
  +--------------------------+-------------------------------------------+
  </code></pre>
  
  <p>2nd database:</p>
  
  <pre><code>mysql&gt; use sample_db;
  
  mysql&gt; show create database sample_db;
  +-----------------+----------------------------------------------------------------------------+
  | Database        | Create Database                                                            
  |
  +-----------------+----------------------------------------------------------------------------+
  | plum_production | CREATE DATABASE `plum_production` /*!40100 DEFAULT CHARACTER SET latin1 */ |
  +-----------------+----------------------------------------------------------------------------+
  
  mysql&gt; 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&gt; show variables like '%coll%';
  +----------------------+-------------------+
  | Variable_name        | Value             |
  +----------------------+-------------------+
  | collation_connection | utf8_general_ci   |
  | collation_database   | latin1_swedish_ci |
  | collation_server     | latin1_swedish_ci |
  +----------------------+-------------------+
  </code></pre>
  
  <p>How to configure my.cnf when we require multiple collation types of db's i.e.,
  i need </p>
  
  <p><strong>my_db</strong>       - character_set utf8 collate utf8_unicode_ci.</p>
  
  <p><strong>Sample_db</strong>   - character_set latin1 collate latin1_swedish_ci.</p>
  
  <p>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 <em>my_db</em> -<em>character_set latin1 collate latin1_swedish_ci</em>,now it is working fine.
  But my requirement was not this.</p>
  
  <p>For <strong>my_db</strong> table &amp; columns: character set- Utf8,collation-utf8_unicode_ci --> To get this done i altered </p>
  
  <p>Database :- Alter database my_db characterset utf8 collate utf8_unicode_ci,</p>
  
  <p>Tables :- For all tables  - ALTER TABLE table_names CHARACTER SET utf8 COLLATE utf8_unicode_ci;</p>
  
  <p>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?</p>
  
  <p>and in <strong>sample_db</strong>:Charcter set-latin1,collation-latin_swedish_ci.</p>
  
  <p>We are using awsrds my.cnf looks like this :-</p>
  
  <pre><code>[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
  </code></pre>
  
  <p>and also how to configure local instance my.cnf(not in aws) ? for example: </p>
  
  <pre><code>[client]
  [mysql]
  [mysqld]
  </code></pre>
  
  <p>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.</p>