Set a persistent global MySQL parameter

Question

I set the following MySQL parameter:

SET GLOBAL wait_timeout = 2147483;

However, after server restart it reverted to its default value 28800. Isn't the SET command persistent? In order to have this parameter persistent, should I edit a configuration file? Would that file be C:\Program Files\MySQL\MySQL Server 5.5\my.ini? If so, is it simply:

wait_timeout=2147483

If so, which section should it be in my.ini?

Birdiebirdlike

Answer 1

Set global doesn't make persistent variables.

You should write that under [mysqld] section in my.cnf:

[mysqld]
wait_timeout=86400

Corsica

Is my.ini the Windows equivalent of linux's my.cfg? – Birdiebirdlike

Yes, Mysql on Windows searches several places for settings file in this order: %WINDIR%\my.ini, %WINDIR%\my.cnf, C:\my.ini, C:\my.cnf, INSTALLDIR\my.ini, INSTALLDIR\my.cnf – Corsica

See follow up question – Birdiebirdlike

Answer 2

You can persist wait_timeout (global variable) to be 2147483 with the SQL below. *The SQL below only changes wait_timeout global variable rather than wait_timeout session variable and the doc explains how to persist global variables:

SET PERSIST wait_timeout = 2147483;

Or:

SET @@PERSIST.wait_timeout = 2147483;

In addition, you can disable wait_timeout to persist with the SQL below. *The SQL below doesn't change wait_timeout global variable and the doc explains how to disable persisted global variables:

RESET PERSIST wait_timeout;

And, you can show wait_timeout persisted variable with the SQL below. *The doc explains persisted_variables table:

SELECT * FROM performance_schema.persisted_variables WHERE VARIABLE_NAME = 'wait_timeout';

Pintail