How to enable MySQL logging?
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?
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.
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.
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.