Page 1 of 1
MySQL backup user
Posted: Mon Aug 04, 2008 7:46 pm
by Peter_w
What rights does a MySQL backup user need?
Re: MySQL backup user
Posted: Tue Aug 05, 2008 3:24 pm
by 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?
Re: MySQL backup user
Posted: Wed Aug 06, 2008 8:21 am
by 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.