mysql %T_【MySQL】Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)

[root@rac1 ~]# mysql

root@rac1 : (none) 21:47:46>

root@rac1 : (none) 21:47:47> show global variables like '%slow%';

+---------------------+----------------+

| Variable_name       | Value          |

+---------------------+----------------+

| log_slow_queries    | ON             |

| slow_launch_time    | 2              |

| slow_query_log      | ON             |

| slow_query_log_file | slow_query.log |

+---------------------+----------------+

4 rows in set (0.06 sec)

root@rac1 : (none) 21:48:19> set global slow_query_log=off;

Query OK, 0 rows affected (0.00 sec)

root@rac1 : (none) 21:48:47>

root@rac1 : (none) 21:48:47> show global variables like '%slow%';

+---------------------+----------------+

| Variable_name       | Value          |

+---------------------+----------------+

| log_slow_queries    | OFF            |

| slow_launch_time    | 2              |

| slow_query_log      | OFF            |

| slow_query_log_file | slow_query.log |

+---------------------+----------------+

4 rows in set (0.01 sec)

root@rac1 : (none) 21:48:49> show global variables like '%slow%';

+---------------------+----------------+

| Variable_name       | Value          |

+---------------------+----------------+

| log_slow_queries    | OFF            |

| slow_launch_time    | 2              |

| slow_query_log      | OFF            |

| slow_query_log_file | slow_query.log |

+---------------------+----------------+

4 rows in set (0.00 sec)

root@rac1 : (none) 21:49:50> set global slow_query_log=on;

ERROR 13 (HY000): Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)

root@rac1 : mysql 21:50:33> show create table slow_log;

ERROR 13 (HY000): Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)

从一个有slow_log 的数据库中查看slow_log的表创建ddl 语句，进行创建！

root@rac1 : mysql 21:51:23> CREATE TABLE `slow_log` (

->   `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

->   `user_host` mediumtext NOT NULL,

->   `query_time` time NOT NULL,

->   `lock_time` time NOT NULL,

->   `rows_sent` int(11) NOT NULL,

->   `rows_examined` int(11) NOT NULL,

->   `db` varchar(512) NOT NULL,

->   `last_insert_id` int(11) NOT NULL,

->   `insert_id` int(11) NOT NULL,

->   `server_id` int(10) unsigned NOT NULL,

->   `sql_text` mediumtext NOT NULL

-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log';

Query OK, 0 rows affected (0.04 sec)

root@rac1 : mysql 21:51:43>

root@rac1 : mysql 21:51:55> set global slow_query_log=on;

Query OK, 0 rows affected (0.00 sec)

root@rac1 : mysql 21:52:13>

root@rac1 : mysql 21:52:13> show global variables like '%out%';

+----------------------------+----------+

| Variable_name              | Value    |

+----------------------------+----------+

| log_output                 | FILE     |

+----------------------------+----------+

11 rows in set (0.00 sec)

root@rac1 : mysql 21:52:20> set global log_output = 'TABLE';

Query OK, 0 rows affected (0.00 sec)

root@rac1 : mysql 21:52:41> show global variables like '%out%';

+----------------------------+----------+

| Variable_name              | Value    |

+----------------------------+----------+

| log_output                 | TABLE    |

+----------------------------+----------+

11 rows in set (0.00 sec)

如果log_output 用TABLE形式记录日志，mysql会以表的形式将slow_log存入mysql表中的slow_log表中。

root@rac1 : mysql 21:52:43> select sleep(60);

+-----------+

| sleep(60) |

+-----------+

|         0 |

+-----------+

1 row in set (1 min 0.00 sec)

root@rac1 : mysql 21:54:01> select * from slow_log \G

*************************** 1. row ***************************

start_time: 2012-08-24 21:54:01

user_host: root[root] @ localhost [127.0.0.1]

query_time: 00:01:00

lock_time: 00:00:00

rows_sent: 1

rows_examined: 0

db: mysql

last_insert_id: 0

insert_id: 0

server_id: 1

sql_text: select sleep(60)

1 row in set (0.00 sec)

root@rac1 : mysql 21:57:53>

root@rac1 : mysql 21:58:25> set global log_output = 'FILE';

Query OK, 0 rows affected (0.00 sec)

root@rac1 : mysql 21:58:30> select sleep(10);

+-----------+

| sleep(10) |

+-----------+

|         0 |

+-----------+

1 row in set (10.00 sec)

由于log_output 用FILE形式记录日志，所以我们在vim  /home/mysql/log/mysql-slow.log 将会看到执行超过0秒的所有的sql

-bash-3.2$ tail -f slow_query.log

Tcp port: 3306  Unix socket: /tmp/mysql.sock

Time                 Id Command    Argument

# Time: 120824 21:58:48

# User@Host: root[root] @ localhost [127.0.0.1]

# Query_time: 10.001827  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0

use mysql;

SET timestamp=1345816728;

select sleep(10);

至此问题完全得到解决。