Show the default value for a variable

Question

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.

Answer 1

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

Answer 2

From the manual:

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.

Answer 3

Could be selecting from information_schema.GLOBAL_STATUS

select VARIABLE_VALUE 
from information_schema.GLOBAL_STATUS 
where VARIABLE_NAME = 'max_connections';

Answer 4

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.

Answer 5

Have you tried using this

mysqld --verbose --help

based on https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html it will display compiled-in default.

To see specific variable you may use grep:

mysqld --verbose --help | grep -i 'max-connections'