MySQL慢查询日志自动分割

MySQL的慢查询日志是揪出"数据库杀手"的好方法。美中不足的是，当我们解决了一个慢查询以后，通过mysqlsla再次分析慢查询日志，并不知道效果如何，因为默认是不会按照日期分割慢查询日志的。

怎么处理？如果不是经常有慢查询，手工做一下即可。MySQL的官方手册写的很清楚：

 For example, if the general and slow query logs are named mysql.log and mysql-slow.log, you can use a series of commands like this:

shell> cd mysql-data-directory shell> mv mysql.log mysql.old shell> mv mysql-slow.log mysql-slow.old shell> mysqladmin flush-logs 

At this point, you can make a backup of mysql.old and mysql-slow.log and then remove them from disk.

 如果你的数据库慢查询太多，囧，可以考虑自动的方法（就是用Logrotate来替你定时完成上面的几个命令，关键是怎么在执行mysqladmin不用输入数据库密码）,参见这里（很详细）

担心哪一天不能访问了,在这里备份一下:)

If you have the resources (CPU + RAM) available on your server then its can be a great troubleshooting tool if you enable MySQL logging which includes server messages, SQL query logs, and slow query logs. If you do not have the resources I would suggest only enable minimal logging such as only server messages and the slow query log since enabling all queries to be written to a file can become expensive rather quickly. Below I discuss enabling three different types of MySQL logging, adding a MySQL configuration file to logrotate, and configuring root to run mysqladmin commands without having to type the password out each time.

Enable MySQL Logging:

First we are going to enable the various types of MySQL logging. I would keep an eye on the query log just in case it becomes to resource intensive. Below I will display the configuration command to enable each type of logging including creating the file and directory that MySQL will log to.

1. Modify The my.cnf MySQL Config File: First lets add the statements to the MySQL configuration file which by default is typically /etc/my.cnf. There will probably be a minimal set of configuration items in this file if you have never modified it. Add the below lines which include comments above each one to explain what will be logged.

   Configure my.cnf To Log Queries, Errors, Slow Queries:

   view plaincopy to clipboardprint?

   1. # Will log MySQL Errors Including Startup Errors 
   2. log-error=/var/log/mysql/mysqld.log 
   3. # Will log MySQL queries, This log will grow quickly 
   4. log=/var/log/mysql/mysql.log 
   5. # Will log MySQL queries that take a long time (in this case over 1 second as specified below) 
   6. log-slow-queries = /var/log/mysql/mysql-slow.log 
   7. long_query_time = 1 

   The above configuration lines should go under the main section of MySQL which should be "[mysqld]" at the beginning of the file. The next section doesn't start until "[mysqld_safe]" so as you can see there are typically two sections and they are divided by these headings.

2. Create MySQL Log Directory: I like to try and split services up in the log directory so I can keep track of things easier so in this case we are going to do the same. In the command list below we will be creating a directory, creating blank files, and then making sure everything has the right permissions and is owned by the proper user which should be the "mysql".

   Create MySQL Log Directory & Log Files:

   view plaincopy to clipboardprint?

   1. [root@dev ~]# mkdir /var/log/mysql 
   2. [root@dev ~]# touch /var/log/mysql/mysql.log 
   3. [root@dev ~]# touch /var/log/mysql/mysqld.log 
   4. [root@dev ~]# touch /var/log/mysql/mysql-slow.log 
   5. [root@dev ~]# chown -R mysql.mysql /var/log/mysql 
   6. [root@dev ~]# chmod -R 664 /var/log/mysql/ 

   So notice that all of the new files will be owned the mysql user in the mysql group and they will allow reading from all users and writing from the mysql user and members of the mysql group.

3. Restart MySQL & Verify Logging: Now lets restart MySQL and verify the logging is operational. You can restart MySQL by issuing the below command.

   Restart MySQL:

   view plaincopy to clipboardprint?

   1. [root@dev ~]# /etc/init.d/mysqld restart 
   2. Stopping MySQL:  [  OK  ] 
   3. Starting MySQL:  [  OK  ] 
   4. [root@dev ~]# 

   If MySQL fails to restart and this is a live server quickly make a backup of my.cnf, remove the changes,and start MySQL so your downtime is minimal. If you follow the directions above you should be fine but I wanted to mention just in case there were any issues with the restart. Now visit some web pages on your site to generate some of the MySQL queries to the database and then view the log files to verify logging is functional. You can view the log files in real time by using the tail command as displayed below.

   View New MySQL Log Files In Real Time With Tail:

   view plaincopy to clipboardprint?

   1. [root@dev mysql]# tail -f mysql.log 
   2. FROM wp_posts WHERE MONTH(post_date) = '12' 
   3. AND YEAR(post_date) = '2009' 
   4. AND post_type = 'post' AND post_status = 'publish' 
   5. AND post_date < '2009-12-20 12:52:13' 
4. Configure Root User MySQL Access: Now in preparation for the logrotate mysql configuration file you want to add a file to the root users home directory so that user can flush the log files without having to use a username and password every single time. If you attempt to flush the MySQL logs before you add the below configuration file you will receive an error similar to the below.

   Error When Running mysqladmin flush-logs Without A Password:

   view plaincopy to clipboardprint?

   1. [root@dev ~]# /usr/bin/mysqladmin flush-logs 
   2. /usr/bin/mysqladmin: connect to server at 'localhost' failed 
   3. error: 'Access denied for user 'root'@'localhost' (using password: NO)' 

   Use your favorite editor such as vi as the root user  to create a file by the name of .my.cnf in /root/. Add the contents below to this file but of make sure to modify the username and password in the example below to include a MySQL username and password that has access to run "mysqladmin flush-logs".

   New /root/.my.cnf File:

   view plaincopy to clipboardprint?

   1. [mysqladmin] 
   2. user               = root 
   3. password        = changeme 

   Make sure that you set the proper permissions for the above file once you have created it so that no other users on the server can read the file contents and find out what the MySQL root password is. To set the proper permissions use the below command.

   Set Permissions On /root/.my.cnf:

   view plaincopy to clipboardprint?

   1. [root@dev ~]# chmod 600 /root/.my.cnf 

   Now you can run the "mysqladmin flush-logs" command as the root user without having to enter a username or password and without receiving an error.

5. Add Logrotate MySQL Configuration File: Now add the below configuration contents to a new file named "mysql" in the "/etc/logrotate.d/" directory.

   The mysql Logrotate Configuration File Contents:

   view plaincopy to clipboardprint?

   1. /var/log/mysql/*.log { 
   2. create 644 mysql mysql 
   3. notifempty 
   4. daily 
   5. rotate 5 
   6. missingok 
   7. nocompress 
   8. postrotate 
   9. # run if mysqld is running 
   10. if test -n "`ps acx|grep mysqld`"; then 
   11. /usr/bin/mysqladmin flush-logs 
   12. fi 
   13. endscript 
   14. } 

   The basis of the file is that it will rotate any files in the MySQL directory that end with ".log". Once the files are rotated the new files will be generated by the mysql user with permissions of 644. The files will not be rotated by Logrotate unless they have contents. The files will be rotated daily and five copies will be retained. Logrotate is not going to panic if there is a file missing and when all is said and done the root user will run "mysqladmin flush-logs" to flush out the log files so logs will continue to write to the proper files.

You are now able to troubleshoot MySQL, web applications, and many other things much easier. Remember to keep an eye on mysql.log to make sure that the file does grow out of hand. Also be sure to check back on the log files after a couple days just to make sure that logrotate is functioning properly. If you don't have logrotate installed and configured yet you can check out the manpage here on QD.