MySQL – Error Logs Not Updating After Log Rotation

Threre is no rotation, because you are using -d option. From manual:

-d Turns on debug mode and implies -v. In debug mode, no changes will be made to the logs or to the logrotate state file.

I have two suggestions:

• Upgrade OS to x86_64 if possible, because you have 2,5GB memory limit per process
• Don't use -pPassword parameter with mysql for security reasons, it's better to use --defaults-extra-file=path_to_extra, which is readable only by root. When somebody, who has access to server execute ps -ef | grep mysql, it will see root password for database.

Here is my logrotate /etc/logrotate.d/mysql script for mysql on CentOS 5 (mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1):

# If the root user has a password you have to create a
# /.my.cnf configuration file with the following
# content:
#
# [mysqladmin]
# password = <secret>
# user = <username>
#
# where "<secret>" is the password and <username> is user.
#
# ATTENTION: This /.my.cnf should be readable ONLY
# for root !
/var/log/mysqld.log {
        create 640 mysql mysql
        notifempty
        missingok
        postrotate
                # just if mysqld is really running
                if test -x /usr/bin/mysqladmin && \
                   /usr/bin/mysqladmin --defaults-extra-file=/root/mysql/logrotate.cnf ping &>/dev/null
                then
                   /usr/bin/mysqladmin --defaults-extra-file=/root/mysql/logrotate.cnf flush-logs
                fi
                /usr/bin/chcon -u system_u -r object_r -t mysqld_log_t /var/log/mysqld.log
        endscript
}

This is probably because even though you truncate the file, the process writing to the file will continue writing at whichever offset it were at last. So what's happening is that logrotate truncates the file, size is zero, process writes to the file again, continuing at the offset it left off, and you now have a file with NULL-bytes up to the point where you truncated it plus the new entries written to the log.

od -c after truncate + sudden growth, generated output along the lines of:

0000000  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
*
33255657600  \0   C   K   B   -   s   e   r   v   e   r       [   h   t   t
33255657620 <more log output>

What this says is from offset 0 to 33255657600 your file consists of null bytes, and then some legible data. Getting to this state doesn't take the same amount of time it would take to actually write all those null-bytes. The ext{2,3,4} filesystems support something called sparse files, so if you seek past a region of a file that doesn't contain anything, that region will be assumed to contain null-bytes and won't take up space on disk. Those null bytes won't actually be written, just assumed to be there, hence the time it takes to go to 0 to 3.5GB don't take a lot of time to. (You can test the amount of time it takes by doing something like dd if=${HOME}/.bashrc of=largefile.bin seek=3432343264 bs=1, this should generate a file of over 3GB in a few milliseconds).

If you run ls -ls on your logfiles after they've been truncated and had a sudden growth again, it should now report a number at the beginning of the line which represents the actual size (in blocks occupied on disk), which probably is orders of magnitude smaller than the size reported by just ls -l.