• MySQL知识库 :: schema
  • How do I change a system variable without having to reset the server?

  • Discussion

    Prior to version 4.x of MySQL, if you want to change the value of a server variable, you would have to make the change it in the options file (my.cnf or my.ini, depending on your system) and then restart the server, or at the command-line when restarting the server. There are times when you may need to change a system variable, but can't afford to restart the server daemon.

    Starting with version 4.x of MySQL, you can change server variables without restarting the server by using the SET statement. It may be used to set a system for global use or for session use. Global variables are availabe to all users. Session variables are available only to the connection thread that creates the variable. For system variables to be recognized as global, the flag GLOBAL must be used since variables are assumed to be for the current session only by default. The variable can be preceded by @@global. instead to signify that it is global. For system variables which you want to limited to the current session, use the SESSION flag, or place @@session. or just @@ before the variable name. LOCAL and @@local. are aliases for SESSION and @@session., respectively. Below are two examples of how you might change system variables:

    SET GLOBAL concurrent_insert =  1;
SET LOCAL interactive_timeout=50000;

    The first SQL statement above disables concurrent inserts for all connections without having to restart the server. The second one changes the interactive time-out to a higher value than normal. This change applies to the current client connection only. It doesn't affect other clients. For the system, this variable will still have the default value.

    To see a list of system variables and their values, use the SHOW VARIABLES statement.