So far we have discussed various mays to configure MySQL, Environmental Variables, Global Variable, Session Variables, Status Variable and Server Variables. All this variables are important and set the variable some of them dynamically affects the system and some required system restart.
Today we will be discussing on very important way to configuration mysql system using Option file also known as Configuration file (my.cnf). If you know MySQL you must be aware of this file. Most of the command-line Variables can be configurable using option file.
This is the standard way of setup the system MySQL read this file at startup and configure the system accordingly, so when you run the command those values will be set.
*again – Want to repeat what I stated earlier that configuration options is totally depends upon:
- Storage engine : Specific to Storage engine like innodb, ISAM, and others
- Operating system: Redhad, other Linux, Windows.
- MySQL Version: Earlier version and new version, like newer versions and MySQL 8 is not moving variables into tables and decrypting Variables
Order of option file read: /etc/my.cnf, /etc/mysql/my.cnf, $MYSQL_HOME/my.cnf, ~/.my.cnf, ~/.mylogin.cnf
Option file has a configuration option in “Groups” and MySQL program read those groups.
Eg. Mysqld_safe read [mysqld] and [mysqld_safe] groups from option file. So all the configuration set in that group will be retrieved during mysqld_safe program.
MySQL makes no guarantee about the order in which option files in the directory will be read.
Some Groups for option file are as follows:
[client] – client – users access this group
[mysql] – mysql client access this group
[mysqld] – mysql startup access this group
[mysqld_safe] – similar to mysqld +mysqld_safe
option file is very important and main file where all MySQL is getting controlled.