MySQL Configuration File Sections Explained

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.

About the Author: Martin Keler

Leave a Reply

Your email address will not be published. Required fields are marked *