I am using SET GLOBAL <variable> = <value>
to modify dynamic settings in mysql and I am wondering if there is some way to get the default value for each variable? For instance, if I use the following:
SET GLOBAL max_connections = 1000;
and then list the variable using:
SHOW GLOBAL VARIABLES LIKE 'max_connections';
I can see the modified value 1000
, but is there possible to get the default value for this system variable without checking the configuration files?
I am using mysql 5.7 on ubuntu 16.04.
To set a global system variable value to the compiled-in MySQL default value [...] set the variable to the value
DEFAULT
.
That means you can do this:
SET @@GLOBAL.max_connections = 1234;
/*
* Proceed in this order
* 1) Backup current value
* 2) Reset to default
* 3) Read the current value
* 4) Restore the backup value if necesssary
*/
SET @oldvalue = @@GLOBAL.max_connections;
SET @@GLOBAL.max_connections = DEFAULT;
SET @defvalue = @@GLOBAL.max_connections;
SET @@GLOBAL.max_connections = @oldvalue;
SELECT @@GLOBAL.max_connections AS `current value`
, @defvalue AS `default value`
-- 1234 and 151
The @oldvalue
and @defvalue
are user variables.