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:
    # Will log MySQL Errors Including Startup Errors
    log-error=/var/log/mysql/mysqld.log
    # Will log MySQL queries, This log will grow quickly
    log=/var/log/mysql/mysql.log
    # Will log MySQL queries that take a long time (in this case over 1 second as specified below)
    log-slow-queries = /var/log/mysql/mysql-slow.log
    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:
    [root@dev ~]# mkdir /var/log/mysql
    [root@dev ~]# touch /var/log/mysql/mysql.log
    [root@dev ~]# touch /var/log/mysql/mysqld.log
    [root@dev ~]# touch /var/log/mysql/mysql-slow.log
    [root@dev ~]# chown -R mysql.mysql /var/log/mysql
    [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:
    [root@dev ~]# /etc/init.d/mysqld restart
    Stopping MySQL:  [  OK  ]
    Starting MySQL:  [  OK  ]
    [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:

    [root@dev mysql]# tail -f mysql.log
    FROM wp_posts WHERE MONTH(post_date) = '12'
    AND YEAR(post_date) = '2009'
    AND post_type = 'post' AND post_status = 'publish'
    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:
    [root@dev ~]# /usr/bin/mysqladmin flush-logs
    /usr/bin/mysqladmin: connect to server at 'localhost' failed
    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:

    [mysqladmin]
    user               = root
    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:

    [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:
    /var/log/mysql/*.log {
    create 644 mysql mysql
    notifempty
    daily
    rotate 5
    missingok
    nocompress
    sharedscripts
    postrotate
    # run if mysqld is running
    if test -n "`ps acx|grep mysqld`"; then
    /usr/bin/mysqladmin flush-logs
    fi
    endscript
    }
    

    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.


List Price: $50.00 USD
New From: $14.00 USD In Stock
Used from: $2.34 USD In Stock


List Price: $39.99 USD
New From: $20.94 USD In Stock
Used from: $0.58 USD In Stock

DeliciousStumbleUponDiggTwitterFacebookRedditLinkedInEmail
Tags: , , , , , , , , , , , , , , ,
20 Responses to “Configure Logrotate To Rotate And Flush MySQL Logs Without A Password”
  1. Chris says:

    In step 2 you need to set 774 instead of 664 at the folder mysql to let mysql open that folder ;)

    [Reply]

    alex Reply:

    Hello Chris,

    I must have made a mistake in the article but as long as you have mysql own the folder then you should only need 755 to accomplish the goal of writing mysql logs to this new directory. Regardless 774 would also work so thanks for taking the time to leave a comment with this information. :)

    Thanks.
    alex

    [Reply]

  2. Rodger says:

    Thanks for the article, not sure why my existing logrotate script was not working, but changed to your version and it works great…

    [Reply]

    alex Reply:

    Hello Roger,

    No problem. Thanks for taking the time to leave feedback.

    Thanks.
    alex

    [Reply]

  3. Luigi says:

    Hi,
    Thanks for this guide, very useful!
    I think thah also the logrotate script must be changed.
    the line create 644 mysql mysql have to became create 755 mysql mysql

    Thanks
    Luigi

    [Reply]

    alex Reply:

    Hello Luigi,

    No problem at all. I actually meant 644 as this generates the files themselves with permissions of 644 which would be rw by the mysql user and readable by all. The directory that contains the log files can be set to 755. Thanks for taking the time to leave feedback.

    Thanks.
    alex

    [Reply]

  4. Mobile Money Machines uu says:

    How’s things, I receive a 504 Gateway Timeout error when I browse this page. This sometimes means the host did not get a timely response. I figured yuo may like to know. Thanks Brian

    [Reply]

    alex Reply:

    Hello.

    Not sure I understand what you are saying… you mean the page this article is located on????

    Thanks.
    alex

    [Reply]

  5. Rick says:

    I think you should really consider adding the following logrotate option:
    sharedscripts

    Without this option, the flush log command will run once for every log it comes across and I don’t believe you want to do that (could cause data loss during the rotate).

    [Reply]

    alex Reply:

    Hello Rick,

    Thanks for mentioning. I overlooked this when writing the article so appreciate you taking the time to correct my mistake.

    Thanks!
    alex

    [Reply]

    alex Reply:

    Hello Rick,

    PS. I did add sharedscripts to the logrotate configuration file noted in the article above.

    Thanks.
    alex

    [Reply]

  6. Mohd Abrar Saleem says:

    Hello Rick,

    Thanks. A good article I deployed and it is working.

    Thanks.
    Abrar

    [Reply]

    alex Reply:

    Hello Mohd Abrar Saleem,

    No problem. Thanks for taking the time to leave feedback.

    Thanks.
    alex

    [Reply]

  7. Abrar says:

    Hi Alex,

    I deployed the above script on . Tried to check if it is working with the command ” logrotate -f mysql” found a new mysql general query log “query.log-20130119″ but after that it is not working, Is it because of the name of new file formed as “query.log-20130119″.
    So, what can be done to make it work.

    Below is the output of the file names.
    -rw-r—–. 1 mysql mysql 55K Jan 18 11:48 mysqld.log.1
    -rw-r–r– 1 mysql mysql 0 Jan 18 11:53 mysqld.log
    -rw-r–r– 1 mysql mysql 865 Jan 18 11:53 query.log.1
    -rw-r–r– 1 mysql mysql 0 Jan 19 03:19 query.log
    drwxr-xr-x. 5 mysql mysql 4.0K Jan 28 08:48 mysqldata
    -rw-r–r– 1 mysql mysql 18M Jan 28 09:00 query.log-20130119

    And below is the script.
    /home/mysql/*.log {
    create 644 mysql mysql
    notifempty
    daily
    rotate 5
    missingok
    nocompress
    sharedscripts
    postrotate
    # run if mysqld is running
    if test -n “`ps acx|grep mysqld`”; then
    /usr/bin/mysqladmin flush-logs
    fi
    endscript
    }

    Awaiting for your response.

    [Reply]

    alex Reply:

    Hello Abrar,

    The issue is exactly as you note… the filename for query.log-20130119 does not fit the naming schema required by the logrotate configuration. You have two options which I have noted below.
    1. Modify whatever is outputting the query.log-DATE log files to output query.log only and let logrotate handle the log rotation which will set the date in archives.
    2. Add a second logrotate configuration file that will handle the query log files. It is going to be a pain for this option though as the query log files are not consistent and will always get rotated because of name changing by date.
    I would suggest figuring out what is outputting query.log-DATE and fix that up. Is it possible that was a one time output from MySQL queries or something?

    Thanks.
    alex

    [Reply]

  8. Abrar says:

    When I use the below command I get this output, and after first run logs are getting formed with date

    [root@DB2 logrotate.d]# logrotate -d -f mysql
    reading config file mysql
    reading config info for /home/mysql/*.log

    Handling 1 logs

    rotating pattern: /home/mysql/*.log forced from command line (5 rotations)
    empty log files are not rotated, old logs are removed
    considering log /home/mysql/mysqld.log
    log does not need rotating
    considering log /home/mysql/query.log
    log does not need rotating
    not running postrotate script, since no logs were rotated

    [Reply]

    alex Reply:

    Hello Abrar,

    Not sure I understand the question in this second post. See my first answer above for how to handle the query.log files not getting picked up by logrotate. The part in the logrotate configuration file that looks for the file names is the very first line or “/var/log/mysql/*.log {” and as you can see “*.log” does not match “query.log-20130119″. So you could match it using *log* however then the archived logs would still get rotated, etc. and that would not be good.

    Thanks.
    alex

    [Reply]

  9. Al says:

    Great tutorial on Mysql log rotation configuration.
    Thanks for taking the time to write it!

    [Reply]

  10.  
Leave a Reply

*Type the letter/number combination in the abvoe field before clicking submit.

*