Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

While trying to backup all MySQL databases with mysqldump I get the following error:

Couldn't execute 'show events': Access denied for user 'user'@'localhost' to database 'performance_schema'

share|improve this question
    
related: serverfault.com/questions/268566/… – rubo77 Nov 20 '13 at 11:49
    
@rubo77, Not really, this error is due to a bug, that is not. – Pacerier Feb 4 '15 at 6:57
up vote 4 down vote accepted

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

share|improve this answer
    
The flag --skip-events is not mentioned in the docs and it doesn't seem to work either. – Marcel Burkhard Nov 2 '15 at 8:02
    
This flag is included in the documentation dev.mysql.com/doc/refman/5.7/en/…. – Adrian Ber Nov 2 '15 at 9:59
    
You linked mysqlpump and --exclude-events – Marcel Burkhard Nov 2 '15 at 18:09
    
No! I actually linked --events. Read at the end of that paragraph (or better, entirely). – Adrian Ber Nov 2 '15 at 19:31
1  
It works for mysqldump too: dev.mysql.com/doc/refman/5.7/en/mysqldump-stored-programs.html. Both are used for backup. – Adrian Ber Nov 3 '15 at 8:49

'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 {} \;
share|improve this answer
    
+1 I was actually using this in a similar script, but I haven't specify in my question. Anyway, good sharing. – Adrian Ber Nov 25 '13 at 11:31

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.