Solved

Cron job for clearing database table?

Posted on 2009-04-08
9
386 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:theGhost_k8
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:theGhost_k8
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 21

Expert Comment

by:theGhost_k8
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:theGhost_k8
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:
theGhost_k8 earned 500 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

Independent Software Vendors: 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!

Question has a verified solution.

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

It is possible to boost certain documents at query time in Solr. Query time boosting can be a powerful resource for finding the most relevant and "best" content. Of course the more information you index, the more fields you will be able to use for y…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
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…

738 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