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
[mysqld]
query_cache_size=16M
max_allowed_packet=1G
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 |
+------------------+----------+