Set up a MySQL user dedicated to backups

It is not a good idea to use the root MySQL account to do backups.

So, let’s create a user dedicated exclusively to doing backups.

Log on to MySQL as root:

mysql -u root -p

Then create the new user and grant it the necessary permissions:

CREATE USER 'backup_user_name'@'localhost' IDENTIFIED BY 'my_pass';
GRANT SELECT, SHOW VIEW, RELOAD, EVENT, TRIGGER, LOCK TABLES ON *.* TO 'backup_user_name'@'localhost';

Now that we are all set up, you can do backups with that new dedicated user:

mysqldump -u backup_user_name -pmy_pass db_name | gzip > /home/ddarazha/backups/ninelets/ninelets_`date '+%Y%m%d'`.sql.gz