MySQL: permissions needed for mysqldump

If you want to give a user permissions to back up a MySQL database, what do you do? The MySQL GRANT page didn’t tell me. Searching wasn’t helpful.

Here’s the answer, in the hopes it will help others: SELECT and LOCK TABLES.

mysql> grant select, lock tables on foo.* to bar@localhost indentified by 'ugh';
mysql> flush privileges;

Join the Conversation

6 Comments

  1. @Paul, thanks. I couldn’t remember. BTW, your site was #6 in Google for “mysqldump permissions”. Don’t know where StackOverflow was; but it didn’t make the top 50.

  2. Very helpful, didn’t want to continue using my own admin account in our sh backup script anymore, but for anyone else interested, you should also give a user the “SHOW DATABASES” perm so the user can do all at once.

  3. If you are using the –single-transaction and –master-data options, you need some additional global permissions:

    grant reload, super, replication client on *.* to bar@localhost identified by 'ugh';

  4. For this:

    /usr/bin/mysqldump –opt –master-data=2 –no-data –databases foo –user=backup –password=bar > $BACKUP_DIR/$SCHEMA_FILE

    I needed this:

    GRANT SELECT, RELOAD, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘backup’@’localhost’ IDENTIFIED BY PASSWORD ‘…’

Leave a comment

Your email address will not be published. Required fields are marked *