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