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.

I believe it is `151` for max_connections. https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_connections — Yaman Jain, Apr 21 '19 at 12:46
This was just an example and does not answer the question :) — Cyclonecode, Apr 21 '19 at 12:47
I think you have to look at official documentation for the variables, one you have overwritten them with custom values. — Yaman Jain, Apr 21 '19 at 12:54
I know I can check the documentation, the question is if and how I can do this without reading the documentation for each variable :) — Cyclonecode, Apr 21 '19 at 13:03

score 3 · Answer 1 · answered Oct 20 '19 at 06:10

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

This seems to be a good solution. I will try this as soon as possible and see if it works as expected. — Cyclonecode, Oct 21 '19 at 05:45

score 2 · Answer 2 · answered Apr 21 '19 at 12:54

2

Could be selecting from information_schema.GLOBAL_STATUS

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

answered Apr 21 '19 at 12:54

ScaisEdge

131,976
10
91
107

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? – Cyclonecode Apr 21 '19 at 13:13
I am runng mysql 5.7 – Cyclonecode Apr 21 '19 at 13:26
the default value is 151 as in doc https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_connections – ScaisEdge May 10 '19 at 09:42
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? – Cyclonecode May 10 '19 at 09:43
In my.ini is not present .. so this param setting is only in DB – ScaisEdge May 10 '19 at 09:51

score 2 · Answer 3 · answered Oct 19 '19 at 20:38

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.

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. — Cyclonecode, Oct 21 '19 at 05:44

score 2 · Accepted Answer · answered Oct 22 '19 at 07:32

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.

score 1 · Answer 5 · answered Oct 22 '19 at 10:18

1

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'

answered Oct 22 '19 at 10:18

elomat

139
4

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. – Cyclonecode Aug 28 '20 at 22:31

Fjor · Answer 6 · 2020-10-04T01:59:17.977

[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.

Show the default value for a variable

6 Answers6