Mysql – Help Outputing MySQL General Log to Named Pipe

MySQL

I have a cPanel, Centos 7 webserver using MySQL 5.6, in which I am trying to output the MySQL general log to a named pipe I have created. I have not had any issues creating the pipe and can write to other replacement log files in the MySQL directory without issues.

However when I create a named pipe and set this as the log file (How do I output MySQL logs to syslog?) I get this error in my errorlog on a restart:

[ERROR] Could not open /var/lib/mysql/server1.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

I have set the owner and group of the named pipe to the mysql user and the permissions to 660 (and even 777 to be sure). These same permissions seem to work fine for a normal log file.

Any suggestions as to what I have done wrong?

Best Answer

Every step in /var/lib/ needs to have at least 755 permissions. That is, each directory needs to be readable and x (searchable) by user mysql. (Technically, none of those steps should be owned by mysql, so 005 is sufficient. However, you may find 750, which won't work.)

/var/lib/mysql/, itself needs to be owned and writable by mysql (700 or better).

Related Solutions

Mysql – How to output MySQL logs to syslog

This is MUCH more simply done this way:

cd to mysql folders:

mkfifo mysql-general.log

in my.cnf tell it:

[mysqld]
general-log-file = /path/to/mysql/dir/mysql-general.log

Then, configure your syslog/syslog-ng to read the FIFO pipe and do with it as it will..

In my case, I pipe it across net to a centralized server with only the error logs and slow query logs however.

In situations where you want to also keep local copy, just set it to output to table and file as described above.

Mysql – Log MySQL DB changing queries and users

This sounds like a perfect use of TRIGGERS to me. You say you have transactions, so you must already be using InnoDB. Attach triggers on INSERT, UPDATE, and DELETE on the tables you care about, each one triggering an INSERT into a log table AFTER the table event. There's your SELECT filter, done. You also have total control over what you log and how.

This is a higher level solution, closer to the application layer, not a low-level system log solution where you have an exact copy of the complete SQL query. But I think it does address some of your concerns.

InnoDB Triggers have access to stored procedures and all the MySQL functions, so I presume you can find the user's login name in there somewhere. The log time is just NOW().

You may end up with a lot of triggers to manage, depending on your number of tables, but it's just a copy/paste job for each one. I maintain the code for all triggers in a single reference SQL file along with the table definitions and views for that database.

I've never tested triggers in a transaction-rich environment to make sure the trigger only fired after the transaction was committed. The docs are thin on this subject, but they do say, "An AFTER trigger is executed only if the BEFORE trigger (if any) and the row operation both execute successfully." Sounds conclusive to me. Now you also know you're not logging things that didn't actually work.
OR set up replication, even to another mysqld instance on the same box if necessary. Turn on your mysqld full query logging on the slave instance. Failed transactions don't get replicated, and neither do SELECTs.

Best Answer

Related Solutions

Mysql – How to output MySQL logs to syslog

Mysql – Log MySQL DB changing queries and users

Related Question