Clearing a MySQL Database daily
Hi again
I have no clue how to do this, so I need some help
I have a MySQL database that I need emptied every day... 12 AM it empties every single day
How would I do this?
I need as detailed instructions as I can get
Thanks
mhalbrook posted this at 09:33 — 17th March 2002.
They have: 5 posts
Joined: Mar 2002
How many tables? if it's just one table, setting a cron job to do a mysql command to "delete from table" should work, I don't have my book handy to verify that. IF it's multiple tables I'd say write a PHP/Perl script to issue the command on each table and run that script as a cron job.
mairving posted this at 12:35 — 17th March 2002.
They have: 2,256 posts
Joined: Feb 2001
Yes, I guess it is how you define emptied. If you mean emptied as in data deleted, you could as mhalbrook says write a script and assign the script to a cron job. If you want to keep the data from the table, you could write a script that would dump the db, drop the db and recreate the db. Or if it was just one table that you wanted to save data from, then you could do a SELECT * from table into local outfile "/home/username/dailydata.txt".
PHP or PERL would work as the scripting language.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
nike_guy_man posted this at 16:04 — 17th March 2002.
They have: 840 posts
Joined: Sep 2000
By emptied, I mean that there are no more rows in the DB
What is a cron job and how do they work?
mairving posted this at 18:43 — 17th March 2002.
They have: 2,256 posts
Joined: Feb 2001
By emptied do you want to keep the data that is in the database? If so you would have to output it to a file or dump the database. You could have problems if you have some auto-increment fields in your database keeping the auto_incremented number from repeating.
A cron job is about as old as the hills. It is a service that runs on 'nix based systems that allows you to run system commands on a timed basis. You can schedule it to run daily, hourly, every 15 minutes, etc. Most hosts would have a way to set up a cron via the control panel. If you have shell access, then you can pretty easily setup a cron. Here is an article about setting up a cron job for a php script.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
nike_guy_man posted this at 18:49 — 17th March 2002.
They have: 840 posts
Joined: Sep 2000
Emptied means delete everything, no coming back
No need for a backup, everythign is saved twice, one keeps daily records, another keeps lifetime records, daily are only used once
I'll work with making a cron job
Thanks
nike_guy_man posted this at 00:43 — 18th March 2002.
They have: 840 posts
Joined: Sep 2000
What should the PHP file look like and what should its permissions be?
I looked at that site and I'm still confused...
mairving posted this at 02:31 — 18th March 2002.
They have: 2,256 posts
Joined: Feb 2001
It would be pretty standard fare. Something like:
<?php
mysql_connect(localhost,username,password) or die(\"Cannot connect to the database.<br>\" . mysql_error());
mysql_select_db(database_name) or die(\"Cannot select the database.<br>\" . mysql_error());
$sql = \"DELETE from tablename\";
$query = mysql_query($sql) or die(\"Cannot query the database.<br>\" . mysql_error());
?>
Do make sure that this file is located below document root, though. I don't think permissions will matter that much in this case.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
nike_guy_man posted this at 03:03 — 18th March 2002.
They have: 840 posts
Joined: Sep 2000
OK i created that file... now what?
mhalbrook posted this at 07:44 — 18th March 2002.
They have: 5 posts
Joined: Mar 2002
Now you have to find out from your host how to set up a cron job. Who is your host, do they give you a control panel?
mairving posted this at 13:14 — 18th March 2002.
They have: 2,256 posts
Joined: Feb 2001
If you have shell access to the system, you setup a cron job using crontab. Here is how to setup a cron job;
crontab -e opens crontab editor
Crontab has this format:
a b c d e f
where...
a = The minute after the hour that you want it done
b = The hour you want it done (Military Time)
c = Day of the Month
d = Month of the Year
e = Day of the week
f = 'command'
So something like 0 0 * * * /home/username/scriptname
would run this script at midnight since it the first value is 0 (minutes), the second value is 0 (hour in military time). The third, fourth and fifth values are * (NULL) which means they are ignored. The sixth value is the script name. It does have to be executable.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
nike_guy_man posted this at 23:23 — 19th March 2002.
They have: 840 posts
Joined: Sep 2000
OK... I am my own host and have a server hosted in my location.
I set that up as you said Mairving and I'm getting cron emails every 5 minutes telling me that cron.weekly cannot run and that the script doesn't work either
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.