Enable MySQL’s slow query log without a restart - Revision history

George2 at 08:42, 18 May 2016

2016-05-18T08:42:11Z

George2 at 08:41, 18 May 2016

2016-05-18T08:41:23Z

George2 at 08:40, 18 May 2016

2016-05-18T08:40:29Z

George2 at 08:39, 18 May 2016

2016-05-18T08:39:13Z

George2 at 08:38, 18 May 2016

2016-05-18T08:38:31Z

George2 at 08:37, 18 May 2016

2016-05-18T08:37:21Z

George2 at 08:36, 18 May 2016

2016-05-18T08:36:46Z

George2: Created page with "Log on to mysql as root
Set the log-file location for the Slow Query log. Make sure mysql has write permission. mysql> SET GLOBAL slow_query_log_file = '/path/to/slow_qu..."

2016-05-18T08:36:12Z

Created page with "Log on to mysql as root <br> Set the log-file location for the Slow Query log. Make sure mysql has write permission. mysql> SET GLOBAL slow_query_log_file = '/path/to/slow_qu..."

New page

Log on to mysql as root <br>
Set the log-file location for the Slow Query log. Make sure mysql has write permission.
mysql> SET GLOBAL slow_query_log_file = '/path/to/slow_query.log';

← Older revision		Revision as of 08:42, 18 May 2016
Line 42:		Line 42:
	mysql> SET GLOBAL slow_query_log = 'OFF';		mysql> SET GLOBAL slow_query_log = 'OFF';
	mysql> FLUSH LOGS;		mysql> FLUSH LOGS;
		+	To disable it permanently in the my.cnf file, set the slow_query_log to 0.<br>
		+	[mysqld]
		+	...
		+	slow_query_log = 0
		+	long_query_time = 10
		+	log_queries_not_using_indexes = YES

← Older revision		Revision as of 08:41, 18 May 2016
Line 38:		Line 38:
	\| long_query_time \| 10.000000 \|		\| long_query_time \| 10.000000 \|
	+-----------------+-----------+		+-----------------+-----------+
		+	<br>
		+	And don't forget to either disable it again after your debug-session, or enable logrotate to prevent the files from growing too large. To disable the logging, but leave the rest of the requested configs intact, use the following at the CLI.<br>
		+	mysql> SET GLOBAL slow_query_log = 'OFF';
		+	mysql> FLUSH LOGS;

← Older revision		Revision as of 08:40, 18 May 2016
Line 17:		Line 17:
	<br><br>		<br><br>
	'''To verify if your settings are working, request the running parameters from MySQL.'''<br>		'''To verify if your settings are working, request the running parameters from MySQL.'''<br>
		+	mysql> SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes';
		+	+-------------------------------+-------+
		+	\| Variable_name \| Value \|
		+	+-------------------------------+-------+
		+	\| log_queries_not_using_indexes \| ON \|
		+	+-------------------------------+-------+
		+
		+	mysql> SHOW GLOBAL VARIABLES LIKE 'slow\_%';
		+	+---------------------+-------------------------------+
		+	\| Variable_name \| Value \|
		+	+---------------------+-------------------------------+
		+	\| slow_query_log \| ON \|
		+	\| slow_query_log_file \| /path/to/slow_query.log \|
		+	+---------------------+-------------------------------+
		+
		+	mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';
		+	+-----------------+-----------+
		+	\| Variable_name \| Value \|
		+	+-----------------+-----------+
		+	\| long_query_time \| 10.000000 \|
		+	+-----------------+-----------+

← Older revision		Revision as of 08:39, 18 May 2016
Line 15:		Line 15:
	long_query_time = 10		long_query_time = 10
	log_queries_not_using_indexes = YES		log_queries_not_using_indexes = YES
		+	<br><br>
		+	'''To verify if your settings are working, request the running parameters from MySQL.'''<br>

← Older revision		Revision as of 08:38, 18 May 2016
Line 7:		Line 7:
	mysql> SET GLOBAL slow_query_log = 'ON';		mysql> SET GLOBAL slow_query_log = 'ON';
	mysql> FLUSH LOGS;		mysql> FLUSH LOGS;
		+	As a bonus, you can also make all queries that do not use indexes be logged to the same Slow Query log.<br>
		+	mysql> SET GLOBAL log_queries_not_using_indexes = 'YES';
		+	If you want to make these changes persistent, modify the my.cnf and add these lines to the [mysqld] part of the config.<br>
		+	[mysqld]
		+	...
		+	slow_query_log = /path/to/slow_query.log
		+	long_query_time = 10
		+	log_queries_not_using_indexes = YES

← Older revision		Revision as of 08:37, 18 May 2016
Line 4:		Line 4:
	Determine what makes a query "slow", by setting the limit (in seconds) after which a query is logged to the slow query log. The example below logs every query that exceeds 10 seconds in duration.<br>		Determine what makes a query "slow", by setting the limit (in seconds) after which a query is logged to the slow query log. The example below logs every query that exceeds 10 seconds in duration.<br>
	mysql> SET GLOBAL long_query_time = 10;		mysql> SET GLOBAL long_query_time = 10;
		+	Now enable the Slow Query log.<br>
		+	mysql> SET GLOBAL slow_query_log = 'ON';
		+	mysql> FLUSH LOGS;

← Older revision		Revision as of 08:36, 18 May 2016
Line 2:		Line 2:
	Set the log-file location for the Slow Query log. Make sure mysql has write permission.		Set the log-file location for the Slow Query log. Make sure mysql has write permission.
	mysql> SET GLOBAL slow_query_log_file = '/path/to/slow_query.log';		mysql> SET GLOBAL slow_query_log_file = '/path/to/slow_query.log';
		+	Determine what makes a query "slow", by setting the limit (in seconds) after which a query is logged to the slow query log. The example below logs every query that exceeds 10 seconds in duration.<br>
		+	mysql> SET GLOBAL long_query_time = 10;