UNIX for Dummies Questions & Answers

hi

i'm a bit stuck trying to find the correct syntax in order to run a cron job which just truncates all info from a single table in my database

i have a main database - db1
and i have a table in that database - userips

i wish to create a cron job that truncates or removes all the info from the userips table everyday

can anyone help me in the correct syntax in order to create a cron job that runs every day at a certain time point

thanks in advance

What database are you using?

hi i'm using mysql

Here is a script to run a sql command just replace the existing query with your truncate. and add script to cron:

Code:

TABLE_NAME=sometable
USER_NAME=someuser
IP_ADDR=localhost
PASSWORD=somepassword

somevar=`echo "select * from <table_name> where <condition>" | mysql -h $IP_ADDR -u $USER_NAME -p$PASSWD $TABLE_NAME `

echo $somevar

hi just been playing around with the code listed

got around to this

Code:

TABLE_NAME=userips
USER_NAME=user
IP_ADDR=localhost
PASSWORD=password
somevar=`echo "TRUNCATE TABLE userips" | mysql -h $IP_ADDR -u $USER_NAME -p $PASSWORD $TABLE_NAME `
echo $somevar

just a few questions though

do i not need to add in my database name somewhere in there as i have around 20 databases on this server

also the somevar= part , is that looking correct or should that be placed after the mysql command

also finally what type of file do i save this as , should it be a .sh file, then do you no the correct syntax i should enter in cron to enable this to run every day



i used something like this to backup my database every day

but i'm not to sure how to modify this in order to change it to truncate or delete info from just one table

the userips table fills up very quickly with lots of ips(10,000 per day), thats why i need to delete hem every day

its a pain having to login to phpmyadmin every day to just click the truncate table button on the usrips table

You shouldnt have to select the database table, ASSUMING the username you are using is only has access to the database you want the script to truncate. Saying that I would NOT use a master login with access to all your databases.

$somevar wil contain what mysql returned when the sql was run.

your contab woud be something like:

Code:

0 0 * * * /bin/sh /path/to/your/script >> /path/to/some/log

this will output what the output was when running the script to path/to/some/log

thanks for the help ikon you have cleared up lots of questions and ponted me in the right dirrection

i will give that a try like you have mentioned, just on thing can i simply name the script with the .sh file ending, thanks for taking the time to answer my questions