Mysqldump without slow query

backuplinuxMySQLmysqldumpperformanceslow-log

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)

Best Answer

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

Related Solutions

MySQLDump wrong dump

Just reading the header you put in the question shows something interesting. In fact, the question shows three things:

MySQL dump 10.13 Distrib 5.1.34, for apple-darwin9.5.0 (i386) indicates you used mysqldump from apple-darwin9.5.0 (i386) binaries
Server version 5.0.51a-24+lenny2 shows the version of mysql you used mysqldump to dump from.
You wanted to load the mysqldump file into Ver 14.14 Distrib 5.1.57, for apple-darwin10.3.0 (i386) using readline 5.1

What a jumble of versions to do this with.

If you want to see if mysqldump has an issue with the line that has DATEDIFF, try dumping just the schema.

mysqldump --no-data --all-databases ... > MySQLSchema.sql

This will display ony the schema. No INSERTs will be in the output. You can then hunt down that lines. You may also want to dump the data onyl without the schema,

mysqldump --no-create-info --all-databases ... > MySQLData.sql

Splitting the dumps allows you to load the schema into an editor and see if there are any problems. If you do not see any problems, load the MySQLSchema.sql into the target server. If the error is reproduced, you can fix the schema file and reload. Once the schema is loaded, you can separate load MySQLData.sql

BTW you should use mysqldump binary whose version is 5.0.51a-24+lenny2. Use dumps from version as mysqld is usually better to port and may minimize problems like this.

Give it a Try !!!

Mysql – Cannot create MySQL database with name ‘5e370227_db’

Interesting problem and I think that I've figured it out.

For some reason, MySQL interprets 1e_a_number_ as an exponent - i.e. 10 to the power of something. 1a_a_number has no mathematical meaning, therefore it's not parsed as being a number and accepted as valid. If you notice at the beginning of my experiments below - pure numbers aren't allowed as database/schema names.

Congratulations - it looks like you've spotted a MySQL bug.

Something like this (underscore following number and e) works.

MariaDB [(none)]> create schema 1_e34343_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>

which appears to confirm my conclusion.

This does also - i.e. 1ex works, but 1e1 fails.

MariaDB [(none)]> create schema 1ex;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create schema 1e1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e1' at line 1
MariaDB [(none)]>

=== Experiments leading me to my conclusion ============

MariaDB [(none)]> create schema 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '5e370227_db' at line 1
MariaDB [(none)]> create database 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '5e370227_db' at line 1
MariaDB [(none)]> create schema  5a370227_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> drop schema  5a370227_db;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> create database  5a370227_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> drop database  5a370227_db;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database 100;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '100' at line 1
MariaDB [(none)]> create database 100_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000000000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1e0000000000000000000000000000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e0000000000000000000000000000000000_db' at line 1
MariaDB [(none)]> create database 1e000000000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e000000000000000_db' at line 1
MariaDB [(none)]> create database 1e0000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e0000000000_db' at line 1
MariaDB [(none)]> create database 1e4_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e4_db' at line 1
MariaDB [(none)]> create database 1e4_zx;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e4_zx' at line 1
MariaDB [(none)]> 
MariaDB [(none)]> create database 1a4_zx;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]>