MySQL configuration file sections are something often overlooked by developers and sysadmins, and not everybody takes the time to read and understand what are the my.cnf file sections. On this post we will explain what are the most important mysql configuration sections inside the /etc/my.cnf file.
Common MySQL Configuration File Sections
On a normal MySQL configuration you will find a configuration file called my.cnf that is located on most Linux distros at /etc/my.cnf.
my.cnf file example
[[email protected]:~]cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock local-infile=1 query_cache_limit=4M query_cache_size=256M query_cache_type=1 max_connections=400 interactive_timeout=30 wait_timeout=30 connect_timeout=10 thread_cache_size=128 key_buffer=200M join_buffer=1M max_allowed_packet=16M table_cache=26536 sort_buffer_size=1M read_buffer_size=1M read_rnd_buffer_size=1M max_connect_errors=10 myisam_sort_buffer_size=64M tmp_table_size=256M max_heap_table_size=256M innodb_buffer_pool_size=2000M innodb_flush_log_at_trx_commit=2 innodb_file_per_table [mysqldump] quick quote-names max_allowed_packet = 16M [mysql.server] user=mysql [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
On this server my.cnf file includes [mysqld], [mysqldump], [mysql.server], [mysqld_safe], however there are other sections that are not included. Let’s describe each of the MYSQL main configuration file sections.
[client] this block of configuration applies to the incoming connections from clients, including mysql cli.
[mysql] settings specified here apply directly to the mysql command line client only.
[mysqld] applies to the mysql server and as you see in the previous example, you can define cache settings and everything related to the mysql server only. [server options]
[mysql.server] & [mysqld_safe] on the previous example, are used to define server settings like MySQL user, pid file and error log, however since MySQL 5.7.x and the implementation of systemd on RPM based distros, this section is no longer necessary.
[mysqldump] options specified inside this configuration section only apply to the MySQLDump tool, used to backup and restore MySQL databases
[mysqladmin] applies to the mysqladmin client, used to perform common mysql operations from command line, for example: shell> mysqladmin [options] command [command-arg] [command [command-arg]]
[mysqlhotcopy] This MySQL section is used to define the configurations for mysqlhotcopy, a common backup solution for “hot copy” type of backups.