How to Recreate mysql.log File

MySQL

I deleted mysql.log using rm. I recreated it using the touch command but it seems like mysql cannot find it. I tried SET GLOBAL general_log = 1; and I got the following error. I can't find any information on the right way to recreate mysql.log

ERROR 29 (HY000): File '/var/log/mysql/mysql.log' not found (Errcode: 13)

Edit:

I resolved it. I removed the log file again and ran SET GLOBAL general_log = 1;

MySQL recreated the log file.

Edit 2:

It seems that I was wrong. I deleted the file again and ran that command and nothing happened. I guess there is an automated process in the background which creates the file again?

Best Answer

You have to disable general logging and enable it again to recreate the log file.

When you enable logging existing file is used or new one is created and its inode is used as filedescriptor for writing. If you delete already opened file filedescriptor is pointing to the nonexisting file. You should close file via general_log=0 and reopen it again via general_log=1

Related Solutions

Mysql – How to set selinux permission for non-standard thesql bin log path

To set mysql selinux on a directory you can:

semanage fcontext -a -t mysqld_db_t "/yourdirectory(/.*)?"

then

restorecon -Rv /yourdirectory

Also check the mysql user has permissions on your directory:

ls -l

Set permissions if needed:

chown -R mysql:mysql /yourdirectory

MySQL – InnoDB Engine Not Loading After Tuning Parameters

You found in the error log that InnoDB refuses to start if the config file sets the innodb_log_file_size to a size different from the physical size of the ib_logfile* files in your data directory.

The reason for this is that the log file are used during crash recovery. If InnoDB tries to initialize, but the log files are suspiciously the wrong size, it's possible they're the wrong files. InnoDB decides not try to crash recovery using the wrong log files, because applying the wrong files could irreparably corrupt your data. So believe it or not, shutting down the storage engine is the best outcome.

You can add another line to your config file to make mysqld refuse to start if InnoDB fails to start:

innodb=force

In MySQL 5.6, they added more flexibility so that InnoDB resizes the log files if you change the config file entry. Here's a blog that explains this in more detail: http://www.mysqlperformanceblog.com/2013/09/05/my-favorite-secret-mysql-5-6-feature/

A couple of other tips

The default InnoDB log file size of 5MB is totally undersized for most production sites. You can read this blog for a method to estimate the log file size your site needs: http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
You're not enabling the InnoDB plugin. MySQL 5.1 ships with two implementations of InnoDB. You can get more performance and scalability for free without upgrading if you switch from the "built-in" InnoDB (statically linked with MySQL 5.1) to the InnoDB plugin. The plugin is where all the new engineering is implemented. If you don't enable it, you're virtually using InnoDB as it existed in MySQL 5.0. See http://dev.mysql.com/doc/refman/5.1/en/replacing-builtin-innodb.html for details.

Best Answer

Related Solutions

Mysql – How to set selinux permission for non-standard thesql bin log path

MySQL – InnoDB Engine Not Loading After Tuning Parameters

Related Question