How do I turn on mysql error logging without restarting?

Question

I have a running mysql system that doesn't have any logging turned on.

How do I enable the logging without restarting the server? I need to find out why mysql replication is failing.

BTW. I tried /etc/init.d/mysqld reload

But reload wasn't a valid option.

This system is running telephones so restarting it will most likely drop calls which I can't afford to do.

Also tried at the mysql command prompt set log_error='/var/log/mysql/error.log' but it came back and said there was no such system variable. Yet show variables lists it.

Warner · Answer 1 · 2010-03-10T20:48:46.387

1

log-error isn't a dynamic system variable. If you need to change it, you're going to have to restart.

I find it difficult to believe that it isn't logging. This setting is specified via two methods. The first is at run time with --log-error=filename, which would often be specified in your init file. More commonly, it would be specified in your my.cnf with log-error. Ultimately if left unset, it defaults to the data directory as your hostname.err.

Search the filesystem for a file with the extension of .err. Chances are, you'll find the log.

find / -name '*.err'
locate .err | egrep '.err$'

edited Mar 10 '10 at 20:48

answered Mar 10 '10 at 20:32

Warner

23,440
2
57
69

This turned up nothing. I'm not actually sure what mysql version it is either. It's a system that was not put in by me but someone who has long left the company. – hookenz Mar 10 '10 at 21:07
Do you know the data directory? If a mysql package, it's likely `/var/lib/mysql`. – Warner Mar 10 '10 at 21:11
datadir=/var/lib/mysql – hookenz Mar 10 '10 at 21:12
What files are in there? Can you review the .cnf to see if logging is specified? – Warner Mar 10 '10 at 21:18
I added log_error values but all other logging is commented out. – hookenz Mar 10 '10 at 22:08

score 0 · Answer 2 · answered Mar 10 '10 at 22:37

If slave replication is failing you can see the errors on the slave without enabling logging and restarting the instance on the master.

Log into the slave instance of MySQL and do a

mysql > show slave status;

That should show you the last error message, from which you should be able to determine the action to fix it.

Alternatively enable logging on the slave and see the queries and errors there.

How do I turn on mysql error logging without restarting?

2 Answers2