MySQL prerequisites


This step has to be performed before creating the database. Restart MySQL for any setting change in my.cnf.

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 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

Ubuntu
service mysql restart
RedHat / CentOS
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.