Until MySQL version 4.0.3, you always had to restart the server to change the variable values. Now, you can use the much more convenient SET statement to make a change without shutting down the server.
You can use the SET statement in two ways. The default is for the change you make to affect the SESSION only, meaning that when you connect next time (and for all other connections) the variable will still be at the setting specified in the configuration file. If you specify the GLOBAL keyword, all new connections will use the new value. When the server restarts, however, it will always use the values set in the configuration file, so you always need to make the changes there as well. To set a variable with the GLOBAL option, you need to have the SUPER permission.
The syntax is as follows:
SET [GLOBAL | SESSION] sql_variable=expression, [[GLOBAL | SESSION]– sql_variable=expression...]
For example, the following:
mysql> SET SESSION max_sort_length=2048;
is the same as this:
mysql> SET max_sort_length=2048;
There is an alternate syntax too, used for compatibility with other database management systems (DBMSs), using the @@ syntax, as follows:
SET @@{global | local}.sql_variable=expression, [@@{global |–
local}.sql_variable=expression]
To repeat the previous example in this syntax, you use the following:
mysql> SET @@local.max_sort_length=2048;
SESSION and LOCAL are synonyms.
If, after experimenting with the new variable, you decide to return to the old value, there’s no need to trust your memory or to look it up in the configuration file. You can use the DEFAULT keyword to restore a GLOBAL value to the value in configuration file, or a SESSION value to the GLOBAL value. For example:
mysql> SET SESSION max_sort_length=DEFAULT;
and
mysql> SET GLOBAL max_sort_length=DEFAULT;
