summaryrefslogtreecommitdiffstats
path: root/contrib/delete_mysql
blob: c0d2ce8a612d47a15a2839e4c3d75ca8adb3bcf4 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#!/bin/bash

# Database maintance script which can be used for rsyslog
# and phplogcon default database schema.
# Michael Mansour suggested it to be included - thx!

# This program was original part of of PHPloghost
# Copyright (C) 2004 Tuatha de Dana
# some modifications for rsyslog by mmeckelein at 2007-08-08

# This program is free software; you can redistribute it and/or 
# modify it under the terms of the GNU General Public License 
# as published by the Free Software Foundation; either version 2 
# of the License, or (at your option) any later version.

# This program is distributed in the hope that it will be useful, 
# but WITHOUT ANY WARRANTY; without even the implied warranty of 
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License 
# along with this program; if not, write to the Free Software 
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111- 1307, USA.

# Change these variables to reflect your situation.
database=sqlsyslogd
export table=logs
sqluser=""
password=""

# Location of the mysql daemon:
mysqld=/usr/bin/mysql

# A couple of steps should be taken to maintain your database.
# If not, the number of messages will fill your database.
# By default, logs are deleted after they're one year old.
# Change this to meet your requirements.
# rsyslog's default database template use two date columns
# ReceivedAt and DeviceReportedTime. You can use either of 
# the two and in most cases it doesn't make a huge difference.
# See the property replacer doc at http://www.rsyslog.com/doc
# for details on the two dates.
SQL_DELETE="DELETE FROM $table WHERE ReceivedAt < CURDATE() - INTERVAL 1 year;"

# After a large amount of rows have been deleted, we should # optimize the table.
SQL_OPT="OPTIMIZE TABLE $table;";

$mysqld -u$sqluser -p$password -e"$SQL_DELETE" -D$database $mysqld -u$sqluser -p$password -e"$SQL_OPT" -D$database