MySQL backup user

[Peter_w]

What rights does a MySQL backup user need?

[Peter_w]

I thought it only needed select.

Code: Select all

grant select on database.* to 'backup'@'localhost' identified by 'password';

But when I try to do a dump I get the following message:

Code: Select all

$ mysqldump -u backup --password="password" database > backupfile.sql
mysqldump: Got error: 1044: Access denied for user 'backup'@'localhost' to database 'database' when using LOCK TABLES

Does anyone know what more rights I need to give the backup user?

[Chris]

Select is enough to do a backup.
But the error message you get is a bug/feature.
Add --skip-lock-tables and it should work.

Code: Select all

$ mysqldump -u backup --password="password" --skip-lock-tables database > backupfile.sql

locking of tables, which is recommended to do a
inclusive dump of all data in the databases. The problem with locks is
that writes are suspended during this time, so it could cause some
application related issues, which is usually not worth the trade-off.