Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • 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
 
theGhost_k8Database 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
 
theGhost_k8Database 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
theGhost_k8Database 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
 
theGhost_k8Database 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
 
theGhost_k8Database 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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