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.
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)
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
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
I want to change the value of ‘ft_min_word_len’=4 to
‘ft_min_word_len’=2 but unknown variable message appears.
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
Sani/Faisal, read my comment from September 24th.
Excellent article, it helped me a lot. Please keep it up.
Pingback: MySQL changing runtime variables without restart « HOBALL ç†è¨˜ç°¿
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!
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
Pingback: How can I enable MySQL’s slow query log without restarting MySQL? | Coding Answers
Pingback: How can I enable MySQL’s slow query log without restarting MySQL? | Ask Programming & Technology
It ‘called one of the best photo editing software.
Rich in effects and customization with amazing lighting effects that allow you to make your photos beautiful masterpieces.
Thousands of people satisfied, now for you is under discount.
Take advantage of it, use IMAGIC PHOTO! info and buy–> http://9nl.co/ImagePhoto
Lose weight Naturally. Just junk products. This video will change your life.! Look at him now. to purchase select Get Started. watch the video -> http://9nl.co/FlatBellyForever