Couldn't execute 'show events' on mysqldump

Solution 1:

This is a virtual database so you will need to add the option --skip-events to make it work.

Solution 2:

The other two answers will work, but their rationale and explanation are wrong.

The error you're getting is not due to the database being virtual, but due to a bug with the query show events: enter image description here

SHOW EVENTS in the performance_schema database returned an access-denied error, rather than an empty result

The bug has been fixed in MySQL server 5.7.6. Simply update your server and you won't be seeing this error anymore.

In the meantime, if you are stuck with older versions of MySQL server, avoid calling show events by using the solutions provided in the other two answers.


Solution 3:

'performance_schema' is a virtual database so you don't have to backup it
(see http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html )

to skip it in your backup script use it like this:

#!/bin/sh
BAK=/var/backups/mysql/
DATE=$(date +%Y-%m-%d)
MYSQLPATH=/var/lib/mysql/
for i in /var/lib/mysql/*/; do
    if [ $i != "$MYSQLPATH"'performance_schema/' ] ; then 
        dbname=`basename "$i"` 
        mysqldump -u root "$dbname" | gzip > $BAK/"$dbname"-$DATE.sql.gz
    fi
done

# optionally delete old backups of databases that don't exist anymore
# find $BACKUP_DIR -atime +30 -exec rm {} \;

Solution 4:

If you encountered this error with an ordinary database, you need to grant your user the EVENT permission.

Tags:

Mysql