MySQL: Changing runtime variables with out restarting MySQL server.


I have run in to the issue where I wanted to change some configuration on our production MySQL server but did not want to restart MySQL since its in production.  One of the parameters we like to change time to time is the “log_slow_queries” so we can see what queries are slow in production environment.  But obviously we don’t want to restart mysql server so we start up mysql client and type: 

mysql> set @@long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'long%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 3 |
+-----------------+-------+
1 row in set (0.00 sec)

Once you are done checking for slow queries, you can reset it back to whatever value you want by repeating the command with higher number (default is 10).

mysql> set @@long_query_time=10;
Query OK, 0 rows affected (0.00 sec
mysql> show variables like 'long%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 10 |
+-----------------+-------+
1 row in set (0.00 sec)

There are many more variables you can change.  Type show variables in the mysql client to see the list and what all those variables are set to.


12 Responses to “ MySQL: Changing runtime variables with out restarting MySQL server. ”

  1. July 23rd, 2007 | 4:29 am

    You’ll like this in 5.1, then:

    mysql> SET GLOBAL slow_query_log = 1;
    Query OK, 0 rows affected (0.00 sec)

    mysql> SET GLOBAL slow_query_log = 0;
    Query OK, 0 rows affected (0.00 sec)

  2. Ravi Gurbaxani
    September 23rd, 2007 | 11:42 pm

    I have to change a variable called ft_min_word_len. I used your way but it did not work.Please help me with this

  3. September 24th, 2007 | 2:03 pm

    Ravi,

    According to mysql docs it looks like you have to restart the server and rebuild your fulltext indexes in order to use ft_min_word_len directive.
    http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html

  4. November 27th, 2007 | 2:21 am

    I want to change the value of ‘ft_min_word_len’=4 to
    ‘ft_min_word_len’=2 but unknown variable message appears.

  5. Faisal
    November 27th, 2007 | 2:39 am

    Good Work,
    but how we change this ft_min_word_len
    i try your example but can’t work
    Please reply as soon as posible

  6. November 27th, 2007 | 6:48 pm

    Sani/Faisal, read my comment from September 24th.

  7. Daud
    September 7th, 2009 | 12:00 pm

    Excellent article, it helped me a lot. Please keep it up.

  8. November 6th, 2009 | 2:18 am
  9. May 18th, 2010 | 5:52 am

    running short on diskspace due to a growing slow_query_log of 9.5 gb on a mysqld 5.0 (where it seems hard to disable slowquerylog dynamically by: set global log_slow_queries = 0;), this method is the perfect option to “stop” the growth of the log, without restarting the mysqld.

    thanks a lot!

  10. Seun Jimoh
    February 2nd, 2011 | 1:59 pm

    Dear All,

    Please forgive me am new to this application, am building application using mysql database back end, but l needed to to edit some system variables please please can you give me an idiot guide please. Many thanks in advance.

    Jimoh

  11. May 24th, 2011 | 6:40 am

    [...] followed the instructions here: http://crazytoon.com/2007/07/23/mysql-changing-runtime-variables-with-out-restarting-mysql-server/ but that seems to only set the [...]

  12. November 2nd, 2013 | 7:28 am

    [...] followed the instructions here: http://crazytoon.com/2007/07/23/mysql-changing-runtime-variables-with-out-restarting-mysql-server/ but that seems to only set the [...]

Leave a reply

*
To prove that you're not a bot, enter this code
Anti-Spam Image