Is it possible to see what sql commands are being run on my CentOS machine?

Question

I bought a program that runs on my CentOS machine. It's written in PHP. It uses a MYSQL database.

One of the functions of the program is to register new users. It uses a SQL query to add the users. I want to write a python program that can also add users to this database, but I dont know what properties and tables it adds (it's quite a large database).

Is it possible to somehow monitor what sql queries this program is sending to MYSQL?

Answer 1

There are two ways you can do it, simpler (and maybe not good enough) and more complicated way which will give you what you are looking for 100%.

1. Simpler is to just look for queries just in time as they are executing with SHOW FULL PROCESSLIST command. That will show you queries, but if action (SQL query you are looking for) is rarely executed you will have to wait a long time and issue above command often to get result you want. There is also a better way.

2. Start MySQL with logging enabled, and then you will be able to see all SQL queries from restart of MySQL server. Here problem is that log file can be really big and it can be time consuming for inexperienced to find what you are looking for, but this approach is generally better. Option is simple:

mysqld --log=name_of_log_file.log

It's that simple.

Just one warning: when you find what you were looking for, restart MySQL but this time with logging disabled, as that will speed up database and will not take up disk space on your server.

Answer 2

To see which MySQL Queries being executed in any platform having mysql.

MySQL query is as below,

• SHOW FULL PROCESSLIST for detailed information.
• SHOW PROCESSLIST for currently being processed queries.

Open # mysql in terminal.

mysql> SHOW FULL PROCESSLIST;

mysql> SHOW PROCESSLIST;

for further documentation, have a look at show-processlist.