Problem : We don’t want to give plain password in mysql_dump command so no one can see password while taking backup

step 1 : Create Backup user (icm_backup) and give required access to user.

Create user 'icm_backup'@'localhost' IDENTIFIED BY 'backup@31';
GRANT LOCK TABLES, SELECT ON *.* TO 'icm_backup'@'localhost' IDENTIFIED BY 'backup@31';

Above user icm_backup has select and lock tables rights on all databases which is required to take backup

step 2 : To avoid giving password on command line create .my.cnf

• Start your terminal and login to your server
• Create .my.cnf file in the home directory

vi ~/.my.cnf
--------COPY BELOW TEXT INTO .my.cnf FILE

Step 3: Copy the content below and replace with your mysql username and password.

[mysqldump]
user = icm_backup
password = xyz@123

Step 4: Now change the file permission to 600 to prevent other user from reading it

chmod 600 ~/.my.cnf

Step 5: Write cron job

crontab -e
59 23 * * * mysqldump -u icm_backup -h localhost --all-databases  > /location/alldb.sql

Above command will take backup of all database from icm_backup and take all databases backup and store in /location/