-1

After trying to activate MySQL slow query mode provided by the user manual (http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_slow_query_log_file) and other answers (How do I enable the MySQL slow query log?) , they all fail for different error messages:

(1)  mysql -h ${SERVER_NAME} -B -N -P ${PORT_NAME} -u ${USER_NAME} --password=${PASSWORD} -e "

    set global slow_query_log = 'ON';

    SET global slow_query_log_file=$HOME/slow-query-log-CLIENT-${CLIENT_ID}-YEARLY-${Local_time}.log;

    SELECT * FROM WHATEVER;"

ERROR 1227 (42000) at line 3: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

(2)  mysql -h ${SERVER_NAME} -B -N -P ${PORT_NAME} -u ${USER_NAME} --password=${PASSWORD} -e "

    set global slow_query_log = 'ON';

    SET global slow_query_log_file=$HOME/slow-query-log-CLIENT-${CLIENT_ID}-YEARLY-${Local_time}.log;

    SET global log_output=/home/user;

ERROR 1227 (42000) at line 3: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

(3) When I follow the MySQL user manual for the command line option, the following error message pops out:

mysql -h ${SERVER_NAME} -B -N -P ${PORT_NAME} -u ${USER_NAME} --password=${PASSWORD} -e --slow_query_log --slow_query_log_file=SLOW-CLIENT-${CLIENT_ID}-${Local_time} --global log_output=/home/user "

mysql: unknown variable 'slow_query_log_file='

Quite frustrating. Could any guru enlighten?

Community
  • 1
  • 1
Chubaka
  • 2,933
  • 7
  • 43
  • 58

1 Answers1

0

Since SETs will be lost if the server crashes, it is not practical to do it the way you describe.

Certain SETs need to be set by the privileged "root" user (or other user with SUPER privilege.

It would be better to do things this way:

Change the config file (possibly /etc/my.cnf) with

long_query_time = 1
slow_query_log = ON
slow_query_log_file = /usr/mysql/slow.log

(or pick some other static path and filename)

Then, to "cycle" the log, have a cron job

cd /usr/mysql
mv slow.log some-fabricated-name
mysql ... -e "flush logs"
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Interesting idea! About the cron job part, what does the "flush logs" mean? – Chubaka Oct 03 '16 at 06:21
  • Also, by following your settings in /etc/mysql/my.cnf, there is no more error message. However, there is no any slow.log generated for unknown reasons. Could you enlighten? – Chubaka Oct 03 '16 at 07:47
  • You did restart `mysqld`? `FLUSH LOGS` closes and reopens various log files; I think the slow log is included. This, plus the `mv`, will effect the cycling. Any relevant messages in mysqld.err or mysql.log? – Rick James Oct 03 '16 at 15:44