mysqlvariablesdefault-valuemysql-variables

Show the default value for a variable


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.


Solution

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