This step has to be performed before creating the database
Character encoding
The database 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)
The database 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 the 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.
Database server collation
A database collation is a set of rules used to compare characters in a particular character set. We configure collation as 'utf8_unicode_ci' 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.