The topic “How to see MySQL log by phpMyAdmin” came up on TODOS・何でも情報交換(Japanese). I checked out this and am going to write what I understood about this.
If you use phpMyAdmin 4.0(maybe) or later, you can audit a status of MySQL servers by phpMyAdmin Monitor. When you log in your phpMyAdmin and go to Status > Monitor, you can reach the graphs. If your server works normal, I think the graphs are enough for you. However, if you need more information, you can refer a slow_query_log or a general_log there.
When you click “Instructions/Setup” at the Monitor page and have the following messages(Also see Fig.02), you have to set xxx_log to ‘ON’ and log_output to ‘TABLE’.
slow_query_log and general_log are disabled.
log_output is not set to TABLE.
If you have enough privileges as a MySQL user, you can set the values by yourself. If not, you have to ask your system administrator. In most case only root users can set the values, if not such server circumstances are very dangerous, I bet.
Your Server works, then I think you don’t need the general_log of your server but the slow_query_log. If you set them by yourself, you need to log in your MySQL as a root at any rate. If you can log in it as a root…
To use command line interface:
SET GLOBAL slow_query_log = ON;
SET GLOBAL log_output= TABLE;
To use phpMyAdmin:
1.Select Variables on Menu with no database.
Input “slow query log” to the filter form and edit the value to ‘ON’
Save
2.Input “log output” to the filter form and edit the value to ‘TABLE’
Save
Log out as the root.
Now you can use a “slow_query_log” from the Monitor of phpMyAdmin, when you log in as a root.
By the way, when you restart mysqld, these settings have gone. If you want to stay the settings eternal, you should add the following lines to my.ini/my.cnf’s [mysqld] area.
slow_query_log = ON
log_output = TABLE
As above, you can use a “slow_query_log” from the Monitor of phpMyAdmin as a root. However, this is less convenient. Don’t you think you’ll safely use it by a WordPress MySQL account? I think so, too. I asked くりくりさん on TODOS・何でも情報交換 “What privileges should I give a normal user who can use this feature?” He told me “It’s OK only for the database”.
The slow_log table exists on the mysql database. So I did the following command.
GRANT SELECT (lock_time, start_time, rows_examined, db, rows_sent, query_time, sql_text, user_host) ON mysql.slow_log TO ‘WP-user’@’localhost’;
This is very limited privileges, so I think it’s acceptable.
At this time, my WordPress MySQL account has the following privileges.
————
GRANT USAGE ON *.* TO ‘WP-user’@’localhost’ IDENTIFIED BY ‘passphrase’;
GRANT ALL PRIVILEGES ON WPdatabase.* TO ‘WP-user’@’localhost’;
GRANT SELECT (lock_time, start_time, rows_examined, db, rows_sent, query_time, sql_text, user_host) ON mysql.slow_log TO ‘WP-user’@’localhost’;
————