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?

Solution

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.

Comments

[Will]

I enabled general logging and the log file produced exactly what I was looking for. It was pretty painless. Thank you for your help, and sorry for wasting your time. This was just a RTFM case after all.