1

I have a database that is being regularly backed up by mysqldump by... something. It's not a script that I wrote, not sure whose it is. I'm trying to find out more about these dumps, such as what database user they are being executed as. Is there a way to identify an execution of mysqldump in the regular mysql logs?

The database being backed up is hosted on a mysql server, version 5.5.49.

I can't provide the options used to call mysqldump because mysqldump is not called from any script of mine. I am trying to find the script (it does not appear to be in any normal cron job - I think it is someone's custom script), and I think that if I can determine what user it runs as, I can probably find the person who wrote it.

Improve this question
  • 1
    The exact signature of the mysqldump will depend on what version of MySQL you are using, and what options are passed in when it is invoked. The ease with which you can pick it out of the surrounding statements will depend on the nature of the surrounding statements. Can you provide a bit more of those details? A general pattern of statements executed by mysqldump would be -- connect, executable comments for compatibility, SELECT from INFORMATION_SCHEMA, Init DB, LOCK TABLES, SELECTs and SETs, UNLOCK TABLES, quit. – WAF Jan 25 '17 at 20:28
  • @WAF: version 5.5.49. I don't know options mysqldump is called with - I don't have access to the script that does this. I'm trying to find it. – FrustratedWithFormsDesigner Jan 25 '17 at 20:43
1

SUGGESTION #1

The quickest way to locate any dump activity is the enable the slow query log

You should find queries that resemble something like the following

SELECT /* SQL_NO_CACHE */ from ...

Any large table running a long dump has to show up in the slow log.

If all the tables are small, then lower long_query_time with

SET GLOBAL long_query_time = 0.2;

Let it run for a few hours. Then change it back to 10.

SET GLOBAL long_query_time = 10;

If you don't change it back, the slow log will grow big very quickly.

SUGGESTION #2

Another way would be to check the process list. Please see my 5-year-old post (How to detect a running mysqldump?)

In your case, just run the following query

SELECT * FROM information_schema.processlist
WHERE info like 'SELECT%'
AND   info like '%SQL_NO_CACHE%';

This query will tell you the Process ID, MySQL username, and host doing the mysqldump.

GIVE IT A TRY !!!

Improve this answer
  • This will only work if the dump is currently in progress, correct? Right now it shows nothing, but I suppose I could write another script to run at the suspected time and check the output of that. – FrustratedWithFormsDesigner Jan 25 '17 at 20:54
  • The first thing I mentioned was checking the slow log. Any large table running a long dump has to show up in the slow log. – RolandoMySQLDBA Jan 25 '17 at 20:55
  • slow log is not enabled, so I can't check it now. I'll try to enable it, then see the results tomorrow. – FrustratedWithFormsDesigner Jan 25 '17 at 20:58
  • Re "I suppose I could write another script to run at the suspected time", if you have the dumped file the last line should contain "dump completed on..." or the equivalent. – WAF Jan 25 '17 at 21:05
  • 1
    @WAF dump completed on... shows up in a dump file not in the process list and not in the slow log. The idea here is to catch the SELECT from a mysqldump in the act. – RolandoMySQLDBA Jan 25 '17 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.