MySQL prerequisites

Character encoding

   MySQL server running on Linux has default character encoding is latin1. Please 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)

A MySQL server running on Linux is case-sensitive with regards to database and table names. This property is defined by the lower_case_table_names system variable. To set the system variable on Linux:

• If my.cnf file is available in the directory where MySQL is installed, then add the following line in the file.

  
  

  lower_case_table_names=1

• If the file is not available, then create the file my.cnf in the folder /etc using the following commands:
  cat > /etc/my.cnf << EOF

  [mysqld]

  datadir=/var/lib/mysql

  socket=/var/lib/mysql/mysql.sock

  lower_case_table_names=1

   

  [mysql.server]

  user=mysql

  basedir=/var/lib

   

  [safe_mysqld]

  err-log=/var/log/mysqld.log

  pid-file=/var/run/mysqld/mysqld.pid

  EOF

Settings for increasing query performance

Open the my.cnf file and add below lines below [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 variable can be safely set to 50% of the available memory if DB server is on different VM and no other apps are running on it.

MySQL server collation 

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

Add below lines under [mysqld] section:

init_connect='SET collation_connection = utf8_unicode_ci'

init_connect='SET NAMES utf8'

collation-server=utf8_unicode_ci

skip-character-set-client-handshake

Restart MySQL service once all configuration is done using the following command:

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.