39

I am running MySQL 5.0.45 on OS X 10.6.1 and I cannot get MySQL to log anything. I am debugging an application and need to see all queries and errors.

I added to the etc/my.cnf:

[mysqld]
bind-address = 127.0.0.1
log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log

I used sudo to create two log files, set permissions to 644, then restarted MySQL.

I referred to Where is my mysql log on OS X? in order to troubleshoot.

Running:

ps auxww|grep [m]ysqld

Returns:

_mysql      71   0.0  0.8   646996  15996   ??  S     7:31PM   0:01.10 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/Macintosh-41.local.pid
_mysql      46   0.0  0.0   600336    744   ??  Ss    7:30PM   0:00.03 /bin/sh /usr/local/mysql/bin/mysqld_safe

And running:

$(ps auxww|sed -n '/sed -n/d;/mysqld /{s/.* \([^ ]*mysqld\) .*/\1/;p;}') --verbose --help|grep '^log'

Returns:

log                               /var/log/mysqld.log
log-bin                           (No default value)
log-bin-index                     (No default value)
log-bin-trust-function-creators   FALSE
log-bin-trust-routine-creators    FALSE
log-error                         /var/log/mysqld.error.log
log-isam                          myisam.log
log-queries-not-using-indexes     FALSE
log-short-format                  FALSE
log-slave-updates                 FALSE
log-slow-admin-statements         FALSE
log-slow-queries                  (No default value)
log-tc                            tc.log
log-tc-size                       24576
log-update                        (No default value)
log-warnings                      1

Running:

mysql> show variables like '%log%';

Returns:

+---------------------------------+---------------------------+
| Variable_name                   | Value                     |
+---------------------------------+---------------------------+
| back_log                        | 50                        | 
| binlog_cache_size               | 32768                     | 
| expire_logs_days                | 0                         | 
| innodb_flush_log_at_trx_commit  | 1                         | 
| innodb_locks_unsafe_for_binlog  | OFF                       | 
| innodb_log_arch_dir             |                           | 
| innodb_log_archive              | OFF                       | 
| innodb_log_buffer_size          | 1048576                   | 
| innodb_log_file_size            | 5242880                   | 
| innodb_log_files_in_group       | 2                         | 
| innodb_log_group_home_dir       | ./                        | 
| innodb_mirrored_log_groups      | 1                         | 
| log                             | ON                        | 
| log_bin                         | OFF                       | 
| log_bin_trust_function_creators | OFF                       | 
| log_error                       | /var/log/mysqld.error.log | 
| log_queries_not_using_indexes   | OFF                       | 
| log_slave_updates               | OFF                       | 
| log_slow_queries                | OFF                       | 
| log_warnings                    | 1                         | 
| max_binlog_cache_size           | 4294967295                | 
| max_binlog_size                 | 1073741824                | 
| max_relay_log_size              | 0                         | 
| relay_log_purge                 | ON                        | 
| relay_log_space_limit           | 0                         | 
| sync_binlog                     | 0                         | 
+---------------------------------+---------------------------+
26 rows in set (0.00 sec)

Any help on how I can get MySQL to log?

0

6 Answers 6

26

Even though you used chmod 644, make sure mysql is the owner of the logs.

touch /var/log/mysql.log
chown mysql:mysql /var/log/mysql.log
touch /var/log/mysql.error.log
chown mysql:mysql /var/log/mysql.error.log

Next, restart mysqld.

Then, log into mysql and run:

mysql> show variables like '%log%';

Look at the values for general_log, general_log_file, log, log_error, etc.

Switch them on as needed and run mysqladmin flushlogs as needed.

1
  • Now logging. Must have been the ownership oversight. Although, see my edited question for results of "show variables like '%log%'" query. MySQL is logging to /var/log/mysql.log, so why doesn't it show?
    – ryonlife
    Oct 5, 2009 at 14:43
17

To enable the log files, you need to make sure that one or more of the following directives are in the [mysqld] section of your mysql server's main configuration file (/etc/my.cnf on mine):

[mysqld]
log-bin
log
log-error
log-slow-queries

The logs will be created, by default, in the same data directory that holds the database subdirectories themselves (typically /var/lib/mysql) and the log file names default to the hostname followed by a suffix that matches the directive names above (eg. -bin, -slow-queries, etc).

To keep your logs in a different path, or using different filenames, specify the base name following the log= directive, and any path and filename you like e.g.:

[mysqld]
log-bin=/var/log/mysql-bin.log
general-log=/var/log/mysql.log
log-error=/var/log/mysql-error.log
slow-query-log=/var/log/mysql-slowquery.log

You need to restart mysqld for these changes to take effect.

1
6

The previous answers were out dated. You can look the configuration file in /etc/my.cnf or on windows ./mysql/my.ini.

In the [mysqld] section add

log_bin
general_log
log_error

The documentation for windows states that the logs get stored in ProgramData/MySQL/MySQL Server 5.6/. Ignore it because it's a lie.

In addition the recommended log option is depreciated and results in the error

ambiguous option '--log' (log-bin, log_slave_updates)

being logged in ./mysql/data/host.err and the daemon silently dying. The correct setting is general-log.

Also log-slow-queries is not valid and caused the error

C:\mysql\bin\mysqld.exe: unknown option '--log-slow-queries'

So be certain to stay away from that also.

2
  • Thank you! I could not start the server with the log= option and could for the life of me figure out why. Finally someone with an answer. general-log Sep 3, 2015 at 20:05
  • log-slow-queries has been deprecated. Use slow-query-log instead.
    – rinogo
    Oct 6, 2020 at 0:21
3

Try different log settings in my.cnf. In my case I needed:

general_log = on

general_log_file=/var/log/mysql/mysql.log
1
  • I also needed this instruction for WAMP, where I used general_log = on general-log-file="c:/wamp/logs/mysql_general.log"
    – Nik Dow
    Dec 27, 2019 at 1:12
2

Enter mysql command line with super privileges and perform:

SET GLOBAL general_log_file = '/var/log/mysql/mysql.log'; 

In my case, it didn't matter if I had this variable already set with the same value! Also, I changed the permissions and the owner of the file as mentioned in the other posts.

0

edit my.conf

general_log=1
log_error=/var/logs/mysql/error.log
general_log_file=/var/logs/mysql/mysql.log

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .