我删除了 general_log 表，如何重新创建它? [英] I dropped general_log table, how do I create it again?

问题描述

我使用启用了日志记录:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

所有执行的查询都记录到 mysql.general_log 表.没问题.

All executed queries was logging to mysql.general_log table. It is fine.

然后表变大了，我想从表中删除所有记录.我执行了:

Then the table became large and I wanted to delete all records from the table. I executed:

DELETE FROM general_log

但这导致了一个错误，提示我无法锁定日志表.因此，我在禁用日志记录后删除了该表:

But this caused to an error that says I cannot lock log tables. So, I dropped the table after disabling logging:

SET GLOBAL general_log = 'OFF';
DROP TABLE general_log;

我希望再次启用日志记录会创建表，但我无法启用它.当我执行此操作时:

I hope that enabling logging again will create the table, but I couldn't enable it. When I execute this:

SET GLOBAL general_log = 'ON';

它给出了这个错误:

表 'mysql.general_log' 不存在

Table 'mysql.general_log' doesn't exist

问题

1. 如何重新创建mysql.general_log?
2. 如何安全无痛地清除mysql.general_log?

推荐答案

重新创建:

USE mysql;

CREATE TABLE IF NOT EXISTS `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';

清除表格:

TRUNCATE table mysql.general_log;

这篇关于我删除了 general_log 表，如何重新创建它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！