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.
2 Answers
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.
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.
mysqldumpas noted in OP.