我有两个 MySQL 服务器,运行主/从复制。
有一次,我不得不在从服务器上写入巨大的 general.log。
我有一个绝妙的想法,将它写入 MySQL 的数据目录。嘲笑我。
后来我删除了该文件。
现在 MySQL 坚持要有一个表 mysql.general_log,但它从来都不是表,而是一个日志文件。我发现这是因为我的 cron-mysqldump 发送了投诉:
#> mysqldump mysql > /home/mysqldump/mysqldump.sql
Error: Couldn't read status information for table general_log ()
mysqldump: Couldn't execute 'show create table `general_log`': Table 'mysql.general_log' doesn't exist (1146)
所以我手动尝试了这个:
mysql> show create table mysql.general_log;
ERROR 1146 (42S02): Table 'mysql.general_log' doesn't exist
并试图删除表格:
mysql> drop table general_log;
ERROR 1051 (42S02): Unknown table 'general_log'
还尝试过:
#> mysqlcheck --repair mysql
无济于事。
按照此关联我找到了一些关于 ib* 文件的信息。所以我
#> service mysqld stop
#> mv mysql/data/ib* mysql.off/data/
#> service mysqld start
#>
现在 mysqldump 抱怨另一个表丢失了。它只是一个 .frm 文件,没有附带 MYD 和 MYI。恢复更改。
alex_sf postetd 的测试结果:
mysql> SET GLOBAL general_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
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';
Query OK, 0 rows affected (0.02 sec)
mysql> DROP TABLE general_log;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@server ~]# mysqldump mysql > /home/mysqldump/mysqldump.sql
Error: Couldn't read status information for table general_log ()
mysqldump: Couldn't execute 'show create table `general_log`': Table 'mysql.general_log' doesn't exist (1146)
我尝试重新初始化数据库并使用来自其他 MySQL 实例的转储,该实例没有显示此错误:
#> mysql_install_db
#> service mysqld start
#> /usr/bin/mysql_secure_installation
#> mysqldump mysql > /home/mysqldump/mysqldump.sql
结果:一些小错误,记不清了。
现在我再次使用“干净的”mysqldump:
#> mysql < /home/mysqldump/mysqldump.master.sql
#> mysqldump mysql > /home/mysqldump/mysqldump.sql
Error: Couldn't read status information for table general_log ()
mysqldump: Couldn't execute 'show create table `general_log`': Table 'mysql.general_log' doesn't exist (1146)
瞧,错误就在 mysqldump 中。grep grep grep ....
alex_sfs第二次测试的结果:
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE `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';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@ns235265 mysql]# mysqldump mysql > /home/mysqldump/mysqldump.sql
Error: Couldn't read status information for table slow_log ()
mysqldump: Couldn't execute 'show create table `slow_log`': Table 'mysql.slow_log' doesn't exist (1146)
之后我意识到我没有放下桌子:
mysql> DROP TABLE general_log;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@server ~]# mysqldump mysql > /home/mysqldump/mysqldump.sql
Error: Couldn't read status information for table general_log ()
mysqldump: Couldn't execute 'show create table `general_log`': Table 'mysql.general_log' doesn't exist (1146)
我开始认为 DB 已经完蛋了……
答案1
已报告错误这里使用 mysqldump 可能会导致此问题。创建表定义,然后再次尝试转储:
CREATE TABLE `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';