How Can I Monitor Executed Queries Across Multiple MySQL Servers?

Viewing Executed Queries on MySQL Servers

Monitoring queries executed across multiple MySQL servers can be valuable for troubleshooting, performance analysis, and security auditing. Here are the methods to capture and access these queries:

Global Logging for Recent Queries (MySQL >= 5.1.12):

1. Set the log_output variable globally to "TABLE":

   SET GLOBAL log_output = 'TABLE';

   Copy after login

2. Turn on general logging:

   SET GLOBAL general_log = 'ON';

   Copy after login

   Copy after login
3. Examine the mysql.general_log table, which records the executed queries.

File-Based Logging for Past Queries:

If you prefer to store logs in a file:

1. Set log_output to "FILE":

   SET GLOBAL log_output = "FILE";

   Copy after login

2. Specify the destination log file path in general_log_file:

   SET GLOBAL general_log_file = "/path/to/your/logfile.log";

   Copy after login

3. Enable general logging:

   SET GLOBAL general_log = 'ON';

   Copy after login

   Copy after login

Benefits of Runtime Configuration:

Using this method to activate logging has several advantages over modifying configuration files:

• It allows temporary logging without changing permanent settings.
• It eliminates the need to search for the log file location.
• It avoids server restarts, which interrupts connections and requires manual re-enabling of logging.

Additional Information:

For further details, refer to the MySQL 5.1 Reference Manual on Server System Variables:
https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_general_log

The above is the detailed content of How Can I Monitor Executed Queries Across Multiple MySQL Servers?. For more information, please follow other related articles on the PHP Chinese website!