Mysql – Identify thesqldump activity in MySQL log file

backupdebuggingMySQLmysqldump

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.

Best Answer

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 !!!

Related Solutions

MySQL – How to Detect a Running mysqldump

Your only hope in this scenario is to have the PROCESS privilege.

The SUPER privilege allows you to kill processes. Naturally, you don't want that. On the other hand, the PROCESS privilege allows you to see the processlist.

According to the MySQL Documentation on the PROCESS Privilege

The PROCESS privilege pertains to display of information about the threads executing within the server (that is, information about the statements being executed by sessions). The privilege enables use of SHOW PROCESSLIST or mysqladmin processlist to see threads belonging to other accounts; you can always see your own threads.

OK, Big Deal. The PROCESS Privilege lets you see the Process List. How does that help?

You can quickly detect a mysqldump in progress when you run SHOW PROCESSLIST; and see a pattern like this in the Info field:

SELECT /*!40001 SQL_NO_CACHE */ * FROM

If you have the PROCESS Privilege and are running MySQL 5.1+, you can run this query:

select COUNT(1) mysqldumpThreads
from information_schema.processlist
where info like 'SELECT /*!40001 SQL_NO_CACHE */%';

Here is a sample output:

mysql> select COUNT(1) mysqldumpThreads
    -> from information_schema.processlist
    -> where info like 'SELECT /*!40001 SQL_NO_CACHE */%';
+------------------+
| mysqldumpThreads |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

All you need to do is query for that number. If you get a nonzero, mysqldump must be running.

Give it a Try !!!

Mysql – generic framework/design pattern/library for real time replication of MySQL database onto other servers

Perhaps I am misreading this, but I am having difficulty understanding how your requirement is any different from built-in MySQL replication.

With MySQL replication, the master database writes changes to a binary log file. The slaves connect to the master, read the binary logs, write them to their local relay log, and play the statements back.

Please understand that realtime copy and backup are not the same thing. A real-time slave can help you recover from certain types of problems, but cannot resolve data-corruption issues (oops, I dropped that table!). For that, you need point-in-time backups. With a backup and a copy of the binary logs, you can recover from almost any type of failure by replaying the binary logs up to the point where the problem occurred.