2

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)
1
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

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.