Mysql – way to hide thesql innodb cluster queries from general_log

logMySQLmysql-cluster

When I start my innodb cluster I get many lines of cluster related logs in the mysql general_log file like below, how can I hide these from the log?

2020-06-26T14:40:43.719029Z     31774 Connect   mysql_router1_6dv772yz7qu1@dbsg1 on  using SSL/TLS
2020-06-26T14:40:43.719494Z     31774 Query     SET @@SESSION.autocommit=1, @@SESSION.character_set_client=utf8, @@SESSION.character_set_re
sults=utf8, @@SESSION.character_set_connection=utf8, @@SESSION.sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
2020-06-26T14:40:43.719659Z     31773 Quit
2020-06-26T14:40:43.720231Z     31774 Query     SET @@SESSION.group_replication_consistency='EVENTUAL'
2020-06-26T14:40:43.720509Z     31774 Query     START TRANSACTION
2020-06-26T14:40:43.720740Z     31774 Query     SELECT * FROM mysql_innodb_cluster_metadata.schema_version
2020-06-26T14:40:43.721185Z     31774 Query     select cluster_type from mysql_innodb_cluster_metadata.v2_this_instance
2020-06-26T14:40:43.723301Z     31774 Query     select I.mysql_server_uuid, I.endpoint, I.xendpoint from mysql_innodb_cluster_metadata.v2_instances I join mysql_innodb_cluster_metadata.v2_gr_clusters C on I.cluster_id = C.cluster_id where C.cluster_name = 'prodcluster' AND C.group_name = '8a70e310-a997-4d59-8e81-92dab9daa5be'
2020-06-26T14:40:43.723985Z     31774 Query     COMMIT
2020-06-26T14:40:43.724317Z     31774 Query     show status like 'group_replication_primary_member'
2020-06-26T14:40:43.725621Z     31774 Query     SELECT member_id, member_host, member_port, member_state, @@group_replication_single_primary_mode FROM performance_schema.replication_group_members WHERE channel_name = 'group_replication_applier'
2020-06-26T14:40:43.793743Z     31771 Query     START TRANSACTION
2020-06-26T14:40:43.933131Z     31770 Query     SELECT * FROM mysql_innodb_cluster_metadata.schema_version
2020-06-26T14:40:44.032471Z     31771 Query     SELECT * FROM mysql_innodb_cluster_metadata.schema_version
2020-06-26T14:40:44.171571Z     31770 Query     select cluster_type from mysql_innodb_cluster_metadata.v2_this_instance
2020-06-26T14:40:44.226921Z     31774 Quit

Best Answer

There is obviously a whole lot of administrative MySQL noise in the general_log.

You cannot really screen them out. So, what can do you ???

SUGGESTION: USE THE SLOW LOG !!!

The Slow Log ??? Yes, that's exactly what I said : USE THE SLOW LOG !!!

You are probably saying : "Why would I use the slow log when I need to general log ???"

Here is the problem you seem to be facing: you want to hide administrative SQL commands like:

select cluster_type from mysql_innodb_cluster_metadata.v2_this_instance

and

select I.mysql_server_uuid, I.endpoint, I.xendpoint from
mysql_innodb_cluster_metadata.v2_instances I join
mysql_innodb_cluster_metadata.v2_gr_clusters C
on I.cluster_id = C.cluster_id
where C.cluster_name = 'prodcluster'
AND C.group_name = '8a70e310-a997-4d59-8e81-92dab9daa5be'

You can make the slow log behave like the general log by setting this:

SET GLOBAL long_query_time = 0;

Then, everything that executes will land in the slow log.

So, how can you screen out the administrative SQL commands ??? Think about how long it takes for administrative SQL to execute. Try setting this:

SET GLOBAL long_query_time = 0.5;

Once you set this, everything that takes less than 0.5 seconds will not be recorded in the slow log. I am sure administrative SQL commands will not take 0.5 seconds or more to run.

Once you are satisfied that administrative SQL commands are not being recorded in the slow log, go add that setting in the my.cnf file:

[mysqld]
long_query_time = 0.5

or if you are using MySQL 8.0

mysql> SET PERSIST long_query_time = 0.5;

SUMMARY

  • Disable the general log
  • Enable the slow log
  • Run SET GLOBAL long_query_time = 0.5; in mysql
  • Add long_query_time = 0.5 under [mysqld] in /etc/my.cnf
Related Question