Monitoring SQL statements (MySQL)

Recently in the making of a tool site, just look at the structure of the people's car experience, look at their pits, I think our system should have a database monitoring and disaster recovery system.

Online Collection Summary under.

Database design should be small and beautiful, concise, database is only a part of the overall project, such as triggers, stored procedures can be implemented, in the overall project can also be used in the application code to complete. We use MySQL, which is a great place to use it, such as tables, indexes, and transactions, instead of having to use all of its functions. Sub-queries are not allowed in the main library of the production environment prior to MySQL5.6. The performance of the subquery before MySQL5.6 is particularly poor. It is recommended to use the MySQL database after mysql5.6

1. A basic SQL monitoring to see the time-consuming execution of SQL

(1) Open profiling parameter: set profiling=1;
(2) Execute SQL
(3) Obtain information about the profile of multiple query saved in the current system by executing the show PROFILES command. (optional)
(4) Calculate the sum of the previously executed SQL time:

However, the usual SQL connection tool in this way will have hints

(Mysql-front)

2. Turn on MySQL's query log and configure the log path

Location is Mysql.ini

　　

Log=c:/appserv/mysql/mysql.log

All of MySQL's SQL operations will be logged to this log.

Linux can be viewed in real time with the command tail-f/var/log/mysql/mysql.log

Windows does not have the tail command to download the Baretail tool for real-time viewing

Monitoring SQL statements (MySQL)