mysql查看sql语句执行历史记录的例子

下面来为各位介绍一个mysql查看sql语句执行历史记录的例子,如果你希望跟踪你程序的执行性能我们就可以使用下面方法,有需要了解的朋友可进入看看.

mysql查看sql语句执行历史记录:

cat ~/.mysql_history

对于mysql版本5.1以后的版本,可以通过以下方式启动日志记录,能够记录下包括应用程序执行的sql语句.

1. MariaDB[(none)]>SETGLOBALlog_output='TABLE';
2. QueryOK,0rowsaffected(0.02sec)
3. MariaDB[(none)]>SETGLOBALgeneral_log='ON';
4. QueryOK,0rowsaffected(0.00sec)
5. MariaDB[(none)]>showdatabases;
6. +--------------------+
7. |Database|
8. +--------------------+
9. |information_schema|
10. |gfan_log|
11. |gfan_pay|
12. |gfanpg|
13. |gfanrc|
14. |mysql|
15. |performance_schema|
16. |ucenter|
17. +--------------------+
18. 8rowsinset(0.02sec)
19. MariaDB[(none)]>usemysql
20. Readingtableinformationforcompletionoftableandcolumnnames
21. Youcanturnoffthisfeaturetogetaquickerstartupwith-A
22. Databasechanged
23. MariaDB[mysql]>showtables;
24. +---------------------------+
25. |Tables_in_mysql|
26. +---------------------------+
27. |columns_priv|
28. |db|
29. |event|
30. |func|
31. |general_log|
32. |help_category|
33. |help_keyword|
34. |help_relation|
35. |help_topic|
36. |host|
37. |ndb_binlog_index|
38. |plugin|
39. |proc|
40. |procs_priv|
41. |proxies_priv|
42. |servers|
43. |slow_log|
44. |tables_priv|
45. |time_zone|
46. |time_zone_leap_second|
47. |time_zone_name|
48. |time_zone_transition|
49. |time_zone_transition_type|
50. |user|
51. +---------------------------+
52. 24rowsinset(0.00sec)
53. MariaDB[mysql]>select*fromgeneral_loglimit10;
54. +----------------------------+---------------------------+-----------+-----------+--------------+-------------------+
55. |event_time|user_host|thread_id|server_id|command_type|argument|
56. +----------------------------+---------------------------+-----------+-----------+--------------+-------------------+
57. |2014-11-1214:29:49.810999|root[root]@localhost[]|1304|0|Query|showdatabases|
58. |2014-11-1214:29:51.951747|root[root]@localhost[]|1304|0|Query|SELECTDATABASE()|
59. |2014-11-1214:29:51.973180|root[root]@localhost[]|1304|0|InitDB|mysql|
60. |2014-11-1214:29:51.975048|root[root]@localhost[]|1304|0|Query|showdatabases|
61. |2014-11-1214:29:51.975689|root[root]@localhost[]|1304|0|Query|showtables|
62. |2014-11-1214:29:51.976347|root[root]@localhost[]|1304|0|FieldList|columns_priv|--phpfensi.com
63. |2014-11-1214:29:51.976800|root[root]@localhost[]|1304|0|FieldList|db|
64. |2014-11-1214:29:51.977431|root[root]@localhost[]|1304|0|FieldList|event|
65. |2014-11-1214:29:51.978052|root[root]@localhost[]|1304|0|FieldList|func|
66. |2014-11-1214:29:51.978224|root[root]@localhost[]|1304|0|FieldList|general_log|
67. +----------------------------+---------------------------+-----------+-----------+--------------+-------------------+
68. 10rowsinset(0.01sec)
69. MariaDB[mysql]>
71. Ifyouwanttooutputtothelogfile:
72. SETGLOBALlog_output=“FILE”;
73. SETGLOBALgeneral_log_file=“/path/to/your/logfile.log”
74. SETGLOBALgeneral_log=‘ON’;
75. RestartMySQLtoapplythechangesifyouedittheconfig,e.g./etc/mysql/my.cnf
76. Now,ifyou’dlikeyoucantail-f/var/log/mysql/mysql.log