cyclic DB clean up

[dobst]

Actually our db grows very fast especially the tables events and iftrack. iftrack gets bigger because several mac addresses are togglein between two interfaces, seems to be a load balancing or redundancy mechanism.

Is there a cyclic clean up which deletes unnessesary entries from the db to prevent garbage? How long are entries kept in the db?
solution: attached script
new in 17.07.2013:

• resolve dependencies for old devices and delete them (configs, links, logs, modules, monitoring, etc.)
• delete files of old devices, such as configs and rrds

[rickli]

It's not automated yet. However in System-Export you can select a query from the bottom of the export-selectbox to trim those tables. I know it's well hidden, but at least something...

[dobst]

Hi there,

I wrote a script to automate these steps. You can use it once or as a cronjob. Currently it does the followting steps:

deleting old events (1/7)
deleting old interface tracking information (2/7)
deleting interface tracking information of toggling interfaces (3/7)
deleting old ip tracking information (4/7)
deleting old devices (5/7)
deleting logs (6/7)
optimizing tables (7/7)

Parameters are fetched from the nedi config file.

12.06.2013:
New version as download available below.
Fixes: Script adapted to new db scheme of v1.0.8

[rickli]

Cool, I'll add it to the archive, if that's ok with you...tx!

[dobst]

Sure, you can add it. It's the first version, so any suggestions and improvments are welcome.

[dobst]

I added a new version with some bugfixes and improvements. See 3rd post.

[rickli]

tx, added your name too

[mduling]

Does this script need to be updated for 1.08?  I'm running the latest RC and it is giving me this:

optimizing tables (7/8)
+-----------------+----------+----------+-----------------------------------+
| Table           | Op       | Msg_type | Msg_text                          |
+-----------------+----------+----------+-----------------------------------+
| nedi.chat       | optimize | status   | Table is already up to date       |
| nedi.configs    | optimize | status   | Table is already up to date       |
| nedi.devdel     | optimize | Error    | Table 'nedi.devdel' doesn't exist |
| nedi.devdel     | optimize | status   | Operation failed                  |


Also, I was getting this message until I inserted a space between & and >>

./nedi_db_maintenance.sh: line 20: syntax error near unexpected token `>'
./nedi_db_maintenance.sh: line 20: `      mysql --host="$dbhost" --user="$dbuser" --password="$dbpass" "$dbname" --ssl -B -e "$sql" &>> $logfile'

[rickli]

Indeed devdel doesn't exist anymore and I've also fixed the space. Thanks!

[dobst]

Sorry for that. I tested the script against the current version v1.07. When we've set up our server to SLES 11 (OpenSuse at the moment) I will move to 1.08 and fix this. But I didn' get the space-error, maybe a different version of bash? Thanks for reporting.

[rickli]

Pity, NeDiO doesn't have bash. Add it with pkg_add, if you get this error on OBSD:
/var/nedi/contrib/nedi_db_maintenance.sh[85]: syntax error: `(' unexpected

With bash I get this one, but all looks good, if I enter FLUSH LOGS;RESET MASTER; on the SQL prompt:
ERROR 1227 (42000) at line 2: Access denied; you need the RELOAD privilege for this operation

But it actually looks really good! I'll probably just have to add it Again, many thanks for that dobst!

[dobst]

if I enter FLUSH LOGS;RESET MASTER; on the SQL prompt:
ERROR 1227 (42000) at line 2: Access denied; you need the RELOAD privilege for this operation

That's because of missing rights. Give nedi these rights:

Code: [Select]

GRANT RELOAD ON *.* TO 'nedi'@'localhost';

[rickli]

I can see that I'm wondering though, why it works when issuing those commands manually?

[dobst]

Maybe you're using diffrent users? When you start mysql from cli as root you've got diffrent rights as the nedi db user, so you need to give nedi the rights you have as root user. To avoid such mistakes.

[rickli]

Umpf, thought I was using the nedi user

However, I'm just trying to make everything a bit more secure and I'm not sure I want leave *.* access to the nedi user. A init has to be done with root as well and so this would be more consistent here too...