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.

Blather

I believe it is 151 for max_connections. dev.mysql.com/doc/refman/5.7/en/… – Bicephalous

This was just an example and does not answer the question :) – Blather

I think you have to look at official documentation for the variables, one you have overwritten them with custom values. – Bicephalous

I know I can check the documentation, the question is if and how I can do this without reading the documentation for each variable :) – Blather

Answer 1

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.

Raymonderaymonds

Answer 2

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

Ezekielezell

This seems to be a good solution. I will try this as soon as possible and see if it works as expected. – Blather

Answer 3

Could be selecting from information_schema.GLOBAL_STATUS

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

Deodorant

Hm, the table does not contain any variable named max_connections? Looking on the global_variables table it contains the variable, but with the changed value. Any thoughts on how to solve this? – Blather

I am runng mysql 5.7 – Blather

the default value is 151 as in doc dev.mysql.com/doc/refman/5.7/en/… – Deodorant

Yes I know I can check the documentation to get this information, but you don't have any thoughts on how to do this by actually querying the database? – Blather

In my.ini is not present .. so this param setting is only in DB – Deodorant

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.

Arrogance

This is a good idea to keep track of the default values, but in this case I don't really have any possibility to create any tables since I don't have write access to the database. – Blather

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'

Schleiermacher

Yes this works, but I am not able to execute any binaries or read any configuration files. The solution must be by querying the database for the information if possible. – Blather

Answer 6

[Edit: Extends elomat's answer; I have not enough reputation to comment in it]

All mysql* commands have the --no-defaults option for not reading configuration files, so you could use:

mysqld --no-defaults --verbose --help | awk '/Variables/,/^$/' | less

As you cannot execute commands I suggest running this command in a virtual machine created with the same distro/distro version/MySQL version (To find MySQL's version use the query select version();).

On the other hand, if it is a locally compiled binary maybe you could copy the executable to another machine with compatible runtime libraries and without execution restrictions.

Notes: The awk patterns filter out the starting options until the word Variable is found, and then keeps printing text until an empty line or end of text is reached. This instance of mysqld does not keep running as it hasn't the --daemonize option.

Groom