MySQL Configuration

Introduction

This page provides an introduction to configure the MySQL server. It first explains how to locate the MySQL configuration file and how it is structured. It then explains how to show and change system variables at runtime. Finally, it shows how to monitor the current server status.

This page is part of the MySQL administration series:

System Variables

MySQL is a very configurable database server. The configuration of a MySQL instance is defined in terms of system variables. System variables can be used to fine-tune the server performance, define the default storage engine, set the session time zone or even configure how the server interacts with its clients.

System variables can be set at start up in a configuration file, as a command line parameter, or at run time using the SET statement.

Configuration File

When MySQL starts up, it loads the parameters defined in the my.ini and my.cnf  files.

MySQL searches for these configuration files in a pre-defined list of directories. If the same variable is defined in multiple files, then last value read is used.

The below command outputs the configuration directories:

In Windows, MySQL reads the configuration files in this order:

You can also set an alternative location with the --defaults-file parameter

All MySQL programs use the same configuration file.  This centralised model makes the administration of MySQL more efficient and less prone to errors. The configuration file is structured into groups and each MySQL executable read from specific groups. For example, mysqld server reads from the   [mysqld] , [server]  and [mysqld-5.6] groups. mysql client, on the other hand, reads from the  [mysql]  and [client] groups.

The configuration file accepts suffix values such as ‘K’, ‘M’ or ‘G’ to denote kilo bytes, mega bytes or giga bytes values:

Show System Variables

You use SHOW VARIABLES to show the current value of system variables

There are a very large number of system variables. To refine your search, you can also use LIKE and WHERE .

There are two types of variables: session and global variables. Session variables influence the current session only. Global variables influence the server, thus affecting all sessions. Some variables can have both a global and session value.

SHOW VARIABLES returns the session variable value if it exists. If not, it then returns the global variable value. You can return global or session variables by adding the GLOBAL or SESSION prefixes.

You can also use the @@  notation to return variable values. If no GLOBAL  or SESSION  is provided, then the session variable is updated.

Finally, you can also access the INFORMATION_SCHEMA  tables to obtain system information

Set Variables at Runtime

To set variables at runtime you can use the SET  command. To refer to global or session variables, you can use the @@  notation:

An alternative syntax to set variables is as follow:

It should be noted that the suffix values such as ‘K’, ‘M’ or ‘G’ available in the configuration file are not available at runtime. Instead, you can use expressions:

Show Status Variables

Status variables can be used to monitor the current server operation at run time. You can display these variables with SHOW STATUS.

As with system variables, you can also show global and session status variables. Global aggregates the values of all sessions, while session shows the status of the current connection.

You can also use LIKE and WHERE.

Finally, you can also access the status variables using the INFORMATION_SCHEMA  tables:

General Purpose System Variables

This table lists the common configuration options used for a MySQL server:

VariablePurpose
port

 

The port number that mysqld listens for TCP/IP connections.

Default is 3306

datadirThe MySQL data directory

In Windows, defaults to C:\ProgramData\MySQL\MySQL Server 5.6

It can be set with the --datadir  command line option.

character-set-serverThe default character set when a new schema or table is created

Default is latin1

Set it to utf8 for UTF-8.

default-storage-engineThe default storage engine used to create new tables

Default is InnoDB

max_allowed_packetThe maximum size of the communication buffer with clients.

The default is 4MB and the maximum is 1GB.

You should increase this value is using BLOB columns or long strings. It should be as large as the larges BLOB you want to send.

When you change this value in the server, also remember to change it in the MySQL clients. For example, the defaults for mysql and mysqldump are 16MB, and 24MB respectively.

max_connectionsThe maximum number of simultaneous client connections the server accepts.

The default is 151.

Increasing this value also increases the number of file descriptors that mysqld requires ( open_files_limit).

table_open_cacheThe maximum number of open tables.

The default is 2000.

Increasing this value also increases the number of file descriptors that mysqld requires ( open_files_limit).

You can monitor the Opened_tables status variable to know how many opened tables your database uses.

open_files_limitThe number of files that the operating system permits mysqld to open.

The default is 5000, with possible adjustment to the max number of files that the Operating System permits.

The open_files_limit needs to be higher than the below formulas:

  • 10 + max_connections + (table_open_cache * 2)
  • max_connections * 5
sort_buffer_sizeThe buffer size used by the ORDER BY  and GROUP BY  operations.

The default is 256KB.

If you see many Sort_merge_passes per second ( SHOW GLOBAL STATUS), then consider increasing.

join_buffer_sizeThe buffer size used for index scans and joins that do not use indexes (and thus perform full table scans)

The default is 256KB.

Bibliography

The following two tabs change content below.

Eduard Manas

Eduard is a senior IT consultant with over 15 years in the financial sector. He is an experienced developer in Java, C#, Python, Wordpress, Tibco EMS/RV, Oracle, Sybase and MySQL.Outside of work, he likes spending time with family, friends, and watching football.

Latest posts by Eduard Manas (see all)

Leave a Reply