online prescription solutions
online discount medstore
pills online
buy lorazepam without prescription
xanax for sale
buy xanax without prescription
buy ambien without prescription
ambien for sale
buy modafinil without prescription
buy phentermine without prescription
modafinil for sale
phentermine for sale
lorazepam for sale
buy lexotan without prescription
bromazepam for sale
xenical for sale
buy stilnox without prescription
valium for sale
buy prosom without prescription
buy mefenorex without prescription
buy sildenafil citrate without prescription
buy adipex-p without prescription
librium for sale
buy restoril without prescription
buy halazepam without prescription
cephalexin for sale
buy zoloft without prescription
buy renova without prescription
renova for sale
terbinafine for sale
dalmane for sale
buy lormetazepam without prescription
nobrium for sale
buy klonopin without prescription
priligy dapoxetine for sale
buy prednisone without prescription
buy aleram without prescription
buy flomax without prescription
imovane for sale
adipex-p for sale
buy niravam without prescription
seroquel for sale
carisoprodol for sale
buy deltasone without prescription
buy diazepam without prescription
zopiclone for sale
buy imitrex without prescription
testosterone anadoil for sale
buy provigil without prescription
sonata for sale
nimetazepam for sale
buy temazepam without prescription
buy xenical without prescription
buy famvir without prescription
buy seroquel without prescription
rivotril for sale
acyclovir for sale
loprazolam for sale
buy nimetazepam without prescription
buy prozac without prescription
mogadon for sale
viagra for sale
buy valium without prescription
lamisil for sale
camazepam for sale
zithromax for sale
buy clobazam without prescription
buy diflucan without prescription
modalert for sale
diflucan for sale
buy alertec without prescription
buy zyban without prescription
buy serax without prescription
buy medazepam without prescription
buy imovane without prescription
mefenorex for sale
lormetazepam for sale
prednisone for sale
ativan for sale
buy alprazolam without prescription
buy camazepam without prescription
buy nobrium without prescription
mazindol for sale
buy mazindol without prescription
buy mogadon without prescription
buy terbinafine without prescription
diazepam for sale
buy topamax without prescription
cialis for sale
buy tafil-xanor without prescription
buy librium without prescription
buy zithromax without prescription
retin-a for sale
buy lunesta without prescription
serax for sale
restoril for sale
stilnox for sale
lamotrigine for sale

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