Shulou(Shulou.com)05/31 Report--

Editor to share with you what to do if the slow_log table in MySQL can not be modified into an innodb engine, I believe most people don't know much about it, so share this article for your reference. I hope you can learn a lot after reading this article. Let's go to know it together.

Background

It is slow to get the slow query log from mysql.slow_log, which is a csv table with no index.

You want to add indexes to speed up access, but the csv engine cannot add indexes (csv engine stores text separated by commas), so you have to change the storage engine to add indexes.

Mysql.slow_log table can be changed to myisam, not innodb.

Mysql > set global slow_query_log=off;Query OK, 0 rows affected (0.00 sec) mysql > alter table mysql.slow_log engine=innodb;ERROR 1579 (HY000): This storage engine cannot be used for log tables "mysql > alter table mysql.slow_log engine=myisam;Query OK, 33760 rows affected (0.37 sec) Records: 33760 Duplicates: 0 Warnings: 0

Mysql.general_log can't be changed to innodb.

Mysql > alter table mysql.general_log engine=myisam;Query OK, 242956 rows affected (2.41 sec) Records: 242956 Duplicates: 0 Warnings: 0mysql > alter table mysql.general_log engine=innodb;ERROR 1579 (HY000): This storage engine cannot be used for log tables "

Official document

Official documentation states that log tables only support the csv engine and the myisam engine.

Why the innodb engine is not supported is not stated

On what basis do you consider that innodb tables are not supported?

Asked for advice from a friend.

It is estimated that this kind of engine will consume a lot of redo and undo resources.

This is not necessary. These data are not important.

Change log table storage engine

SET @ old_log_state = @ @ global.general_log;SET GLOBAL general_log = 'OFF';ALTER TABLE mysql.general_log ENGINE = MyISAM;SET GLOBAL general_log = @ old_log_state

Archive log table

USE mysql;DROP TABLE IF EXISTS general_log2;CREATE TABLE general_log2 LIKE general_log;RENAME TABLE general_log TO general_log_backup, general_log2 TO general_log; above are all the contents of this article "what if the slow_log table in MySQL can not be modified into innodb engine". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.