Setting log rotation in Centos servers – How to linux

Setting log rotation in Centos servers:

Logrotation helps to reduce disk size by rotating log files and compressing them. It helps to prevent over sizing of log files and disk space issue by removing old logs data, creating small chunk of specific numbers of log files.

 

Default log rotate file is located in /etc/logrotate.conf

 

# see "man logrotate" for details
# rotate log files weekly
weekly
# keep 4 weeks worth of backlogs
rotate 4
# create new (empty) log files after rotating old ones
create
# use date as a suffix of the rotated file
dateext
# uncomment this if you want your log files compressed
#compress
# RPM packages drop log rotation information into this directory
include /etc/logrotate.d
# no packages own wtmp and btmp -- we'll rotate them here
/var/log/wtmp {
 monthly
 create 0664 root utmp
 minsize 1M
 rotate 1
}
/var/log/btmp {
 missingok
 monthly
 create 0600 root utmp
 rotate 1
 }

Linux comes with defualt file settings  for many modules which can be found inside /etc/logrotate.d/ . For example if we want to do log rotation for mysql we can find default mysql file called mysqld,  so full path is : /etc/logrotate.d/mysqld.

 

Do the vi to edit the file and uncomment settings:

 

# ATTENTION: This /root/.my.cnf should be readable ONLY
 # for root !
# Then, un-comment the following lines to enable rotation of mysql's log file. default name for mysql log is mysqld.log so the setting contains log rotation settings for mysqld.log file. Do check your my.cnf file  of mysql settings mostly located in /etc/my.cnf and make sure log path is /var/log/mysqld.log. We can add multiple log files like error log , query log e.t.c in same settings. These settings can also be copied to my.cnf file or can be added as seperate file in /etc/logrotate.d/ folder.

/var/log/mysqld.log {
 create 640 mysql mysql
 notifempty
 weekly
 rotate 3
 missingok
 compress
 postrotate
 # just if mysqld is really running
 if test -x /usr/bin/mysqladmin && \
 /usr/bin/mysqladmin ping &>/dev/null
 then
 /usr/bin/mysqladmin flush-logs
 fi
 endscript
 }

 

Settings options:

create: mode owner group: Create log file with given mode and user/group settings.

notifempty :
Do not rotate the log if it is empty

weekly : Log files are rotated if the current weekday is less then the weekday of the last rotation or if more then a week has passed since the last rotation

rotate 3 : Log files are rotated 3 times before being removed or mailed to the address specified in a mail directive. If count is 0, old versions are removed rather then rotated

missingok : If the log file is missing, go on to the next one without issuing an error message.

compress : Old versions of log files are compressed with gzip to save disk space.

postrotate: start of script.

endscript: The lines between postrotate and endscript (both of which must appear on lines by themselves) are executed after the log file is rotated. These directives may only appear inside a log file definition. In our case we are checking is mysql is running and flushing logs.

 

More setting options can be found from http://linuxcommand.org/man_pages/logrotate8.html

 

 

Remove spam / duplicate MySQL records from MySQL tables.

Remove MySQL duplicate and spam content from MySQL table:

 

1) Remove duplicate content:

Create a tmp table and create a unique index to the column. then copy the unique content form your table to tmp table.

create table tmp like `table`;
ALTER TABLE `tmp` ADD UNIQUE INDEX(text1, text2, text3, text4, text5, text6);
insert IGNORE into `tmp` select * from `table`;

delete duplicate content from your main table:

Delete from
`table`
where id not in (select id from tmp);

We can also do this with the main table it self, but if you data is too large it will take some time so i prefer above
way :

ALTER IGNORE TABLE
`table`
ADD UNIQUE INDEX (mycolumn1, mycolumn2);

Once the process is complete remove unique index from the column.

2) Remove spam content:

i) Identify the spam first, for me spam contents are those who have certain keywords in their text. These can be links, foul words,
e.t.c so we can remove those lines by.

Delete from
`table`
where `mycolumn` REGEXP 'foul word|http://|buy|purchase'

ii) Remove non english charectors like chinese, russians e.t.c

Delete from
`table`
where `mycolumn` != CONVERT(myclumn USING ASCII)

 

iii)Remove very records which doesnt meet the text length standards, for examples comments column with just 3 char is not a valid comments.

Delete from `table` where LENGTH(mycloumn)<5;