• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

Cron job for clearing database table?

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
cwdo
Asked:
cwdo
  • 5
  • 4
1 Solution
 
K VDatabase ConsultantCommented:

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
 
K VDatabase ConsultantCommented:
#############
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
 
cwdoAuthor Commented:
If the database name is "db" and the table name is "cache", what would the query be?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
K VDatabase ConsultantCommented:
If by cache you mean a table you can truncate it. Your query will be:
truncate table db.cache;
0
 
cwdoAuthor Commented:
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
 
K VDatabase ConsultantCommented:
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
 
cwdoAuthor Commented:
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
 
K VDatabase ConsultantCommented:
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
 
cwdoAuthor Commented:
Thanks! Also got this to work by linking to a PHP file containing the mySQL commands, incidentally.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now