Différences
Ci-dessous, les différences entre deux révisions de la page.
Prochaine révision Les deux révisions suivantes | |||
tuto:ipasserelle:web:squid_log_mysql [09/10/2012 10:35] dani créée |
tuto:ipasserelle:web:squid_log_mysql [09/10/2012 10:46] dani [Script de journalisation] |
||
---|---|---|---|
Ligne 242: | Ligne 242: | ||
--squidguard --squidlogfile=/ | --squidguard --squidlogfile=/ | ||
</ | </ | ||
+ | |||
+ | ===== Rotation et compression des tables ===== | ||
+ | |||
+ | La journalisation dans une base de donnée peut consommer **beaucoup** d' | ||
+ | |||
+ | <file bash squid-rotate.sh> | ||
+ | #!/bin/bash | ||
+ | |||
+ | DB_HOST=localhost | ||
+ | # COnservation des journaux en nombre de jours | ||
+ | RETENTION=365 | ||
+ | SQL_DB=squid_log | ||
+ | TABNAME=" | ||
+ | SQLCMD=" | ||
+ | MONTH=$(date +%m) | ||
+ | YEAR=$(date +%Y) | ||
+ | |||
+ | # We rotate on the first day of a new month | ||
+ | if [ " | ||
+ | MONTH=12 | ||
+ | else | ||
+ | MONTH=$(($MONTH-1)) | ||
+ | fi | ||
+ | |||
+ | # Pad with 0 | ||
+ | MONTH=$(printf " | ||
+ | |||
+ | DATE=$MONTH" | ||
+ | |||
+ | for T in ${TABNAME}; do | ||
+ | # create table 0 | ||
+ | echo " | ||
+ | |||
+ | # Rotate table | ||
+ | echo "FLUSH TABLES ${T}; RENAME TABLE ${T} TO ${T}_$DATE; RENAME TABLE ${T}_0 TO ${T}" | ${SQLCMD} | ||
+ | |||
+ | # Drop _0 table if we rotate more than two times a month | ||
+ | if echo " | ||
+ | echo "DROP TABLE ${T}_0;" | ||
+ | fi | ||
+ | |||
+ | #compress 2 | ||
+ | cd / | ||
+ | echo "FLUSH TABLE ${T}_${DATE};" | ||
+ | myisampack -s " | ||
+ | myisamchk -s -rq --sort-index --analyze " | ||
+ | echo "FLUSH TABLE ${T}_${DATE}" | ||
+ | done | ||
+ | |||
+ | # Now check existing table to drop olds ones | ||
+ | for T in $(echo "show tables" | ||
+ | TMONTH=$(echo $T | perl -pe ' | ||
+ | TYEAR=$(echo $T | perl -pe ' | ||
+ | # Drop table if older than configured retention | ||
+ | if [ " | ||
+ | echo "DROP TABLE $T;" | $SQLCMD | ||
+ | fi | ||
+ | done | ||
+ | |||
+ | </ |