MySQL prerequisites

Editing my.cnf file

If my.cnf file is usually present in the MySQL directory. , then add the following lines in the file.

Character encoding (for Linux)

The database server running on Linux has default character encoding is latin1. Change this property add the following line in /etc/my.cnf file under [mysqld] section :

  [mysqld]

  character-set-server=utf8

  Add below lines above the [mysqld] session.

  [client]
  default-character-set=utf8

Case sensitivity (for Linux)

The database server on Linux is case-sensitive with regards to database and table names. 

Add below lines below [mysqld] section:

lower_case_table_names=1

Increase memory for better query performance

Open the my.cnf file and add below lines under [mysqld] section.

• innodb_buffer_pool_size = 1536M
• log_bin_trust_function_creators = 1
• optimizer_search_depth = 0

Note: The value of the innodb_buffer_pool_size the variable can be safely set to 50% of the available RAM memory if database server is on different VM and no other apps are running on it.

Server collation 

A database collation is a set of rules used to compare characters in a character set. We configure collation as 'utf8_unicode_ci' because it supports all character used in the US, Canada, Australia, and Europe (Latin and Greek).

Add below lines under [mysqld] section in my.cnf file:

• init_connect='SET collation_connection = utf8_unicode_ci'
• init_connect='SET NAMES utf8'
• collation-server=utf8_unicode_ci
• skip-character-set-client-handshake

Restarting service

service mysql restart

systemctl restart mysqld 

Once MySQL is restarted. Check all the variables are set to its correct value or not.

• Connect to MySQL.
• Run query: show variables like '%char%';
  The below output should get displayed.

• Run step #2 query for all variables and confirm correct values are assigned.