Show the default value for a variable
In MySQL 5.7 you can use performance_schema
to get the variables.
Before modifying or set
you can select the variable to see the default value then modify.
Method #1
SELECT
VARIABLE_VALUE
FROM
performance_schema.global_variables
WHERE
VARIABLE_NAME = 'max_connections';
Output #1
| VARIABLE_VALUE | | :------------- | | 151 |
Method #2
If you are not sure the exact name of variable use like
, it can used used also in above query too.
SHOW GLOBAL VARIABLES LIKE 'max_connect%';
Output #2
Variable_name | Value :----------------- | :---- max_connect_errors | 100 max_connections | 151
Method #3
SELECT @@GLOBAL.max_connections;
Output #3
| @@GLOBAL.max_connections | | -----------------------: | | 151 |
Refer here db-fiddle
Note: If you need to have a history kind of thing then you need to create a table to store those values before changing.
P.S. There is one more type of variables session
. By replacing global
to session
those variables can be changed but it will affect only to the current session.
credits: @scaisedge, @richard
Could be selecting from information_schema.GLOBAL_STATUS
select VARIABLE_VALUE
from information_schema.GLOBAL_STATUS
where VARIABLE_NAME = 'max_connections';
This might not be ideal but if it were me trying to solve the problem I'd create my own table with all of the initial values and reference that when I needed.
CREATE TABLE
default_variables
SELECT
*
FROM
information_schema.GLOBAL_STATUS;
This would require you to spin up a new DB installation that is the same version as the one you're currently using, but I think you could use this method to solve your problem.
Simply export the data and import it wherever you need it.
I don't currently see any built-in way to query the defaults.