Rotating log information that gets stored in a mysql database

Posted on 2013-10-16
Medium Priority
Last Modified: 2013-10-18
I set up an rsyslog server that logs to mysql using this guide:

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)

Question by:itnifl
LVL 13

Accepted Solution

dhoffman_98 earned 2000 total points
ID: 39576485
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.

Author Closing Comment

ID: 39584285
Seems like this works.

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Virtualization software lets you run different versions of Windows, Ubuntu Linux and other versions of Linux all at the same time, rather than running each one directly from your computer's hard drive.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question