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
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. Please change this property add the following line in /etc/my.cnf file under
Add below line below [mysqld] section:
[mysqld]
character character-set-server=utf8
Add below lines above the [mysqld] session.
...
Case sensitivity (for Linux)
A MySQL 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/mysql...
Add below line 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 the database server is on a different VM and no other apps are running on it.
...
Server collation
A MySQL database collation is a set of rules used to compare characters in a particular a character set. We configure collation as 'utf8_unicode_ci' collation because it supports almost all character characters 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 ##Remove this setting for MySQL 8 to avoid the collation error in mysqldump.
Restarting service
| Code Block | ||||
|---|---|---|---|---|
| ||||
service mysql restart |
...