Mysqldump without slow query

Question

Situation:

I currently monitor an instance of MySQL for slow queries. We have a mysql user dedicated to backups which performs a mysqldump, but the mysqldump gets logged as a slow query.

Question:

How can I set the system up to prevent the mysqldump from ever being being seen as slow query?

Restrictions given by security team:

• The backup user can only have select
• The backup can run anywhere from once a day to hourly and the process will be applied to varying sizes of databases (so I can't just speed up the process)
• Only the backup user can be used

Environment:

• OS: Ubuntu 14.04
• MySQL: Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.3
• MySQLdump: Ver 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64)

Answer 1

The easiest way would be to change the global setting for long_query_time to a ridiculously high number, run the mysqldump, and change long_query_time back to its original value.

Perhaps something like this

MYSQL_USER=backupuser
MYSQL_PASS=backupuserpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Record the long_query_time value
#
SQL="SELECT variable_value FROM information_schema.global_variables"
SQL="${SQL} WHERE variable_name='long_query_time'"
ORIGINAL_LONG_QUERY_TIME=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
#
# Change long_query_time to 1 year 
#
SQL="SET GLOBAL long_query_time = 31536000"
mysql ${MYSQL_CONN} -ANe"${SQL}
#
# Perform mysqldump
#
mysqldump ${MYSQL_CONN} ...
#
# Change long_query_time to original value
#
SQL="SET GLOBAL long_query_time = ${ORIGINAL_LONG_QUERY_TIME}"
mysql ${MYSQL_CONN} -ANe"${SQL}

or disable the slow query log

MYSQL_USER=backupuser
MYSQL_PASS=backupuserpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Disble Slow Query Log
#
SQL="SET GLOBAL slow_query_log = 'OFF'"
mysql ${MYSQL_CONN} -ANe"${SQL}"
#
# Perform mysqldump
#
mysqldump ${MYSQL_CONN} ...
#
# Enable Slow Query Log
#
SQL="SET GLOBAL slow_query_log = 'ON'"
mysql ${MYSQL_CONN} -ANe"${SQL}"

Give it a Try !!!

CAVEAT #1

Your DBA must give the SUPER privilege to backupuser so that backupuser can globally change the long_query_time setting. Perhaps a DBA can have a cronjob give away SUPER privilege to backupuser just before the mysqldump and revoke SUPER privilege when the backup is done.

CAVEAT #2

Unfortunately, any long running queries that occur during the mysqldump might not be recorded