Table of Contents
| Note |
|---|
This step has to be performed before creating the database. Restart MySQL for any setting change in my.cnf. |
Editing my.cnf file
If my.cnf file is usually present in the MySQL directory. , then add the following lines in the file.
Character encoding
...
(for Linux)
The database server running on Linux has default character encoding is latin1. Please change Change this property add the following line in /etc/my.cnf file under [mysqld] section :
[mysqld]
charactercharacter-set-server=utf8
Add below lines above the [mysqld] session.
...
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
...
cat > /etc/my.cnf << EOF[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock
Add below lines below [mysqld] section:
lower_case_table_names=1
[mysql.server]user=mysqlbasedir=/var/lib[safe_mysqld]err-log=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidEOF...
Increase memory for better query performance
Open the my.cnf file and add below lines below 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 DB 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 particular a character set. We configure collation as 'utf8_unicode_ci' because it supports almost all character used in the US, Canada, Australia, and Europe (Latin and Greek), Indian languages.
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
| Code Block | ||||
|---|---|---|---|---|
| ||||
service mysql restart |
...