[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cron job for clearing database table?

Posted on 2009-04-08
9
Medium Priority
?
391 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 2 hours left to enroll

829 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