It sounds like the mysql process (mysqld_safe, I believe) is unable to create the log file after you use logrotate to move it out of the way and send a HUP? If I'm way off here, let me know.

Assuming I'm not way off, there are a few options here:

• Move the log to a directory owned by the same user as the mysqld_safe process. For example, create a /var/log/mysqld/ directory and save the logfiles there. If the directory is mysql:mysql 700 then new files can be created without an issue.
• Use the copy/truncate logrotate method instead of the move/SIGHUP method. The copy/truncate method will copy the current log files (mysqld.slow.log) to a new file (mysqld.slow.log.1) then truncate the original file to zero bytes. This is useful if you don't want to interrupt the process that is writing to the log for some reason. The downside, of course, is that there is additional disk overhead with copying the original file to a new file before wiping the original file back to zero bytes. This is done by adding the copytruncate option to the logrotate stanza for that file and removing the no-longer-required postrotate section.

You should use general log, not as a text file, but as a MySQL table.

Why create the general_log as a table?

• You can run SQL against it.
• You can rotate out very easily.

How do you configure it?

Starting in MySQL 5.1, the following option was introduced: log-output.

• Setting log-output to FILE (default) writes log entries to the text file.
• Setting log-output to TABLE writes log entries to mysql.general_log.
• Setting log-output to TABLE,FILE writes log entries to mysql.general_log and the text file.

There is one major thing that must be changed.

Let's look at a standard mysql.general_log table:

mysql> show create table mysql.general_log\G 
*************************** 1. row *************************** 
       Table: general_log 
Create Table: CREATE TABLE `general_log` ( 
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
      ON UPDATE CURRENT_TIMESTAMP, 
  `user_host` mediumtext NOT NULL, 
  `thread_id` int(11) NOT NULL, 
  `server_id` int(10) unsigned NOT NULL, 
  `command_type` varchar(64) NOT NULL, 
  `argument` mediumtext NOT NULL 
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' 
1 row in set (0.09 sec) 

It's a CSV file ? Yuck !!! Who has time for that? Not to worry, turn it into a MyISAM table

ALTER TABLE mysql.general_log ENGINE = MyISAM; 
ALTER TABLE mysql.general_log ADD INDEX (event_time); 

If you already started MySQL, do it like this instead:

SET @old_log_state = @@global.general_log; 
SET GLOBAL general_log = 'OFF'; 
ALTER TABLE mysql.general_log ENGINE = MyISAM; 
ALTER TABLE mysql.general_log ADD INDEX (event_time); 
SET GLOBAL general_log = @old_log_state; 

Now, the general log table looks like this:

mysql> show create table general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
      ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL,
  KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.00 sec)

How do you rotate the general log when it's a MyISAM table?

Here is an example of how to blank out mysql.general_log:

SET @old_log_state = @@global.general_log; 
SET GLOBAL general_log = 'OFF'; 
CREATE TABLE mysql.general_log_new LIKE mysql.general_log; 
DROP TABLE mysql.general_log; 
ALTER TABLE mysql.general_log_new RENAME mysql.general_log; 
SET GLOBAL general_log = @old_log_state; 

Here is an example of how to keep the last 3 days of entries:

SET @old_log_state = @@global.general_log; 
SET GLOBAL general_log = 'OFF'; 
CREATE TABLE mysql.general_log_new LIKE mysql.general_log; 
INSERT INTO mysql.general_log_new 
SELECT * FROM mysql.general_log WHERE event_time > NOW() - INTERVAL 3 DAY; 
DROP TABLE mysql.general_log; 
ALTER TABLE mysql.general_log_new RENAME mysql.general_log; 
SET GLOBAL general_log = @old_log_state; 

CAVEAT

This feature is available in MySQL 5.1/5,5/5.6. You should upgrade from MySQL 5.0.77 to the latest

EPILOGUE

I could write a lot more here but have discussed this many times in the DBA StackExchange. here are some of my posts on this subject:

• Feb 11, 2012 : MySQL general log
• Jan 07, 2012 : How to enable MySQL general log?
• Dec 27, 2011 : How to do MySQL User Accounting
• Feb 24, 2011 : Audit logins on MySQL database