0

I noticed a SELECT * FROM query running on a big table from time to time and i wonder if this could be, because of mysqldump. We have mysqldump backups running for some tables. Does somebody know what kind of sql statements mysqldump is executing during operation? Is it actually executing SELECT * FROM statements? Guidance on this would be appreciated thank you.

5
  • How else do you suppose it extracts data from tables if not by running queries against them?
    – mustaccio
    Nov 9, 2023 at 21:53
  • @mustaccio Having said that, grown up backup systems do use better ways of reading data, such as going direct to the file pages. But MySQL..... Nov 10, 2023 at 0:26
  • @Charlieface Are you familiar with MySQL Enterprise Backup or Percona XtraBackup? Nov 10, 2023 at 14:56
  • @BillKarwin Somewhat. But I was referring to the more standard mysqldump as noted in OP. Nov 11, 2023 at 18:39
  • Well, there are both logical backup tools like mysqldump and the MySQL shell dump and load tool, and physical backup tools like the ones I mentioned. Both have their uses. I'm just trying to point out that you shouldn't assume such tools don't exist for MySQL. Nov 11, 2023 at 18:42

2 Answers 2

1

Yes, mysqldump runs long-running queries like SELECT * FROM <table> WHERE .... Mysqldump is single-threaded, so it will do one table at a time.

You could create a distinct MySQL user to run mysqldump. That would allow you to view which queries it is running with SHOW PROCESSLIST.

Mysqldump also runs a few types of SHOW commands and queries against information_schema tables to gather metadata, but these should be brief.

If you're interested in the concrete details, the code for mysqldump is in client/mysqldump.cc in the MySQL source.

1

what kind of sql statements mysqldump is executing during operation? Is it actually executing SELECT * FROM statements? Guidance on this would be appreciated

  • Create separate user account temporarily.
  • Enable General Log.
  • Perform database backup using above account.
  • Disable General Log. Drop temporary user acccount.

Investigate obtained General Log. You will see complete statements list which was performed by mysqldump during the database backup.

I recommend you to create separate database temporarily, create all kind of objects in it (2-3 tables with 2-3 rows each, a view, a trigger, stored procedure and function, and so on), and backup this database. Tiny database backup won't decrease your server performance during your experiment. Rather than General Log enabled, so the experiment must be as fast/short as possible.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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