Rotating log information that gets stored in a mysql database

I set up an rsyslog server that logs to mysql using this guide:
http://www.unixmen.com/install-and-configure-rsyslog-in-centos-6-4-rhel-6-4/

The database is stored on a 10GB Logical Volume that got filled up. I have added another disk to the Volume Group and extended the logical volume. Space is no longer an issue, but I would like to rotate the logging information logged in the mysql database so that the disk does not fill up again. What is the best approach in doing so?

This is how it looks like:

mysql> show tables;
+------------------------+
| Tables_in_rsysdb       |
+------------------------+
| SystemEvents           |
| SystemEventsProperties |
| logcon_charts          |
| logcon_config          |
| logcon_dbmappings      |
| logcon_fields          |
| logcon_groupmembers    |
| logcon_groups          |
| logcon_savedreports    |
| logcon_searches        |
| logcon_sources         |
| logcon_users           |
| logcon_views           |
+------------------------+
13 rows in set (0.00 sec)

mysql>
LVL 2
itniflAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dhoffman_98Commented:
Without knowing what your fields are in each table, I'm assuming that one of the fields is a timestamp. Depending on the format of your timestamp, you could execute a query like the following on each of your tables:

delete from sometable where timestamp < subdate(curdate(), 30);

The number 30 represents the number of days after which items will be deleted. This allows you to set a finite amount of time to keep log entries, and roll off the ones that exceed that time.

You can create a cron task that runs every night and executes a command like that on each of your tables.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
itniflAuthor Commented:
Seems like this works.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.