Many MySQL system variables can be set on the fly at the command line, without restarting the server. This is a great thing at times, like when that new code which was so thoroughly “tested” winds up consuming five times the database connections as the old version. Oops! Well, within the limits of the resources available on the database server, we can make more connections available without bouncing.
mysql> select @@global.max_connections; +--------------------------+ | @@global.max_connections | +--------------------------+ | 100 | +--------------------------+ 1 row in set (0.00 sec) mysql> set @@global.max_connections = 1250; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.max_connections; +--------------------------+ | @@global.max_connections | +--------------------------+ | 1250 | +--------------------------+ 1 row in set (0.00 sec) |
We’d better make this change in my.cnf so it will persist if the server restarts. That file will be found in /etc or /usr/local/mysql — or possibly /usr/local/mysql/data for older versions. Just find the max_connections entry in the mysqld section of the file, or add your own if it doesn’t exist.
max_connections = 1250 |
You can also use the “default” keyword to reset many variables back to their default value. In our case, this would set the max_connections back to 100, the MySQL default.
mysql> set @@global.max_connections=default; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.max_connections; +--------------------------+ | @@global.max_connections | +--------------------------+ | 100 | +--------------------------+ 1 row in set (0.00 sec) |
Super privileges are required for setting global system variables. Many system variables have session values, as well. These may be set without special privileges, but only for the client’s own session. For a full list of MySQL dynamic system variables, see http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html.
