Admin Linux ... MySQL DBA

Rotating MySQL Log Files on Linux. A. Check logrotate must on daily cron #less /etc/cron.daily/logrotate B. Check on logrotate.d there must be mysql #less /etc/logrotate.d/mysql Its default Content is: # by setting the variable "err-log" # in the [safe_mysqld] section as follows: # # [safe_mysqld] # err-log=/var/lib/mysql/mysqld.log # # If the root user has a password you have to create a # /root/.my.cnf configuration file with the following # content: # # [mysqladmin] # password = # user= root # # where " " is the password. # # ATTENTION: This /root/.my.cnf should be readable ONLY # for root ! /var/lib/mysql/mysqld.log { # create 600 mysql mysql notifempty daily 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

First enable slow query logging, then generate a slow query and finally look at the slow query log. mysqldumpslow This program parses and summarizes a 'slow query log'. -v verbose -d debug -s=WORD what to sort by (t, at, l, al, r, ar etc) -r reverse the sort order (largest last instead of first) -t=NUMBER just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n=NUMBER abstract numbers with at least n digits within names -g=WORD grep: only consider stmts that include this string -h=WORD hostname of db server for *-slow.log filename (can be wildcard) -i=WORD name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time eg. 1. mysqldumpslow -t 10 /var/lib/mysql/mysql-slow.log.1 > /tmp/top_ten_slow_query.tx