38

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?

Community
  • 1
ryonlife
  • 491
  • 1
  • 4
  • 8

6 Answers6

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.

Kalle Richter
  • 268
  • 6
  • 18
randomx
  • 1,004
  • 1
  • 8
  • 14
  • 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 05 '09 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.

Welsh King
  • 103
  • 2
gigawatt
  • 189
  • 1
  • 6
  • 1
    Great answer, but `log` and `log-slow-queries` are deprecated. Use [`general-log`](https://dev.mysql.com/doc/refman/8.0/en/query-log.html) and [`slow-query-log`](https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html) instead. – rinogo Oct 06 '20 at 00:15
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.

masegaloeh
  • 18,236
  • 10
  • 57
  • 106
user3338098
  • 40
  • 2
  • 3
  • 13
  • 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` – jiveturkey Sep 03 '15 at 20:05
  • `log-slow-queries` has been deprecated. [Use `slow-query-log`](https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html) instead. – rinogo Oct 06 '20 at 00: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
HBruijn
  • 77,029
  • 24
  • 135
  • 201
Ray
  • 41
  • 2
  • 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 '19 at 01: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.

sentifool
  • 15
  • 4
Lucia
  • 145
  • 1
  • 7
0

welcome back to the StackExchange! Beyond clustering architectures, data protection also includes disk mirroring, snapshots, and redundant networks. While the tools available to system administrators have evolved, the general approach to data protection has always been one of resilience through redundancy. Thank you very much all.

Beat Eggli
  • 1