A quick note as I had difficulty finding a clear answer on this. I need a cron job setup where the permissions for a database user is required to do a backup (reading and copying data from a LIVE database) to a copy of the database (inaccessible via other users).
How?
I want to list 2 methods here. One to create a .sql file of the backup but another to copy the contents to a copy of the database accessible only by root and and this database "backuper" user. For both methods, I grant the following to the user doing the backup on the database I want a backup of:
GRANT SELECT, LOCK TABLES, EVENT, INDEX, REFERENCES, TRIGGER, SHOW VIEW ON 'my_live_db'.* TO 'mybackupuser'@'localhost' IDENTIFIED BY 'mybackupuser';Or via cPanel the following screenshot when adding a user to a database:
Method #1:
The following command on a cron job:
// not recommended: /usr/bin/mysqldump -u dbusername -p'dbpassword' dbname > /path_to/backup.sql // recommended: using credentials stored in external file: mysqldump --defaults-file=/path-to-file/.my.cnf my_db > /path_to/backup.sql // and in your cnf file: [mysqldump] user=my_user password=my_passwordThe one to rule them all:
// everything: using external file, suffixing date and compressing: mysqldump --defaults-file=/full-path-to/.my.cnf my_db | gzip > /full-path-to/backups/my_db/backup_`date +"\%Y-\%m-\%d"`.sql.gz
Additional
Others around the web have suggested the following but I don't have these options to grant and would have to grant them via a terminal/command line to MySQL.
GRANT SELECT, LOCK TABLES, RELOAD, PROCESS, TRIGGER, SUPER, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'mybackupuser'@'localhost' IDENTIFIED BY 'mybackupuser';
Suggestions?
I'm all up for any suggestions people might have on how this can be improved but it's the one I'm going on for the moment which works fine.
Discussion
Comments
Questions, corrections and useful additions are reviewed before appearing here.
No comments yet. Start the discussion.