I wanted to create a user who had just enough permissions to make a SQL dump of my database for backup purposes. I hunted all over the ‘net, but no one told what permissions were needed so by trial and error I found out:
GRANT SELECT, LOCK TABLES ON *.* TO backup_user@localhost IDENTIFIED BY ‘xxx’;
Now I can have a script that runs with my backup tools that executes:
mysqldump –user backup –password=xxx –all-databases –compact | gzip -9 > db_backup.sql.gz
Thanks for the useful post! To automatically put the day’s date in the output file, I add: `/bin/date +\%Y\%m\%d`. This looks like:
/usr/bin/mysqldump –user backup –password=XXX –all-databases –compact | gzip -9 > db_backup.`/bin/date +\%Y\%m\%d`.sql.gz
You may also need some of the SHOW permissions. For mine to work I needed SHOW VIEW. I imagine it would be similar for SP but I don’t use them on this server.
If you have views, then you’ll need to add the “SHOW VIEWS” permission to the backup user.
[…] This guy says to assign the “lock tables” permission too, which makes […]
[…] This guy says to assign the “lock tables” permission too, which makes […]