how to run cron job to truncate database table

wingchun22 · #1 (**permalink**) 12-02-2008

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

Ikon · #2 (**permalink**) 12-02-2008

What database are you using?

wingchun22 · #3 (**permalink**) 12-02-2008

hi i'm using mysql

Ikon · #4 (**permalink**) 12-02-2008

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

wingchun22 · #5 (**permalink**) 12-02-2008

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

Quote:

0 0 * * * /usr/bin/mysqldump -u user database -p pass > /var/www/vhosts/mysite.com/httpdocs/sql-backup/dbbackup.sql

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

Ikon · #6 (**permalink**) 12-02-2008

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

wingchun22 · #7 (**permalink**) 12-02-2008

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

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Creating a table (graphic not database)	dlam	UNIX for Dummies Questions & Answers	2	11-21-2008 05:58 AM
Delete database table based on months using script.	Maverick79	UNIX for Advanced & Expert Users	0	08-30-2007 02:27 AM
ccall database and collect data from one table	rinku	Shell Programming and Scripting	0	05-28-2007 01:16 AM
Upload of the images from the folder to the Database table	shashisaini24	UNIX for Advanced & Expert Users	0	11-29-2006 06:03 AM
export table from oracle database	inquirer	Shell Programming and Scripting	1	06-24-2003 09:48 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: