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:
copyraw
Or via cPanel the following screenshot when adding a user to a database:GRANT SELECT, LOCK TABLES, EVENT, INDEX, REFERENCES, TRIGGER, SHOW VIEW ON 'my_live_db'.* TO 'mybackupuser'@'localhost' IDENTIFIED BY 'mybackupuser';
- GRANT SELECT, LOCK TABLES, EVENT, INDEX, REFERENCES, TRIGGER, SHOW VIEW ON 'my_live_db'.* TO 'mybackupuser'@'localhost' IDENTIFIED BY 'mybackupuser';
Method #1:
The following command on a cron job:
copyraw
The one to rule them all:
// 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_password
- // 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_password
copyraw
GRANT SELECT, LOCK TABLES, RELOAD, PROCESS, TRIGGER, SUPER, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'mybackupuser'@'localhost' IDENTIFIED BY 'mybackupuser';
- GRANT SELECT, LOCK TABLES, RELOAD, PROCESS, TRIGGER, SUPER, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'mybackupuser'@'localhost' IDENTIFIED BY 'mybackupuser';
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.
Category: cPanel :: Article: 672
