MySQL prerequisites

Editing my.cnf file

The 'my.cnf'file is usually present in the MySQL directory. , add the following lines in the file.

Character encoding (for Linux)

The database server running on Linux has default character encoding is latin1. 

Add below line below [mysqld] section:

  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 line 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 the database server is on a 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 characters 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 ##Remove this setting for MySQL 8 to avoid the collation error in mysqldump.

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.