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;

6 Responses to MySQL: permissions needed for mysqldump

  1. Tony says:

    Isn’t this wot StackOverflow is for?

  2. oobx says:

    @Tony, you need to run “GRANT SELECT, INSERT, UPDATE ON *.* TO helpful_source@* identified by ‘helpful_info’;” to fix your problem.

  3. oobx says:

    @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.

  4. Kevin says:

    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.

  5. David says:

    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';

  6. Imran-UK says:

    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 Reply

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