mariadb : user log & logrotate Hint

# vi /etc/my.cnf

[mysqld]

general-log

general-log-file=/var/log/mariadb/queries.log

log-output=table,file

[mysqld_safe]

long_query_time = 3

#log=/var/log/mariadb/mysqld.log

log-slow-queries=/var/log/mariadb/mysqld-slow.log

log-error=/var/log/mariadb/mariadb.log

[mysqladmin]

password = XXXXXXX

user = root

# vi /etc/logrotate.d/mariadb

/var/log/mariadb/mariadb.log {

create 640 mysql mysql

notifempty

daily

rotate 3

missingok

compress

postrotate

if test -x /usr/bin/mysqladmin && \

/usr/bin/mysqladmin ping &>/dev/null

then

/usr/bin/mysqladmin flush-logs

endscript

}

/var/log/mariadb/queries.log {

create 640 mysql mysql

notifempty

daily

rotate 3

missingok

compress

postrotate

if test -x /usr/bin/mysqladmin && \

/usr/bin/mysqladmin ping &>/dev/null

then

/usr/bin/mysqladmin flush-logs

endscript

}

# vi /root/bin/mysqlUserLog.sh

#!/bin/sh

logrotate=7

password=XXXX

mysql -uroot -p$password -e "CREATE TABLE mysql.general_log_root ENGINE=CSV DEFAULT CHARSET=utf8 (SELECT * FROM mysql.general_log WHERE 1=0) "

mysql -uroot -p$password -e "delete from mysql.general_log_root where event_time < DATE_SUB(NOW() , INTERVAL $logrotate day) "

mysql -uroot -p$password -e "insert into mysql.general_log_root select * from mysql.general_log where user_host like '[root]%' or user_host like 'root%' "

for db in db1 db2 db3

mysql -uroot -p$password -e "CREATE TABLE $db.general_log ENGINE=CSV DEFAULT CHARSET=utf8 (SELECT * FROM mysql.general_log WHERE 1=0) "

mysql -uroot -p$password -e "delete from $db.general_log where event_time < DATE_SUB(NOW() , INTERVAL $logrotate day) "

mysql -uroot -p$password -e "insert into $db.general_log select * from mysql.general_log where user_host like '[$db]%' or user_host like '$db%'"

done

mysql -uroot -p$password -e "truncate mysql.general_log"

# chmod 755 /root/bin/mysqlUserLog.sh

# vi /etc/cron.d/mysql_dbname

5 * * * * root /root/bin/mysqlUserLog.sh

by owl