Technology Insights

Configure Logrotate To Rotate And Flush MySQL Logs Without A Password

22 responses

  1. Chris
    January 26, 2011

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

    Reply

    • alex
      January 26, 2011

      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
    July 6, 2011

    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
      August 26, 2011

      Hello Roger,

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

      Thanks.
      alex

      Reply

  3. Luigi
    October 6, 2011

    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
      October 7, 2011

      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
    November 2, 2011

    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
      November 2, 2011

      Hello.

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

      Thanks.
      alex

      Reply

  5. Rick
    March 12, 2012

    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
      March 12, 2012

      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
      March 12, 2012

      Hello Rick,

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

      Thanks.
      alex

      Reply

  6. Mohd Abrar Saleem
    January 17, 2013

    Hello Rick,

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

    Thanks.
    Abrar

    Reply

    • alex
      January 20, 2013

      Hello Mohd Abrar Saleem,

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

      Thanks.
      alex

      Reply

  7. Abrar
    January 27, 2013

    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
      January 28, 2013

      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
    January 27, 2013

    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
      January 28, 2013

      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
    October 18, 2013

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

    Reply

  10. Ric
    April 7, 2016

    Dear Alex,

    great tutorial!
    I have created a new /root/.my.cnf file with the following configuration:

    [mysqladmin]
    user = root
    password = changeme

    and set the following permissions:

    chmod 600 /root/.my.cnf

    1st question: should I put my root password or choose a brand new one (I’ve tried both)?

    I have then tried the following command:

    /usr/bin/mysqladmin flush-logs

    But it says:

    /usr/bin/mysqladmin: connect to server at ‘localhost’ failed error: ‘Access denied for user ‘root’@’localhost’ (using password: YES)’

    What could be the problem? Thanks in advance for any help.

    Reply

  11. Eduardo Krieg
    September 7, 2016

    Hi Alex

    Good tutorial, i’d like to add a security issue here:

    instead of using the mysql root user here i would recommend to create a new one with only the required permissions to make the flush command:

    create user logrotate@localhost identified by ‘password’;
    grant flush on *.* to logrotate@localhost;

    This, to avoid other possible users that can log to the OS root user to see the actual mysql root password.

    Do you have a similar approach on Windows Server?

    Regards!

    Reply

Leave a Reply

 

 

 

mobile desktop