Runtime Configuration

The MySQL server maintains sytem variables that indicate how it is configured. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an options file.

The server maintains two kinds of variables.

  • Global variables affect the overall operation of the server.
  • Session variables affect its operation for individual connections. A given system variable can have both a global and session value.

Some system variables can be dynamically changed whilst the system is running by using the SET statement without having to stop and restart the server.

Setting variables on the command line

mysqld --query_cache_size=16M --max_allowed_packet=1G

Setting variables in the options in the


Restrict maximum value of system variable

You can prefix the keyword --maximum to specify the maximum value the variable can be set to e.g to restrict the query cache being set above 32M.

mysqld --query_cache_size=16M --maximum-query-cache-size=32M

Show compiled-in defaults plus values read from options file

mysqld --verbose --help

Show compiled-in defaults, ignoring settings in options file

mysqld --no-defaults --verbose --help

Show current values used by a running server

mysql> show variables;

or using mysqladmin variables on the terminal

joseph@ubuntu:~$ mysqladmin -u joseph -p variables

Dynamic System Variables

Dynamic system variables can be set at runtime using SET GLOBAL/SET @@GLOBAL.variable_name or SET SESSION/SET @@SESSION.variable_name.

Show global variable state

You can use the SELECT statement with @@global to show the value of the variable

mysql> select @@global.query_cache_size;
| @@global.query_cache_size |
| 16777216 |

Some variables are not available as SESSION variaables and will give an error

mysql> select @@session.query_cache_size;
ERROR 1238 (HY000): Variable 'query_cache_size' is a GLOBAL variable

Selecting session variables

Use the @@session to show the session variable

mysql> select @@session.max_join_size;
| @@session.max_join_size |
| 18446744073709551615 |

Setting Variables

You can use the SET statment with scope of either GLOBAL or SESSION to set the value of the variable

mysql> set global max_allowed_packet=16*1024*1024;
Query OK, 0 rows affected (0.01 sec)

Using the qualify of K for Kilobytes, M for megabytes or G for Gigabytes is not allowed when setting value during runtime

mysql> set global max_allowed_packet=16M;
ERROR 1232 (42000): Incorrect argument type to variable 'max_allowed_packet'

During server starup the qualifies are allowed

joseph@ubuntu:~$ mysql --max_allowed_packet=24M
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.13-0ubuntu0.16.04.2 (Ubuntu

Setting the values to default

You can set the variables back to their defaults

mysql> set global max_allowed_packet=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

and checking again, the max_allowed_packet has been reset to the default. The value will persist as long the server has not been restarted.

mysql> select @@global.max_allowed_packet;
| @@global.max_allowed_packet |
| 4194304 |

Setting true\/false values

Some system variables can be enabled with the SET statement by setting them to ON or 1, or disabled by setting them to OFF or 0.

However, to set such a variable on the command line or in an option file, you must set it to 1 or 0, setting it to ON or OFF will not work.

Using SHOW statement

To display system variable names and values use the the SHOW VARIABLES statments.

mysql> show variables;

By default the SHOW statement displays session variables. Use SHOW GLOBAL to show global variables

mysql> show global variables;

Filter Results of SHOW VARIABLES with LIKE

Use the like to filter the result of the SHOW VARIABLES

mysql> show variables like 'max_connections';
| Variable_name | Value |
| max_connections | 151 |

and you can also use wildcards like _ for a single character and % any character

mysql> show variables like '%cache%';
| Variable_name | Value |
| binlog_cache_size | 32768 |
| have_query_cache | YES |
| host_cache_size | 279 |
| innodb_disable_sort_file_cache | OFF |
| innodb_ft_cache_size | 8000000 |
| query_cache_wlock_invalidate | OFF |
| stored_program_cache | 256 |
| table_definition_cache | 615 |
| table_open_cache | 431 |

or altenative use a WHERE clause

mysql> show variables where variable_name = 'query_cache_size';
| Variable_name | Value |
| query_cache_size | 16777216 |

results matching ""

    No results matching ""