Option Files and Variables

The Option File and Server Variables

MySQL 524 programs read INI-style option files at startup: [mysqld] for the server, [client] and [mysql] for clients. On Ubuntu 225 that means /etc/my.cnf, /etc/mysql/my.cnf and ~/.my.cnf, later values winning (The Filesystem Hierarchy). /etc/mysql/my.cnf links to mysql.cnf, which includes conf.d/ and then mysql.conf.d/, home of the package's mysqld.cnf. Leave packaged files alone and add your own that sorts last:

mysql.conf.d/zz-app.cnf: local overrides that survive package upgradesSQL

[mysqld]
max_connections         = 200
innodb_buffer_pool_size = 512M
slow_query_log          = ON
long_query_time         = 0.5

Check the merge with my_print_defaults mysqld, then restart. (On Windows the file is my.ini in C:\ProgramData\MySQL\MySQL Server 9.7.) Copied into the Docker 514 server's conf.d/ from a Windows folder, this file arrived with mode 777 and was rejected: World-writable config file '/etc/mysql/conf.d/zz-app.cnf' is ignored. Keep option files at mode 644.

Most settings are also system variables you can change at runtime. In the Docker server:

Global and persisted changesSQL

SET GLOBAL  max_connections = 300;   -- every new connection, until restart
SET PERSIST long_query_time = 1;     -- now, and after every restart
SET PERSIST_ONLY back_log   = 300;   -- read-only at runtime: next start only
SET GLOBAL  back_log        = 300;

Output

ERROR 1238 (HY000) at line 4: Variable 'back_log' is a read only variable

PERSIST writes JSON to mysqld-auto.cnf in the data directory, read after all other option files. After a restart, the Performance Schema names each value's source:

Where did each setting come from?SQL

SELECT VARIABLE_NAME, VARIABLE_SOURCE, VARIABLE_PATH
FROM performance_schema.variables_info
WHERE VARIABLE_NAME IN ('max_connections', 'long_query_time', 'back_log', 'sort_buffer_size');

Output

+------------------+-----------------+--------------------------------+
| VARIABLE_NAME    | VARIABLE_SOURCE | VARIABLE_PATH                  |
+------------------+-----------------+--------------------------------+
| back_log         | PERSISTED       | /var/lib/mysql/mysqld-auto.cnf |
| long_query_time  | PERSISTED       | /var/lib/mysql/mysqld-auto.cnf |
| max_connections  | GLOBAL          | /etc/mysql/conf.d/zz-app.cnf   |
| sort_buffer_size | COMPILED        |                                |
+------------------+-----------------+--------------------------------+

SET GLOBAL did not survive (the file's 200 is back); both persisted values did. PERSIST suits servers whose files you cannot edit, but hides settings from anyone reading my.cnf. Undo with RESET PERSIST long_query_time, never by editing the file. Server Tuning tunes the variables that matter.

MENU

Option Files and Variables