?
Solved

Cron job for clearing database table?

Posted on 2009-04-08
9
Medium Priority
?
388 Views
Last Modified: 2013-12-26
I need to create a cron job that will clear the cache table in my mySQL db every week. What's the simplest way to do this?
0
Comment
Question by:cwdo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 21

Expert Comment

by:K V
ID: 24096769

Add following line to shell file your-file.sh
mysql -uroot -pnitin -e "your query goes here"

shell> crontab -e
 *  *  *  *  1 sh /path/to/your-file.sh
ref: http://en.wikipedia.org/wiki/Cron
0
 
LVL 21

Expert Comment

by:K V
ID: 24096791
#############
Damn !!
Calling Zone Expers - please again obfuscate my credentials !!
Thanks in advance
#############

By cache table iIf you mean query cache then query will be "reset query cache".
0
 

Author Comment

by:cwdo
ID: 24097076
If the database name is "db" and the table name is "cache", what would the query be?
0
Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

 
LVL 21

Expert Comment

by:K V
ID: 24097736
If by cache you mean a table you can truncate it. Your query will be:
truncate table db.cache;
0
 

Author Comment

by:cwdo
ID: 24098508
Thanks for the quick response! It appears to work now except I can't access the db. How would I handle access to the db? Could you give an example of how to access the db via username and password in either an .sh file, or .php file? If you could provide a full example of the above in either an .sh or .php file, including sample username and password commands to access the db that would be greatly appreciated.
0
 
LVL 21

Expert Comment

by:K V
ID: 24098564
Your Commad 'd be:
mysql -uUSER -pPWD -hDB-HOSTNAME/IPADDRESS  -e "truncate table db.cache"

The USER should have proper privileges to access the server if you're connecting remotely.
You can give permission using grant command.
http://dev.mysql.com/doc/refman/5.1/en/grant.html
0
 

Author Comment

by:cwdo
ID: 24149748
How exactly do I enter the hostname/IP address, and do I need both? Right now I have
-hdatabasename-localhost and I get the message "unknown MySQL server". Should there be spaces after -u, -p, -h, etc.?
0
 
LVL 21

Accepted Solution

by:
K V earned 2000 total points
ID: 24154532
mysql -uUSER -pPWD -hDB-HOSTNAME/IPADDRESS
consider your user name is 'root' and password is 'secret' and mysql-server's ip address is '10.10.10.10':
you will do:

mysql -uroot -psecret -h10.10.10.10  -e "truncate table db.cache"
0
 

Author Closing Comment

by:cwdo
ID: 31568004
Thanks! Also got this to work by linking to a PHP file containing the mySQL commands, incidentally.
0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question