19 July 2009

Create a specific user for MySQL Database backup

For security reasons, it is interesting to create a specific user for MySQL Database backup with the less privileges possible.
In fact, only LOCK TABLES and SELECT privileges are requested to use mysqldump.


So it is enough to use the following SQL query:
GRANT LOCK TABLES, SELECT ON XXX.* TO 'YYY'@'localhost' IDENTIFIED BY 'ZZZ';
GRANT LOCK TABLES, SELECT ON XXX.* TO 'YYY'@'localhost.localdomain' IDENTIFIED BY 'ZZZ';
FLUSH PRIVILEGES;


With variables:
XXX: the database name
YYY: the specific backup user login
ZZZ: the specific backup user password

The following instruction allows full backup with this limited privileges user:
mysqldump -u "YYY" --password="ZZZ" --all-databases > /path/to/my/dump/file

No comments:

Post a Comment

Thank you for your visit, let's share your point of view: