hosted by liquidweb


Go Back   Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : setting up crontab flush query cache?
Reply

Forum Jump

setting up crontab flush query cache?

Reply Post New Thread In Programming Discussion Subscription
 
Send news tip View All Posts Thread Tools Search this Thread Display Modes
  #1  
Old 10-06-2009, 02:07 PM
acctman acctman is offline
Web Hosting Evangelist
 
Join Date: Apr 2004
Location: Atlanta, GA
Posts: 535

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.



Sponsored Links
  #2  
Old 10-07-2009, 09:25 AM
mattle mattle is offline
Web Hosting Master
 
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  
Old 10-07-2009, 10:29 AM
acctman acctman is offline
Web Hosting Evangelist
 
Join Date: Apr 2004
Location: Atlanta, GA
Posts: 535
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?

Sponsored Links
  #4  
Old 10-07-2009, 10:40 AM
mattle mattle is offline
Web Hosting Master
 
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  
Old 10-07-2009, 11:02 AM
acctman acctman is offline
Web Hosting Evangelist
 
Join Date: Apr 2004
Location: Atlanta, GA
Posts: 535
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  
Old 10-07-2009, 11:06 AM
mattle mattle is offline
Web Hosting Master
 
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  
Old 10-07-2009, 11:29 AM
acctman acctman is offline
Web Hosting Evangelist
 
Join Date: Apr 2004
Location: Atlanta, GA
Posts: 535
thanks for all the help

  #8  
Old 10-07-2009, 06:40 PM
foobic foobic is online now
Community Liaison 2.0
 
Join Date: Feb 2005
Location: Australia
Posts: 5,809
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

Reply

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

Related posts from TheWhir.com
Title Type Date Posted
Building a Big Data Service Webinars 2014-09-18 14:58:57
Squarespace Gets $40 Million in Funding to Scale Engineering and Infrastructure Teams Web Hosting News 2014-05-01 08:33:50
Barracuda Network Releases Version 5.4 of Cloud Backup Solution Web Hosting News 2013-08-26 12:18:47
Cloud Operations and Social Support with Kellie Green Web Hosting News 2013-02-06 18:52:45
NetDNA Launches Integrated CDN for Developers to Optimize Web Application Performance Web Hosting News 2012-11-19 15:30:18


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump
Login:
Log in with your username and password
Username:
Password:



Forgot Password?
Advertisement:
Web Hosting News:
WHT Membership
WHT Membership



 

X

Welcome to WebHostingTalk.com

Create your username to jump into the discussion!

WebHostingTalk.com is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?