Got it Harald, thanks. OK, I've got this working
now, so I'll do a quick overview of what I've
learned... for the archives:
I am setting up mysql with msyslog to be a
centralized logging server. My servers (Windows
& Red Hat) will send their logs to this mysql
box. To keep the mysql db from growing beyond a
certain size, I first estimate the size of my
average record and divide by the total byte size
I want to allow on disk, to determine about how
many records I want as a maximum (of course, one
must look at and consider the size of any indexes
for your db, also, and leave some extra room for
error). For this example, I'll say I've figured
out that I can allow a maximum of 250,000
records, and I have:
- a db named "msyslog"
- and a table within it named "syslogTB"
syslogTB has an autoincrement field "seq". What I
do is set up a cron job to run a scan of the
database periodically, and yank out all records
beyond 250,000. The cron job runs as <sql-user>
with password <PASSWORD> and calls a plain text
file "/root/delete_old.sql" for it's input. The
cron job will thus run this as its command:
/<path>/mysql -u <sql-user> --password=<PASSWORD>
msyslog < /root/delete_old.sql
...and in /root/delete_old.sql, there is only
this text (2 lines):
select (@aa:=seq) as low_seq from syslogTB order
by seq DESC limit 250000,1;
delete from syslogTB where seq < @aa;
Thanks everyone for your help!!
Scott
--- Harald Fuchs wrote:
> Scott H wrote:
> > That's fine. Thus if have "seq" as an
> > autoincrement field, and I wanted to stay
> around
> > say 1000 records, deleting the oldest
> records, I
> > would need to run a cron job that would
> somehow
> > nest or relate these 2 sql statements:
>
> > select (@aa:=seq) as low_seq from logtable
> order
> > by seq limit 1000,1
>
> > delete from logtable where seq < @aa
>
> > I've tried putting this into a subquery
> format
> > but no luck. But I'm a noob, so I keep
> trying,
> > thinking I might hit on the right syntax.
> Or, is
> > there some way to pull the value of "low_seq"
> > into an environment variable and use it in a
> > script file to run the 2nd statement?
>
> That's exactly what the two statements above
> do, except that MySQL has
> "user variables" (the @aa shown above) instead
> of "environment variables".
=====
--
To announce that there must be no criticism of the President, or that we are to stand
by the President, right or wrong, is not only unpatriotic and servile, but is morally
treasonable to the American public.
-- Theodore Roosevelt, 1918
.
__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
