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 thoughts on “MySQL: Changing runtime variables with out restarting MySQL server.

  1. Ravi Gurbaxani

    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

  2. Faisal

    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

  3. Pingback: MySQL changing runtime variables without restart « HOBALL 筆記簿

  4. alex

    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!

  5. Seun Jimoh

    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

  6. Pingback: How can I enable MySQL’s slow query log without restarting MySQL? | Coding Answers

  7. Pingback: How can I enable MySQL’s slow query log without restarting MySQL? | Ask Programming & Technology

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>