mysql 表清空，查看sql在进程中的正在运行的sql ，log日志

本文链接：https://blog.youkuaiyun.com/terry_water/article/details/24729101

本文展示了如何通过执行SQL命令来清空多个表的数据，并通过展示MySQL的进程列表来监控当前数据库的状态。具体包括了清空如`index_process_event`等表的内容以及详细列出了不同时间点的进程列表。

TRUNCATE TABLE  `index_process_event`;
TRUNCATE TABLE  `dataflow_batch_export`;
TRUNCATE TABLE  `dataflow_batch_import`;
TRUNCATE TABLE  `log_customer`;
TRUNCATE TABLE  `log_quote`;
TRUNCATE TABLE  `log_summary`;
TRUNCATE TABLE  `log_summary_type`;
TRUNCATE TABLE  `log_url`;
TRUNCATE TABLE  `log_url_info`;
TRUNCATE TABLE  `log_visitor`;
TRUNCATE TABLE  `log_visitor_info`;
TRUNCATE TABLE  `log_visitor_online`;
TRUNCATE TABLE  `report_viewed_product_index`;
TRUNCATE TABLE  `report_compared_product_index`;
TRUNCATE TABLE  `report_event`;

show processlist;

| Id     | User        | Host                | db          | Command | Time | State                     | Info                                                                                                 |
+--------+-------------+---------------------+-------------+---------+------+---------------------------+------------------------------------------------------------------------------------------------------+
| 345313 | tomtopcom17 | 10.12.167.130:43452 | tomtopcom17 | Query   |    0 | NULL                      | show processlist                                                                                     |
| 358510 | tomtopcom17 | 10.12.167.130:33700 | tomtopcom17 | Query   |    0 | update                    | INSERT INTO `core_url_rewrite` (`store_id`,`category_id`,`product_id`,`id_path`,`request_path`,`targ |
| 367400 | tomtopcom17 | 10.12.167.130:41015 | tomtopcom17 | Query   |  941 | updating reference tables | UPDATE `sales_flat_quote` AS `t1`
 INNER JOIN (SELECT `t2`.`quote_id` AS `entity_id` FROM `sales_fla |
| 370506 | tomtopcom17 | 10.12.167.130:53252 | tomtopcom17 | Sleep   |    0 |                           | NULL                                                                                                 |
| 370516 | tomtopcom17 | 10.12.167.130:53265 | tomtopcom17 | Sleep   |    0 |                           | NULL                                                                                                 |
| 370519 | tomtopcom17 | 10.12.167.130:53269 | tomtopcom17 | Query   |    0 | statistics                | SELECT `customer_entity_varchar`.* FROM `customer_entity_varchar` WHERE (entity_id ='263430') UNION  |
| 370521 | tomtopcom17 | 10.12.167.130:53271 | tomtopcom17 | Query   |    0 | Copying to tmp table      | SELECT `color_idx`.`value`, COUNT(color_idx.entity_id) AS `count` FROM `catalog_product_entity` AS ` |
| 370523 | tomtopcom17 | 10.12.167.130:53273 | tomtopcom17 | Sleep   |    0 |                           | NULL                                                                                                 |
| 370524 | tomtopcom17 | 10.12.167.130:53274 | tomtopcom17 | Sleep   |    0 |                           | NULL                                                                                                 |
| 370528 | tomtopcom17 | 10.12.167.130:53278 | tomtopcom17 | Sleep   |    0 |                           | NULL                                                                                                 |
| 370529 | tomtopcom17 | 10.12.167.130:53279 | tomtopcom17 | Sleep   |    0 |                           | NULL                                                                                                 |

+--------+-------------+---------------------+-------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id     | User        | Host                | db          | Command | Time | State          | Info                                                                                                 |
+--------+-------------+---------------------+-------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 345313 | tomtopcom17 | 10.12.167.130:43452 | tomtopcom17 | Query   |    0 | NULL           | show processlist                                                                                     |
| 358510 | tomtopcom17 | 10.12.167.130:33700 | tomtopcom17 | Query   |    0 | update         | INSERT INTO `core_url_rewrite` (`store_id`,`category_id`,`product_id`,`id_path`,`request_path`,`targ |
| 371840 | tomtopcom17 | 10.12.167.130:58485 | tomtopcom17 | Query   |    1 | Sorting result | SELECT `core_url_rewrite`.`product_id`, `core_url_rewrite`.`request_path` FROM `core_url_rewrite` WH |
| 371842 | tomtopcom17 | 10.12.167.130:58487 | tomtopcom17 | Query   |    1 | Sending data   | SELECT `t_d`.`entity_id`, `t_d`.`attribute_id`, `t_d`.`value` AS `default_value`, `t_s`.`value` AS ` |
| 371847 | tomtopcom17 | 10.12.167.130:58492 | tomtopcom17 | Query   |    0 | Sending data   | SELECT `tag`.* FROM `tag` WHERE (name = 'led shirt')                                                 |
| 371848 | tomtopcom17 | 10.12.167.130:58493 | tomtopcom17 | Sleep   |    0 |                | NULL                                                                                                 |
| 371850 | tomtopcom17 | 10.12.167.130:58495 | tomtopcom17 | Query   |    0 | NULL           | commit                                                                                               |
| 371851 | tomtopcom17 | 10.12.167.130:58496 | tomtopcom17 | Query   |    0 | NULL           | commit                                                                                               |
| 371852 | tomtopcom17 | 10.12.167.130:58498 | tomtopcom17 | Query   |    0 | NULL           | commit                                                                                               |
| 371853 | tomtopcom17 | 10.12.167.130:58499 | tomtopcom17 | Sleep   |    0 |                | NULL                                                                                                 |
+--------+-------------+---------------------+-------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+

mysql> show processlist;
+--------+-------------+---------------------+-------------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id     | User        | Host                | db          | Command | Time | State  | Info                                                                                                 |
+--------+-------------+---------------------+-------------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| 345313 | tomtopcom17 | 10.12.167.130:43452 | tomtopcom17 | Query   |    0 | NULL   | show processlist                                                                                     |
| 358510 | tomtopcom17 | 10.12.167.130:33700 | tomtopcom17 | Query   |    0 | update | INSERT INTO `core_url_rewrite` (`store_id`,`category_id`,`product_id`,`id_path`,`request_path`,`targ |
| 371878 | tomtopcom17 | 10.12.167.130:60439 | tomtopcom17 | Query   |    0 | NULL   | commit                                                                                               |
| 371880 | tomtopcom17 | 10.12.167.130:60441 | tomtopcom17 | Sleep   |    0 |        | NULL                                                                                                 |
| 371881 | tomtopcom17 | 10.12.167.130:60443 | tomtopcom17 | Query   |    0 | NULL   | commit                                                                                               |
| 371882 | tomtopcom17 | 10.12.167.130:60445 | tomtopcom17 | Sleep   |    0 |        | NULL                                                                                                 |
+--------+-------------+---------------------+-------------+---------+------+--------+------------------------------------------------------------------------------------------------------+

mysql> show processlist;
+--------+-------------+---------------------+-------------+---------+------+------------+------------------------------------------------------------------------------------------------------+
| Id     | User        | Host                | db          | Command | Time | State      | Info                                                                                                 |
+--------+-------------+---------------------+-------------+---------+------+------------+------------------------------------------------------------------------------------------------------+
| 345313 | tomtopcom17 | 10.12.167.130:43452 | tomtopcom17 | Query   |    0 | NULL       | show processlist                                                                                     |
| 358510 | tomtopcom17 | 10.12.167.130:33700 | tomtopcom17 | Query   |    0 | update     | INSERT INTO `core_url_rewrite` (`store_id`,`category_id`,`product_id`,`id_path`,`request_path`,`targ |
| 371888 | tomtopcom17 | 10.12.167.130:60451 | tomtopcom17 | Query   |    0 | statistics | SELECT COUNT(*) FROM `sales_flat_creditmemo_grid` AS `main_table` WHERE (order_id = '276339')        |
| 371894 | tomtopcom17 | 10.12.167.130:60459 | tomtopcom17 | Sleep   |    0 |            | NULL                                                                                                 |
| 371897 | tomtopcom17 | 10.12.167.130:60462 | tomtopcom17 | Sleep   |    0 |            | NULL                                                                                                 |
| 371898 | tomtopcom17 | 10.12.167.130:60463 | tomtopcom17 | Query   |    0 | NULL       | commit                                                                                               |
+--------+-------------+---------------------+-------------+---------+------+------------+------------------------------------------------------------------------------------------------------+

mysql> show processlist;
+--------+-------------+---------------------+-------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id     | User        | Host                | db          | Command | Time | State        | Info                                                                                                 |
+--------+-------------+---------------------+-------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 345313 | tomtopcom17 | 10.12.167.130:43452 | tomtopcom17 | Query   |    0 | NULL         | show processlist                                                                                     |
| 358510 | tomtopcom17 | 10.12.167.130:33700 | tomtopcom17 | Query   |    0 | update       | INSERT INTO `core_url_rewrite` (`store_id`,`category_id`,`product_id`,`id_path`,`request_path`,`targ |
| 371907 | tomtopcom17 | 10.12.167.130:60473 | tomtopcom17 | Query   |    0 | Updating     | UPDATE `log_visitor` SET `session_id` = 'lhi7ij9taj27m876odnoetk3c2', `first_visit_at` = '2014-04-29 |
| 371911 | tomtopcom17 | 10.12.167.130:60477 | tomtopcom17 | Query   |    1 | Sending data | SELECT COUNT(DISTINCT main_table.tag_id) FROM `tag` AS `main_table`
 LEFT JOIN `tag_summary` AS `sum |
| 371914 | tomtopcom17 | 10.12.167.130:60483 | tomtopcom17 | Query   |    0 | statistics   | SELECT `t_d`.`entity_id`, `t_d`.`attribute_id`, `t_d`.`value` AS `default_value`, `t_s`.`value` AS ` |
+--------+-------------+---------------------+-------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+