Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Location
    Atlanta, GA
    Posts
    537

    setting up crontab flush query cache?

    can someone assist me with setting up a crontab that will "flush query cache" for all databases... i know its something like mysql -u root -pyour_root_password -e "flush query cache"; but i want it to cover all databases on the server.

  2. #2
    Join Date
    May 2009
    Posts
    766
    I believe you only have the one query cache for all databases...I don't see anything to the contrary here: http://dev.mysql.com/doc/refman/5.0/en/flush.html

    Unless you have multiple instances of mysqld running on different ports/sockets...then you would just have to maintain a list of them and cycle through it, sending the command to each instance.

  3. #3
    Join Date
    Apr 2004
    Location
    Atlanta, GA
    Posts
    537
    Quote Originally Posted by mattle View Post
    I believe you only have the one query cache for all databases...I don't see anything to the contrary here: http://dev.mysql.com/doc/refman/5.0/en/flush.html

    Unless you have multiple instances of mysqld running on different ports/sockets...then you would just have to maintain a list of them and cycle through it, sending the command to each instance.
    i just have one mysqld, how would the crontab line look to flush the query?

  4. #4
    Join Date
    May 2009
    Posts
    766
    Check this out for cron syntax. It all depends on how often/when you want the flush to run:

    http://en.wikipedia.org/wiki/Cron

    As far as the command line goes, the only difference between what you can type at the command line and what cron will execute is that cron's PATH variable won't be as robust as yours. You can either set the PATH at the top of your crontab, or make sure you give full paths to your commands:

    /usr/local/mysql/bin/mysql -uroot ...

    If you don't know how to find the path to your mysql client, use this command:

    which mysql

    To edit your crontab, use this command:

    crontab -e

    To view your crontab, use this:

    crontab -l

    (man crontab for more)

    Your crontab will come up in the default editor (usually vi), but you can change your EDITOR env var if you prefer to work in something else:

    $ export EDITOR=/usr/bin/nano
    $ crontab -e

    Hope that helps,
    Matt

  5. #5
    Join Date
    Apr 2004
    Location
    Atlanta, GA
    Posts
    537
    Quote Originally Posted by mattle View Post
    Check this out for cron syntax. It all depends on how often/when you want the flush to run:

    http://en.wikipedia.org/wiki/Cron

    As far as the command line goes, the only difference between what you can type at the command line and what cron will execute is that cron's PATH variable won't be as robust as yours. You can either set the PATH at the top of your crontab, or make sure you give full paths to your commands:

    /usr/local/mysql/bin/mysql -uroot ...

    If you don't know how to find the path to your mysql client, use this command:

    which mysql

    To edit your crontab, use this command:

    crontab -e

    To view your crontab, use this:

    crontab -l

    (man crontab for more)

    Your crontab will come up in the default editor (usually vi), but you can change your EDITOR env var if you prefer to work in something else:

    $ export EDITOR=/usr/bin/nano
    $ crontab -e

    Hope that helps,
    Matt
    thanks, another question though since this is a root/system crontab i'm editting do I need to insert the mysql root password?

  6. #6
    Join Date
    May 2009
    Posts
    766
    Do you need to give mysql the root password when you run the client as root from the command line? With the exception to giving the full path to the executable, your command line should be the same.

    If you want to verify the output, use this:

    /path/to/mysql [options] 2>&1 | /path/to/mail -s "mysql cron output" your_email@abc.com

    You're doing two things here:

    2>&1 -- redirect STDERR to STDOUT (ie, merge error messages into the normal output stream)
    | mail ... -- take the entire output stream (normal messages + error messages) and email them to yourself

    Alternatively, you can make sure that cron is sending email upon job completion and make sure that you're either checking your root mailbox, or aliasing the root address to your preferred email address.
    Last edited by mattle; 10-07-2009 at 11:15 AM.

  7. #7
    Join Date
    Apr 2004
    Location
    Atlanta, GA
    Posts
    537
    thanks for all the help

  8. #8
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,840
    Don't ever put passwords into command lines - it's just bad practice. For MySQL you can create a .my.cnf file in the user's (or root's) home dir containing this, and you'll be able to log in automatically:
    Code:
    [client]
    user = username
    password = secret
    (you can also add "host" if the database isn't on localhost)
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  9. Newsletters

    Subscribe Now & Get The WHT Quick Start Guide!

Similar Threads

  1. query cache denied?
    By elfz in forum Hosting Security and Technology
    Replies: 5
    Last Post: 04-14-2010, 06:52 AM
  2. MySQL Query Cache
    By JoyceBabu in forum Programming Discussion
    Replies: 8
    Last Post: 12-14-2008, 10:53 AM
  3. mysql: schedule flush query cache
    By hpham in forum Hosting Security and Technology
    Replies: 1
    Last Post: 12-05-2007, 10:45 PM
  4. Flush Cache on CentOS
    By MziB in forum Hosting Security and Technology
    Replies: 2
    Last Post: 06-15-2007, 03:59 PM
  5. mysql and query cache question
    By koppan in forum Hosting Security and Technology
    Replies: 2
    Last Post: 01-04-2007, 12:06 PM

Related Posts from theWHIR.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •